效能為王：SQL標量子查詢的優化案例分析

本篇整理內容是黃廷忠在“雲和恩墨大講堂”微信分享中的講解案例，SQL優化及SQL稽核，是從源頭解決效能問題的根本手段，無論是開發人員還是DBA，都應當持續深入的學習SQL開發技能，從而為解決效能問題打下根基。

本篇為系列案例之一：標量子查詢優化

以下案例來自於某省電信系統EDW效能優化實踐，資料庫版本為11.2.0.3，執行在ORACLE Exadata一體機上，是個典型的OLAP環境，表上無索引，表無統計資訊。

SQL效能問題診斷

SQL的基本邏輯如下：

SELECTOFFER_SERV_SUM AS N37364,

LOCAL_CODE LOCAL_CODE,

AREA_ID AREA_ID,

DVLP_AREA_ID MG_AREA_ID,

DVLP_ORG_ID ORG_ID,

CASE

WHENOFFER_SPEC_IDIN(SELECTLOCAL_ITEM_CODE

FROMPU_META_DIM.CODE_ITEM

WHERE PROV_TYPE_ID = 49

AND PROV_ITEM_ID = 64) THEN

1ELSE0

END || CASE

WHEN TO_CHAR(OFFER_SPEC_ID) IN

(SELECTLOCAL_ITEM_CODE

FROMPU_META_DIM.CODE_ITEM A

WHERE PROV_TYPE_ID = 49

AND PROV_ITEM_ID IN (85, 86, 68, 69, 70, 71)) THEN

1ELSE 0

END || CASE

WHEN TO_CHAR(OFFER_SPEC_ID) IN

(SELECT OFFER_SPEC_ID

FROMPU_META_DIM.D_DRAGON_PLAN

WHERE BT_CODE = 1

AND (UPPER(NAME) LIKE'%4S%'ORNAMELIKE'%??%')) THEN

1ELSE0

END || CASE

WHEN TO_CHAR(OFFER_SPEC_ID) IN

(SELECT LOCAL_ITEM_CODE

FROMPU_META_DIM.CODE_ITEM

WHERE PROV_TYPE_ID = 49

AND PROV_ITEM_ID = 64) THEN

1ELSE0

END || CASE

WHEN TO_CHAR(OFFER_SPEC_ID) IN

(SELECT OFFER_SPEC_ID

FROMPU_META_DIM.TY_SUIT_CFG) THEN

1ELSE0

END || CASE

WHEN TO_CHAR(OFFER_SPEC_ID) IN

(SELECT OFFER_SPEC_ID

FROMPU_META_DIM.D_DRAGON_PLAN

WHERE BT_CODE = 2

AND (UPPER(NAME)LIKE'%4S%'ORNAMELIKE'%??%')) THEN

1ELSE0

END | C_ALL

FROMPU_BASE_IND.DM_SP_SUB_OFFER_SERV_D_201407

PARTITION(P20140727) A

WHERE LOCAL_CODE = '028'

這個SQL一共格式化出來有600多行，由於篇幅的限制，這裡把其它一些無用的列的資訊去掉。下面來查詢一下SQL的執行計劃：

從上面的關鍵字部分STORAGE FULL FIRST ROWS這部分，我們也可以看到是一個Exadata一體機的環境。

SQL的執行計劃很簡單，一共只有10行。FROM後對一個分割槽表的一個子分割槽執行全分割槽掃描。

下面來看看這個SQL每次執行消耗的物理讀與邏輯讀。

這裡需要關注幾點：

1， 每次執行消耗的物理讀（diskpre exec）

2， 每次執行平均消耗的邏輯讀（getpre exec）

3， 每次執行平均返回的行數(rowspre exec)

這個指令碼的輸出我們還需要計算一下

1， 每次執行SQL，返回的每行平均消耗的邏輯讀338280770/4302704=78.62，大概邏輯讀78才能換回一行。

2， 每次執行SQL，返回的每行平均消耗的物理讀22610/4302704=0.005

從上面幾點，大概知道這個SQL存在效能問題。

基礎資訊分析PU_BASE_IND.DM_SP_SUB_OFFER_SERV_D_201407是個分割槽表，下面查詢一下表分割槽的資訊：

這裡可以看到表是一個RANGE-LIST的分割槽表。下面查詢SQL關聯的表的大小：

由於是分割槽表，所有這裡需要去查詢訪問的分割槽的大小：

這裡看到訪問的分割槽只有84M，加上code_item,ty_suit_cfg,d_dragon_plan表一共才4288+84=4372M，等於 =559616 個BLOCK。

