【原創】ORACLE 深入解析10053事件

新年新說：

新年伊始，2012年過去了，我們又踏上了2013年的，回顧2012我們付出了很多，辛勤和汗水換來了知識和友誼，當我們技術成長的時候我才發現長路漫漫，唯心可敬。一份耕耘一份收獲，走技術之路是艱辛的 孤獨的 漫長的，在此向剛入門的小伙子們，說一說心得體會。做好心理準備，可能你為了小小的虛榮心，為了生活所迫，才走上此路，但你也要走的灑脫 走的穩健，當你站在第一個里程碑時回顧來時路，你會發現你的收獲是值得的，你的付出是有意思的，你才能有繼續走下去的勇氣。我要感謝 Alantany  tigerfish  海哥  張老師  飚哥  寅總 dingjun  晶晶 童mm 蓓蓓 還有 好多好多 幫助過我的人們，我的成長離不開你們的鼓勵。飆完淚之后開始上干貨吧：）

ORACLE 深入解析10053事件

本次我們主要講解oracle 10053事件和實驗，好多朋友可能對這個事件不是很熟悉，因為在日常運維中用到的不是很多。Oracle 10046和10053 都是非官方trace sql的方法，在官方文檔上是找不到相關資料的，但在MOS上可以找到。sql_trace是官方推薦的trace sql的方法，在官方文檔上是可以查詢出來的。

10053事件：用來描述oracle如何選擇執行計劃的過程，然后輸出到trace文件里，共我們參考，因為我們經常看執行計劃怎么執行的消耗了哪些資源，而不是常看執行計劃怎么選擇出來了的。

10053場景：當SQL語句執行時走的是錯誤的執行計劃，而又找不到原因時，這時請用10053來分析一下原因。

10053特點：

（1）只可以了解oracle執行計劃的選擇過程

（2）無法獲知代價的計算公式，因為這是oracle內部的商業機密，而且每個oracle版本的優化器計算公式都不相同差距還是蠻大的，不同版本的同一個語句的代價也不一樣，優化器現在還不是很成熟，還有待完善。

（3）在這個里面我們重點要了解的是“代價”是如何計算出來的，然后我們才能了解執行計劃是如何選擇的。

（4）在10053中可以了解哪些因素影響sql的執行代價

（5）oracle 8i cost等價IO資源消耗   9i以后cost等價IO+CPU+網絡+等待事件+其他代價

一般IO資源的權重比較大 CPU權重較小

10053內容：

參數區：初始化參數，隱含參數，這些參數可以左右oracle工作方式

SQL區：執行的SQL語句，是否使用綁定變量，是否進行了轉換操作

系統信息區：操作系統統計信息  cpu主頻 CPU執行時間 IO尋址時間 單塊讀時間 多塊讀時間

對象統計信息區：

數據訪問方式：訪問方式不一樣計算代價的方法也不一樣，全表掃描 走索引 多表關聯 代價都不同

關聯查詢：把每張表都作為驅動表去組合，擇優選擇“代價”最小的關聯方式，與哪個表在前無關系

代價的最后修正：oracle會對選擇出來的代價再進行最后的修正，使其更準確一些，更合理一些

選擇出最終執行計劃：這個過程是非常快速的，毫秒級就搞定啦

實驗環境

LEO1@LEO1> select * from v$version;                     這是我的oracle edition

BANNER

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

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

PL/SQL Release 11.2.0.1.0 - Production

CORE    11.2.0.1.0      Production

TNS for Linux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

1.驗證全表掃描的成本計算公式，貼出執行計劃和計算公式。

LEO1@LEO1> col sname for a20

LEO1@LEO1> col pname for a20

LEO1@LEO1> col pual1 for a30

LEO1@LEO1> col pual2 for a30

LEO1@LEO1> select * from sys.aux_stats$;    查看操作系統統計信息

SNAME              PNAME                  PVAL1    PVAL2

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

SYSSTATS_INFO        STATUS                             COMPLETED

SYSSTATS_INFO        DSTART                             08-15-2009 00:49

SYSSTATS_INFO        DSTOP                              08-15-2009 00:49

SYSSTATS_INFO        FLAGS                         1

SYSSTATS_MAIN        CPUSPEEDNW            2657.0122  

SYSSTATS_MAIN        IOSEEKTIM                    10   

SYSSTATS_MAIN        IOTFRSPEED                 4096

SYSSTATS_MAIN        SREADTIM      

SYSSTATS_MAIN        MREADTIM      

SYSSTATS_MAIN        CPUSPEED      

SYSSTATS_MAIN        MBRC      

SYSSTATS_MAIN        MAXTHR

SYSSTATS_MAIN        SLAVETHR

說明

aux_stats$是sys管理員用戶下的一個基表后綴為$，必須寫schema才能查詢到，所謂的基表就是給動態性能視圖提供數據的原始表，由于基表非常重要，oracle規定不允許直接訪問和修改基表，如果你比較了解這些那么另說了。這個表中記錄了“操作系統統計信息”。Oracle會利用操作系統統計信息來修正執行計劃的代價，也就是說這些信息是影響代價計算的因素之一。

注意：如果oracle收集了操作系統統計信息，那么CBO采用工作量統計模式計算代價

      如果oracle沒有收集操作系統統計信息，那么CBO采用非工作量統計模式計算代價，看上面MBRC沒有參數值就說明還沒有收集操作系統統計信息

這兩個模式計算代價的公式是不同的。

