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_HIS 4. 参数命名：函数的参数命名以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,Job 2. 尽可能少的创建数据库对象。除非程序必须使用或可以提升程序性能的情况，一般都不要去创建数据库对象。数据库对象 数据库对象如果需要占用存储空间的（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创建需要综合考虑，不要轻易在标准的表或者不了解的表上面建立自己的index Index的创建要有一个统一的管理机制，严格避免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