下面我們考慮一種極端的條件下，SQL訪問的幾張表都走全表掃描，並且走HASH連線。那麼此時物理讀加邏輯讀應該接近 559，616 (這裡不考慮TEMP等消耗，不考慮事務一致性等原因，只考慮表的大小)，但是整個SQL消耗的物理都為22610，邏輯讀是：338，280，770。遠遠大於SQL訪問的表佔用的物理大小。所以初步判斷在執行計劃中存在某個物件被輪詢。

下面查詢訪問的分割槽的行數，這裡由於是OLAP系統，無統計資訊，只能手動執行SQL來查詢，如果有統計資訊，可以大概根據統計資訊來計算，雖然不是很準確，但是我覺得足夠我們判斷SQL效能了，何況CBO都是基於統計資訊的。

這裡看到028返回了400W行的資料。

問題定位與SQL改寫通過上面的資訊，可以知道SQL變慢的原因 :

由於PU_BASE_IND.DM_SP_SUB_OFFER_SERV_D_201407 PARTITION(P20140727) 返回一行，就需要去執行所有的標量子查詢，雖然在一體機環境這裡有first rows，但是所有的標量子查詢被輪詢的次數達到了4，302，704次，並且子查詢的表走的全表掃描，所以出現了邏輯讀很高。

邏輯讀比物理讀效能好，並且邏輯讀消耗的時間很短，但是過高的邏輯讀會帶來CPU使用率的增加，RAC環境會導致過多的GC等待，還有可能會影響後來的一些TX,INDEX ITL等等待事件的出現，前不久就曾經遇到一個邏輯讀導致GC等待，又引起了TX,INDEX ITL,BBW等待事件，沒有多久，業務連線池就滿了，最後整個業務受影響。

為了減少標量子查詢被輪詢的次數，這裡可以把標量子查詢改為外連線。

為了減少SQL長度，下面是去掉SELECT中一些不需要的部分，我們手動測試一下SQL改為外連線與原SQL執行SQL的差異：

SELECT OFFER_SERV_SUM AS N37364,

LOCAL_CODE LOCAL_CODE,

AREA_ID AREA_ID,

DVLP_AREA_ID MG_AREA_ID,

DVLP_ORG_ID ORG_ID,

CASE WHEN a.OFFER_SPEC_ID = b.LOCAL_ITEM_CODE THEN 1 ELSE 0 END

|| CASE

WHEN TO_CHAR (a.OFFER_SPEC_ID) = e.LOCAL_ITEM_CODE THEN 1

ELSE 0

END

|| CASE

WHEN TO_CHAR (a.OFFER_SPEC_ID)= c.offer_spec_id

AND c.bt_code = 1

THEN

1

ELSE

0

END

|| CASE WHEN a.OFFER_SPEC_ID = b.LOCAL_ITEM_CODE THEN 1 ELSE 0 END

|| CASE

WHEN TO_CHAR (a.OFFER_SPEC_ID) = d.OFFER_SPEC_ID THEN 1

ELSE 0

END

|| CASE

WHEN TO_CHAR (a.OFFER_SPEC_ID)= c.offer_spec_id

AND c.bt_code = 2

THEN

1

ELSE

0

END

C_ALL

FROM PU_BASE_IND.DM_SP_SUB_OFFER_SERV_D_201407PARTITION (P20140727) A,

(SELECT DISTINCT LOCAL_ITEM_CODE

FROMPU_META_DIM.CODE_ITEM

WHERE PROV_TYPE_ID = 49 AND PROV_ITEM_ID = 64) b,

(SELECT DISTINCT OFFER_SPEC_ID, bt_code

FROMPU_META_DIM.D_DRAGON_PLAN

WHERE BT_CODE IN (1, 2)

AND (UPPER (NAME) LIKE '%4S%' ORNAME LIKE '%??%')) c,

(SELECT DISTINCT OFFER_SPEC_ID FROM PU_META_DIM.TY_SUIT_CFG) d,

(SELECT DISTINCT LOCAL_ITEM_CODE

FROM PU_META_DIM.CODE_ITEM A

WHERE PROV_TYPE_ID = 49 AND PROV_ITEM_ID IN (85, 86, 68, 69, 70, 71)) e

WHERE LOCAL_CODE = '028'

AND a.OFFER_SPEC_ID = b.LOCAL_ITEM_CODE(+)

AND TO_CHAR (a.OFFER_SPEC_ID) = c.OFFER_SPEC_ID(+)

AND TO_CHAR (a.OFFER_SPEC_ID) = d.OFFER_SPEC_ID(+)

AND TO_CHAR (a.OFFER_SPEC_ID) = e.LOCAL_ITEM_CODE(+)

執行計劃及統計資訊：

這裡可以看到所有的表都是通過HASH 來做UNIQUE

這裡看到邏輯讀是303475，行返回是4，302，704，不知道大家是否還記得在之前我們獲取SQL的統計資訊的時候，看到SQL每次執行平均換回的行數是4，302，704。