SNAME：是指操作系統統計信息

PNAME：parameter name 參數名

PVAL1：參數值

PVAL2：參數值

參數解釋

FLAGS：標志

CPUSPEEDNW：非工作量統計模式下CPU主頻，直接來自硬件

IOSEEKTIM：IO尋址時間（毫秒），直接來自硬件

IOTFRSPEED：IO傳輸速率（字節/毫秒）

SREADTIM：讀取單個數據塊的平均時間

MREADTIM：讀取多個數據塊的平均時間

CPUSPEED：工作量統計模式下CPU主頻，根據當前工作量評估出一個合理值

MBRC：oracle收集完統計信息后評估出的一次多塊讀可以讀幾個數據塊db_file_multiblock_read_count

MAXTHR：最大IO吞吐量（字節/秒）

SLAVETHR：平均IO吞吐量（字節/秒）

后面這6個參數是在oracle收集完統計信息后才能得出的參數值，有什么用呢？我來解釋一下下

CBO在計算SQL語句的代價時，需要使用數據庫對象例如表 索引 等對象統計數據，還要使用操作系統統計數據例如CPU周期 IO速度 數據塊讀時間等，選擇花費時間最少的執行計劃為最佳執行計劃。

Oracle使用dbms_stats.gather_system_stats存儲過程來收集操作系統統計信息，收集來的數據存放在sys.aux_stats$表中，如果我們做了收集操作那么會有統計數據，如果沒有做就沒有統計數據，這兩種計算代價的方法是不同的，后續會講。

dbms_stats.gather_system_stats語法

execute  dbms_stats.gather_system_stats（

gathering_mode varchar2 default ‘noworkload’

interval integer default null,

stattab varchar2 default null,

statid varchar2 default null,

statown varchar2 default null);

解釋

gathering_mode 參數，默認值“noworkload”，還可以設置為“workload”含義

noworkload：非工作量統計模式，收集上來的數據都是來自硬件

workload：工作量統計模式，收集上來的數據需要在特定的數據庫負載間隔內統計出來的，這樣的數據才能真實反映出數據庫的操作系統參數（需要執行sql測評出來）

interval：可以指定收集統計信息的時間間隔，例如 5 收集5分鐘的統計信息

命令：execute dbms_stats.gather_system_stats(‘noworkload’,5);  

START和STOP關鍵字自己決定何時開始何時結束收集統計信息

命令：execute dbms_stats.gather_system_stats(‘start’);

上下兩條指令間隔3分鐘執行，然后把這3分鐘的統計信息寫入到sys.aux_stats$表里面

execute dbms_stats.gather_system_stats(‘stop’);

注意：上面有個MBRC參數我想多聊一下，它是初始化參數db_file_multiblock_read_count的簡寫中文翻譯“一次讀多少個數據塊or一次多塊讀可以讀幾個數據塊”，如果收集了統計信息那么CBO會用MBRC計算代價，如果沒有收集統計信息CBO會用這個初始化參數db_file_multiblock_read_count計算代價。

LEO1@LEO1> show parameter db_file_multiblock_read_count     這是我機器上參數默認值

NAME                                 TYPE        VALUE

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

db_file_multiblock_read_count            integer       79

LEO1@LEO1> show parameter db_block_size                   我們的一個塊大小為8k

NAME                                 TYPE        VALUE

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

db_block_size                           integer      8192

這個參數值并不是無限大的，大多數平臺下的oracle都是128。一般oracle block size =8k

128*8=1M，也就是說1M是大多數操作系統一次最大IO的限制，如果還有其他限制要從這1M里面扣除，初始化參數db_file_multiblock_read_count的最大值之所以定為128，也是為了保守策略。

79*8k=632K

測試

LEO1@LEO1> drop table leo1 purge;                      清空環境

Table dropped.

LEO1@LEO1> create table leo1 as select * from dba_objects;   創建leo1表

Table created.

LEO1@LEO1> begin

     dbms_stats.gather_table_stats(                      收集表的統計信息

          wnname=>'leo1',                            用戶名

          tabname=>'leo1',                             表名

          cascade=>true,                               級聯操作

          estimate_percent=>null,                       全表采樣

          method_opt=>'for all columns size 1');   不作直方圖分析，減小代價計算的影響

end;

/

  2    3    4    5    6    7    8    9  

PL/SQL procedure successfully completed.

LEO1@LEO1> show parameter db_file_multiblock_read_count      

NAME                                 TYPE        VALUE

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

db_file_multiblock_read_count           integer     79

LEO1@LEO1> alter session set db_file_multiblock_read_count=16;    把多塊讀參數修改成16方便計算

Session altered.

LEO1@LEO1> show parameter db_file_multiblock_read_count

NAME                                 TYPE        VALUE

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

db_file_multiblock_read_count           integer     16

LEO1@LEO1> select * from sys.aux_stats$;         沒有收集操作系統統計信息

SNAME                PNAME                     PVAL1 PVAL2

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

SYSSTATS_INFO        STATUS                          COMPLETED

SYSSTATS_INFO        DSTART                          08-15-2009 00:49

SYSSTATS_INFO        DSTOP                           08-15-2009 00:49

SYSSTATS_INFO        FLAGS                         1

SYSSTATS_MAIN        CPUSPEEDNW            2657.0122

SYSSTATS_MAIN        IOSEEKTIM                    10

SYSSTATS_MAIN        IOTFRSPEED                 4096

SYSSTATS_MAIN        SREADTIM

