对Oracle问题SQL的查找和分析.doc

附件：典型经验 对 Oracle 问题 SQL 的查找和分析 报送单位：宁夏电力公司 审核人：李月华 类型：业务应用 关键字：Oracle 优化 SQL 速度慢 1 1、引言、引言 各业务应用系统的问题 SQL 是目前应用系统宕机的重要影响因 素，当程序中存在执行效率极差的 SQL 语句或不当的索引时，会导 致 ORACLE 数据库响应缓慢，部分还导致了应用系统内存溢出及 weblogic 应用服务器宕机，同时占有了大量的系统物理资源。 2 2、现象描述、现象描述 宁夏电力公司综合数据库主要运行着“SG186 工程”综合业务 应用近 15 个小型业务应用系统，主要包括远程教育培训、经济法 律、安监系统、审计系统等等。在日常运维过程中，多次发生了 ORACLE RAC 集群单节点宕机的问题。 硬件： 服务器：IBM P570 Power5 2.2GHz4, 7744M RAM 存储： HP XP 24000 软件： 操作系统：IBM AIX HACMP 5.3 数据库： ORACLE 10G RAC 10.216.3.*1 zhnxdb1 10.216.3.*2 zhnxdb2 处理过程 问题分析 1）ORACLE AWR 报告分析 Top 5 Timed Events Zhnxdb1： Zhnxdb2： 查看时间模型统计信息，可以看到 zhnxdb1 大量的数据库时间 被 enq：TX row lock contention 所消耗，另外还有 db file scattered read，gc cr multi block request， db file sequentail read。 Zhnxdb2 的大量时间被 CPU time 和 db file scattered read，gc cr multi block request， db file sequentail read ，control file sequential read 消耗。 从 top5 事件可以看到两个实例都产生了大量的 I/O 读写操作， 实例 2 在有大部分 CPU time 的同时又大量的 I/O 读写操作，这里 的 CPU time 只要是执行 SQL 语句占用。TX row lock contention 的出现，主要原因是系统中有多个会话同时对单一数据 表进行修改而导致。该问题经常 UPDATE、DELETE 等语句导致。 db file scattered read 问题主要是由于对数据库表的大量全表扫描 而发生，产生数据表的连续读取数据排队问题，该问题经常出现在 数据表中存在不合理的索引而导致。 AWR 报告 Time Model Statistics Zhnxdb1： Zhnxdb2： 根据时间模型确定影响性能主要原因为问题 SQL 语句： SQL 语句执行持续时间（sql execute elapsed time），实例 1 占了 DB time 的 99.06%，实例 2 占了 Dbtime 的 95.82%。 2）对 AWR 报告 SQL 语句长时间执行问题 Zhnxdb1： Zhnxdb2： 下面依次分析占用时间最长的几条 SQL 语句： 1、SELECT * FROM ( SELECT ROW_.*, ROWNUM ROWNUM_ FROM ( select distinct i.* from (select distinct * from (select substr(t.SOURCE_REFERENCE_ID, 4, length(t.SOURCE_REFERENCE_ID) - 4) id, g.user_id, to_char(t.create_time, yyyy-mm-dd) fdate from wf_activity t, wf_assignment_end g, law_t_contract_info i where t.activity_id = g.activity_id and substr(t.SOURCE_REFERENCE_ID, 4, length(t.SOURCE_REFERENCE_ID) - 4) = i.id union select substr(t.SOURCE_REFERENCE_ID, 4, length(t.SOURCE_REFERENCE_ID) - 4) id, t.user_id, to_char(t.create_time, yyyy-mm-dd) fdate from wf_end_task_list t) T, law_t_contract_info i where T.id= i.id and T.USER_ID=O00000000002372 and t.fdate LIKE 2011-07% or t.fdate LIKE 2011-08% or t.fdate LIKE 2011- 09% order by i.CREATE_DATE ) ROW_ WHERE ROWNUM :2 该语句为经济法律系统问题 SQL 语句，五层嵌套，效率极差， 同时通过执行计划分析，存在全表扫描。 2、SELECT COUNT(1) FROM (select distinct i.* from (select distinct * from (select substr(t.SOURCE_REFERENCE_ID, 4, length(t.SOURCE_REFERENCE_ID) - 4) id, g.user_id, to_char(t.create_time, yyyy-mm-dd) fdate from wf_activity t, wf_assignment_end g, law_t_contract_info i where t.activity_id = g.activity_id and substr(t.SOURCE_REFERENCE_ID, 4, length(t.SOURCE_REFERENCE_ID) - 4) = i.id union select substr(t.SOURCE_REFERENCE_ID, 4, length(t.SOURCE_REFERENCE_ID) - 4) id, t.user_id, to_char(t.create_time, yyyy-mm-dd) fdate from wf_end_task_list t) T, law_t_contract_info i where T.id= i.id and T.USER_ID=O00000000002372 and t.fdate LIKE 2011-07% or t.fdate LIKE 2011-08% or t.fdate LIKE 2011- 09% order by i.CREATE_DATE) T 该语句同样为经济法律系统问题 SQL 语句。 3、UPDATE AJ_T_PUB_TASK SET ACTION_STATUS = :1 , URL_PARAM = :2 , NEXT_TASK_ID = :3 WHERE TABLE_NAME = :4 AND TABLE_ID = :5 AND ROLE_ID = :6 DELETE FROM APP_AJ_T_PUB_TASK T WHERE T.SEND_DATE SYSDATE - 1 delete from app_aj_t_pub_task where task_id=8a58835830f382860131a836a9d03484 上述三条语句是导致 TX row lock contention 的主要问题， 该语句存在于安监系统。APP_AJ_T_PUB_TASK 表有 30 余万条记 录，系统“工作任务”模块大量执行上述三条语句。此外最关键的 是 APP_AJ_T_PUB_TASK 表没有在 TABLE_NAME、TABLE_ID、ROLE_ID 等字段建任何索引，导致 全表扫描。 4、select ( SELECT COUNT(UNIQUE USER_ID) FROM (SELECT USER_ID FROM PUB_ONLINE pon , pub_organ po, pub_stru ps WHERE pon.corporation_id = ps.stru_id and an_id = an_id and an_code like 99995055% and an_type like 1% UNION ALL SELECT USER_ID FROM PUB_ONLINE_HIST poh, pub_organ po, pub_stru ps WHERE poh.corporation_id = ps.stru_id AND poh.LOGIN_TIME LIKE TO_CHAR(SYSDATE, YYYYMMDD) | % and an_id = an_id and an_code like 99995055% and an_type like 1% ) ), ( SELECT COUNT(UNIQUE USER_ID) FROM (SELECT USER_ID FROM PUB_ONLINE pon , pub_organ po, pub_stru ps WHERE pon.corporation_id = ps.stru_id and an_id = an_id and an_code like 9999505599 and an_type like 1% UNION ALL SELECT USER_ID FROM PUB_ONLINE_HIST poh, pub_organ po, pub_stru ps WHERE poh.corporation_id = ps.stru_id AND poh.LOGIN_TIME LIKE TO_CHAR(SYSDATE, YYYYMMDD) | % and an_id = an_id and an_code like 9999505599 and an_type like 1% ) ), ( SELECT COUNT(UNIQUE USER_ID) FROM (SELECT USER_ID FROM PUB_ONLINE pon , pub_organ po, pub_stru ps WHERE pon.corporation_id = ps.stru_id and an_id = an_id and an_code like 9999505551 and an_type like 1% UNION ALL SELECT USER_ID FROM PUB_ONLINE_HIST poh, pub_organ po, pub_stru ps WHERE poh.corporation_id = ps.stru_id AND poh.LOGIN_TIME LIKE TO_CHAR(SYSDATE, YYYYMMDD) | % and an_id = an_id and an_code like 9999505551 and an_type like 1% ) ), ( SELECT COUNT(UNIQUE USER_ID) FROM (SELECT USER_ID FROM PUB_ONLINE pon , pub_organ po, pub_stru ps WHERE pon.corporation_id = ps.stru_id and an_id = an_id and an_code like 9999505552 and an_type like 1% UNION ALL SELECT USER_ID FROM PUB_ONLINE_HIST poh, pub_organ po, pub_stru ps WHERE poh.corporation_id = ps.stru_id AND poh.LOGIN_TIME LIKE TO_CHAR(SYSDATE, YYYYMMDD) | % and an_id = an_id and an_code like 9999505552 and an_type like 1% ) ), ( SELECT COUNT(UNIQUE USER_ID) FROM (SELECT USER_ID FROM PUB_ONLINE pon , pub_organ po, pub_stru ps WHERE pon.corporation_id = ps.stru_id and an_id = an_id and an_code like 9999505553 and an_type like 1% UNION ALL SELECT USER_ID FROM PUB_ONLINE_HIST poh, pub_organ po, pub_stru ps WHERE poh.corporation_id = ps.stru_id AND poh.LOGIN_TIME LIKE TO_CHAR(SYSDATE, YYYYMMDD) | % and an_id = an_id and an_code like 9999505553 and an_type like 1% ) ), ( SELECT COUNT(UNIQUE USER_ID) FROM (SELECT USER_ID FROM PUB_ONLINE pon , pub_organ po, pub_stru ps WHERE pon.corporation_id = ps.stru_id and an_id = an_id and an_code like 9999505554 and an_type like 1% UNION ALL SELECT USER_ID FROM PUB_ONLINE_HIST poh, pub_organ po, pub_stru ps WHERE poh.corporation_id = ps.stru_id AND poh.LOGIN_TIME LIKE TO_CHAR(SYSDATE, YYYYMMDD) | % and an_id = an_id and an_code like 9999505554 and an_type like 1% ) ), ( SELECT COUNT(UNIQUE USER_ID) FROM (SELECT USER_ID FROM PUB_ONLINE pon , pub_organ po, pub_stru ps WHERE pon.corporation_id = ps.stru_id and an_id = an_id and an_code like 9999505555 and an_type like 1% UNION ALL SELECT USER_ID FROM PUB_ONLINE_HIST poh, pub_organ po, pub_stru ps WHERE poh.corporation_id = ps.stru_id AND poh.LOGIN_TIME LIKE TO_CHAR(SYSDATE, YYYYMMDD) | % and an_id = an_id and an_code like 9999505555 and an_type like 1% ) ), ( SELECT COUNT(UNIQUE USER_ID) FROM (SELECT USER_ID FROM PUB_ONLINE pon , pub_organ po, pub_stru ps WHERE pon.corporation_id = ps.stru_id and an_id = an_id and an_code like 9999505556 and an_type like 1% UNION ALL SELECT USER_ID FROM PUB_ONLINE_HIST poh, pub_organ po, pub_stru ps WHERE poh.corporation_id = ps.stru_id AND poh.LOG IN_TIME LIKE TO_CHAR(SYSDATE, YYYYMMDD) | % and an_id = an_id and an_code like 9999505556 and an_type like 1% ) ), ( SELECT COUNT(UNIQUE USER_ID) FROM (SELECT USER_ID FROM PUB_ONLINE pon , pub_organ po, pub_stru ps WHERE pon.corporation_id = ps.stru_id and an_id = an_id and an_code like 9999505557 and an_type like 1% UNION ALL SELECT USER_ID FROM PUB_ONLINE_HIST poh, pub_organ po, pub_stru ps WHERE poh.corporation_id = ps.stru_id AND poh.LOGIN_TIME LIKE TO_CHAR(SYSDATE, YYYYMMDD) | % and an_id = an_id and an_code like 9999505557 and an_type like 1% ) ), ( SELECT COUNT(UNIQUE USER_ID) FROM (SELECT USER_ID FROM PUB_ONLINE pon , pub_organ po, pub_stru ps WHERE pon.corporation_id = ps.stru_id and an_id = an_id and an_code like 9999505593 and an_type like 1% UNION ALL SELECT USER_ID FROM PUB_ONLINE_HIST poh, pub_organ po, pub_stru ps WHERE poh.corporation_id = ps.stru_id AND poh.LOGIN_TIME LIKE TO_CHAR(SYSDATE, YYYYMMDD) | % and an_id = an_id and an_code like 9999505593 and an_type like 1% ) ), ( SELECT COUNT(UNIQUE USER_ID) FROM (SELECT USER_ID FROM PUB_ONLINE pon , pub_organ po, pub_stru ps WHERE pon.corporation_id = ps.stru_id and an_id = an_id and an_code like 9999505595 and an_type like 1% UNION ALL SELECT USER_ID FROM PUB_ONLINE_HIST poh, pub_organ po, pub_stru ps WHERE poh.corporation_id = ps.stru_id AND poh.LOGIN_TIME LIKE TO_CHAR(SYSDATE, YYYYMMDD) | % and an_id = an_id and an_code like 9999505595 and an_type like 1% ) ), ( SELECT COUNT(UNIQUE USER_ID) FROM (SELECT USER_ID FROM PUB_ONLINE pon , pub_organ po, pub_stru ps WHERE pon.corporation_id = ps.stru_id and an_id = an_id and an_code like 9999505596 and an_type like 1% UNION ALL SELECT USER_ID FROM PUB_ONLINE_HIST poh, pub_organ po, pub_stru ps WHERE poh.corporation_id = ps.stru_id AND poh.LOGIN_TIME LIKE TO_CHAR(SYSDATE, YYYYMMDD) | % and an_id = an_id and an_code like 9999505596 and an_type like 1% ) ), ( S