Oracle数据库性能SQL优化案例
一． 背景描述
总体感觉运行比较缓慢，主要消耗在数据库模块，其中数据库所在的主机资源紧张，CPU的IDLE很低，说明数据库急需优化。
二． 总体调优
1. COMMIT提交过频繁（已解决）
分析数据库运行一周以来的AWR报表，发现数据库存在日至切换频繁的情况，其中wait class 为commit的log file sync等待事件居然占了23%，一周时间内等待了3918701次近400万秒，很显然数据库应用存在单次提交过频繁，未有效的批量提交的情况。
通过如下查询，发现timeTask@itsm_ht (TNS V1-V3)模块有一个更新语句非常频繁,产生了200多万次提交，当时查看该SESSION而登录仅仅不过6小时而已。
效果：经过和后台开发人员沟通发现，这是后台程序的BUG，修正后，提交大幅度减少，数据库中COMMIT相关的log file sync等待得到极大的改善。
2. 库的统计信息收集未开启（已解决）
由于数据库总体运行缓慢，偶尔从同事的某些SQL的语句执行计划中发现驱动顺序明显错误得到启发，检查数据库的统计信息情况，发现居然返回了7431条，几乎占了bosswg和basedba用户的对象的全部！
接下来发现，原来ORACLE 的自动收集统计信息的功能被关闭了，具体如下：
开启自动收集exec dbms_scheduler.enable('GATHER_STATS_JOB');后，数据库统计信息得以正常收集
3. 手工收集统计信息含全局临时表（已解决）
全局临时表是不能被收集统计信息的，否则容易出大问题，影响执行计划，当前调和模块的全局临时表RN_IDENTIFICATION_BATCH被收集了统计信息，如下：
解决方法就是删除表的统计信息：
EXEC dbms_stats.delete_table_stats(ownname => 'BOSSWG',tabname => 'RN_IDENTIFICATION_BATCH') ; 当前已经解决（注：20121119完成这个回收全局临时表统计信息的改造）
4. 大量索引有并行属性（已解决）
在随后的一小时的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多行