跑批SQL效能異常分析

跑批是序列進行的，同時伴隨著線上業務，整個跑批過程中只卡在SQL（ 6hqva0h4awrxh ）執行環節上，相關等待事件為gc current grant 2-way，其他環節及線上業務都不受影響，主機整體資源較為空閒。最後透過重建問題SQL涉及物件的索引解決。

故障時間段的awr、ash效能報告和資料庫hanganalyze全域性dump資訊：

如截圖所示並沒有出現異常指標及全域性的效能問題，種種跡象表明資料庫整體負載較為正常。問題只出現在單個

從 systemdump  該會話的資訊可以看出並沒有任何會話堵塞，歷史的等待一直為 gc current grant 2-way 。讀取的物件是 4 號資料檔案 919380 塊，其中 4 號資料檔案正是 undotbs ，說明會話程式讀取資料塊前映象，大量 DML 操作在同一個物件上發生。

說明：

gc current grant 2-way ：這個等待事件說明當前例項向主節點申請了一個 current 塊，而且這個申請已經被主節點響應，其中並沒有出現超時。但是，這個被申請的資料塊不包含在任何例項的資料庫緩衝區中，它是需要申請例項從資料檔案讀取出來的，當 current grant 2-way 的等待事件很多，並且消耗了很多等待時間的話，可能的原因如下：

原因 1 ：網路頻寬

原因 2 ：應用程式的角度來講，導致大量 current/cr grant 2-way 等待事件的原因如下：

2.1 ：某些 SQL 語句的執行計劃出現了問題

2.2 ：資料庫緩衝區被設定過小，這導致很多資料被頻繁地寫入資料檔案

2.3 ：檢查點過於頻繁。例如： fast_start_mttr_target 設定的過短

其中原因 1 透過 osw 系統監控被排除， 2.2 和 2.3 可能性也不大。剩下 2.1 ，我們看一下問題 SQL 語句：

update Bs_account_jnls_tax
  set status = '04'
where status = '00'
  and acc_date = to_date('2024-02-10', 'yyyy-mm-dd')
  and trans_jnls_no in
      (select distinct trans_jnls_no
         from jn_batch_result
        where batch_no in ('75110058', '75110068')
          and acc_date = to_date('2024-02-10', 'yyyy-mm-dd'))

一條簡單的 update 語句使用非繫結變數，以下為記憶體中的執行計劃：

由於使用非繫結變數，CBO計算存在誤差，實際和看到的執行計劃存在不一致的現象。（實際的執行計劃需要透過10053單獨跟蹤驗證），這也驗證了為什麼現場工作人員透過重建索引改變執行計劃來解決問題。

總結

透過上述分析判斷，最主要的問題還是 SQL 語句本身未使用繫結變數導致執行計劃不穩定，執行效率低所導致。

解決方法：

1.  規範開發 SQL 語句編寫統一使用繫結變數

2. 評估 Bs_account_jnls_tax 表大小， keep 到 buffer   cache 記憶體中，或問題 SQL 前先執行 select count(*) from  trans_jnls_no / Bs_account_jnls_tax

3. 如果建議 1 應用短時間內無法變更，再次出現故障時可以嘗試從 shared pool 中手動刷出去執行計劃重新生成新的，方法如下：

查詢對應 sql 的 address 和 hash value

select s.SQL_TEXT, s.ADDRESS || ',' || s.HASH_VALUE from v$sqlarea s where sql_id = '10kq6nc2rfrf0' ；

exec sys.dbms_shared_pool.purge('0000000141AE1310,91708864','c');

注： 0000000141AE1310,91708864 為 s.ADDRESS || ',' || s.HASH_VALUE 的值