SYSSTATS_MAIN        MREADTIM

SYSSTATS_MAIN        CPUSPEED

SYSSTATS_MAIN        MBRC

SYSSTATS_MAIN        MAXTHR

SYSSTATS_MAIN        SLAVETHR

我們沒有收集操作系統統計信息，所以CBO采用了非工作量統計模式(noworkload)來計算代價

LEO1@LEO1> select blocks from user_tables where table_name='LEO1';   LEO1表總數據塊為1051

    BLOCKS

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

      1051

LEO1@LEO1> set autotrace trace explain

LEO1@LEO1> select * from leo1;

Execution Plan

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

Plan hash value: 2716644435

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      | 71968 |  6817K|   233   (1)| 00:00:03 |

|   1 |  TABLE ACCESS FULL| LEO1  | 71968 |  6817K|   233   (1)| 00:00:03 |

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

全表掃描的成本等于233，其中CPU代價占整個權重百分比的1%

###################################################################################

成本的計算公式如下：
Cost = (
       #SRds * sreadtim +
       #MRds * mreadtim +
       CPUCycles / cpuspeed
       ) / sreadtime
      
#SRds - number of single block reads    單塊讀的次數
#MRds - number of multi block reads    多塊讀的次數
#CPUCyles - number of CPU cycles      一個CPU周期

sreadtim - single block read time       讀取單個數據塊的平均時間
mreadtim - multi block read time      讀取多個數據塊的平均時間
cpuspeed - CPU cycles per second     CPU周期/秒

注意：如果oracle收集了操作系統統計信息，那么CBO采用工作量統計模式計算代價

      如果oracle沒有收集操作系統統計信息，那么CBO采用非工作量統計模式計算代價我們現在處于“非工作量統計模式”

#SRds=0,因為是全表掃描，單塊讀為0，全都使用的是多塊讀
#MRds=表的塊數/多塊讀參數=1051/16=65.6875

mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeed=10+16*8192/4096=42

sreadtim=ioseektim+db_block_size/iotfrspeed=10+8192/4096=12

CPUCycles 等于 PLAN_TABLE里面的CPU_COST

LEO1@LEO1> explain plan for select * from leo1;

Explained.

LEO1@LEO1> select cpu_cost from plan_table;

  CPU_COST

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

  38430873

cpuspeed 等于 CPUSPEEDNW= 2657.0122

COST=65.6875*42/12+38430873/2657.0122/12/1000(毫秒換算成秒)=229.90625+1.20532=231.11157

229.90625 是IO代價

1.20532   是CPU代價

手工計算出來的COST用四舍五入等于232，和我們看到的233有差別，這是由于隱含參數_table_scan_cost_plus_one參數造成的

LEO1@LEO1> conn / as sysdba        切換到sys用戶才能查看隱含參數

SYS@LEO1> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM x$ksppi x,x$ksppcv y

  WHERE x.inst_id = USERENV ('Instance')

   AND y.inst_id = USERENV ('Instance')

   AND x.indx = y.indx

   AND x.ksppinm LIKE '%_table_scan_cost_plus_one%';  2    3    4    5  

NAME                     VALUE        DESCRIB

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

_table_scan_cost_plus_one    TRUE         bump estimated full table scan and index ffs cost by one

根據該參數的描述，在table full scan和index fast full scan的時候會將cost+1 即 232+1=233

我們把_table_scan_cost_plus_one參數禁用看看cost變化

SYS@LEO1> alter session set "_table_scan_cost_plus_one"=false;    禁用

Session altered.

SYS@LEO1> SELECT x.ksppinm NAME, y.ksppstvl VALUE, x.ksppdesc describ FROM x$ksppi x,x$ksppcv y

  WHERE x.inst_id = USERENV ('Instance')

   AND y.inst_id = USERENV ('Instance')

   AND x.indx = y.indx

   AND x.ksppinm LIKE '%_table_scan_cost_plus_one%';  2    3    4    5    生效

NAME                     VALUE        DESCRIB

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

_table_scan_cost_plus_one    FALSE        bump estimated full table scan and index ffs cost by one

SYS@LEO1> select * from leo1.leo1;

Execution Plan

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

Plan hash value: 2716644435

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      | 71968 |  6817K|   232   (1)| 00:00:03 |

|   1 |  TABLE ACCESS FULL| LEO1  | 71968 |  6817K|   232   (1)| 00:00:03 |

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

這次得到的COST等于232，與計算值正好匹配，這是禁用隱含參數的結果

SYS@LEO1> alter session set db_file_multiblock_read_count=32;   我們修改一下多塊讀參數

Session altered.

SYS@LEO1> select * from leo1.leo1;

Execution Plan

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

Plan hash value: 2716644435

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT  |      | 71968 |  6817K|   204   (1)| 00:00:03 |

|   1 |  TABLE ACCESS FULL| LEO1  | 71968 |  6817K|   204   (1)| 00:00:03 |

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

#SRds=0,因為是全表掃描，單塊讀為0，全都使用的是多塊讀
#MRds=表的塊數/多塊讀參數=1051/32=32.84375

mreadtim=ioseektim+db_file_multiblock_count*db_block_size/iotftspeed=10+32*8192/4096=74

sreadtim=ioseektim+db_block_size/iotfrspeed=10+8192/4096=12

CPUCycles=38430873

cpuspeed 等于 CPUSPEEDNW= 2657.0122

