資料庫優化之臨時表優化

 

業務 SQL 效能問題

資料庫整體效能正常，某條 sql 前臺執行緩慢，後天執行正常。

某客戶的業務系統區域性出現效能問題，某條 SQL 語句前臺執行緩慢，後臺執行很快，根據此現象深度排查，由於臨時表所引起，新增所有後恢復正常。

經過檢查 AWRSQRPT （執行 @?/rdbms/admin/awrrpti.sql 產生報告）發現業務系統效能主要是由一條 SQL 引起，如下所示：

具體的 SQL 顯示如下：

可以看到這條 SQL 執行一次需要 406814ms ，但執行計劃顯示確實相當完美（ cost 不大，每一步驟執行時間很短），顯示如下：

但是實際執行統計資訊確相當糟糕，尤其 buffer gets 讀取很高，達到了 595764242 ，顯示如下：

根據執行計劃顯示，最大的一張表 GL_VERIFY_LOG 才 17710 條記錄，至於其它表的資料量更加可以忽略不計。在規模如此小之下，出現這麼高的 buffer gets 數量是及其不正常的。於是猜測，執行計劃資訊和實際執行情況不符合可能是由於資料庫物件的統計資訊不準引起的，於是對 SQL 涉及到的物件進行了取樣 100% 的統計，但是現象依舊。

由於這條 SQL 沒有繫結變數，不會出現 bind peeking 現象（即執行計劃不穩定）。所以奇怪的是，這條 SQL 每次後臺執行很慢，但前臺執行很快，執行時間幾乎不用 1 秒，而且後臺執行時顯示的執行計劃和 AWR 報告中的執行計劃一樣。於是懷疑是不是中介軟體和網路出現了問題？檢查發現中介軟體和網路不存在問題。

進行到這裡，本次效能優化暫時碰到了難題。但是我注意到了一個細節。由於之前我對 SQL 涉及到的物件進行了取樣 100% 的統計，但後臺執行的執行計劃出現了動態取樣（ Dynamic Sampling ）。顯示如下：

根據這一現象，推測該 SQL 中存在臨時表，因為 Oracle 生成臨時表的執行計劃時會進行動態取樣。經過檢查， ASSTEMPORA 為臨時表，前臺程式在執行時載入了大量的資料，進而導致前臺執行很慢，所以執行期間產生大量 buffer get 也是正常的。而我們在後臺執行時，由於臨時表的資料為空，所以執行起來很快。於是在臨時表 ASSTEMPORA 中增加了一條索引，業務模組效能恢復正常：