awr的top sql分析

连续看了两天的awr报表，发现此db中存在大量的等待事件。Log file sync、全表扫描，全索引扫描、latch等待，热点块（很可能是由于全表全索引扫描导致的热点快竞争）、检查点未完成，队列锁的竞争等的。

这个db的awr报表出现了很大的问题，其实在生产库中也没有几次看awr报表的经验，虽然对buffer hit、buffer nowait等系统评估有一定了解，但是要说去具体如何查处性能瓶颈乃至解决性能瓶颈自身还存在很多不足。

公司内网服务器192.168.0.119有32g的内存和16个cpu，硬件性能上面应该不存在太多问题，要说问题就是做的raid 5，写入效率相对较差。而从linux操作系统上oracle分配的内存有24g以上，这个内存一般是足够的，查看db的awr报表发现系统存在很大的瓶颈,系统繁忙时存在大量的等待事件。而从顶级消耗的sql来看，系统下列sql语句存在较大的性能问题。

Sql语句1：SELECT A0.ID, A0.url, A0.MD5HASH, A0.LASTUPDATED FROM ARTURLMD5 A0 WHERE A0.LASTUPDATED BETWEEN :1 AND :2

发现使用绑定变量传递的参数导致上述sql语句在一个小时采样时间里执行了9次，平均一次执行时间Elap per Exec(s)是836.59秒，平均cpu time是1298秒，消耗大量的资源，查看其执行计划是对DESKTOP.ARTURLMD5执行了全表扫描，而这个表段的大小有26G,全表扫描消耗了大量的cpu和IO，导致系统性能下降。

建议可以对上述sql语句不使用绑定变量：

SELECT A0.ID, A0.url, A0.MD5HASH, A0.LASTUPDATED FROM ARTURLMD5 A0 WHERE A0.LASTUPDATED BETWEEN sysdate-1 AND sysdate

或者使用hint强制走rbo的索引

SELECT /*+rule*/A0.ID, A0.url, A0.MD5HASH, A0.LASTUPDATED FROM ARTURLMD5 A0 WHERE A0.LASTUPDATED BETWEEN :1 AND :2

Sql语句2：

SELECT 'all' TYPE,

SUM(total) total,

SUM(last1day) last1day,

SUM(last1hour) last1hour

FROM (SELECT COUNT(*) total, 0 last1day, 0 last1hour

FROM Article

UNION ALL

SELECT 0 total,

COUNT(*) last1day,

SUM(CASE

WHEN lastupdated > SYSDATE - 1 / 24 THEN

1

ELSE

0

END) last1hour

FROM Article

WHERE lastupdated > SYSDATE - 1)

UNION ALL

SELECT 'art' TYPE,

SUM(total) total,

SUM(last1day) last1day,

SUM(last1hour) last1hour

FROM (SELECT COUNT(*) total, 0 last1day, 0 last1hour

FROM Article

WHERE status IN (1, 2, 4)

UNION ALL

SELECT 0 total,

COUNT(*) last1day,

SUM(CASE

WHEN lastupdated > SYSDATE - 1 / 24 THEN

1

ELSE

0

END) last1hour

FROM Article

WHERE lastupdated > SYSDATE - 1

AND status IN (1, 2, 4))

在1个小时的采样中看出执行了7次，平均执行一次时间以后1990.36秒，而消耗了大量的资源。发现其执行计划中是因为下列sql语句中的：

SELECT COUNT(*) total, 0 last1day, 0 last1hour

FROM Article

SELECT COUNT(*) total, 0 last1day, 0 last1hour

FROM Article

WHERE status IN (1, 2, 4)

分别对article表的列（ID，STATUS）组合索引IDX_ARTICLE_IDSTATUS进行全索引扫描，其索引段达到6G大小，全索引扫描消耗了大量的资源。对于该sql语句执行计划理论上是正确的，从优化上只能看是否存在曾经删除过大量数据，导致该索引上存在大量无用空间，从而重建索引来达到减小该sql语句的消耗，但是如何来判断索引是否需要重建了，这里又是一个值得深入的地方，对sql语句1的传递的绑定变量为什么会引起全表扫描，而开发给的信息是提到传递的绑定变量的波动值比我上面写死的sysdate-1和sysdate还要小，那就更没有必要走全表扫描了。对于使用绑定变量机制导致oracle选择了错误的执行计划这里面具体的机制暂时还是没有理清楚。

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25362835/viewspace-1057786/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25362835/viewspace-1057786/