被Oracle全域暫存資料表坑了

被Oracle全域暫存資料表坑了

   今天淩晨4點多鐘，在客戶現場的負責人打電話給我，說很奇怪，下載功能時快時慢。此下載功能非常複雜，之前一直是我最佳化，在半夢半醒中開啟電腦，通過遠程看著現場同事在PL/SQL developer中操作。執行同一條SQL，時快時慢，快的時候大概0.6s，慢的時候超過1分鐘。

    這條SQL有調用一個函數，功能是動態產生接近200條查詢語句，SQL中都是有綁定變數的。是現場的測試環境，剛剛部署，心想應該不是資料庫負載所致。

    1. 抓取資料庫AWR報告，完全沒有壓力，資料庫伺服器配置都是杠杠的。此刻心裡有點亂，頭一次遇到這種問題。現場9點鐘要跟客戶示範，此時已經快5點鐘了。

    2. 神器出場，打算用10046 trace定位到到底是那條SQL有問題，trace了多次，只有一次是慢的。期間也有插曲，現場不太會用sqlplus,互動化了很多時間。從眾多的SQL中抽絲剝繭，終於定位到SQL，對比是:

SELECT DISTINCT D.ID, D.TABLE_NAME, DCT.COLUMN_NAME, GG.DATA_TYPE,
  GG.TECHPARAM_NAME,DCT.SORT_NO FROM GG_CLASSIFY_TECHPARAM DCT, GG_TECHPARAM
  GG, GG_CLASSIFY D, REL_OID_CLASSIFY T WHERE DCT.TECHPARAM_ID = GG.ID AND
  D.ID = DCT.CLASSIFY_ID AND T.CLASSIFY_ID = D.ID
call    count      cpu    elapsed      disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00      0.00          0          0          0          0
Execute      1      0.00      0.00          0          0          0          0
Fetch        2    61.00      61.04          0  25968917          0        156
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3    61.00      61.04          0  25968917          0        156

call    count      cpu    elapsed      disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00      0.00          0          0          0          0
Execute      1      0.00      0.00          0          0          0          0
Fetch        2      0.80      0.81          0      32461        0        156
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.80      0.81          0      32461        0        156

  3. 分析問題，第一感覺是SQL邏輯是否有問題，可惜10046裡面沒有trace到執行計畫，不過看邏輯讀，慢的那次應該是產生了笛卡爾積。經過簡單的檢查，SQL邏輯沒有問題，人的第一感覺不一定靠譜。

  4. 我在想是什麼導致執行計畫不準呢，猛然想起REL_OID_CLASSIFY是全域暫存資料表，快速的想到一種可能，REL_OID_CLASSIFY的統計資訊不準導致，通過user_tables查看這張表是沒有統計資訊的。那就是每次執行都動態採集囉，在Oracle 11g中執行autotrace，發現level=2,我想試試把動態採樣的層級，說幹就幹。

 SELECT /*+ dynamic_sampling(T 10) */DISTINCT D.ID, D.TABLE_NAME, DCT.COLUMN_NAME, GG.DATA_TYPE,
  GG.TECHPARAM_NAME,DCT.SORT_NO FROM GG_CLASSIFY_TECHPARAM DCT, GG_TECHPARAM
  GG, GG_CLASSIFY D, REL_OID_CLASSIFY T WHERE DCT.TECHPARAM_ID = GG.ID AND
  D.ID = DCT.CLASSIFY_ID AND T.CLASSIFY_ID = D.ID;

        發給開發人員，修改相關函數。增量後，多次測試後發現問題解決了。此時已經快7點了，天已經大亮，我有點倦意，但無法再次入睡。

      總結：對於這次暫存資料表的問題，我想問題在於採樣率低了以後造成的惡果。對於全域暫存資料表要注意兩點，一是要鎖定暫存資料表收集統計資訊的功能，因為你收集的統計資訊肯定是錯的；二是使用它時最好是使用動態採用。學習知識，基礎很重要。

在CentOS 6.4下安裝Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虛擬機器中安裝步驟

Debian 下 安裝 Oracle 11g XE R2