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

原文地址：https://blogs.oracle.com/Database4CN/entry/%E5%8F%AA%E5%AF%B9%E6%9F%90%E4%B8%AA%E7%89%B9%E5%AE%9A%E7%9A%84sql%E8%AF%AD%E5%8F%A5%E5%BC%80%E5%90%AF10046_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語句讀取物理塊時花費了更多的時間。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/8520577/viewspace-2122028/，如需轉載，請註明出處，否則將追究法律責任。