COST=32.84375*74/12+38430873/2657.0122/12/1000(毫秒換算成秒)= 202.53645+1.20532=203.74177

四舍五入等于204，與執行計劃中COST=204相一致

小結：從實驗中可以得出，oracle 11gR2中，全表掃描非工作量統計模式下COST計算公式依然和9i/10g一樣，沒有變化。同時我們也看到了IO成本占整個代價權重的極大部分，是影響SQL效率的主要因素，需要我們多關注。

2.給出B-tree索引 Unique scan的成本計算公式，貼出執行計劃和計算公式。

CBO各種類型成本計算公式如下：
全表掃描

Full table scan cost= HWM/dbf_mbrc
索引唯一掃描
Unique scan cost = blevel +1
索引快速全掃描
Fast Full Scan cost=leaf_blocks/adj_mbrc
只訪問索引，不訪問原表掃描
Index-only cost = Blevel + effective index selectivity * leaf_blocks
索引范圍掃描
Range Cost = Blevel + effectivity index selectivity* leaf_blocks
                           + effective table selectivity * clustering_factor
嵌套循環關聯
nested loop join cost =outer access cost + (inner access cost * outer cardinality)
排序合并關聯
sort merge join cost = outer access cost + inner access cost + sort costs
哈希關聯
hash join cost = (outer access cost * # of hash partitions) + inner access cost

實驗

LEO1@LEO1> drop table leo2 purge;                       清理環境

Table dropped.

LEO1@LEO1> create table leo2 as select * from dba_objects;   創建leo2表

Table created.

LEO1@LEO1> create index idx_leo2 on leo2(object_id);        創建idx_leo2

Index created.

LEO1@LEO1> begin

     dbms_stats.gather_table_stats(                      收集表的統計信息

          wnname=>'leo1',                            用戶名

          tabname=>'leo2',                             表名

          cascade=>true,                               級聯操作

          estimate_percent=>null,                       全表采樣

          method_opt=>'for all columns size 1');   不作直方圖分析，減小代價計算的影響

end;

/

  2    3    4    5    6    7    8    9  

PL/SQL procedure successfully completed.

必須要做分析，如果表沒有分析，下面統計信息就沒有了

LEO1@LEO1> select index_name,blevel,leaf_blocks,clustering_factor,num_rows,distinct_keys from dba_indexes where index_name='IDX_LEO2';

INDEX_NAME   BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR   NUM_ROWS DISTINCT_KEYS

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

IDX_LEO2      1      159         1076                71968     71968

BLEVEL：索引層數  1表示就1層

LEAF_BLOCKS：索引樹的葉子塊數  159

CLUSTERING_FACTOR：索引聚簇因子

NUM_ROWS：有索引的行數    71968和數據行數相匹配

DISTINCT_KEYS：不同的索引鍵值  71968

LEO1@LEO1> select count(*) from leo2;

  COUNT(*)

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

     71968

LEO1@LEO1> select * from leo2 where object_id=10000;

Execution Plan

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

Plan hash value: 2495991774

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

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT            |          |     1 |    97 |     2   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID | LEO2     |     1 |    97 |     2   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | IDX_LEO2 |     1 |       |     1   (0)| 00:00:01 |

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

COST=2，其中CPU代價=0，等值查詢與索引的條數無關，消耗CPU資源可以忽略不計

Predicate Information (identified by operation id):

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

   2 - access("OBJECT_ID"=10000)

公式
Unique scan cost = blevel +1

INDEX UNIQUE SCAN的COST=1    就是blevel，CBO看看需要遞歸幾層索引，與統計信息中的blevel一致

TABLE ACCESS BY INDEX ROWID的COST=1  通過索引rowid訪問表產生的代價

因此最終COST=1+1=2

3.通過10053事件分析一個SQL執行計劃的產生過程，需要貼出trace中的相關信息和必要的文字說明。

測試

LEO1@LEO1> drop table leo3 purge;                         清理環境

Table dropped.

LEO1@LEO1> drop table leo4 purge;

Table dropped.

LEO1@LEO1> create table leo3 as select * from dba_objects;     創建leo3表

Table created.

LEO1@LEO1> create table leo4 as select * from leo3 where rownum<100;    創建leo4表

Table created.

LEO1@LEO1> select count(*) from leo4;                      這是個小表

  COUNT(*)

----------

        99

LEO1@LEO1> create index idx_leo3 on leo3(object_id);          創建了索引

Index created.

LEO1@LEO1> create index idx_leo4 on leo4(object_id);          同上

Index created.

LEO1@LEO1> begin

     dbms_stats.gather_table_stats(                leo3表做統計分析

          wnname=>'leo1',

          tabname=>'leo3',

          cascade=>true,

          estimate_percent=>null,

          method_opt=>'for all columns size 1');

end;

/

  2    3    4    5    6    7    8    9  

PL/SQL procedure successfully completed.

LEO1@LEO1> begin

     dbms_stats.gather_table_stats(                 leo4表做統計分析

          wnname=>'leo1',

          tabname=>'leo4',

          cascade=>true,

          estimate_percent=>null,

          method_opt=>'for all columns size 1');

end;

/

  2    3    4    5    6    7    8    9  

PL/SQL procedure successfully completed.

LEO1@LEO1> alter session set events '10053 trace name context forever,level 1';     啟動10053事件

10053事件有2個level，1和2，1級比2級內容要詳細的多

Session altered.

LEO1@LEO1> select count(*) from leo3,leo4 where leo3.object_id=leo4.object_id;    執行SQL

  COUNT(*)

----------

        99

LEO1@LEO1> alter session set events '10053 trace name context off';           關閉10053事件

Session altered.

LEO1@LEO1> select value from v$diag_info where name='Default Trace File';   當前會話寫入的trace

VALUE

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

/u01/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_ora_22298.trc

下面我們來看看trace文件中相關信息

參數區        包含初始化參數和隱含參數等

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

----- Current SQL Statement for this session (sql_id=fh7dku2xy52rc) -----   這個會話的SQL_ID

select count(*) from leo3,leo4 where leo3.object_id=leo4.object_id

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

Legend   下面這些縮寫都是優化器使用的trace標識

The following abbreviations are used by optimizer trace.

CBQT - cost-based query transformation

JPPD - join predicate push-down

OJPPD - old-style. (non-cost-based) JPPD

FPD - filter push-down

PM - predicate move-around

CVM - complex view merging

SPJ - select-project-join

…………….

Compilation Environment Dump

optimizer_mode_hinted               = false

optimizer_features_hinted           = 0.0.0

parallel_execution_enabled          = true

parallel_query_forced_dop           = 0

parallel_dml_forced_dop             = 0

parallel_ddl_forced_degree          = 0

這些都是參數的默認值

……………………………………

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

Column Usage Monitoring is ON: tracking level = 1     標識10053事件用的時level1級別

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

SQL區    SQL查詢轉換    合并塊   計數統計

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

Query transformations (QT)

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

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

QUERY BLOCK TEXT           查詢塊文本，就是執行的哪個SQL語句

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

select count(*) from leo3,leo4 where leo3.object_id=leo4.object_id

操作系統統計信息區

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

SYSTEM STATISTICS INFORMATION

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

  Using NOWORKLOAD Stats        基于非工作量統計模式

  CPUSPEEDNW: 2657 millions instructions/sec (default is 100)  非工作量統計模式下CPU主頻

  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)     IO傳輸速率（字節/毫秒）

  IOSEEKTIM: 10 milliseconds (default is 10)                 IO尋址時間（毫秒）

  MBRC: -1 blocks (default is 8)                           一次多塊讀可以讀幾個數據塊

