如何识别最耗资源的SQL[转]

http://www.ningoo.net/html/2007/how_to_identify_resource_intensive_sql.html
一般来说，调优的第一手资料，很可能就是典型业务期的一个statspack报告，那么如何根据statspack报告来判断是哪些SQL消耗了最多的系统资源？哪些SQL是最需要调整的呢？这里给出了一个大致的优化思路。当然，思路是死的，人是活的，优化也需要随需应变。

一般来说，需要关注下面四种Top SQL

• 消耗最多CPU的（逻辑IO过多）
• 导致过多物理I/O的
• 执行次数较频繁的
• 执行时间较长的

我们知道，一个语句的响应时间有个很著名的公式：

响应时间=服务时间+等待时间

其中服务时间就是CPU为执行该语句花费的时间。

服务时间=分析时间+递归时间+执行时间

分析时间是CPU用于分析语句的时间，递归时间是CPU用于语句的递归SQL的时间，剩下的则就是CPU用于执行语句的真正时间了。

那么，上面的这些时间信息从哪里来的？Oracle提供的系统统计信息中就有部分的时间统计信息：

服务时间=CPU used by this session
分析时间=parse time cpu
递归时间=recursive cpu usage

那么，执行时间就可以根据上面三个统计信息计算得出：

执行时间=CPU used by this session - parse time cpu - recursive cpu usage

• 如果执行时间在整个响应时间中占较大的比例，那么下一步就是找出那些造成了最多逻辑IO的SQL语句，可以从statspack报告的SQL ordered by Gets部分找到。
• 如果分析时间在整个响应时间中占较大的比例，那么下一步就是查找哪些SQL分析过多，这在statspack报告中在SQL ordered by Parse Calls中列出。
• 如果等待时间在整个响应时间中占较大的比例，并且主要是块读取相关的等待时，下一步就是找出哪些SQL造成了过多的物理读，可以查看statspack报告中的SQL ordered by Reads部分。

那么，根据上面列出的一个简单的原则，我们需要关注三个关于CPU时间的统计信息: CPU used by this session, parse time cpu和recursive cpu usage，以及top5等待事件中和IO相关的等待时间。如果是其他的一些等待事件出现在Top5中，那么可能需要根据不同的等待事件来分析原因了。然后优先调优时间消耗最多的相关SQL。

除了上面的SQL ordered by Gets（逻辑IO最多），SQL ordered by Parse Calls（软解析过多），SQL ordered by Reads（物理IO过多），statspack还按照其他的一些方式列出了Top SQL，这些Top SQL在某些情况下都是需要给予特别关注的。比如

• SQL ordered by Executions 执行次数超过100的
• SQL ordered by Sharable Memory 占用library cache超过1M的
• SQL ordered by Version Count 子cursor超过20的

如果没有statspack，那么根据v$sysstat/v$sesstat中的统计信息，结合v$sql/v$sqlarea，一样可以得到相关的SQL。

v$sql对于每一个子cursor都有一行统计记录，而v$sqlarea则对同一个父cursor只有一行统计记录，也就是v$sqlarea是对v$sql按照父cursor进行group by后的一个结果。这两个视图中都有诸如buffer_gets,parse_calls,disk_reads,,executions,sharable_mem等列，和上面提到的statspack中列出Top SQL的条件对应。

参考文章：Metalink Note:232443.1