Oracle数据库性能SQL优化案例.docx

精品word学习资料可编辑 名师归纳总结——欢迎下载 Oracle 数据库性能 SQL优化案例 一． 背景描述 总体感觉运行比较缓慢，主要消耗在数据库模块，其中数据库所在的主机资源紧急， CPU 的 IDLE 很低， 说明数据库急需优化； 二． 总体调优 COMMIT 提交过频繁（已解决） 分析数据库运行一周以来的 AWR 报表，发觉数据库存在日至切换频繁的情形，其中 wait class 为 commit 的log file sync 等待大事竟然占了 23%， 一周时间内等待了 3918701 次近 400 万秒 ，很明显数据库应用存在单次提交过频繁，未有效的批量提交的情形； 通过如下查询，发觉 timeTask@itsm_ht (TNS V1 -V3) 模块有一个更新语句特别频繁 ,产生了 200 多万次提交 ， 当时查看该 SESSION 而登录仅仅不过 6 小时而已； 精品word学习资料可编辑 名师归纳总结——欢迎下载 成效： 经过和后台开发人员沟通发觉， 这是后台程序的 BUG ，修正后， 提交大幅度削减， 数据库中 COMMIT 相关的 log file sync 等待得到极大的改善； 库的统计信息收集未开启（已解决） 由于数据库总体运行缓慢， 有时从同事的某些 SQL 的语句执行方案中发觉驱动次序明显错误得到启示 ，检查数据库的统计信息情形，发觉竟然返回了 7431 条，几乎占了 bosswg 和 basedba 用户的对象的全部！ 接下来发觉，原先 ORACLE 的自动收集统计信息的功能被关闭了 ，详细如下： 开启自动收集 exec dbms_scheduler.enable(GATHER_STATS_JOB); 后， 数据库统计信息得以正常收集 手工收集统计信息含全局临时表（已解决） 全局临时表是不能被收集统计信息的，否就简单出大问题，影响执行方案，当前调和模块的全局临时表 RN_IDENTIFICATION_BATCH 被收集了统计信息，如下： 精品word学习资料可编辑 名师归纳总结——欢迎下载 解决方法就是删除表的统计信息： EXEC dbms_stats.delete_table_stats(ownname = BOSSWG,tabname = RN_IDENTIFICATION_BATCH) ; 当前已经解决（注：完成这个回收全局临时表统计信息的改造） 大量索引有并行属性（已解决） 在随后的一小时的 AWR 报表分析中，发觉 PX 的等待也特别明显，这是由于并行度设置在表或索引属性中引发的一种常见等待大事，如下所示， 在一小时的采样中竟然有近 1 万秒的 PX 等待 ： 查看后发觉索引竟然有 1334 个设置有并行度属性，如下所示，略去大部分呈现： SQL select t.owner, t.table_name, index_name, degree, status from dba_indexes t where owner in (BOSSWG, BASEDBA) and t.degree 1; OWNER TABLE_NAME INDEX_NAME DEGREE STATUS BOSSWG PERF_HOST_FILESYSTEM_HIS IDX_TEMP1 4 VALID BOSSWG PERF_WEBLOGIC_WEBMODULE_HIS IDX_TEMP2 4 VALID BOSSWG V3_REPLACE_CI_RELATION_LOG PK_V3_REPLACE_CI_RELATION_LOG 9 VALID BOSSWG V3_REPLACE_CI_LOG PK_V3_REPLACE_CI_LOG 9 VALID BOSSWG V3_REPLACE_CI_CLASS PK_V3_REPLACE_CI_CLASS 9 VALID BOSSWG IFACE_TODO_LIST PK_IFACE_TODO_LIST 9 VALID BOSSWG IFACE_TODO PK_IFACE_TODO 9 VALID BOSSWG IFACE_STAFF PK_IFACE_STAFF 9 VALID --以下略去 1000 多行 精品word学习资料可编辑 名师归纳总结——欢迎下载 1334 rows selected. 成效： 用如下方法，将这些并行取消后， 数据库的 PX 等待大事从今消逝了 ； select alter index || t.owner||.||index_name || noparallel; from dba_indexes t where owner in (BOSSWG, BASEDBA) and t.degree 1; 众多表记录需