基本統計信息（對象級別統計信息）  OLAP系統而言擁有對象級別統計信息就已經足夠了

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

BASE STATISTICAL INFORMATION        這些統計信息都來自于視圖

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

Table Stats::  來自user_tables視圖

  Table: LEO4  Alias: LEO4

#Rows: 99  #Blks:  5  AvgRowLen:  75.00   

行數      塊數      平均行長

Index Stats::  來自user_indexes視圖

  Index: IDX_LEO4  Col#: 4

LVLS: 0  #LB: 1  #DK: 99  LB/K: 1.00  DB/K: 1.00  CLUF: 2.00

索引幾層 葉子塊數 多少個唯一鍵值 每個鍵值有多少個葉塊 每個鍵值有多少個數據塊 聚簇因子

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

Table Stats::

  Table: LEO3  Alias: LEO3

#Rows: 71969  #Blks:  1051  AvgRowLen:  97.00

行數         塊數         平均行長

Index Stats::

  Index: IDX_LEO3  Col#: 4

LVLS: 1  #LB: 159  #DK: 71969  LB/K: 1.00  DB/K: 1.00  CLUF: 1078.00

索引幾層 葉子塊數 多少個唯一鍵值 每個鍵值有多少個葉塊 每個鍵值有多少個數據塊 聚簇因子

Access path analysis for LEO3    LEO3表訪問路徑的不同代價

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

SINGLE TABLE ACCESS PATH

  Single Table Cardinality Estimation for LEO3[LEO3]  

  Table: LEO3  Alias: LEO3

Card: Original: 71969.000000  Rounded: 71969  Computed: 71969.00  Non Adjusted: 71969.00

     原始行數             近似值         精確值             非修正值

  Access Path: TableScan     全表掃描代價

    Cost:  286.71  Resp: 286.71  Degree: 0    總代價=286.71

      Cost_io: 286.00  Cost_cpu: 22598123     總代價=IO代價+CPU代價

      Resp_io: 286.00  Resp_cpu: 22598123    并行訪問代價

  Access Path: index (index (FFS))   索引快速全掃描

    Index: IDX_LEO3

    resc_io: 45.00  resc_cpu: 9768589           串行訪問代價=45（因為索引是串行存儲的）

ix_sel: 0.000000  ix_sel_with_filters: 1.000000  ix_sel=1/DK=1/71969=0.000013 索引選擇率

ix_sel_with_filters帶過濾條件索引選擇率

  Access Path: index (FFS)

    Cost:  45.31  Resp: 45.31  Degree: 1       索引并行訪問代價=45.31>45(串行訪問代價)

      Cost_io: 45.00  Cost_cpu: 9768589        所以要選擇串行訪問

      Resp_io: 45.00  Resp_cpu: 9768589       并行度=1

  Access Path: index (FullScan)     索引全掃描

    Index: IDX_LEO3

    resc_io: 160.00  resc_cpu: 15533230        串行訪問代價=160，這個比較高

    ix_sel: 1.000000  ix_sel_with_filters: 1.000000

    Cost: 160.49  Resp: 160.49  Degree: 1       并行度=1

  Best:: AccessPath: IndexFFS

  Index: IDX_LEO3

         Cost: 45.31  Degree: 1  Resp: 45.31  Card: 71969.00  Bytes: 0

