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

附件：典型经验 对Oracle问题SQL的查找和分析 报送单位：宁夏电力公司 审核人：李月华 类型：业务应用 关键字：Oracle 优化 SQL 速度慢 1、引言 各业务应用系统的问题SQL是目前应用系统宕机的重要影响因素，当程序中存在执行效率极差的SQL语句或不当的索引时，会导致ORACLE数据库响应缓慢，部分还导致了应用系统内存溢出及weblogic应用服务器宕机，同时占有了大量的系统物理资源。 2、现象描述 宁夏电力公司综合数据库主要运行着“SG186工程”综合业务应用近15个小型业务应用系统，主要包括远程教育培训、经济法律、安监系统、审计系统等等。在日常运维过程中，多次发生了ORACLE RAC 集群单节点宕机的问题。 硬件： 服务器：IBM P570 Power5 2.2GHz×4, 7744M RAM 存储： HP XP 24000 软件： 操作系统：IBM AIX 5.3.0.5＋HACMP 5.3 数据库： ORACLE 10G 10.2.0.3 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