我们对 SQL 语句进行调优时，非常重要的一个方法就是分析 SQL 的执行计划。查看 Oracle 中 SQL 语句的执行计划有很多方法，例如通过 PL/SQL Developer 、 EM 或者 SQLPLUS 工具等。这里以 SQLPLUS 为例。

在 SQLPLUS 使用命令 SET AUTOTRACE ON 后，可以在查询结果后看到该语句的执行计划。也可以使用 SET AUTOTRACE TRACEONLY 命令，只返回执行计划而不返回查询结果。例如执行一个简单的全表扫描语句，返回执行计划如下：

从上述执行计划可以看到，对表的访问为全表扫描（ TABLE ACCESS FULL ）。这种情况一般是没有索引或者索引不可用。我们执行的 SQL 语句是 select * from hr.employees ，该语句没有指定搜索条件，所以也只能是全表扫描。

接下来我们还可以看到一些关于执行计划的统计信息。对于这些信息的分析非常重要。通过查询 Oracle 的文档，可以看到具体解释如下：

Recursive Calls：Number of recursive calls generated at both the user and system level.

Oracle Database maintains tables used for internal processing. When it needs to change these tables, Oracle Database generates an internal SQL statement, which in turn generates a recursive call.

In short, recursive calls are basically SQL performed on behalf of your SQL. So, if you had to parse the query, for example, you might have had to run some other queries to get data dictionary information. These would be recursive calls. Space management, security checks, calling PL/SQL from SQL—all incur recursive SQL calls.

（意思大概是我们的 SQL 执行中需要对数据库系统的一些内部表进行操作，如关于空间管理的检查、安全检查以及调用 PL/SQL 等，这些都会产生递归调用。）

DB Block Gets： Number of times a CURRENT block was requested. Current mode blocks are retrieved as they exist right now, not in a consistent read fashion. Normally, blocks retrieved for a query are retrieved as they existed when the query began. Current mode blocks are retrieved as they exist right now, not from a previous point in time.

During a SELECT, you might see current mode retrievals due to reading the data dictionary to find the extent information for a table to do a full scan (because you need the "right now" information, not the consistent read). During a modification, you will access the blocks in current mode in order to write to them.

(DB Block Gets ：当前模式块被请求的数量。当前模式块与一致性读取块相对，当前模式块是数据块现在的状态，而一致性读取块是放在回滚段中的数据块的旧的映像。 DB Block Gets 请求的数据块在 buffer cache 可以得到满足。 )

Consistent Gets：Number of times a consistent read was requested for a block. This is how many blocks you processed in "consistent read" mode. This will include counts of blocks read from the rollback segment in order to roll back a block. This is the mode you read blocks in with a SELECT, for example. Also, when you do a searched UPDATE/DELETE, you read the blocks in consistent read mode and then get the block in current mode to actually do the modification.

(Consistent Gets ：请求的一致性读取模式下的数据块的数量。 Consistent Gets 的数据请求在回滚段 Buffer 中得到满足。 )

Physical Reads： Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.

 (Physical Reads ：请求的数据块在 buffer cache 中没有的话，就需要读取磁盘上的数据文件。这里指的就是从磁盘读到 Buffer Cache 的数据块数量。 )

Redo size：对数据块进行修改时，会将旧的内容写入回滚段，用于一致性读取操作。这里的 redo size 指的是操作产生的 redo 信息数量，单位是字节。

Sorts (disk)： Number of sort operations that required at least one disk write. Sorts that require I/O to disk are quite resource intensive. Try increasing the size of the initialization parameter SORT_AREA_SIZE.

（ Sorts (disk) ：排序操作首先会使用内存空间的 SORT_AREA_SIZE ，如果不足则会使用临时表空间，这样就产生了磁盘访问。磁盘中的排序非常消耗资源，建议调整 SORT_AREA_SIZE 参数的值。）

通常在第一次访问某个表时，会产生 Physical Reads ，因为要从数据文件读取数据块。但是后续的查询就不再产生 Physical Reads 。这一点可以通过清空 buffer cache ，重复执行相同 SQL 语句来考察。

SQL> alter system flush buffer_cache;

在进行 SQL 的性能分析时，应该重点关注 Physical Reads 和 Sorts (disk) 这两个数据。

