ORACLEE 10053 事件
alter table emp enable row movement;
alter table emp shrink space cascade;
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 選擇執行計劃
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/235507/viewspace-1195911/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 10053事件事件
- 【10053 事件】10053事件的跟蹤檔案解析事件
- Oracle 10053 事件Oracle事件
- Oracle 10053事件Oracle事件
- 解析10053事件事件
- 10053事件初探.TXT事件
- 深入解析10053事件事件
- 10046 事件 與 10053 事件事件
- 【最佳化】10053事件事件
- ORACLE 深入解析10053事件Oracle事件
- 深入解析10053事件(ZT)事件
- oracle 10053診斷事件Oracle事件
- oracle診斷事件及深入解析10053事件Oracle事件
- 10053事件處理步驟事件
- oracle中的10053事件命令Oracle事件
- 【原創】ORACLE 深入解析10053事件Oracle事件
- 透過10053事件分析一個SQL執行計劃事件SQL
- 使用10053事件跟蹤CBO優化器決策(上)事件優化
- 使用10053事件跟蹤CBO優化器決策(下)事件優化
- 藉助10053跟蹤事件理解SQL執行過程事件SQL
- histogram與10053(zt)Histogram
- 【效能】解讀10053檔案
- Index Range Scan成本與10053Index
- django中出現 錯誤 Errno 10053Django
- Index Range Scan成本 Histogram 和 10053IndexHistogram
- sql_trace、10046、10053、tkprofSQL
- loadruner11 socket指令碼-10053錯誤指令碼
- 使用10046 ,10053 並讀懂 trace 檔案
- SQL調整優化與10053跟蹤分析一例SQL優化
- Event 10053 執行計劃 繫結變數 Bind peeking變數
- 生產環境使用10053分析Oracle的執行計劃Oracle
- SQL調整優化與10053跟蹤分析一例(zt)SQL優化
- JS事件(事件冒泡和事件捕獲)JS事件
- 事件 滑鼠事件 表單事件 from表單事件
- javaScript事件(五)事件型別之滑鼠事件JavaScript事件型別
- [20111220]Capturing 10053 trace files.txtAPT
- React 事件和 Dom 事件React事件
- onscroll 事件和onScrollCapture事件事件APT