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多行
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';
5. 众多表记录需要瘦身（完成部分）
以下记录中PERF_HOST_FILESYSTEM和INP_DATA_PERF表都是同一版本的表，记录都达到几亿条。 此外AH_GATHER_ALLFLOW_RESULT这个表虽然只有2千万，但是很奇怪的代码是天天删除，怎么会有这么多？
SQL> SELECT COUNT(*) FROM PERF_HOST_FILESYSTEM; COUNT(*) ----------------- 231049804
SQL> SELECT COUNT(*) FROM INP_DATA_PERF; COUNT(*) ---------------- 332761103
SQL> SELECT COUNT(*) FROM AH_GATHER_ALLFLOW_RESULT AG; COUNT(*) ----------------- 17778694
优化思路：考虑大表的历史数据能清理就清理，采样频率能适当的降低，其中的INP_DATA_PERF表记录很大且索引不少，已经影响了如下SQL语句的入库速度，具体见SQL调优部分的SQL_ID=6vv2w2k5jan6d部分。
6. 存在未使用绑定变量问题（解决大部分）
在最糟糕的时段，安徽居然出现软解析仅70%比率的糟糕情况，说明系统存在代码大量硬解析的情况，主要在调和模块的部分代码，已经在pkp_cmdb_reconcile_engine程序的v2.0版本中更新了。
类似如下（以下脚本调用次数极为频繁，却未使用绑定变量）： 原脚本： v_sql:='delete from '||rec.table_name|| ' where instance_id in (select instance_id from ci_base_element where '||i_condition|| ' and class_id='||v_class_id||')'; execute immediate v_sql; 修正为： v_sql:='delete from '||rec.table_name|| ' where instance_id in (select instance_id from ci_base_element where '||i_condition|| ' and class_id=:1)'; execute immediate v_sql using v_class_id; 原脚本： v_identification_sql := 'update ci_base_element set reconciliation_id=' || v_reconciliation_id || ' where INSTANCE_ID in (' || instencerec.instance_id1 || ',' || instencerec.instance_id2 || ') and reconciliation_id is null'; execute immediate v_identification_sql; 修正为： v_identification_sql := 'update ci_base_element set reconciliation_id=:1 where INSTANCE_ID in (:2,:3) and reconciliation_id is null'; execute immediate v_identification_sql using v_reconciliation_id,instencerec.instance_id1,instencerec.instance_id2; 三． SQL调优
当前数据库不少SQL需要优化改进，其中调和模块由于调用频繁且运行时间较长，优先级最高。
1. SQL_ID= d4hw7rpzdvmsd（调和）
紧急程度 SQL描述 紧急 该语句一周执行700次左右，平均每次执行2000秒左右，产生300多亿的逻辑读 其中CI_BASE_RELATIONSHIP记录217670条，CI_BASE_ELEMENT记录194475条。CI_CLASS_RELATION_TREE 记录只有10来条，RN_IDENTIFICATION_BATCH为全局临时表