只對某個特定的SQL語句開啟10046 trace

只對某個特定的SQL語句開啟10046 trace

最近碰到了這樣一個有趣的問題: 有一條SQL語句,大部分時間它的執行時間是幾十個毫秒; 但是偶爾某次的執行時間會長於2秒鐘。因為應用對這個語句的執行時間非常的敏感，我們必須診斷是因為什麼原因導致它偶爾執行時間長於2秒。

這個問題為什麼會有挑戰性呢？因為我們很難收集慢的時候的10046 trace：首先我們不知道這個問題什麼時候會發生，也不知道會在哪個session裡發生。如果對所有的session全天開啟10046 trace, 會產生很多比較大的trace並影響資料庫整體的效能。

好在這個資料庫是11g的，在11g中event++的特性允許我們只對某個特定的SQL收集10046 trace. 即在運行這條SQL時開啟10046 trace,在這條SQL運行完之後關閉10046 trace.這樣可以顯著的降低產生的trace的大小。但是因為我們無法確定哪個session會產生問題，所以只要運行過這個SQL的session都會產生一個trace檔案。

開啟的步驟是(要把下面的awsh60c8mpfu1替換成那條SQL的SQL_ID)：

alter system set events 'sql_trace [sql: awsh60c8mpfu1] level 12';

而關閉的步驟是(要把下面的awsh60c8mpfu1替換成那條SQL的SQL_ID)：

alter system set events 'sql_trace [sql: awsh60c8mpfu1] off';

在收集到很多10046 trace，並使用tkprof格式化後（需指定AGGREGATE=NO,這樣tkprof會對每一次執行都產生匯總報告），我們最後定位到了問題發生時SQL語句讀取物理塊時花費了更多的時間。