ITSM V3 调优跟踪
梁敬彬
一． 背景描述
ITSM V3 版本上线以来，总体感觉运行比较缓慢，主要消耗在数据库模块，其中数据库所在的主机资源紧
张，CPU 的 IDLE 很低，说明数据库急需优化。
二． 总体调优
1. COMMIT 提交过频繁（已解决）
分析数据库运行一周以来的 AWR 报表，发现数据库存在日至切换频繁的情况，其中 wait class 为 commit 的
log file sync 等待事件居然占了 23%，一周时间内等待了 3918701 次近 400 万秒，很显然数据库应用存在单次提交
过频繁，未有效的批量提交的情况。
通过如下查询，发现 timeTask@itsm_ht (TNS V1-V3)模块有一个更新语句非常频繁,产生了 200 多万次提交，
当时查看该 SESSION 而登录仅仅不过 6 小时而已。
SQL>select t1.sid, t1.value, t2.name
from v$sesstat t1, v$statname t2
--where t2.name like '%commit%'
where t2.name like '%user commits%' --可以只选 user commits,其他系统级的先不关心
and t1.STATISTIC# = t2.STATISTIC#
and value >= 10000
order by value desc;
SID STATISTIC# VALUE NAME
---------- ---------- ---------- ----------------------------------------------------------------
991 4 2281122 user commits
1404 4 233029 user commits
--略去
SQL> select sid,username, t.PROGRAM,sql_id , t.PREV_SQL_ID from v$session t where sid in =991;
SID USERNAME PROGRAM SQL_ID PREV_SQL_ID
---------- ------------------------------ ----------------------------- ------------- ----------------------------------------------------------------------
991 BOSSWG timeTask@itsm_ht (TNS V1-V3) avms342zm5k9u
SQL> select sql_text from v$sql where sql_id='avms342zm5k9u';
SQL_TEXT
--------------------------------------------------------------------------------
update MONITOR_INFO set step=:step,update_time=sysdate,remark=:remark
效果： 经过和后台开发人员沟通发现，这是后台程序的 BUG，修正后，提交大幅度减少，数据库中 COMMIT
相关的 log file sync 等待得到极大的改善。
2. 库的统计信息收集未开启（已解决）
由于数据库总体运行缓慢，偶尔从同事的某些 SQL 的语句执行计划中发现驱动顺序明显错误得到启发，检查
数据库的统计信息情况，发现居然返回了 7431 条，几乎占了 bosswg 和 basedba 用户的对象的全部！
SQL>select count(*)
from dba_tab_statistics t
where owner in ('BOSSWG', 'BASEDBA')
and (t.last_analyzed is null or t.last_analyzed <= sysdate - 14);
COUNT(*)
---------------
7431
接下来发现，原来 ORACLE 的自动收集统计信息的功能被关闭了，具体如下：
SQL> select t.JOB_NAME,t.PROGRAM_NAME,t. state,t.enabled
from dba_scheduler_jobs t
where job_name = 'GATHER_STATS_JOB';
JOB_NAME PROGRAM_NAME STATE ENABL
------------------------------ -------------------------------- ---------- ------------------------------------------------------------
GATHER_STATS_JOB GATHER_STATS_PROG DISABLED FALSE
开启自动收集 exec dbms_scheduler.enable('GATHER_STATS_JOB');后，数据库统计信息得以正常收集
SQL> select t.JOB_NAME,t.PROGRAM_NAME,t. state,t.enabled
from dba_scheduler_jobs t
where job_name = 'GATHER_STATS_JOB';
JOB_NAME PROGRAM_NAME STATE ENABL
------------------------------ -------------------------------- ---------- ------------------------------------------------------------
GATHER_STATS_JOB GATHER_STATS_PROG SCHEDULED TRUE
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;