兩個值是一樣的，說明我們在此條SQL改寫後是等價的。

這裡用到了”此條”，因為如果在連線列有一些空值的情況下得到的結果可以不一樣，大家可以測試一下。

效能優化效果對比下面看看原SQL去掉SELECT不需要的部分執行的資訊

SELECT OFFER_SERV_SUM AS N37364,

LOCAL_CODE LOCAL_CODE,

AREA_ID AREA_ID,

DVLP_AREA_ID MG_AREA_ID,

DVLP_ORG_ID ORG_ID,

CASE

WHEN OFFER_SPEC_ID IN

(SELECT LOCAL_ITEM_CODE

FROMPU_META_DIM.CODE_ITEM

WHERE PROV_TYPE_ID = 49AND PROV_ITEM_ID = 64)

THEN

1

ELSE

0

END

|| CASE

WHEN TO_CHAR (OFFER_SPEC_ID) IN

(SELECT LOCAL_ITEM_CODE

FROMPU_META_DIM.CODE_ITEM A

WHERE PROV_TYPE_ID = 49

AND PROV_ITEM_IDIN (85, 86, 68, 69, 70, 71))

THEN

1

ELSE

0

END

|| CASE

WHEN TO_CHAR (OFFER_SPEC_ID) IN

(SELECT OFFER_SPEC_ID

FROMPU_META_DIM.D_DRAGON_PLAN

WHERE BT_CODE = 1

AND ( UPPER (NAME) LIKE '%4S%'

OR NAME LIKE'%??%'))

THEN

1

ELSE

0

END

|| CASE

WHEN TO_CHAR (OFFER_SPEC_ID) IN

(SELECT LOCAL_ITEM_CODE

FROMPU_META_DIM.CODE_ITEM

WHERE PROV_TYPE_ID = 49AND PROV_ITEM_ID = 64)

THEN

1

ELSE

0

END

|| CASE

WHEN TO_CHAR (OFFER_SPEC_ID) IN

(SELECT OFFER_SPEC_ID FROMPU_META_DIM.TY_SUIT_CFG)

THEN

1

ELSE

0

END

|| CASE

WHEN TO_CHAR (OFFER_SPEC_ID) IN

(SELECT OFFER_SPEC_ID

FROM PU_META_DIM.D_DRAGON_PLAN

WHERE BT_CODE = 2

AND ( UPPER (NAME) LIKE '%4S%'

OR NAME LIKE'%??%'))

THEN

1

ELSE

0

END

C_ALL

FROM PU_BASE_IND.DM_SP_SUB_OFFER_SERV_D_201407 PARTITION (P20140727) A

WHERE LOCAL_CODE = '028'

這條語句，執行了30分鐘，都沒有任何行返回：

可以看到執行了27分鐘，才處理1908315，接近1/2的結果集行數。如果要處理完，估計需要60分鐘以上。

總結下面來總結一下此案例：

1. 在標量子查詢中，當主查詢返回一行資料時，所有的標量子查詢就要執行一次，如果在連線列有索引時，標量子查詢在主表返回的行很少的情況下，對效能影響不大，常常出現在OLTP環境，並且連線列一般都有索引；如果在OLAP環境中，看到標量子查詢千萬要小心，通常，主表返回的行很多，並且子查詢中的表通常在連線列上面無索引，導致效能很低下，本案例就是這種情況；

2. 平均每次執行時消耗的邏輯讀、物理讀，返回的行平均消耗的邏輯讀、物理讀，平均返回的行數等資訊可以用於初步判斷SQL是否存在效能問題；

遇到這種標量子查詢，就得修改SQL，也就意味著業務需要修改程式碼，像電信運營商這種環境可能要好一點，有專門的開發團隊，但是估計提交修改申請、開發修改、業務測試上線，差不多也要1到2個月的時間，如果在一些小的環境，估計開發商都找不到了，就跟談不上改業務了。

12c新特性改進對於類似以上的情況，在Oracle Database 12C中，優化器已經可以自動實現等價改寫，但是需要注意的在12.1.0.2版本中有BUG，可能導致結果集不準確。

在12C中，標量子查詢自動改寫的功能由隱含引數 _optimizer_unnest_scalar_sq 控制，預設是TRUE，意味著開啟，如果遇到BUG或者效能問題，可以更改為FALSE。

 

About Me

....................................................................................................................................................

本文來自於微信公眾號轉載文章，若有侵權，請聯絡小麥苗及時刪除

ITPUB BLOG：http://blog.itpub.net/26736162

QQ：642808185 若加QQ請註明您所正在讀的文章標題

【版權所有，文章允許轉載，但須以連結方式註明源地址，否則追究法律責任】

....................................................................................................................................................

 

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26736162/viewspace-2080676/，如需轉載，請註明出處，否則將追究法律責任。