360
【雲和恩墨大講堂】Oracle線上嘉年華第二講
編輯手記：Oracle線上嘉年華，正在持續分享中。本次的主題是係統割接中的SQL解析問題和結合業務的SQL優化改寫技巧。
1嘉賓介紹小魚（鄧秋爽）
雲和恩墨專家，有超過5年超大型數據庫專業服務經驗，擅長oracle 數據庫優化、SQL優化和troubleshooting
新係統割接的library cache問題
這是我們在做係統割接的時候的一個案例，可能並不是很常見，這個案例是將Oracle 11g升級到12c的時候遇到的問題，出現了大量的library cache的問題。具體情況是：
新係統割接後，不定時出現大量library cache lock、library cache:mutex X，幾分鍾後係統自動恢複。
在短暫的時間裏，我們來不及做systemdump的，而且出現的頻率和時間也是不固定的，很難抓取到當時係統的信息。
先獲取故障時段的AWR報告：
我們看到這兩個等待事件已經占了整體DB time的72%，大家看到這個問題，可能一般都會想到解析，
我們從以下幾個角度分析：
TopSQL的解析、執行頻率是否合理：
故障點的library lock相關的SQL來看，基本占據db time、parse閾值高的Top SQL解析、執行頻率並沒有數量級的增加，他們更像是受害者。
Oraclebug是否存在：
係統版本Oracle 12.1.0.2，經過原廠排查並不存在相關bug引起。
SQL解析是否存在問題，綁定變量使用分析：
查看awr報告硬解析次數很高，但是挖掘sharedpool發現係統中並未發現大幅度未使用綁定變量的SQL。
在oracle 10g的時候，V$SQLAREA視圖有一個FORCE_MATCHING_SIGNATURE 參數，可以將SQL經過綁定變量代替後生成一個hashvalue值，通過這個值找到未使用綁定變量的SQL，而開發商的SQL的質量比較高，並未發現核心業務SQL未使用綁定變量的情況。
這樣看來，這個問題是很棘手的，硬解析次數很高，但我們找不到對應的SQL在哪裏。
我們接著分析，來看AWR報告裏麵的time model statistic
我們看到紅色標記的部分，解析時間消耗了63.74%、解析失敗消耗了50.55%。
解析失敗是什麼？Oracle的解釋是這樣的：
failed parse：語法、權限等無法執行的SQL解析,也是硬解析，並且解析失敗是不能被重用的，當然它也不會存儲在V$SQLAREA視圖中，所以也挖掘不到這類SQL。
我們如何去發現在係統中解析失敗的SQL呢？
Oracle提供了event 10035，會將解析失敗的SQL記錄到alert 日誌裏麵
從上麵的日誌可以看到各種解析錯誤的代碼，其中error=942，表示：表不存在,因此判斷這是他們做係統變更的時候做過一些表的刪除，我們可能在係統割接的時候都會做一些舊表的drop或者rename，這時候一定要嚴格挖掘應用端的代碼，將下線的業務代碼停掉，避免錯誤解析導致數據庫出現嚴重的性能問題。
SQL優化改寫技巧
接下來和大家分享執行計劃結合業務邏輯的一個等價改寫的例子
案例中的SQL如上，大致由兩部分組成，上下各是一個標量子查詢，然後用union all聯合在一起做了一個order by，在結果顯示中使用了分頁。
我們通過腳本獲得該SQL單次邏輯讀將近18000000.返回行數為10行，響應時間達到104036MS。
這是個很複雜的SQL，包含標量子查詢、表連接、unionall、排序、分頁，還有一些複雜的decode、nvl等函數，通過awr報告我們得知該SQL單次執行需要1500多萬到1900多萬的邏輯讀，平均都隻返回10行數據，單次執行時間也要100秒左右。
我們可以將SQL簡化如下：
select * from (select row_.*, rownum
rownum_ from
(SELECT ST.* FROM (Select …TO_CHAR(T.SIGNDATE,
‘YYYYMMDDHH24:MI:SS’) AS SIGNDATE …(標量子查
詢1）、(標量子查詢2）... From
MM_MK_CUSTMGR_SIGN T WHERE T.REGION = 14
Union all
Select …TO_CHAR(T.SIGNDATE,
'YYYYMMDDHH24:MI:SS') AS SIGNDATE …(標量子查詢
1）、(標量子查詢2）... From MM_MK_CUSTMGR_SIGN T
WHERE T.REGION = 14
AND T.SIGNOUTOID IS NOT NULL) ST
ORDER BY SIGNDATE DESC) row_ where rownum <= :1)
where rownum_ > :2
對於這種複雜的SQL，我們先看執行計劃
這個執行計劃我們做過相應刪減。
在優化SQL中，我們優先考慮能否優化cost高的步驟，比如大表全表掃描、大表全索引快速掃描、跳躍索引掃描、大表排序等cost消耗；
其次看filter(優化了的nestedloop）、nested loop、hash join、笛卡爾積等表關聯步驟cost消耗。
在上麵的標量子查詢中，Cost消耗最高的在這個view操作，COST消耗達到了14M、rows達到了501K，而這個view是由兩部分union all組成的。
在下麵的標量子查詢中，兩部分union all發現上層部分主查詢MM_MK_CUSTMGR_SIGN T估算返回501k Rows,下層主查詢則隻有1Rows數據。
注：在Oracle的估算中是不存在0 Rows的情況，如果評估的結果是0，會算作1.
對於標量子查詢，我們簡單做個介紹，就是說優化器在這種情況下永遠隻做一種操作就是filter，這是一種變相優化nest loop。對於這種標量自從查詢，我們知道其實SQL之所以出現問題是因為下麵的501k導致需要驅動上麵那堆複雜的標量子查詢，
那麼如何優化呢？
常規優化：對於標量子查詢，可以使用等價改寫為表的外連接方式讓其走hash jion的執行計劃，但是如果標量子查詢中有大表則並不合適，該SQL恰恰包含大表，並不適合用常規的等價外連接的方式來改寫。
業務結合執行計劃分析：那麼這個ORDER BYSIGNDATE DESC排序後rownum能否推進到主查詢MM_MK_CUSTMGR_SIGN T表中，rownum限製後再去驅動標量子查詢，減少標量子查詢的循環次數。
接下來主要針對第二種，結合業務進行分析改寫。
在上麵的SQL中，是先取501k數據做了驅動，然後再做標量子查詢和order by的操作，我們能不能把order by的操作推回到標量子查詢前麵，這樣子的話標量子查詢要驅動的隻是前麵排序取rownum限製條件的數據，我們通過畫圖的方式來分析一下：
首先是兩個同樣的表，做了標量子查詢的操作，這裏的數據是501k,然後標量子查詢完了之後，做了order by後rownum的限製，這是原SQL的執行業務邏輯。
我認為應該寫成這樣，我們想限製標量子查詢的循環次數，那我們就先去對主查詢取order by排序rownum限製後的數據，再將主查詢取出來的這部分數據去驅動標量子查詢，做完後再做一次order by rownum的限製。（這裏並不會改變SQL的業務邏輯，雖然我們是先排序取rownum限製了，但是標量子查詢時主查詢是先排序還是後排序取rownum限製對於主查詢返回結果集沒有任何影響）
根據這種思路，我把SQL改寫如下：
SELECT * FROM
(SELECT row_.*, rownum rownum_ FROM
(SELECT ST.* FROM
(SELECT …TO_CHAR(T.SIGNDATE,
‘YYYYMMDDHH24:MI:SS’) AS SIGNDATE …(標量子查詢1）、(標
量子查詢2）... FROM
(SELECT * FROM tbcs.MM_MK_CUSTMGR_SIGN x
WHERE x.REGION = 14
ORDER BY SIGNDATE DESC offset :2 rows
FETCH NEXT :1 rows only) T
UNION ALL
SELECT …TO_CHAR(T.SIGNDATE,
'YYYYMMDDHH24:MI:SS') AS SIGNDATE …(標量子查詢1）、(標
量子查詢2）... FROM
(SELECT * FROM tbcs.MM_MK_CUSTMGR_SIGN x
WHERE x.REGION = 14
ORDER BY SIGNDATE DESC offset :2 rows
FETCH NEXT :1 rows only ) T
) ST ORDER BY SIGNDATE DESC) row_
WHERE rownum <= :1)
WHERE rownum_ > :2
其中紅色部分是12C的改寫方式。是用一個分析函數的方式去做的。
它的執行計劃如下：
先訪問表MM_MK_CUSTMGR_SIGN排序取rownum限製（前10行數據後），再去驅動那堆複雜的標量子查詢，最後再次排序取rownum條件數據，邏輯讀從千萬級降低到了26661。
這個SQL在改寫後，資源消耗降低了許多，基本上能夠滿足業務的需求。
如果我們再去剖析原SQL代碼，發現union all部分是同一個MM_MK_CUSTMGR_SIGN表的查詢，下麵那個UNION ALL部分查詢出來的結果是上麵UNION ALL部分的子集。
而跟研發溝通發現實際上union all的下層查詢可以去掉,去掉後則該SQL無需改寫rownum就可以直接推進到主查詢中，從這個例子可以看到不嚴謹的代碼容易造成性能隱患，影響優化器評估最合理的執行計劃。
通過以上分享，我們得出：
1、線上係統變更、表下線需要嚴格挖掘應用端代碼，避免因為表結構變更導致SQL解析錯誤。
2、複雜業務邏輯對應的SQL需要核查，對於不需要的結果和表關聯等盡可能去掉，簡化表關聯數量，合理利用優化器。
文章轉自數據和雲公眾號，原文鏈接
最後更新：2017-07-18 20:36:29