###############################################################################

Access path analysis for LEO4      LEO4表訪問路徑的不同代價

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

SINGLE TABLE ACCESS PATH

  Single Table Cardinality Estimation for LEO4[LEO4]

  Table: LEO4  Alias: LEO4

Card: Original: 99.000000  Rounded: 99  Computed: 99.00  Non Adjusted: 99.00

原始行數              近似值       精確值          非修正值

  Access Path: TableScan     全表掃描代價

    Cost:  3.00  Resp: 3.00  Degree: 0    總代價=3

      Cost_io: 3.00  Cost_cpu: 56397      IO代價+CPU代價

      Resp_io: 3.00  Resp_cpu: 56397     并行訪問代價

  Access Path: index (index (FFS))   索引快速全掃描

    Index: IDX_LEO4

    resc_io: 2.00  resc_cpu: 19001        串行訪問代價=2

ix_sel: 0.000000  ix_sel_with_filters: 1.000000   ix_sel=1/DK=1/99=0.01 索引選擇率

ix_sel_with_filters帶過濾條件索引選擇率

  Access Path: index (FFS)

    Cost:  2.00  Resp: 2.00  Degree: 1    索引并行訪問代價=2，并行度=1

      Cost_io: 2.00  Cost_cpu: 19001

      Resp_io: 2.00  Resp_cpu: 19001

  Access Path: index (FullScan)     索引全掃描

    Index: IDX_LEO4

    resc_io: 1.00  resc_cpu: 26921        串行訪問代價=1，這個最低，就是它了

    ix_sel: 1.000000  ix_sel_with_filters: 1.000000

    Cost: 1.00  Resp: 1.00  Degree: 1

  Best:: AccessPath: IndexRange

  Index: IDX_LEO4

         Cost: 1.00  Degree: 1  Resp: 1.00  Card: 99.00  Bytes: 0

關聯查詢—驅動表的選擇

OPTIMIZER STATISTICS AND COMPUTATIONS     優化器的統計和計算

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

GENERAL PLANS                            選擇執行計劃

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

Considering cardinality-based initial join order.

Permutations for Starting Table :0

Join order[1]:  LEO4[LEO4]#0  LEO3[LEO3]#1    關聯的對象

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

Now joining: LEO3[LEO3]#1  現在要用leo4小表關聯leo3大表，leo4做驅動表

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

NL Join嵌套循環關聯      leo4表中有99條，小表為驅動表

驅動表 Outer table: Card: 99.00  Cost: 1.00  Resp: 1.00  Degree: 1  Bytes: 3

Access path analysis for LEO3

  Inner table: LEO3  Alias: LEO3

  Access Path: TableScan    全表掃描-嵌套循環關聯COST=28253.17

    NL Join:  Cost: 28253.17  Resp: 28253.17  Degree: 1

      Cost_io: 28183.00  Cost_cpu: 2237241142

      Resp_io: 28183.00  Resp_cpu: 2237241142     并行訪問代價

  Access Path: index (index (FFS))  索引快速全掃描

    Index: IDX_LEO3

    resc_io: 43.08  resc_cpu: 9768589              串行訪問代價

    ix_sel: 0.000000  ix_sel_with_filters: 1.000000

  Inner table: LEO3  Alias: LEO3

  Access Path: index (FFS)

    NL Join:  Cost: 4296.33  Resp: 4296.33  Degree: 1   并行訪問

      Cost_io: 4266.00  Cost_cpu: 967117228

      Resp_io: 4266.00  Resp_cpu: 967117228

  Access Path: index (AllEqJoinGuess)

Index: IDX_LEO3

    resc_io: 1.00  resc_cpu: 8171

    ix_sel: 0.000014  ix_sel_with_filters: 0.000014

    NL Join (ordered): Cost: 100.03  Resp: 100.03  Degree: 1

      Cost_io: 100.00  Cost_cpu: 835894

      Resp_io: 100.00  Resp_cpu: 835894

  Best NL cost: 100.03   leo4為驅動表，小表為驅動表，最后代價100.03

          resc: 100.03  resc_io: 100.00  resc_cpu: 835894    串行方式的代價  IO代價+CPU代價

          resp: 100.03  resp_io: 100.00  resc_cpu: 835894    并行方式的代價

Outer table:  LEO4  Alias: LEO4

SM Join    先排序后合并關聯

  SM cost: 268.06       代價268.06

     resc: 268.06 resc_io: 265.00 resc_cpu: 97470464

     resp: 268.06 resp_io: 265.00 resp_cpu: 97470464

HA Join    哈希關聯

  HA cost: 47.03        代價47.03，最好是哈希代價最小

     resc: 47.03 resc_io: 46.00 resc_cpu: 32949334

     resp: 47.03 resp_io: 46.00 resp_cpu: 32949334

Best:: JoinMethod: Hash  最后關聯方法選擇：哈希hash

       Cost: 47.03  Degree: 1  Resp: 47.03  Card: 99.00 Bytes: 8  返回記錄數+字節

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

Now joining: LEO4[LEO4]#0    現在要用leo3大表關聯leo4小表，leo3做驅動表

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

NL Join   嵌套循環關聯      leo3表中有71969條，大表為驅動表

  Outer table: Card: 71969.00  Cost: 45.31  Resp: 45.31  Degree: 1  Bytes: 5

