前言：

為了加快SQL語句的執行速度,你都用過哪些辦法呢?我常常聽到不少DBA總結道：“調優也沒什么難的,常用的不就那三板斧嗎：

1. 收集表的統計信息；

2. 給執行計劃中產生全表掃的表加索引；

3. 再不行就給語句加上并行…”

每每聽到這樣的說法,我倒也無法直接反駁,上面說的辦法確實是我們常用的辦法,但是,我深知其中涉及的知識遠不止口頭上說的那么簡單,而是在深刻分析后得出的輕巧.

比如,如果我們看到下面的執行計劃,你會覺得這里的問題大概在哪里呢?為何執行效果還是那么不如人意呢?

看完,我想有很多有經驗的DBA一定在說,這個一看就能知道問題是在哪?而如果我告訴你這其中的問題也許并不在你以往的經驗范圍中時,你會打算如何進一步分析呢?

今天我們來看看CESOUG成員卞卞的分享,看他是如何來一步步分析這個問題的!如果你也曾輕易的認為,如果SQL執行緩慢,不管原因先收集統計信息,加并行,加索引總應該就能辦理的話,也許這篇小文能稍稍改變你的觀點；

問題來了

剛到客戶現場,客戶就把問題拋了出來.

“小卞工,我們批量的一個語句跑了十幾個小時了,還是跑不完啊!“

”你們都干了什么,原來也這么慢嗎?“

“原來也比較慢,我們想加快一下速度,于是照著網上的辦法,加了索引,收集了統計信息,還加了并行,原本以為速度會大大加快的,沒想到反而變慢了!”

“好吧!看來三板斧砍到了自己腳上了.”

先確認環境如下：

ORACLE 11.2.0.4 RAC (兩節點)

嗯,看起來配置還不錯,看看問題是出在哪吧.

問題在哪里

SQL語句跑的慢,那自然先收一個AWRSQLRPT來看看,具體語句如下：

SELECT

...........

FROMXXX_ STMT _X T1

LEFTJOIN(SELECT P.ACCOUNT AS XACCOUNT,

P.MONTH_NBR,

... AS SHOPPING_POINT

FROMXXX_PTLOG P

WHERE P.CREATE_DATE <=SYSDATE+1

AND P.CREATE_DATE > ADD_MONTHS(SYSDATE,-12)

GROUPBY P.ACCOUNT, P.MONTH_NBR) T2

ONT1.XACCOUNT = T2.XACCOUNT

ANDT1.MONTH_NBR = T2.MONTH_NBR

LEFTJOINXXX_STMT_X T3

ONT1.XACCOUNT = T3.XACCOUNT

ANDT1.MONTH_NBR -1= T3.MONTH_NBR

ANDT3.STMT_DATE <= ADD_MONTHS(SYSDATE,-1)

ANDT3.STMT_DATE > ADD_MONTHS(SYSDATE,-13)

WHERET1.STMT_DATE <=SYSDATE+1

AND T1.STMT_DATE > ADD_MONTHS(SYSDATE,-12)

語句特征

1.可以看語句涉及兩張表：XXX_STMT_X(別名分別是為T1、T3),XXX_PTLOG(別名為T2)

2.兩個表在查詢條件中都涉及一個時間字段的過濾條件(T1、T3的STMT_DATE字段,T2的CREATE_DATE字段)

3.T1表分別與T2和T3存在等值關聯；

接下來看SQL語句的執行情況統計：

可以看到SQL的CPU時間總計超過100萬秒,邏輯讀達到了26G*8K即將近200個T!

這樣看來,大部分執行時間都在CPU上,而非等到上,可以確定執行計劃存在問題的!再具體看看執行計劃：

看上去,問題就簡單了,對表XXX_STMT_X(T1)在過濾后評估行數為1,因此這里使用了nested loop的方式與T3關聯,時間與邏輯讀主要就花費在這里了；

以我的經驗來看,如果這里XXX_STMT_X表與自己關聯使用hash join的方式,再與表XXX_PTLOG表進行hash join；整個執行過程就不會太長；

