OraclePLSQL规范、性能.ppt

日程,PLSQL 性能规范,PLSQL 程序规范,PLSQL程序规范概述,PLSQL程序规范的目的：,1.增强程序可读性。,2.降低程序BUG概率。,3.程序一致性。,4.增强程序可维护性。,5.提升程序性能。,PLSQL程序规范：命名规范,程序包命名：必须以模块名开头（例如：客户化程序用CUX_开头），用_PKG结尾 示例：CUX_PAYROLL_PUBLIC_PKG(含义：客户化的薪资部分公用包)函数命名：一般用GET_开头，用来描述通过函数可以得到一个返回值 示例：GET_EMPLOYEE_SALARY(含义：获取员工薪酬值)3.过程命名：一般以体现具体用途的英语单词或者缩写来命名，没有其他特殊要求 示例：UPDATE_EMPLOYEE_DEPT_INFO（含义：更新员工部门信息）别名：一般别名尽量使用表名的含义缩写或者用表被_符号分隔的第一字母的拼合作为别名 示例：PS_EMPLOYEE_PAYMENT_HISTORY PEPH PS_EMPLOYEE_PAYMENT_HISTORY PAY_HIS4.参数命名：函数的参数命名以P_开头，例如：P_EMPLOYEE_ID NUMBER 过程的参数命名中，输入参数以P_开头，例如：P_EMPLOYEE_ID IN NUMBER 输出参数以X_开头，例如：X_RESULT OUT VARCHAR2 输入/输出参数以P_开头，例如：P_EMPLOYEE_STATUS,好的命名规范，可以明显的增强程序的可读性和可维护性。PLSQL程序，无论是程序名称还是变量名称，总体遵循简单明了的基本原则，使得程序名或者变量名称具有相关的含义。,PLSQL程序规范：命名规范,5.变量命名:不同类型的变量，遵循不同的命名规则,PLSQL程序规范：大小写规范,使用统一标准的大小写规范，可以明显的增强程序的可读性、以及程序的美观程度。注：1.使用PL/SQL DEVELOPER工具，可以设置自动大小写功能 2.原则上SQL代码中保持一致的风格就可以，不强求一定大写，或者一定小写，尽量不要出现非数据库保留字之外的代码不统一的情况。,PLSQL程序规范：代码对齐规范,使用良好的对齐规范，可以明显的增强程序的可读性、以及程序的美观程度，同时可以减少程序出错的概率，增强可维护性。总体来说，对齐要基于程序层次情况，使用首尾对齐原则，如下例所示：,FUNCTION GET_EMPLOYEE_SALARY(P_EMPLOYEE_ID NUMBER,P_MONTH NUMBER)RETURN NUMVER IS V_SALARY NUMBER;CURSOR CUR_EMPLOYEE_PAYROLL_ELEMENT IS SELECT payroll_element_code,base_salary FROM ps_payroll_elements ppe WHERE ppe.EMPLOYEE_id=p_EMPLOYEE_id AND ppe.payment_month=p_month;BEGIN V_SALARY:=0;FOR C1 IN CUR_EMPLOYEE_PAYMENT_ELEMENT LOOP IF C1.base_salary is not null then V_SALARY:=V_SALARY+C1.base_salary;END IF;END LOOP;END;RETURN V_SALARY_AMOUNT;END GET_EMPLOYEE_SALARY;,PLSQL程序规范：注释信息规范,程序代码注释规范：2.1 单行代码禁用，可以用PLSQL屏蔽符：-2.2 多行，大范围代码禁用，必须使用PLSQL大范围屏蔽符:/*需要屏蔽的内容*/程序关键点注释，程序的一些关键点的注释可以很清晰的分出程序的结构，增强程序的逻辑性和可读性。例如：IF V_RESULT=S THEN-如果运行成功，则.ELSIF V_RESULT=E THEN-如果运行失败，则.ELSIF V_RESULT=W THEN-如果运行警告，则.END IF;,注释信息可以很好的描述程序的功能，为后续的维护带来非常有价值的基础信息，原则要求程序包各个部分都必须有相应的注释信息,PLSQL程序规范：数据库对象创建规范,程序开发中，不可避免的会遇到创建客户化的数据库对象，为更好的管理数据库对象，需要遵循数据库对象创建规范：,数据库对象包括：Table,Index,View,Package,Procedure,Function,Trigger,Job2.尽可能少的创建数据库对象。除非程序必须使用或可以提升程序性能的情况，一般都不要去创建数据库对象。数据库对象数据库对象如果需要占用存储空间的（Table,Index,物化视图），都必须创建到规定的表空间中。不能随意存放。所有数据库对象的创建/变更，都需要有明确的文件记录。所有应用系统自创建的标准的数据库对象，都尽可能的不去做变动，以免影响性能或者导致未知问题。6.数据库对象的命名要规范。,PLSQL程序规范：程序管理规范,客户化开发的PLSQL程序，需要规范的管理，以更好的维护数据库以及程序,1.客户化程序需要有文件记录 客户化procedure,function必须放在程序包中，不要单独存在数据库中 客户化的公用的procedure,function尽量放在公用程序包中，不要建太多 的程序包。不能随意创建客户化的PLSQL程序，能少创建就尽量少创建。5.定期检查客户化程序包的状态，保持程序包都是正常的可用状态。,日程,PLSQL 性能规范,PLSQL 程序规范,PLSQL性能规范-性能影响分析,Oracle数据库的性能与PLSQL代码的性能有非常直接的关系。在大量的客户化程序中，不可避免的会存在客户化代码的性能问题。PLSQL代码的性能影响表现在如下几个方面：,程序本身运行效率低下，由于本身的程序性能问题，导致运行时间长。程序运行导致的数据库读写I/O巨大，直接导致数据库的性能下降。程序长时间运行，占有大片内存不释放，导致其他程序运行性能下降。程序长时间运行，占有特点表或者特点数据的控制权，导致其他程序无法有效获得数据控制权，而使得其他程序长时间处于等待状态。程序本身性能差，长时间运行导致回滚段过旧错误。6.程序大量排序操作，且长时间运行不释放资源，导致临时表空间无法正常释放空间给其他程序进行排序，进而导致临时表空间大量增长。,PLSQL性能规范-性能规范概述,PLSQL代码性能的好坏，与下列因素有着直接关系,SQL程序开发的理念 程序开发过程中，始终要保持一个良好的程序员态度，坚持用做到最好，最完美的态度来完成程序的开发。在每一段SQL定性之前，都要对性能进行分析，优化。让程序达到一个最佳性能状态。2.SQL程序的结构设计 在程序开发之前，一定要先做好程序结构的设计，不要盲目的进行开发，从而导致代码臃肿不合理的情况发生。在经过程序结构设计之后，程序开发就会有很清晰明确的设计蓝图，在最佳的路径里完成的代码一般都能有比较好的性能基础。3.SQL代码语句的规范 程序开发过程中，养成一个良好的性能关注习惯，每一个SQL语句都多考虑性能情况。经过长期的习惯养成之后，写出的SQL代码自然而然的就是一个性能比较好的结果。,PLSQL性能规范-开发理念,程序组成部分以及所占比例：,PLSQL性能规范-程序结构,良好的程序结构，可以是程序有一个好的性能基础，另外也能让后续的变更更加灵活。基本的结构概念如下：,小步快跑原则。尽量不要使用一个单一的超级大超级复杂的SQL代码去实现复杂的功能。结构简单清晰。尽量是程序的结构看起来易懂，清晰，简单。不要设计只有自己能看懂的程序。变量定义不要过多，不要太多重复，尽量使用可以重用的变量(绑定变量可以提高性能)。程序结构严谨，过程有输入参数就需要有输出参数返回状态，有正确完善的异常处理机制。增加程序的重用性，对于使用次数多的Procedure,Function，尽量做到能够共享，且有好的运行性能。对于大批量数据处理的程序，良好的COMMIT/ROLLBACK机制。对于取出的大批量数据，尽量保存在内存中，不要多次重复读取大批量数据。适当使用临时表作为数据中转池。,PLSQL性能规范-代码语句规范,良好的代码语句规范，可以尽可能的减少对数据表的全表扫描，尽可能好的使用索引，从而使用最优的执行计划。执行计划的好坏直接影响SQL的执行性能。,在不同的数据库优化器情况下，SQL优化的方法有很多不同的地方。在Oracle数据库10g之前的版本中（7-9i)主要使用RBO优化器（基于规则的优化器）。在Oracle数据库10g之后的版本，Oracle数据库基本放弃RBO优化器，而全面使用CBO优化器（基于成本的优化器）。CBO优化器的推广及使用，使得程序员的代码水平对程序性能的影响降到最低水平，数据库本身基于成本的优化器机制，会自动判断访问的数据表的访问成本，从而得出Oracle数据库本身认为最优的执行计划。目前10g之后版本的数据库已经成为主流数据库平台，9i以及之前版本的数据库用户已经很少或者已经升级到了10g和之后版本。因此后续我们所讲的SQL性能优化方面的技巧都是基于CBO优化器。而基于RBO的优化器的优化技巧不做讲述。RBO:Rule-Based Optimizer CBO:Cost-Based Optimizer,PLSQL性能规范-代码语句规范 子查询,在SQL中经常会有机会使用子查询的方式来得到某一个值，例如汇总值。而在HCM系统中，会非常多的用到max(*_date)这样的方式来获取一个有效的日期。,尽可能的不使用子查询的机制。如果必须使用子查询，也尽可能不要在子查询中嵌套更多层次的子查询。如果可以替代，尽量使用Function的计算来得到想要的返回值(通过变量的使用而 使得SQL的重用性增强，不需要再次解析从而提升性能)。,PLSQL性能规范-代码语句规范 建立索引规范,索引出现的目的就是要提升性能，而SQL性能提升的最快最有效的途径就是经可能的使用索引。下面给出了数据库表适合建立Index的字段的规范：,Index应该建立在可以细分数据的字段Index应该多建立在 Number(ID),Date，以及具有较少字符长度的char类型字段上(例如类型)Index尽量避免建立在存在大量汉字的字段上Index尽量避免建立在存在许多空值的字段上Index创建需要综合考虑，不要轻易在标准的表或者不了解的表上面建立自己的indexIndex的创建要有一个统一的管理机制，严格避免Index多而杂的为不同程序一次性使用,PLSQL性能规范-代码语句规范 使用索引规范,对于已经存在Index的表，如果程序语句写法不对，会使得可以使用的Index无法使用，具体如下：,在Where条件中不要对Index字段进行类型转换处理，例如：to_char(employee_number)=12345,这样的写法会让Index无效。如果一定需要做转换，应该转换为：employee_number=to_number(12345)类似的转换还有：to_number,to_date,trunc在Where条件中，不要对Index字段进行数据附加操作，例如：where action_date+1=to_date(2009-01-01,yyyy-mm-dd)而应该修改为：where action_date=to_date(2009-01-01,yyyy-mm-dd)-1在where条件中，不要对Index字段进行!=操作（不等于操作），因为Index只记录了有什么，而没有记录没有什么，例如：where salary_amount!=0 应该修改为：where salary_amount0在where条件中，不要对两个index字段做拼合操作，例如：where employee_id|employee_no=123|C001 应该修改为：where employee_id=123 and employee_no=C001,PLSQL性能规范-代码语句规范 使用索引规范,如果出现SQL会使用到多个INDEX，而被选择的INDEX不是最优选择的时候，可以使用强制禁用不需要的INDEX的办法来迫使系统选择你希望的INDEX例如：,Select*from ps_employee pe where pe.action_date sysdate-365 and pe.dept_id=111;如果发现使用action_date这个index的性能没有dept_id这个index好，那么可以修改成 select*from ps_employee pe where pe.action_date+1 sysdate 366 and pe.dept_id=111 通过强制屏蔽action_date这个index被使用，就可以迫使系统选择dept_id作为index来运作。,PLSQL性能规范-代码语句规范 in/not in exists/not exists使用规范,在SQL中，会用的IN/NOT IN的操作，用来判断相关数据是否存在于其他表中，需要遵循规则如下：,除了where employee_name in(abc,cde)这样的指明的常量（或者传入参数的变量）以为，都不能使用in的操作，而应该使用exists来代替in。例如：where employee_id in(seleet employee_id from ps_payment_history pph where pph.employee_id=emp.employee_id and pph.payment_month=200912);应该修改为：where exists(selct pph.employee_id from ps_payment_history pph where pph.employee_id=emp.employee_id and pph.payment_month=200912);2.同样的原理，使用not exists代替not in的操作,PLSQL性能规范-代码语句规范 Group by使用规范,在SQL中，会用的GROUP BY的操作，用来取得汇总的数据，GROUP BY操作会产生数据库的排序操作，而排序的数据量的大小也直接影响了运行的性能。GROUP BY需要遵循如下规范：,尽量少的使用GROUP BY操作（如果有函数可以替代的话）。尽量少的使用GROUP BY的字段。在GROUP BY之前的where条件中，尽可能的先缩小数据范围。尽可能的使用索引字段作为GROUP BY 字段。,PLSQL性能规范-代码语句规范 distinct使用规范,在SQL中，会用的distinct的操作，用来取得不重复的数据，distinct操作会产生数据库的排序操作，排序操作会影响SQL性能。Distinct操作的使用规范如下：,尽可能少的使用distinct操作。尽量通过程序的条件本身来排除相同数据的出现。在SQL中可以使用exists来代替where/and条件，从而排除重复数据的出现。,PLSQL性能规范-代码语句规范 减少都表的访问次数,在SQL中，对表的访问次数越多，带来的性能成本越高，产生的磁盘IO越大如下例：,低效:UPDATE EMP SET EMP_CAT=(SELECT MAX(CATEGORY)FROM EMP_CATEGORIES),SAL_RANGE=(SELECT MAX(SAL_RANGE)FROM EMP_CATEGORIES)WHERE EMP_DEPT=0020;高效:UPDATE EMP SET(EMP_CAT,SAL_RANGE)=(SELECT MAX(CATEGORY),MAX(SAL_RANGE)FROM EMP_CATEGORIES)WHERE EMP_DEPT=0020;,PLSQL性能规范-代码语句规范*使用规范,在SQL中，有些程序员喜欢用select*，count(*)这样的操作，实际上这样的操作要浪费很多性能，带来不必要的磁盘I/O，相关规则如下：,坚决杜绝使用select/count*from table 的操作。用select/count index字段 from table 的操作,PLSQL性能规范-代码语句规范 ROWID,在数据库中，每一个表的每一行记录都有一个整个数据库都唯一的ROWID值，而且这个ROWID值正常是不会发生变化（除非表重建后导入原有数据），而Index也都是会对应到这个ROWID，因此在可以的情况下，尽量使用ROWID来直接定位到记录进行操作，例如：,Cursor cur_employee is select rowid from ps_employee emp where emp.status=INVALID;Begin for c1 in cur_employee loop update ps_employee pe set pe.active_flag=N where pe.rowid=c1.rowid;end loop;commit;End;,PLSQL性能规范-代码语句规范 Having的使用规范,在SQL中，会需要使用having来做是否判断，但应该避免使用HAVING子句,HAVING 只会在检索出所有记录之后才对结果集进行过滤.这个处理需要排序,总计等操作.如果能通过WHERE子句限制记录的数目,那就能减少这方面的开销相关规则如下：,Having的语句只应用在需要判断count汇总的值是否满足条件。不能用来代替where条件作为判断，例如：低效:SELECT REGION，AVG(LOG_SIZE)FROM LOCATION GROUP BY REGION HAVING REGION REGION!=SYDNEY AND REGION!=PERTH高效 SELECT REGION，AVG(LOG_SIZE)FROM LOCATION WHERE REGION REGION!=SYDNEY AND REGION!=PERTH GROUP BY REGION,PLSQL性能规范-代码语句规范 truncate/delete,对数据库表数据进行删除操作，如果没有任何条件的全部删除，使用delete的话，数据库会把被删除的数据库放入rollback segment中，这样即占用Rollback空间，也降低了性能。而Truncate table的操作，不需要对数据进行丢失保护，从而具有更好的性能，因此：,在可以删除全部表数据的情况下，一定使用truncate 来代替delete如果这种操作在一个大的流程中，需要确保其他的流程处理都正常结束之后再做这个操作，因为truncate是不需要commit的一种操作，同样也无法rollback；因此要把握好使用的时机。,PLSQL性能规范-代码语句规范 尽可能的共享SQL语句,SQL对数据库的访问，系统会把SQL代码缓存在内存（shared buffer pool），如果运行的SQL能在shared buffer pool找到一样的代码，那么这个SQL就不需要再次解析的过程，从而执行性能得到提升，注意规则如下,尽可能的使用频繁使用的公用的函数，来代替私有的特殊的写法但相同功能的代码。代码中使用绑定的变量的方式，来保持SQL的一致性，例如：第一组的两个SQL语句是相同的(可以共享),而第二组中的两个语句是不同的(即使在运行时,赋于不同的绑定变量相同的值)1.select pin,name from people where pin=:blk1.pin;select pin,name from people where pin=:blk1.pin;2.select pin,name from people where pin=:blk1.ot_ind;select pin,name from people where pin=:blk1.ov_ind;,