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 HISTORYPEPHPS EMPLOYEE PAYMENT HISTORYPAY HIS4 参数命名 函数的参数命名以P 开头 例如 P EMPLOYEE IDNUMBER过程的参数命名中 输入参数以P 开头 例如 P EMPLOYEE IDINNUMBER输出参数以X 开头 例如 X RESULTOUTVARCHAR2输入 输出参数以P 开头 例如 P EMPLOYEE STATUS 好的命名规范 可以明显的增强程序的可读性和可维护性 PLSQL程序 无论是程序名称还是变量名称 总体遵循简单明了的基本原则 使得程序名或者变量名称具有相关的含义 PLSQL程序规范 命名规范 5 变量命名 不同类型的变量 遵循不同的命名规则 PLSQL程序规范 大小写规范 使用统一标准的大小写规范 可以明显的增强程序的可读性 以及程序的美观程度 注 1 使用PL SQLDEVELOPER工具 可以设置自动大小写功能2 原则上SQL代码中保持一致的风格就可以 不强求一定大写 或者一定小写 尽量不要出现非数据库保留字之外的代码不统一的情况 PLSQL程序规范 代码对齐规范 使用良好的对齐规范 可以明显的增强程序的可读性 以及程序的美观程度 同时可以减少程序出错的概率 增强可维护性 总体来说 对齐要基于程序层次情况 使用首尾对齐原则 如下例所示 FUNCTIONGET EMPLOYEE SALARY P EMPLOYEE IDNUMBER P MONTHNUMBER RETURNNUMVERISV SALARYNUMBER CURSORCUR EMPLOYEE PAYROLL ELEMENTISSELECTpayroll element code base salaryFROMps payroll elementsppeWHEREppe EMPLOYEE id p EMPLOYEE idANDppe payment month p month BEGINV SALARY 0 FORC1INCUR EMPLOYEE PAYMENT ELEMENTLOOPIFC1 base salaryisnotnullthenV SALARY V SALARY C1 base salary ENDIF ENDLOOP END RETURNV SALARY AMOUNT ENDGET EMPLOYEE SALARY PLSQL程序规范 注释信息规范 程序代码注释规范 2 1单行代码禁用 可以用PLSQL屏蔽符 2 2多行 大范围代码禁用 必须使用PLSQL大范围屏蔽符 需要屏蔽的内容 程序关键点注释 程序的一些关键点的注释可以很清晰的分出程序的结构 增强程序的逻辑性和可读性 例如 IFV RESULT S THEN 如果运行成功 则 ELSIFV RESULT E THEN 如果运行失败 则 ELSIFV RESULT W THEN 如果运行警告 则 ENDIF 注释信息可以很好的描述程序的功能 为后续的维护带来非常有价值的基础信息 原则要求程序包各个部分都必须有相应的注释信息 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 BasedOptimizerCBO Cost BasedOptimizer 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字段进行数据附加操作 例如 whereaction date 1 to date 2009 01 01 yyyy mm dd 而应该修改为 whereaction date to date 2009 01 01 yyyy mm dd 1在where条件中 不要对Index字段进行 操作 不等于操作 因为Index只记录了有什么 而没有记录没有什么 例如 wheresalary amount 0应该修改为 wheresalary amount 0在where条件中 不要对两个index字段做拼合操作 例如 whereemployee id employee no 123 C001 应该修改为 whereemployee id 123andemployee no C001 PLSQL性能规范 代码语句规范 使用索引规范 如果出现SQL会使用到多个INDEX 而被选择的INDEX不是最优选择的时候 可以使用强制禁用不需要的INDEX的办法来迫使系统选择你希望的INDEX例如 Select fromps employeepewherepe action date sysdate 365andpe dept id 111 如果发现使用action date这个index的性能没有dept id这个index好 那么可以修改成select fromps employeepewherepe action date 1 sysdate 366andpe dept id 111通过强制屏蔽action date这个index被使用 就可以迫使系统选择dept id作为index来运作 PLSQL性能规范 代码语句规范 in notinexists notexists使用规范 在SQL中 会用的IN NOTIN的操作 用来判断相关数据是否存在于其他表中 需要遵循规则如下 除了whereemployee namein abc cde 这样的指明的常量 或者传入参数的变量 以为 都不能使用in的操作 而应该使用exists来代替in 例如 whereemployee idin seleetemployee idfromps payment historypphwherepph employee id emp employee idandpph payment month 200912 应该修改为 whereexists selctpph employee idfromps payment historypphwherepph employee id emp employee idandpph payment month 200912 2 同样的原理 使用notexists代替notin的操作 PLSQL性能规范 代码语句规范 Groupby使用规范 在SQL中 会用的GROUPBY的操作 用来取得汇总的数据 GROUPBY操作会产生数据库的排序操作 而排序的数据量的大小也直接影响了运行的性能 GROUPBY需要遵循如下规范 尽量少的使用GROUPBY操作 如果有函数可以替代的话 尽量少的使用GROUPBY的字段 在GROUPBY之前的where条件中 尽可能的先缩小数据范围 尽可能的使用索引字段作为GROUPBY字段 PLSQL性能规范 代码语句规范 distinct使用规范 在SQL中 会用的distinct的操作 用来取得不重复的数据 distinct操作会产生数据库的排序操作 排序操作会影响SQL性能 Distinct操作的使用规范如下 尽可能少的使用distinct操作 尽量通过程序的条件本身来排除相同数据的出现 在SQL中可以使用exists来代替where and条件 从而排除重复数据的出现 PLSQL性能规范 代码语句规范 减少都表的访问次数 在SQL中 对表的访问次数越多 带来的性能成本越高 产生的磁盘IO越大如下例 低效 UPDATEEMPSETEMP CAT SELECTMAX CATEGORY FROMEMP CATEGORIES SAL RANGE SELECTMAX SAL RANGE FROMEMP CATEGORIES WHEREEMP DEPT 0020 高效 UPDATEEMPSET EMP CAT SAL RANGE SELECTMAX CATEGORY MAX SAL RANGE FROMEMP CATEGORIES WHEREEMP DEPT 0020 PLSQL性能规范 代码语句规范 使用规范 在SQL中 有些程序员喜欢用select count 这样的操作 实际上这样的操作要浪费很多性能 带来不必要的磁盘I O 相关规则如下 坚决杜绝使用select count fromtable的操作 用select countindex字段fromtable的操作 PLSQL性能规范 代码语句规范 ROWID 在数据库中 每一个表的每一行记录都有一个整个数据库都唯一的ROWID值 而且这个ROWID值正常是不会发生变化 除非表重建后导入原有数据 而Index也都是会对应到这个ROWID 因此在可以的情况下 尽量使用ROWID来直接定位到记录进行操作 例如 Cursorcur employeeisselectrowidfromps employeeempwhereemp status INVALID Beginforc1incur employeeloopupdateps employeepesetpe active flag N wherepe rowid c1 rowid endloop commit End PLSQL性能规范 代码语句规范 Having的使用规范 在SQL中 会需要使用having来做是否判断 但应该避免使用HAVING子句 HAVING只会在检索出所有记录之后才对结果集进行过滤 这个处理需要排序 总计等操作 如果能通过WHERE子句限制记录的数目 那就能减少这方面的开销相关规则如下 Having的语句只应用在需要判断count汇总的值是否满足条件 不能用来代替where条件作为判断 例如 低效 SELECTREGION AVG LOG SIZE FROMLOCATIONGROUPBYREGIONHAVINGREGIONREGION SYDNEY ANDREGION PERTH 高效SELECTREGION AVG LOG SIZE FROMLOCATIONWHEREREGIONREGION SYDNEY ANDREGION PERTH GROUPBYREGION PLSQL性能规范 代码语句规范 truncate delete 对数据库表数据进行删除操作 如果没有任何条件的全部删除 使用delete的话 数据库会把被删除的数据库放入rollbacksegment中 这样即占用Rollback空间 也降低了性能 而Truncatetable的操作 不需要对数据进行丢失保护 从而具有更好的性能 因此 在可以删除全部表数据的情况下 一定使用truncate来代替delete如果这种操作在一个大的流程中 需要确保其他的流程处理都正常结束之后再做这个操作 因为truncate是不需要commit的一种操作 同样也无法rollback 因此要把握好使用的时机 PLSQL性能规范 代码语句规范 尽可能的共享SQL语句 SQL对数据库的访问 系统会把SQL代码缓存在内存 sharedbufferpool 如果运行的SQL能在sharedbufferpool找到一样的代码 那么这个SQL就不需要再次解析的过程 从而执行性能得到提升 注意规则如下 尽可能的使用频繁使用的公用的函数 来代替私有的特殊的写法但相同功能的代码 代码中使用绑定的变量的方式 来保持SQL的一致性 例如 第一组的两个SQL语句是相同的 可以共享 而第二组中的两个语句是不同的 即使在运行时 赋于不同的绑定变量相同的值 1 selectpin namefrompeoplewherepin blk1 pin selectpin namefrompeoplewherepin blk1 pin 2 selectpin namefrompeoplewherepin blk1 ot ind selectpin namefrompeoplewherepin blk1 ov ind