第二篇：对Oracle问题SQL的查找和分析

附件：典型经验

对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

t.activity_id g, = law_t_contract_info g.activity_id i where and

4, substr(t.SOURCE_REFERENCE_ID,

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 <= :1) WHERE ROWNUM_ > :2

该语句为经济法律系统问题SQL语句，五层嵌套，效率极差，同时通过执行计划分析，存在全表扫描。

2、SELECT COUNT(1) FROM (select distinct i.* from (select distinct * from (select

4, substr(t.SOURCE_REFERENCE_ID,

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

t.activity_id g, = law_t_contract_info g.activity_id i where and

4, substr(t.SOURCE_REFERENCE_ID,

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 ps.organ_id = po.organ_id and po.organ_code like '99995055%' and po.organ_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 ps.organ_id = po.organ_id and po.organ_code like '99995055%' and po.organ_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 ps.organ_id = po.organ_id and po.organ_code like '9999505599' and po.organ_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 ps.organ_id = po.organ_id and po.organ_code like '9999505599' and po.organ_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 ps.organ_id = po.organ_id and

po.organ_code like '9999505551' and po.organ_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 ps.organ_id = po.organ_id and po.organ_code like '9999505551' and po.organ_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 ps.organ_id = po.organ_id and po.organ_code like '9999505552' and po.organ_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 ps.organ_id = po.organ_id and po.organ_code like '9999505552' and po.organ_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 ps.organ_id = po.organ_id and po.organ_code like '9999505553' and po.organ_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 ps.organ_id = po.organ_id and po.organ_code like '9999505553' and po.organ_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 ps.organ_id = po.organ_id and po.organ_code like '9999505554' and po.organ_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 ps.organ_id = po.organ_id and po.organ_code like '9999505554' and po.organ_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 ps.organ_id = po.organ_id and po.organ_code like '9999505555' and po.organ_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 ps.organ_id = po.organ_id and po.organ_code like '9999505555' and po.organ_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 ps.organ_id = po.organ_id and po.organ_code like '9999505556' and po.organ_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 ps.organ_id = po.organ_id and po.organ_code like '9999505556' and po.organ_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 ps.organ_id = po.organ_id and po.organ_code like '9999505557' and po.organ_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 ps.organ_id = po.organ_id and po.organ_code like '9999505557' and

po.organ_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 ps.organ_id = po.organ_id and po.organ_code like '9999505593' and po.organ_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 ps.organ_id = po.organ_id and po.organ_code like '9999505593' and po.organ_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 ps.org an_id = po.organ_id and po.organ_code like '9999505595' and po.organ_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 ps.organ_id = po.organ_id and po.organ_code like '9999505595' and po.organ_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 ps.organ_id = po.organ_id and po.organ_code like '9999505596' and po.organ_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 ps.organ_id = po.organ_id and po.organ_code like '9999505596' and po.organ_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 ps.organ_id = po.organ_id and po.organ_code like '9999505558' and po.organ_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 ps.organ_id = po.organ_id and po.organ_code like '9999505558' and po.organ_type like '1%' ) ) from dual

安监系统中存在的非常复杂的SQL语句，存在8重UNION ALL查询操作，效率极差。

5、update visitor set visitor=visitor+1

远程教育培训IMS接口的问题SQL语句，没有任何条件，同时由于系统开发问题导致visitor表又存在大量数据。

3、处理过程

1）反馈各系统开发厂家进行问题SQL语句整改同时合理建立索引。

2）进一步扩大ORACLE SVG空间，扩展物理资源。

4、原因分析

各业务应用系统开发水平参差不齐，存在非常初级的系统开发问题，导致在应用过程中，需要非常可观的硬件环境，系统故障不断。执行效率不高的SQL语句以及没有对系统各类表建立合理的索引提升SQL语句的执行效率是导致系统速度慢效率不高的主要原因。

5、经验总结

系统资源整合是有效减少硬件投资的重要手段，但是开发过程中，必须有效保障系统的开发质量,否则整合之后会带来互相影响的问题。其次，应不断对数据库系统进行SQL执行分析，不断发现并优化问题SQL，持续对系统进行后续完善。

6、技术人员

信息通信分公司 陈 鹏 0951-4915151