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選擇了錯誤的執行計劃這裡面具體的機制暫時還是沒有理清楚。