我們對XXX_STMT_X表進行查詢后得知,表XXX_STMT_X有1900+W行記錄,應用過濾條件后數據大概也有一半數據量900+萬記錄,怎么到這里優化器評估這里的記錄條數為1了呢?看起來,問題好像也沒有什么特別的,就是統計信息不正確導致的執行計劃問題嘛,也許收集一下統計信息就好了.你覺得呢?

統計信息的問題?

“我們之前還專門對表XXX_STMT_X做了統計信息收集,是不是統計信息收集的辦法不對啊?”—客戶繼續說道；

我現場核查了統計信息的收集時間,以及統計信息顯示出的信息,確實是沒問題的,那么,會不會是過濾條件的特殊性導致的呢?這個可能需要小小的驗證一下才行：

可以看到統計信息收集是沒有問題的,單獨做一個查詢,優化器對表XXX_STMT_X的估算值還是挺準的; 可是怎么到了客戶的批量語句里就出錯了呢?是因為語句更多的連接關系?還是真的像客戶所說的建了索引以后導致的?一切不得而知!如果是你,你接下來會如何分析這個問題呢?不如停下來思考思考.

.

找到錯誤評估的根源

這樣的情況,我其實也沒有遇見過,也沒有可以直接借鑒的經驗,不過作為老司機的一線DBA,我處理起來還是游刃有余的；要知道其生成執行計劃時不準的原因,我們不妨做個10053看一看：

oradebug setmypid

oradebug event 10053 trace namecontext forever,level 1;

然后執行批量語句...

查看trace中對于表XXX_STMT_X的估算：

Accesspath analysis for XXX_STMT_X

***************************************

SINGLE TABLE ACCESS PATH --估算單表拜訪路徑

Single Table CardinalityEstimation for XXX_STMT_X[T1]

** Performing dynamicsampling initial checks. ** --啟動了動態采樣檢查