Access path analysis for LEO4

  Inner table: LEO4  Alias: LEO4

  Access Path: TableScan

    NL Join:  Cost: 97632.61  Resp: 97632.61  Degree: 1

      Cost_io: 97505.00  Cost_cpu: 4068618676

      Resp_io: 97505.00  Resp_cpu: 4068618676

  Access Path: index (index (FFS))

    Index: IDX_LEO4

    resc_io: 0.27  resc_cpu: 19001

    ix_sel: 0.000000  ix_sel_with_filters: 1.000000

  Inner table: LEO4  Alias: LEO4

  Access Path: index (FFS)

    NL Join:  Cost: 19581.20  Resp: 19581.20  Degree: 1

      Cost_io: 19538.00  Cost_cpu: 1377283224

      Resp_io: 19538.00  Resp_cpu: 1377283224

  Access Path: index (AllEqJoinGuess)

    Index: IDX_LEO4

    resc_io: 0.00  resc_cpu: 1050

    ix_sel: 0.010101  ix_sel_with_filters: 0.010101

    NL Join (ordered): Cost: 47.68  Resp: 47.68  Degree: 1

      Cost_io: 45.00  Cost_cpu: 85336039

Resp_io: 45.00  Resp_cpu: 85336039

  Best NL cost: 47.68    嵌套循環關聯最后代價47.68

          resc: 47.68  resc_io: 45.00  resc_cpu: 85336039

          resp: 47.68  resp_io: 45.00  resc_cpu: 85336039

SM Join   先排序后合并關聯

  SM cost: 269.06       代價269.06

     resc: 269.06 resc_io: 265.00 resc_cpu: 129384180

     resp: 269.06 resp_io: 265.00 resp_cpu: 129384180

Hash join: Resc: 106.17  Resp: 106.17  [multiMatchCost=0.00]   哈希關聯，代價=106.17

