1        相遇是緣

---

star童鞋最喜歡的西安美食之一“三秦套餐”剛上來，突然電話響了，某核心BOSS系統資料庫異常緩慢，做為專業小’DBA’er,第一時間趕赴現場解決問題。。。。。。

趕到現場時SQLPLUS已無法登陸，資料庫hang住，客戶反應部分業務已受影響。

透過top命令檢視資源使用情況，有2個oracle 客戶端程式CPU使用率100%,經與客戶確認，結束這2個程式後資料庫恢復正常。

透過檢視資料庫故障時間歷史會話檢視，進行問題定位與分析，

select * from dba_hist_active_sess_historyy where y.sample_time >= trunc(sysdate)+9/24 and y.sample_time <=trunc(sysdate)+13/24；

統計分析線上活動程式：

 透過分析歷史會話表中出現異常的資料庫程式資訊，

步驟1： sid: 118,event:enq: US – contention鎖定批次資料庫程式

步驟2：檢視程式118，發現118程式被sid:170，event: DFSlock handle鎖定

步驟3：Sid: 170進各沒有被阻塞

步驟4：在170執行前，sid:97號程式，執行語句：1234567896u1長時間未結束，造成資料庫批次row cache lock，資料庫執行環境惡化

步驟5：最終170成為觸發條件，造成批次程式互鎖，資料庫HANG。。。。

鎖定異常語句1234567896u1，相遇是緣。

 

2        初識SQL君

---

檢視資料庫AWR報告

sql語句：1234567896u1每執時一次7413秒，2個小時！！！

檢視執行計劃

Coat: 1445，從執行計劃看該語句,未發現嚴重效能問題，語句執行不應該如此慢。

 

    

   

        

3        山窮水盡

---

檢視語句：

--類似以下語句，由於安全問題，源語句較複雜無法提供，以下語句僅供參考

SELECTCOUNT(*) d, 0 q

  FROM aaa A, bbb B, ccc C

 WHERE A.aa = B.b

   ANDSUBSTR(B.bb, 6) = C.cc

   AND A.sj BETWEEN to_date('20170301','yyyymmdd') AND to_date('20170331','yyyymmdd')  AND C.c IN (1, 2, 3,4, 5, 6, 7)

  AND A.a = 'ddd'

該語句邏輯較為簡單，由於未使用繫結變數，在sqlplus中再次執行，執行結果0.01秒就出來。

懷疑1、是否由於快取軟解析的原因，修改日期再次執行

SELECTCOUNT(*) d, 0 q

  FROM aaa A, bbb B, ccc C

 WHERE A.aa = B.b

   ANDSUBSTR(B.bb, 6) = C.cc

   AND A.sj BETWEEN to_date('20170301','yyyymmdd') AND to_date('20170331','yyyymmdd')  AND C.c IN (1, 2, 3,4, 5, 6, 7)

  AND A.a = 'ddd'

執行結果還是0.01秒出來。

懷疑2、是否該語句在執行時有大批次資料變化

透過檢視AWRSQL、檢視儲存過程與業務溝通均未發現異常。

懷疑3、是否為OracleBug

非常簡單的語句，客戶端執行很快，程式執行異常緩慢，是否為Oracle Bug！！！

有點山窮水盡的感覺。

4  峰迴路轉

---

決定細緻化深入分析AWRSQL,發現存在 TABLE ACCESS FULL等待事件，說明 b表存在全表掃描，檢視A 、B、C表數分佈情況

SQL> set line999

SQL> selectt.OWNER, t.TABLE_NAME, t.NUM_ROWS, t.LAST_ANALYZED, t.PARTITIONED

  from dba_tables t where t.TABLE_NAME in ('a','b', 'c')

檢視A表資料分佈情況，

SELECTCOUNT(*) d, 0 q

  FROM aaa A, bbb B, ccc C

 WHERE A.aa = B.b

   ANDSUBSTR(B.bb, 6) = C.cc

   AND A.sj BETWEEN to_date('20170301','yyyymmdd') AND to_date('20170331','yyyymmdd')

   AND C.c IN (1, 2, 3, 4, 5, 6, 7)

  AND A.a = 'ddd'

結合資料量分析sql語句，A表資料量40W,A表有兩個條件sj日期型別、a欄位字元型別，檢視SQL語句對欄位sj、a資料的抽取率，

表A資料總量42W,

SQL> selectcount(1) from a.a;

 

  COUNT(1)

----------

    420474

 

     欄位sj日期型別，抽取資料1827

    SQL> select count(1) from a.a a where A.aBETWEEN to_date('20170301','yyyymmdd')ANDto_date('20170331','yyyymmdd');

 

 COUNT(1)

----------

     1827

統計資料抽取比例0.43%,資料抽取比例較好，但需要抽取1827

SQL> selectround(1827/420474,4) from dual;

 

ROUND(1827/420474,4)

--------------------

               .0043

檢視2個欄位同時使用，資料抽取比例，只抽取4條記錄

SQL> selectcount(1) from aaa.a a where A.a BETWEEN to_date('20170301','yyyymmdd')ANDto_date('20170331','yyyymmdd')and  A.b = '111';

 

  COUNT(1)

----------

         4

峰迴路轉是否可以透過新增索引最佳化。

5        柳岸花明

---

   為表A建立聯合索引最佳化後執行計劃

 

索引最佳化前cost:1445

 

  索引最佳化後cost: 59

,

檢視執行計劃全表掃描變為索引掃描，資料庫評估效能提升20倍以上，實際最佳化效果還有待進一步測試。

預估實際執行效果效能提升200倍以上，欲知實際執行效果，請見後續更新。