Column (#107):

NewDensity:0.000557,OldDensity:0.000000 BktCnt:5387, PopBktCnt:5387, PopValCnt:126, NDV:126

Column (#107): STMT_DATE(

AvgLen: 8 NDV: 126Nulls: 0 Density: 0.000557 Min: 2456779 Max: 2457734

Histogram: Freq #Bkts: 126 UncompBkts: 5387 EndPtVals: 126

** Dynamic sampling initialchecks returning TRUE (level = 5).

--動態才有檢查完成,級別為5

** Generated dynamicsampling query: --生成動態采樣SQL

query text :

………………

** Executed dynamic sampling query:

level : 5

sample pct. : 0.015328—采樣比0.015328

total partitions : 128 –-分區表分區總數128,實際分區數是127

partitions forsampling : 128

partitions actuallysampled from : 32 --真正采樣的分區個數為32個

actual sample size : 0 --采集到的行數為0

filtered sample card. : 0 --滿足過濾條件的為0

orig. card. : 19677057

block cnt. table stat. : 889706

block cnt. for sampling: 835048

partition subset block cnt. : 208762

max. sample block cnt. : 64

sample block cnt. : 32

min. sel. est. : 0.46621496

** Using single table dynamic sel. est. :0.00000000

Table: MDM_STMT_X Alias: T1

Card:Original: 19677057.000000 Rounded:1 Computed: 0.00 Non Adjusted: 0.00

--動態采樣估算的返回行是0行

這里的問題就來了：明明這里XXX_STMT_X表中的數據有幾百萬上千萬條記錄,為什么采樣后評估的記錄數是0呢?

動態采樣的算法過于internal,我們沒法輕易拿到其各個細節的算法,不過,我們可以從MOS找到相關的bug類似如下：

Bug17760686 Bad Cardinality estimation with dynamic sampling

Affects:

• 11.2.0.4

• 11.2.0.3

• 11.2.0.2

Fixed:

• 12.1.0.2 (Server Patch Set)

Description

A dynamic sampling may return a little or zero cardinalityestimate for a query involving a partitioned table stored on anASSM tablespace.

--當查詢涉及到分區表時,動態采樣可能會估算出很小甚至是0的結果集；

Workaround

Raise the level of dynamic sampling.

這個bug看起來很像,我們的XXX_STMT_X表同樣也是分區表,但是并沒有更多信息來確認我們的CASE就與這個bug有關；

在客戶現場,我們應該怎么調整呢?照著這里的workaround的調整動態采樣的級別嗎?然而,當考慮這個workaround的時候,我才發現,我已經掉入了一個分析的陷阱,也許我們換個角度會更明朗,你發現了嗎?

.

跳出分析的陷阱

當我們閱讀10053的trace文件發現了動態采樣出來的評估行數為0時,我們便一股腦進入到這個問題的分析/查找過程中；

然而,在這個過程中卻著實忽略了一個重要的問題：表XXX_STMT_X上明明存在統計信息,為什么要使用動態采樣呢,如果我們避免動態采樣,是不是整個問題其實也就迎刃而解了呢?

要驗證這個問題,我們簡單的加個hint再看執行計劃即可：

SELECT /*+dynamic_sampling(t1 0) dynamic_sampling(t3 0) */T1.BANK ,

…..

WHERE T1.STMT_DATE <= SYSDATE+ 1

AND T1.STMT_DATE > ADD_MONTHS(SYSDATE, -12) ;

查看對應的執行計劃：

對表XXX_STMT_X的預估返回行都在700+W左右,和半年的數據量800+W在一個數據級.

并且由于預估返回行較多,所有優化器選擇了HASH JOIN,而不是之前的NESTED LOOP.

由于在會話級加了16個并行,實測10min內可以執行完成；

沒錯,就是動態采樣的問題,有統計信息而再去做動態采樣,問題出在了哪里呢?

通常,我們會認為是數據庫中參數設置的問題,然而我們可以在10053的trace文件中看到,系統中的非默認參數只包含下面幾個：

***************************************

PARAMETERS USED BYTHE OPTIMIZER

********************************

*************************************

PARAMETERS WITH ALTERED VALUES

******************************

CompilationEnvironment Dump

parallel_query_forced_dop = 16

_pga_max_size = 1678540 KB

parallel_query_mode = forced

parallel_query_default_dop = 64

parallel_force_local = true

total_processor_group_count = 2

Bug Fix ControlEnvironment

*************************************

PARAMETERS WITH DEFAULT VALUES

******************************

可以看到,并沒有特殊參數可以直接導致動態采樣；那么,這里動態采樣到底是從何而來呢?

找到問題的根本原因

當問題再次來到了經驗范圍之外時,MOS經常能給我們進一步拓寬思路,我們使用關鍵字dynamic sampling搜索,能找到不少相關文章,稍微瀏覽一番便能找到自己想要的東西；總結的文檔有如下：

Different Level for DynamicStatistics (Dynamic Sampling) used than the Level Specified (文檔 ID 1102413.1)

High Waits for 'cursor: pin S waiton X' due to Dynamic Sampling Against Parallel Queries (文檔 ID 2006145.1)

Bug 17760686 - Bad Cardinalityestimation with dynamic sampling (文檔 ID 17760686.8)

其中,文檔2006145.1有如下論述：

Changes

The change in dynamic statistics is a result of anenhancement introduced in 11.2.

--11.2之后,Oracle對動態采樣進行了增強.

In previous versions the default value lead to certain situationswhere the result from using dynamic statistics resulted in bad plans, forexample, when the tables referenced in the query are

very large.

With this enhancement the optimizer itself changes the dynamic value undercertain conditions such as with large tables and parallelism. Thelevel is automatically adjusted based on the size of the tables in the query.This is done only for queries that will run in parallel which is typical forData Warehouses and large databases.

--增強的功能是：在某些條件下,比如大表,開啟并行和復雜謂詞條件,優化器會自動啟動動態采樣,并且自行定義動態采樣的級別.

The automatic setting of the Dynamic Statistic level is designed such thatoverhead from using Dynamic Statistics is kept to a very minimum yet it willimprove its effectiveness.

While the fix for this unpublished enhancement request bug7452863 ER: USE DYNAMIC SAMPLING FOR KEY CHOICES WHEN ROWESTIMATES ARE DUBIOUS, allows the auto-adjustment of the dynamicstatistic level for some queries, the tracing in 10053 may not correctlyreflect the adjustment made, and you may see a message of the sort:

--此特性是Oracle未公開的增強特性,相關FIX號是7452863

很顯然,從我們的執行計劃中可以很清晰的看到SQL啟用了并行功能,而在11.2之后oracle提供的增強特性中,對于并行/大表的復雜條件,即使表上存在統計信息,它也會開啟動態采樣的功能,試圖來更精準的評估返回結果集的記錄數；然而,這里很不幸,遇上了動態采樣本身的缺陷,導致動態采樣后評估的行數反而大大偏離了實際情況,導致SQL語句執行時間超長；

對于該增強特性,MOS也提供了相應的辦理方案：

Solution

1. Use workaround of turning off the following “_fix_control”. To do this, login assysdba and run the following:

alter system set “_fix_control”=’7452863:OFF';

--使用_fix_control參數關閉此特性

This turns off the automatic decisions about parallelism anddynamic sampling level.

2. Turn off parallelism from the table and indexes or set it to 1:

ALTER TABLE... NOPARALLEL;

ALTER TABLE ... PARALLEL 1;

ALTER INDEX... NOPARALLEL;

ALTER INDEX ... PARALLEL 1;

--或者不啟動并行機制

到這里,考慮到批量語句中的涉及的表都非常大,還是需要使用并行查詢來加快查詢速度；同時,在沒有進行全面測試的情況下,因為一個批量語句而修改系統整體參數顯然也并不可取；

于是我們采取的最終辦理方案是,在會話級別禁用這種增強特性：

alter session set “_fix_control”=’7452863:OFF';

最終,調整后,批量整體正常10分鐘運行完成.

我們經歷了什么

其實整個處理過程,寫下來似乎有些平淡,但是在處理過程中還是經歷了不少波折,特別是在偶爾的經驗主義的作祟下,偏執于一點通常會限制我們的辦理思路；那我們再回過頭來看看我們到底經歷了什么：

1. 執行計劃偏離了我們的預期,看起來像是統計信息不準

2. 確認統計信息是相對準確的

3. 通過10053跟蹤發現,評估值不正確是因為優化器使用了動態采樣而不是表上已有的統計信息來評估

4. 對于大表XXX_STMT_X,動態采樣的結果居然是0,顯然動態采樣過程存在問題

5. 找到疑似動態采樣結果不準的bug,但是無法精確匹配確認

6. 跳出“動態采樣不準”這一問題的陷阱,先思考為何要使用動態采樣

7. 再次核查相關文檔,從中可以發現11.2版本后,oracle存在未公開的增強特性,對于大表/并行查詢,即使相關表上存在統計信息,它依然會啟動動態采樣；

8. 辦理方案,通過參數關閉該增強特性,避免不必要的動態采樣,也就避免了考慮動態采樣不準確的問題

經驗提示

對于這樣CASE,我收獲的經驗是：

1. 如果你以往的經驗在問題中并不能幫助到你,這時就需要從原理上來分析該問題了;

2. 在處理問題時,如果在一個問題點上長時間無法得到有效的方法,不妨跳出來,看看全局的方法;

從這個CASE可以看到,如果不了解SQL運行的背景和ORACLE的相關特性,而盲目的加索引、收集統計信息乃至加并行,并不能加快SQL的執行速度,甚至反而會讓執行速度變慢(這里加并行后的執行效果反而不如加并行前)；不知道這個CASE是否能給你帶來這樣的啟發.

另,針對本CASE中動態采樣不準的問題,在辦理客戶的問題后,我進一步進行研究驗證,可以認為在動態采樣過程中,是由于采樣比例/采樣語句中錯誤的hint及多種原因共同造成的,這里篇幅限制,不再進一步闡述,有興趣的同學可以自行模擬出類似的CASE進行探索研究.