Final cost for query block SEL$1 (#0) - All Rows Plan:

  Best join order: 1    最終代價選擇47.0334，用leo4小表驅動表

  Cost: 47.0334  Degree: 1  Card: 99.0000  Bytes: 792

  Resc: 47.0334  Resc_io: 46.0000  Resc_cpu: 32949334

  Resp: 47.0334  Resp_io: 46.0000  Resc_cpu: 32949334

SQL執行計劃的選擇

============

Plan Table

============

------------------------------------------+-----------------------------------+

| Id  | Operation               | Name    | Rows  | Bytes | Cost  | Time      |

------------------------------------------+-----------------------------------+

| 0   | SELECT STATEMENT        |         |       |       |    47 |           |

| 1   |  SORT AGGREGATE        |         |     1 |     8 |       |           |

| 2   |   HASH JOIN             |         |    99 |   792 |    47 |  00:00:01 |

| 3   |    INDEX FULL SCAN       | IDX_LEO4|    99 |   297 |     1 |  00:00:01 |

| 4   |    INDEX FAST FULL SCAN   | IDX_LEO3|   70K |  351K |    45 |  00:00:01 |

------------------------------------------+-----------------------------------+

Predicate Information:

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

2 - access("LEO3"."OBJECT_ID"="LEO4"."OBJECT_ID")

選擇的執行計劃和上面分析結果是相匹配的

來看看我們真實的執行計劃的樣子

LEO1@LEO1> set autotrace trace exp

LEO1@LEO1> select count(*) from leo3,leo4 where leo3.object_id=leo4.object_id;

Execution Plan

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

Plan hash value: 172281424

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

| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT       |          |     1 |     8 |    47   (3)| 00:00:01 |

|   1 |  SORT AGGREGATE       |          |     1 |     8 |            |          |

|*  2 |   HASH JOIN            |          |    99 |   792 |    47   (3)| 00:00:01 |

|   3 |    INDEX FULL SCAN      | IDX_LEO4 |    99 |   297 |     1   (0)| 00:00:01 |

|   4 |    INDEX FAST FULL SCAN  | IDX_LEO3 | 71969 |   351K|    45   (0)| 00:00:01 |

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

小結：一模一樣對吧，這說明我們的優化器在對比完不同代價后選擇的執行計劃是最優的，如果我們在實際工作中，遇到了執行計劃選擇錯誤的情景，我們可以通過10053事件來做詳細的分析。

4.當統計信息不準確時，CBO可能產生錯誤的執行計劃，請給出這樣的一個例子，在10053 trace中找到CBO出錯的位置，并給出必要的文字說明。

LEO1@LEO1> drop table leo5 purge;                       清空環境

Table dropped.

LEO1@LEO1> create table leo5 as select * from dba_objects;   創建leo5表

Table created.

LEO1@LEO1> create index idx_leo5 on leo5(object_id);       創建B-tree索引

Index created.

為了讓CBO產生錯誤的執行計劃，我把leo5數據分布變的傾斜一些

LEO1@LEO1> select count(*) from leo5;                   總記錄數是72010

  COUNT(*)

----------

     72010

LEO1@LEO1> update leo5 set object_id=1 where object_id<70000;  我們更改了68840行，現在object_id=1 占 96%

68840 rows updated.

LEO1@LEO1> commit;                                  提交

LEO1@LEO1> update leo5 set object_id=2 where object_id>1;

3170 rows updated.

LEO1@LEO1> select count(*) from leo5 where object_id=1;     object_id等于1的有68840

  COUNT(*)

----------

     68840

LEO1@LEO1> select count(*) from leo5 where object_id=2;    object_id等于2的有3170

  COUNT(*)

----------

      3170

LEO1@LEO1> begin

     dbms_stats.gather_table_stats(             對leo5進行表分析

          wnname=>'leo1',

          tabname=>'leo5',

          cascade=>true,

          estimate_percent=>null,

          method_opt=>'for all columns size 254');

end;

/

  2    3    4    5    6    7    8    9  

PL/SQL procedure successfully completed.

LEO1@LEO1> select count(object_name) from leo5 where object_id=1;    查看執行計劃信息

Execution Plan

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

Plan hash value: 2750404108

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |     1 |    28 |   287   (1)| 00:00:04 |

|   1 |  SORT AGGREGATE    |      |     1 |    28 |            |          |

|*  2 |   TABLE ACCESS FULL| LEO5 | 68840 |  1882K|   287   (1)| 00:00:04 |

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

全表掃描68840，還是比較準確的，說明表分析生效了

LEO1@LEO1> select count(object_name) from leo5 where object_id=2;

Execution Plan

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

Plan hash value: 2542459021

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

| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |          |     1 |    28 |    57   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE             |          |     1 |    28 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| LEO5     |  3170 | 88760 |    57   (0)| 00:00:01 |

|*  3 |    INDEX RANGE SCAN          | IDX_LEO5 |  3170 |       |    11   (0)| 00:00:01 |

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

對于比較少的行走索引也是正確的

LEO1@LEO1> update leo5 set object_id=3 where rownum<60000;    修改了一下object_id分布

59999 rows updated.

LEO1@LEO1> select count(*) from leo5 where object_id=1;   object_id的值從68840變成了8857

  COUNT(*)

----------

      8857

LEO1@LEO1> commit;      提交

Commit complete.

LEO1@LEO1> select count(object_name) from leo5 where object_id=1;

Execution Plan

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

Plan hash value: 2750404108

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

| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT   |      |     1 |    28 |   287   (1)| 00:00:04 |

|   1 |  SORT AGGREGATE    |      |     1 |    28 |            |          |

|*  2 |   TABLE ACCESS FULL| LEO5 | 68840 |  1882K|   287   (1)| 00:00:04 |

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

此時CBO依然選擇走全表掃描，我們從記錄數的變化上就可以知道應該走索引效率更高些，就像object_id=2的執行計劃一樣INDEX RANGE SCAN代價更小些，為什么CBO會選擇了錯誤的執行計劃呢？這是因為我們雖然修改了記錄值但沒有及時更新leo5表的對象統計信息，CBO還是使用了當初最早的統計信息，所以在計算COST的時候還是認為走全表掃描的代價最優。下面我們再把對象統計信息重新統計一下，得出最新的代價列表進行篩選。

LEO1@LEO1> begin

     dbms_stats.gather_table_stats(

          wnname=>'leo1',

          tabname=>'leo5',

          cascade=>true,

          estimate_percent=>null,

          method_opt=>'for all columns size 254');

end;

/

  2    3    4    5    6    7    8    9  

PL/SQL procedure successfully completed.

LEO1@LEO1> alter session set events '10053 trace name context forever,level 1';   啟動10053事件

Session altered.

LEO1@LEO1> select count(object_name) from leo5 where object_id=1;     執行SQL語句

COUNT(OBJECT_NAME)

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

              8857

LEO1@LEO1> alter session set events '10053 trace name context off';       關閉10053事件

Session altered.

LEO1@LEO1> select value from v$diag_info where name='Default Trace File';  查看trace文件

VALUE

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

/u01/app/oracle/diag/rdbms/leo1/LEO1/trace/LEO1_ora_22298.trc

[oracle@leonarding1 trace]$ vim LEO1_ora_22298.trc      查看生成的trace文件內容

Table Stats::
  Table:  LEO5  Alias:  LEO5
    #Rows: 72010  #Blks:  1051  AvgRowLen:  75.00  
Index Stats::
  Index: IDX_ LEO5  Col#: 1
    LVLS: 0  #LB: 1  #DK: 3  LB/K: 1.00  DB/K: 1.00  CLUF: 2.00
Access path analysis for LEO5
***************************************
SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for LEO5[LEO5]
  Table: LEO5  Alias: LEO5
    Card: Original: 72010.000000  Rounded: 72010  Computed: 72010.00  Non Adjusted: 72010.00
  Access Path: TableScan
    Cost:  287.55  Resp: 287.55  Degree: 0
      Cost_io: 287.00  Cost_cpu: 22598123
      Resp_io: 287.00  Resp_cpu: 22598123
  Access Path: index (AllEqRange)
    Index: IDX_LEO5
    resc_io: 31.00  resc_cpu: 12862199
    ix_sel: 0.333333 ix_sel_with_filters: 0.333333
    Cost: 31.33  Resp: 31.33  Degree: 1

LEO1@LEO1> select count(object_name) from leo5 where object_id=1;

Execution Plan

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

Plan hash value: 2542459021

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

| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |

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

|   0 | SELECT STATEMENT             |          |     1 |    28 |   158   (0)| 00:00:02 |

|   1 |  SORT AGGREGATE             |          |     1 |    28 |            |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| LEO5     |  8857 |   242K|   158   (0)| 00:00:02 |

|*  3 |    INDEX RANGE SCAN          | IDX_LEO5 |  8857 |       |    31   (0)| 00:00:01 |

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

小結：經過對比CBO最終選擇了索引，當我們更新完統計信息，CBO選擇了正確的執行計劃

10053 cost 執行計劃 CBO  計算公式  10046  選擇執行計劃