【性能优化】 之 10053 事件
生活随笔
收集整理的這篇文章主要介紹了
【性能优化】 之 10053 事件
小編覺得挺不錯的,現在分享給大家,幫大家做個參考.
1.驗證全表掃描的成本計算公式，貼出執行計劃和計算公式。<br>
2.給出B-tree索引 Unique scan的成本計算公式，貼出執行計劃和計算公式。<br>
3.通過10053事件分析一個SQL執行計劃的產生過程，需要貼出trace中的相關信息和必要的文字說明。<br>
4.當統計信息不準確時，CBO可能產生錯誤的執行計劃，請給出這樣的一個例子，在10053 trace中找到CBO出錯的位置，并給出必要的文字說明。<br>
========================================================================================================
1.驗證全表掃描的成本計算公式，貼出執行計劃和計算公式。<br>
答：
C:\Users\Administrator>sqlplus tang/sa@orcl
SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 19 15:35:55 2013
Copyright (c) 1982, 2010, Oracle.? All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table t12 as select * from dba_objects;
Table created.
SQL>
--對表t12進行統計分析，不做直方圖
SQL> begin
? 2? dbms_stats.gather_table_stats(user,'T12',CASCADE=>TRUE,ESTIMATE_PERCENT=> NULL,
? 3? METHOD_OPT=>'for all columns size 1');
? 4? end;
? 5? /
PL/SQL procedure successfully completed.
SQL>
SQL> set linesize 800;
SQL> set pagesize 400;
--數據庫塊大小8K
SQL>show parameter db_block_size
?
NAME???????????????????????????????? TYPE??????? VALUE
------------------------------------ ----------- ------------------------------
db_block_size??????????????????????? integer???? 8192
--查看一次IO讀多少個塊
SQL> show parameter db_file;
NAME?? ??? ??? ??? ??? ??? ??? ??? ?TYPE?? ??? ??? ??? ??? ??? ??? ??? ?VALUE
-------?? ??? ??? ??? ??? ??? ??? ??? ?-------------?? ??? ??? ??? ??? ??? ?----------------------
db_file_multiblock_read_count??????? integer?????????????????????????????? 128
db_file_name_convert???????????????? string
db_files???????????????????????????? integer?????????????????????????????? 200
SQL>
--查看t表所用塊數
SQL> select blocks from user_tables where table_name='T12';
??? BLOCKS
----------
????? 1115
SQL>
SQL>select * from sys.aux_stats$
? ??? ?SNAME?? ??? ??? ?PNAME?? ??? ?PVAL1?? ??? ??? ?PVAL2
?? ?-----------------------------------------------------
1?? ?SYSSTATS_INFO?? ?STATUS?? ??? ??? ??? ??? ??? ?COMPLETED
2?? ?SYSSTATS_INFO?? ?DSTART?? ??? ??? ??? ??? ??? ?11-03-2011 06:38
3?? ?SYSSTATS_INFO?? ?DSTOP?? ??? ??? ??? ??? ??? ?11-03-2011 06:38
4?? ?SYSSTATS_INFO?? ?FLAGS?? ??? ?1?? ?
5?? ?SYSSTATS_MAIN?? ?CPUSPEEDNW?? ?1720.20725388601?? ?
6?? ?SYSSTATS_MAIN?? ?IOSEEKTIM?? ?10?? ?
7?? ?SYSSTATS_MAIN?? ?IOTFRSPEED?? ?4096?? ?
8?? ?SYSSTATS_MAIN?? ?SREADTIM?? ??? ?
9?? ?SYSSTATS_MAIN?? ?MREADTIM?? ??? ?
10?? ?SYSSTATS_MAIN?? ?CPUSPEED?? ??? ?
11?? ?SYSSTATS_MAIN?? ?MBRC?? ??? ?
12?? ?SYSSTATS_MAIN?? ?MAXTHR?? ??? ?
13?? ?SYSSTATS_MAIN?? ?SLAVETHR?? ??? ?
SQL> set autot trace exp;
SQL> select * from t12;
Execution Plan
----------------------------------------------------------
Plan hash value: 2727786061
--------------------------------------------------------------------------
| Id? | Operation???????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
--------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT? |????? | 76437 |? 7315K|?? 306?? (1)| 00:00:04 |
|?? 1 |? TABLE ACCESS FULL| T12? | 76437 |? 7315K|?? 306?? (1)| 00:00:04 |
--------------------------------------------------------------------------
SQL>
成本計算公式：
?Cost = (
???????? #SRds +
???????? #MRds * mreadtim/sreadtim +
???????? CPUCycles / (cpuspeed*sreadtim)
???????? )
?--注釋：
?#SRDs - number of single block reads?? ?單塊讀 的塊數
?#MRDs - number of multi block reads?? ?多塊多 的塊數
?#CPUCycles - number of CPU Cycles?? ??? ?CPU頻率
?sreadtim? - single block read time?? ??? ?單塊讀的時間
?mreadtim - multi block read time?? ??? ?多塊讀的時間
?cpuspeed? -? CPU cycles per second?? ??? ?CPU當前速度
1.#SRds=0 因為是全表掃描；
2.#MRds=塊數/多塊讀參數值=1115/128 =8.7109375 ?
SELECT TABLE_NAME,T.BLOCKS
FROM DBA_TABLES T WHERE T.TABLE_NAME='T12'
? ??? ?TABLE_NAME?? ?BLOCKS
?? ?-----------------------
1?? ?T12?? ?1115
3.#mreadtim
mreadtim =ioseektim+db_file_multiblock_count*db_block_size/iotftspeed=10+128*8192/4096=266
?? ?相關參數查詢：
?? ?SQL> select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM';
?? ??? ? PVAL1
?? ?----------
?? ??? ??? ?10
?? ?SQL> select value from v$parameter? where name='db_file_multiblock_read_count';
?? ??? ?VALUE
?? ??? ?---------------------------------------------------------------------------------------
?? ??? ?128
?? ?SQL> select value from v$parameter where name='db_block_size';
?? ?VALUE
?? ?--------------------------------------------------------------------------------
?? ?8192
?? ?SQL>? select pval1 from SYS.aux_stats$ where pname='IOTFRSPEED';
?? ??? ? PVAL1
?? ?----------
?? ??? ?? 4096
sreadtim =ioseektim+db_block_size/iotfrspeed=10+8192/4096=12
?相關參數數值：
??? SQL> select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM';
????? PVAL1
?----------
???????? 10
???? SQL> select value from v$parameter where name='db_block_size';
???? VALUE
???? --------------------------------------------------------------------------------------
???? 8192
???? SQL> select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED';
????? PVAL1
??? ----------
?????? 4096
?
CPUCycles? = 40808.316
CPUCycles等于 PLAN_TABLE里面的CPU_COST數值。
?? ?SQL> explain plan for select * from t12;
?? ?Explained.
?? ?
?? ?SQL> select cpu_cost from plan_table;
?? ? CPU_COST
?? ? --------------
?? ?40808316
?? ??? ?所以CPUCycles數值為40808316 ms，換算成s為 40808.316。
?? ?
6、cpuspeed
?? ??? ? cpuspeed 等于 CPUSPEEDNW的數值。
?? ??? ? SQL> select pval1 from sys.aux_stats$ where pname='CPUSPEEDNW';
?? ??? ?? PVAL1
?? ? ----------
?? ? 1720.20725
cpuspeed =? 1720.20725
?
Cost =#SRds+#MRds* mreadtim/sreadtim+#CPUCycles/(cpuspeed*sreadtim)
=0+(1115/128) * 266 / 12+? 40808.316/(1720.20725* 12)
?SQL> select ceil(0+1115/128 * 266 / 12+? 40808.316/(1720.20725* 12)) from dual;
?=196
?
196 與執行計劃的306 差異為? 113，差異很大。還得再找原因。
經測試，如果把參數db_file_multiblock_read_count修改成 16以后，
上面的公式計算出的成本 為：
mreadtim =ioseektim+db_file_multiblock_count*db_block_size/iotftspeed=10+16*8192/4096=42
Cost = ceil(0+(1115/16) * 42 / 12+? 40808.316/(1720.20725* 12)) =246
倒是很執行計劃的計算沒有多少差別了。
SQL> alter session set db_file_multiblock_read_count=16;
Session altered.
SQL> show parameter db_file_multi;
NAME?? ??? ??? ??? ??? ??? ??? ??? ?TYPE?? ????? VALUE
------ ------------------------------------------------------
db_file_multiblock_read_count??????? integer????? 16
SQL> set autot trace exp;
SQL> select * from t12;
Execution Plan
----------------------------------------------------------
Plan hash value: 2727786061
--------------------------------------------------------------------------
| Id? | Operation???????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
--------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT? |????? | 76437 |? 7315K|?? 248?? (1)| 00:00:03 |
|?? 1 |? TABLE ACCESS FULL| T12? | 76437 |? 7315K|?? 248?? (1)| 00:00:03 |
--------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
2.給出B-tree索引 Unique scan的成本計算公式，貼出執行計劃和計算公式。<br>
?? ?SQL>
?? ?SQL> set autot trace exp;
?? ?SQL> select * from t12;
?? ?Execution Plan
?? ?----------------------------------------------------------
?? ?Plan hash value: 2727786061
?? ?--------------------------------------------------------------------------
?? ?| Id? | Operation???????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
?? ?--------------------------------------------------------------------------
?? ?|?? 0 | SELECT STATEMENT? |????? | 76437 |? 7315K|?? 306?? (1)| 00:00:04 |
?? ?|?? 1 |? TABLE ACCESS FULL| T12? | 76437 |? 7315K|?? 306?? (1)| 00:00:04 |
?? ?--------------------------------------------------------------------------
?? ?SQL> show parameter version;
?? ?SQL>
?? ?SQL>
?? ?SQL> create unique index pk_t12_id on t12(object_id);
?? ?Index created.
?? ?SQL> set linesize 200;
?? ?SQL> set pagesize 500;
?? ?SQL> set autot trace exp
?? ?SQL> ;
?? ?? 1* create unique index pk_t12_id on t12(object_id)
?? ?SQL> select * from t12 where object_id=1000;
?? ?Execution Plan
?? ?----------------------------------------------------------
?? ?Plan hash value: 1871783013
?? ?-----------------------------------------------------------------------------------------
?? ?| Id? | Operation?????????????????? | Name????? | Rows? | Bytes | Cost (%CPU)| Time???? |
?? ?-----------------------------------------------------------------------------------------
?? ?|?? 0 | SELECT STATEMENT??????????? |?????????? |???? 1 |??? 98 |???? 2?? (0)| 00:00:01 |
?? ?|?? 1 |? TABLE ACCESS BY INDEX ROWID| T12?????? |???? 1 |??? 98 |???? 2?? (0)| 00:00:01 |
?? ?|*? 2 |?? INDEX UNIQUE SCAN???????? | PK_T12_ID |???? 1 |?????? |???? 1?? (0)| 00:00:01 |
?? ?-----------------------------------------------------------------------------------------
?? ?Predicate Information (identified by operation id):
?? ?---------------------------------------------------
?? ??? 2 - access("OBJECT_ID"=1000)
?? ?SQL> set autot off;
?? ?SQL> select index_name,blevel,leaf_blocks,clustering_factor,num_rows from user_indexes where index_name='PK_T12_ID';
?? ?INDEX_NAME????????? BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR?? NUM_ROWS
?? ?------------------ ---------- ----------- ----------------- ----------
?? ?PK_T12_ID????????? 1???????? 159????????????? 1299????? 76430
?? ?SQL>
?? ?--計算Unique scan成本
?? ? Unique scan的成本計算公式=blevel+1=1+1=2
?? ? --小結：
?? ? 手工計算的Unique scan的成本為1與執行計劃的Cost相同
--------------------------------------------------------------------------------------------------
3.通過10053事件分析一個SQL執行計劃的產生過程，需要貼出trace中的相關信息和必要的文字說明。<br>
3.1為了查看有索引時的執行計劃，先建立一個索引
SQL> create index idx_t12_owner on t12(owner);
Index created.
SQL>
SQL> execute dbms_stats.gather_table_stats(user,'T12',cascade=>true,estimate_percent=>null,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed
SQL>
SQL> create table t12_owner as select * from (select distinct owner from t12 ) where rownum<=10;
Table created.
SQL> execute dbms_stats.gather_table_stats(user,'T12_OWNER',cascade=>true,estimate_percent=>null,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed
SQL> alter system flush shared_pool;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> set linesize 400;
SQL> set pagesize 400;
SQL> select count(*) from t12_owner o,t12? t where o.owner=t.owner;
Execution Plan
----------------------------------------------------------
Plan hash value: 1246238126
-------------------------------------------------------------------------------------
| Id? | Operation?????????? | Name????????? | Rows? | Bytes | Cost (%CPU)| Time???? |
-------------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT??? |?????????????? |???? 1 |??? 15 |??? 53?? (0)| 00:00:01 |
|?? 1 |? SORT AGGREGATE???? |?????????????? |???? 1 |??? 15 |??????????? |????????? |
|?? 2 |?? NESTED LOOPS????? |?????????????? | 24657 |?? 361K|??? 53?? (0)| 00:00:01 |
|?? 3 |??? TABLE ACCESS FULL| T12_OWNER???? |??? 10 |??? 90 |???? 3?? (0)| 00:00:01 |
|*? 4 |??? INDEX RANGE SCAN | IDX_T12_OWNER |? 2466 | 14796 |???? 5?? (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 4 - access("O"."OWNER"="T"."OWNER")
SQL>
SQL> set autot off;
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> alter session set events '10053 trace name context forever, level 2';
Session altered.
SQL> select count(*) from t12_owner o,t12? t where o.owner=t.owner;
? COUNT(*)
----------
????? 6162
SQL> alter session set events '10053 trace name context off';
Session altered.
SQL>EXIT
trace file contect:
-------------------------------------------------------------------------------------------------
跟蹤文件內容解讀
? ***************************************
? PARAMETERS IN OPT_PARAM HINT
? ****************************
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
JF: Checking validity of join factorization for query block SEL$1 (#0)
JF: Bypassed: not a UNION or UNION-ALL query block.
ST: not valid since star transformation parameter is FALSE
TE: Checking validity of table expansion for query block SEL$1 (#0)
TE: Bypassed: No partitioned table in query block.
CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
CBQT: Validity checks failed for 530jrynjh1h0c.
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE:???? CSE not performed on query block SEL$1 (#0).
OBYE:?? Considering Order-by Elimination from view SEL$1 (#0)
***************************
Order-by elimination (OBYE)
***************************
OBYE:???? OBYE bypassed: no order by to eliminate.
JE:?? Considering Join Elimination on query block SEL$1 (#0)
*************************
Join Elimination (JE)?? ?
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "TANG"."T12_OWNER" "O","TANG"."T12" "T" WHERE "O"."OWNER"="T"."OWNER"
JE:?? cfro: T12_OWNER objn:98415 col#:1 dfro:T12 dcol#:1
JE:?? cfro: T12 objn:98532 col#:1 dfro:T12_OWNER dcol#:1
SQL:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "TANG"."T12_OWNER" "O","TANG"."T12" "T" WHERE "O"."OWNER"="T"."OWNER"
Query block SEL$1 (#0) unchanged
CVM: Considering view merge in query block SEL$1 (#0)
OJE: Begin: find best directive for query block SEL$1 (#0)
OJE: End: finding best directive for query block SEL$1 (#0)
CNT:?? Considering count(col) to count(*) on query block SEL$1 (#0)
*************************
Count(col) to Count(*) (CNT)
*************************
CNT:???? COUNT() to COUNT(*) not done.
JE:?? Considering Join Elimination on query block SEL$1 (#0)
*************************
Join Elimination (JE)?? ?
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "TANG"."T12_OWNER" "O","TANG"."T12" "T" WHERE "O"."OWNER"="T"."OWNER"
JE:?? cfro: T12_OWNER objn:98415 col#:1 dfro:T12 dcol#:1
JE:?? cfro: T12 objn:98532 col#:1 dfro:T12_OWNER dcol#:1
SQL:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "TANG"."T12_OWNER" "O","TANG"."T12" "T" WHERE "O"."OWNER"="T"."OWNER"
Query block SEL$1 (#0) unchanged
query block SEL$1 (#0) unchanged
Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
JF: Checking validity of join factorization for query block SEL$1 (#0)
JF: Bypassed: not a UNION or UNION-ALL query block.
ST: not valid since star transformation parameter is FALSE
TE: Checking validity of table expansion for query block SEL$1 (#0)
TE: Bypassed: No partitioned table in query block.
CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
CBQT: Validity checks failed for 530jrynjh1h0c.
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE:???? CSE not performed on query block SEL$1 (#0).
SU: Considering subquery unnesting in query block SEL$1 (#0)
********************
Subquery Unnest (SU)
********************
SJC: Considering set-join conversion in query block SEL$1 (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: not performed
JE:?? Considering Join Elimination on query block SEL$1 (#0)
*************************
Join Elimination (JE)?? ?
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "TANG"."T12_OWNER" "O","TANG"."T12" "T" WHERE "O"."OWNER"="T"."OWNER"
JE:?? cfro: T12_OWNER objn:98415 col#:1 dfro:T12 dcol#:1
JE:?? cfro: T12 objn:98532 col#:1 dfro:T12_OWNER dcol#:1
SQL:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "TANG"."T12_OWNER" "O","TANG"."T12" "T" WHERE "O"."OWNER"="T"."OWNER"
Query block SEL$1 (#0) unchanged
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
PM:???? PM bypassed: Outer query contains no views.
PM:???? PM bypassed: Outer query contains no views.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
"O"."OWNER"="T"."OWNER"
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "O"."OWNER"="T"."OWNER"
apadrv-start sqlid=5873318390038315020
? :
??? call(in-use=2056, alloc=16344), compile(in-use=58144, alloc=62216), execution(in-use=3352, alloc=4032)
*******************************************
Peeked values of the binds in SQL statement
*******************************************
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "TANG"."T12_OWNER" "O","TANG"."T12" "T" WHERE "O"."OWNER"="T"."OWNER"
kkoqbc: optimizing query block SEL$1 (#0)
?????? ?
??????? :
??? call(in-use=2112, alloc=16344), compile(in-use=59408, alloc=62216), execution(in-use=3352, alloc=4032)
kkoqbc-subheap (create addr=0x000000001F2CFAA0)
****************
QUERY BLOCK TEXT
****************
select count(*) from t12_owner o,t12? t where o.owner=t.owner
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0
? fro(0): flg=0 objn=98532 hint_alias="O"@"SEL$1"
? fro(1): flg=0 objn=98415 hint_alias="T"@"SEL$1"
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
? Using NOWORKLOAD Stats
? CPUSPEEDNW: 1720 millions instructions/sec (default is 100)
? IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
? IOSEEKTIM:? 10 milliseconds (default is 10)
? MBRC:?????? NO VALUE blocks (default is 8)
***************************************
BASE STATISTICAL INFORMATION
基礎統計信息
***********************
Table Stats::
? Table: T12? Alias:? T
#? 表：T12 統計信息中的： 行數：76437，數據塊：1115；平均行長度
#Column? 字段OWNER 的統計信息（直方圖中的字段統計信息）
#AvgLen：6? 平均長度
#NDV: 31 (NUM_DISTINCT 列的不重復值數)
# Nulls: 0 (NUM_NULLS? 列的空行數)
# Density: 0.032258 (DENSITY 列密度，沒有直方圖的情況下= 1/NDV)
??? #Rows: 76437? #Blks:? 1115? AvgRowLen:? 98.00? ChainCnt:? 0.00
? Column (#1): OWNER(
??? AvgLen: 6 NDV: 31 Nulls: 0 Density: 0.032258
索引的統計信息
#LVLS: 1? (BLEVEL BTREE索引高度)
#LB: 180 (LEAF_BLOCKS 索引葉塊數)
#DK: 31 (DISTINCT_KEYS 不重復索引關鍵字)
#LB/K: 5.00 (AVG_LEAF_BLOCKS_PER_KEY 葉塊/關鍵字)
#DB/K: 65.00 (AVG_DATA_BLOCKS_PER_KEY 數據塊/關鍵字)
#CLUF: 2031.00 (CLUSTERING_FACTOR 索引聚合因子)
#主要內容：索引幾層；葉子塊數；多少個唯一鍵值； 每個鍵值有多少個葉塊；每個鍵值有多少個數據塊聚簇因子
Index Stats::
? Index: IDX_T12_OWNER? Col#: 1
??? LVLS: 1? #LB: 180? #DK: 31? LB/K: 5.00? DB/K: 65.00? CLUF: 2031.00
? Index: PK_T12_ID? Col#: 4
??? LVLS: 1? #LB: 159? #DK: 76430? LB/K: 1.00? DB/K: 1.00? CLUF: 1299.00
***********************
表T12_OWNER 的統計信息
Table Stats::
? Table: T12_OWNER? Alias:? O
??? #Rows: 10? #Blks:? 4? AvgRowLen:? 9.00? ChainCnt:? 0.00
關聯字段OWNER 的統計信息（直方圖中的字段統計信息）
? Column (#1): OWNER(
??? AvgLen: 9 NDV: 10 Nulls: 0 Density: 0.100000
Access path analysis for T12_OWNER
***************************************
#分析單表的訪問路徑，有些信息來自于dba_tables
#原始行數 近似值 精確值 非修正值
SINGLE TABLE ACCESS PATH
? Single Table Cardinality Estimation for T12_OWNER[O]
? Table: T12_OWNER? Alias: O
? #(Computed=Card: Original *FF Filter Factor? FF在沒有直方圖的情況下=Density)
??? Card: Original: 10.000000? Rounded: 10? Computed: 10.00? Non Adjusted: 10.00
? Access Path: TableScan
??? Cost:? 3.00? Resp: 3.00? Degree: 0
????? Cost_io: 3.00? Cost_cpu: 29986
????? Resp_io: 3.00? Resp_cpu: 29986
#全表掃描成本
? Best:: AccessPath: TableScan ?
???????? Cost: 3.00? Degree: 1? Resp: 3.00? Card: 10.00? Bytes: 0
Access path analysis for T12
***************************************
SINGLE TABLE ACCESS PATH
? Single Table Cardinality Estimation for T12[T]#單表基數估計
? Table: T12? Alias: T
??? Card: Original: 76437.000000? Rounded: 76437? Computed: 76437.00? Non Adjusted: 76437.00
? Access Path: TableScan?? ??? ??? ??? ??? ??? ?#全表掃描代價
??? Cost:? 304.94? Resp: 304.94? Degree: 0
????? Cost_io: 304.00? Cost_cpu: 19405956
????? Resp_io: 304.00? Resp_cpu: 19405956
? Access Path: index (index (FFS))?? ??? ??? ??? ?#索引快速全掃描
??? Index: IDX_T12_OWNER
??? resc_io: 51.00? resc_cpu: 10454299?? ??? ??? ??? ?#串行訪問代價=51（因為索引是串行存儲的）
??? ix_sel: 0.000000? ix_sel_with_filters: 1.000000? #帶過濾條件索引選擇率
? Access Path: index (FFS)
??? Cost:? 51.51? Resp: 51.51? Degree: 1?? ??? ?#索引并行訪問代價=51.51>51(串行訪問代價)
????? Cost_io: 51.00? Cost_cpu: 10454299
????? Resp_io: 51.00? Resp_cpu: 10454299
? Access Path: index (FullScan)?? ??? ??? ??? ??? ?#索引全掃描
??? Index: IDX_T12_OWNER
??? resc_io: 181.00? resc_cpu: 16576381
??? ix_sel: 1.000000? ix_sel_with_filters: 1.000000 #ix_sel索引選擇率 ix_sel_with_filters帶過濾條件索引選擇率
??? Cost: 181.80? Resp: 181.80? Degree: 1
******** Begin index join costing ********
? ****** trying bitmap/domain indexes ******?? ?#索引關聯掃描
? Access Path: index (FullScan)
??? Index: IDX_T12_OWNER
??? resc_io: 181.00? resc_cpu: 16576381
??? ix_sel: 1.000000? ix_sel_with_filters: 1.000000
??? Cost: 181.80? Resp: 181.80? Degree: 0
? Access Path: index (FullScan)?? ??? ??? ??? ??? ?#全索引掃描
??? Index: IDX_T12_OWNER
??? resc_io: 181.00? resc_cpu: 16576381
??? ix_sel: 1.000000? ix_sel_with_filters: 1.000000
??? Cost: 181.80? Resp: 181.80? Degree: 0
? Bitmap nodes:?? ??? ??? ??? ??? ??? ??? ??? ??? ?#BITMAP 掃描
??? Used IDX_T12_OWNER
????? Cost = 227.253778, sel = 1.000000
? ****** finished trying bitmap/domain indexes ******
******** End index join costing ********
? Best:: AccessPath: IndexFFS
? Index: IDX_T12_OWNER
???????? Cost: 51.51? Degree: 1? Resp: 51.51? Card: 76437.00? Bytes: 0
***************************************
#關聯查詢—驅動表的選擇
#優化器的統計和計算
#把各種情況羅列出來，選擇最優的查詢路徑
#分別用兩個表做驅動表，及各種關聯方式的成本 對比。
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS?? ??? ?選擇執行計劃
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]:? T12_OWNER[O]#0? T12[T]#1 #關聯的表對象
***************
Now joining: T12[T]#1?? ??? ?#1 現在要用T12_OWNER小表關聯T12大表
***************
NL Join?? ??? ??? ??? ??? ??? ?#NL Join嵌套循環關聯? OUTER TABLE 驅動表
#確定一個驅動表(outer table)，另一個表為inner table，驅動表中的每一行與inner表中的相應記錄JOIN
? Outer table: Card: 10.00? Cost: 3.00? Resp: 3.00? Degree: 1? Bytes: 9
Access path analysis for T12?? ??? ?
? Inner table: T12? Alias: T?? ??? ?#inner TABLE 內表 T12
? Access Path: TableScan
??? NL Join:? Cost: 3034.40? Resp: 3034.40? Degree: 1?? ??? ?#全表掃描-嵌套循環關聯COST=3034.40
????? Cost_io: 3025.00? Cost_cpu: 194089542
????? Resp_io: 3025.00? Resp_cpu: 194089542
? Access Path: index (index (FFS))?? ??? ??? ??? ??? ??? ??? ?#快速索引掃描
??? Index: IDX_T12_OWNER
??? resc_io: 48.90? resc_cpu: 10454299?? ??? ??? ??? ??? ??? ?#串行訪問代價
??? ix_sel: 0.000000? ix_sel_with_filters: 1.000000
? Inner table: T12? Alias: T
? Access Path: index (FFS)?? ??? ??? ??? ??? ??? ??? ??? ??? ?#并行訪問代價
??? NL Join:? Cost: 497.07? Resp: 497.07? Degree: 1
????? Cost_io: 492.00? Cost_cpu: 104572978
????? Resp_io: 492.00? Resp_cpu: 104572978
? Access Path: index (AllEqJoinGuess)
??? Index: IDX_T12_OWNER
??? resc_io: 5.00? resc_cpu: 529657
??? ix_sel: 0.032258? ix_sel_with_filters: 0.032258
??? NL Join : Cost: 53.26? Resp: 53.26? Degree: 1
????? Cost_io: 53.00? Cost_cpu: 5326558
????? Resp_io: 53.00? Resp_cpu: 5326558
? Best NL cost: 53.26?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?#最優NL JOIN? 嵌套循環關聯 成本 ：53.26
????????? resc: 53.26? resc_io: 53.00? resc_cpu: 5326558?? ?#串行方式的代價 IO代價+CPU代價?? ?
????????? resp: 53.26? resp_io: 53.00? resc_cpu: 5326558?? ?#并行方式的代價
Join Card:? 24657.096774 = outer (10.000000) * inner (76437.000000) * sel (0.032258)
Join Card - Rounded: 24657 Computed: 24657.10
? Outer table:? T12_OWNER? Alias: O
??? resc: 3.00? card 10.00? bytes: 9? deg: 1? resp: 3.00
? Inner table:? T12? Alias: T
??? resc: 51.51? card: 76437.00? bytes: 6? deg: 1? resp: 51.51
??? using dmeth: 2? #groups: 1
??? SORT ressource???????? Sort statistics
????? Sort width:???????? 980 Area size:???? 1048576 Max Area size:?? 171642880
????? Degree:?????????????? 1
????? Blocks to Sort: 1 Row size:???? 20 Total Rows:???????????? 10
????? Initial runs:?? 1 Merge passes:? 0 IO Cost / pass:????????? 0
????? Total IO sort cost: 0????? Total CPU sort cost: 20643984
????? Total Temp space used: 0
??? SORT ressource???????? Sort statistics
????? Sort width:???????? 980 Area size:???? 1048576 Max Area size:?? 171642880
????? Degree:?????????????? 1
????? Blocks to Sort: 160 Row size:???? 17 Total Rows:????????? 76437
????? Initial runs:?? 2 Merge passes:? 1 IO Cost / pass:???????? 88
????? Total IO sort cost: 248????? Total CPU sort cost: 80448497
????? Total Temp space used: 1860000
? SM join: Resc: 307.41? Resp: 307.41? [multiMatchCost=0.00]
SM Join?? ?#先排序后合并關聯
? SM cost: 307.41
???? resc: 307.41 resc_io: 302.00 resc_cpu: 111576765
???? resp: 307.41 resp_io: 302.00 resp_cpu: 111576765
? Outer table:? T12_OWNER? Alias: O
??? resc: 3.00? card 10.00? bytes: 9? deg: 1? resp: 3.00
? Inner table:? T12? Alias: T
??? resc: 51.51? card: 76437.00? bytes: 6? deg: 1? resp: 51.51
??? using dmeth: 2? #groups: 1
??? Cost per ptn: 0.87? #ptns: 1
??? hash_area: 256 (max=41905) buildfrag: 1? probefrag: 168? ppasses: 1
? Hash join: Resc: 55.38? Resp: 55.38? [multiMatchCost=0.00]
HA Join?? ?
? HA cost: 55.38 ?
???? resc: 55.38 resc_io: 54.00 resc_cpu: 28450728
???? resp: 55.38 resp_io: 54.00 resp_cpu: 28450728
Best:: JoinMethod: NestedLoop
?????? Cost: 53.26? Degree: 1? Resp: 53.26? Card: 24657.10 Bytes: 15
***********************
Best so far:? Table#: 0? cost: 3.0015? card: 10.0000? bytes: 90
????????????? Table#: 1? cost: 53.2580? card: 24657.0968? bytes: 369855
***********************
Join order[2]:? T12[T]#1? T12_OWNER[O]#0
***************
Now joining: T12_OWNER[O]#0
***************
NL Join?? ?#NL Join 嵌套循環關聯?? ?與T12為驅動表
? Outer table: Card: 76437.00? Cost: 51.51? Resp: 51.51? Degree: 1? Bytes: 6
Access path analysis for T12_OWNER
? Inner table: T12_OWNER? Alias: O
? Access Path: TableScan
??? NL Join:? Cost: 82971.54? Resp: 82971.54? Degree: 1
????? Cost_io: 82860.00? Cost_cpu: 2302475836
????? Resp_io: 82860.00? Resp_cpu: 2302475836
? Best NL cost: 82971.54
????????? resc: 82971.54? resc_io: 82860.00? resc_cpu: 2302475836
????????? resp: 82971.54? resp_io: 82860.00? resc_cpu: 2302475836
Join Card:? 24657.096774 = outer (76437.000000) * inner (10.000000) * sel (0.032258)
Join Card - Rounded: 24657 Computed: 24657.10
? Outer table:? T12? Alias: T
??? resc: 51.51? card 76437.00? bytes: 6? deg: 1? resp: 51.51
? Inner table:? T12_OWNER? Alias: O
??? resc: 3.00? card: 10.00? bytes: 9? deg: 1? resp: 3.00
??? using dmeth: 2? #groups: 1
??? SORT ressource???????? Sort statistics
????? Sort width:???????? 980 Area size:???? 1048576 Max Area size:?? 171642880
????? Degree:?????????????? 1
????? Blocks to Sort: 160 Row size:???? 17 Total Rows:????????? 76437
????? Initial runs:?? 2 Merge passes:? 1 IO Cost / pass:???????? 88
????? Total IO sort cost: 248????? Total CPU sort cost: 80448497
????? Total Temp space used: 1860000
??? SORT ressource???????? Sort statistics
????? Sort width:???????? 980 Area size:???? 1048576 Max Area size:?? 171642880
????? Degree:?????????????? 1
????? Blocks to Sort: 1 Row size:???? 20 Total Rows:???????????? 10
????? Initial runs:?? 1 Merge passes:? 0 IO Cost / pass:????????? 0
????? Total IO sort cost: 0????? Total CPU sort cost: 20643984
????? Total Temp space used: 0
? SM join: Resc: 307.41? Resp: 307.41? [multiMatchCost=0.00]
SM Join
? SM cost: 307.41
???? resc: 307.41 resc_io: 302.00 resc_cpu: 111576765
???? resp: 307.41 resp_io: 302.00 resp_cpu: 111576765
SM Join (with index on outer)
? Access Path: index (FullScan)
??? Index: IDX_T12_OWNER
??? resc_io: 181.00? resc_cpu: 16576381
??? ix_sel: 1.000000? ix_sel_with_filters: 1.000000
??? Cost: 181.80? Resp: 181.80? Degree: 1
? Outer table:? T12? Alias: T
??? resc: 181.80? card 76437.00? bytes: 6? deg: 1? resp: 181.80
? Inner table:? T12_OWNER? Alias: O
??? resc: 3.00? card: 10.00? bytes: 9? deg: 1? resp: 3.00
??? using dmeth: 2? #groups: 1
??? SORT ressource???????? Sort statistics
????? Sort width:???????? 980 Area size:???? 1048576 Max Area size:?? 171642880
????? Degree:?????????????? 1
????? Blocks to Sort: 1 Row size:???? 20 Total Rows:???????????? 10
????? Initial runs:?? 1 Merge passes:? 0 IO Cost / pass:????????? 0
????? Total IO sort cost: 0????? Total CPU sort cost: 20643984
????? Total Temp space used: 0
? SM join: Resc: 185.80? Resp: 185.80? [multiMatchCost=0.00]
? Outer table:? T12? Alias: T
??? resc: 51.51? card 76437.00? bytes: 6? deg: 1? resp: 51.51
? Inner table:? T12_OWNER? Alias: O
??? resc: 3.00? card: 10.00? bytes: 9? deg: 1? resp: 3.00
??? using dmeth: 2? #groups: 1
??? Cost per ptn: 1.06? #ptns: 1
??? hash_area: 256 (max=41905) buildfrag: 168? probefrag: 1? ppasses: 1
? Hash join: Resc: 55.68? Resp: 55.68? [multiMatchCost=0.12]
? Outer table:? T12_OWNER? Alias: O
??? resc: 3.00? card 10.00? bytes: 9? deg: 1? resp: 3.00
? Inner table:? T12? Alias: T
??? resc: 51.51? card: 76437.00? bytes: 6? deg: 1? resp: 51.51
??? using dmeth: 2? #groups: 1
??? Cost per ptn: 0.87? #ptns: 1
??? hash_area: 256 (max=41905) buildfrag: 1? probefrag: 168? ppasses: 1
? Hash join: Resc: 55.38? Resp: 55.38? [multiMatchCost=0.00]
HA Join?? ?哈希關聯
? HA cost: 55.38 swapped?? ?
???? resc: 55.38 resc_io: 54.00 resc_cpu: 28450728
???? resp: 55.38 resp_io: 54.00 resp_cpu: 28450728
Join order aborted: cost > best plan cost
***********************
(newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000
*********************************
Number of join permutations tried: 2
*********************************
Consider using bloom filter between O[T12_OWNER] and T[T12] with ??
kkoBloomFilter: join ndv:0 reduction:1.000000 (limit:0.500000)? rejected because not a hash join
Enumerating distribution method (advanced)
--- Distribution method for
join between O[T12_OWNER](serial) and T[T12](serial); jm = 12; right side access path = IndexRange
---- NLJ default -> BROADCAST-LEFT
(newjo-save)??? [1 0 ]
Trying or-Expansion on query block SEL$1 (#0)
Transfer Optimizer annotations for query block SEL$1 (#0)
id=0 frofkks[i] (index start key) predicate="O"."OWNER"="T"."OWNER"
id=0 frofkke[i] (index stop key) predicate="O"."OWNER"="T"."OWNER"
Final cost for query block SEL$1 (#0) - All Rows Plan:?? ??? ??? ??? ??? ?#最后得出最佳成本 執行計劃
? Best join order: 1
? Cost: 53.2580? Degree: 1? Card: 24657.0000? Bytes: 369855
? Resc: 53.2580? Resc_io: 53.0000? Resc_cpu: 5326558
? Resp: 53.2580? Resp_io: 53.0000? Resc_cpu: 5326558
kkoqbc-subheap (delete addr=0x000000001F2CFAA0, in-use=27840, alloc=32840)
kkoqbc-end:
??????? :
??? call(in-use=14400, alloc=65656), compile(in-use=60960, alloc=62216), execution(in-use=3352, alloc=4032)
kkoqbc: finish optimizing query block SEL$1 (#0)
apadrv-end
????????? :
??? call(in-use=14400, alloc=65656), compile(in-use=61944, alloc=62216), execution(in-use=3352, alloc=4032)
Starting SQL statement dump
user_id=84 user_name=TANG module=SQL*Plus action=
sql_id=530jrynjh1h0c plan_hash_value=1246238126 problem_type=3
----- Current SQL Statement for this session (sql_id=530jrynjh1h0c) -----
select count(*) from t12_owner o,t12? t where o.owner=t.owner
sql_text_length=62
sql=select count(*) from t12_owner o,t12? t where o.owner=t.owner
----- Explain Plan Dump -----
----- Plan Table -----
?
============
Plan Table
============
--------------------------------------------+-----------------------------------+
| Id? | Operation??????????? | Name???????? | Rows? | Bytes | Cost? | Time????? |
--------------------------------------------+-----------------------------------+
| 0?? | SELECT STATEMENT???? |????????????? |?????? |?????? |??? 53 |?????????? |
| 1?? |? SORT AGGREGATE????? |????????????? |???? 1 |??? 15 |?????? |?????????? |
| 2?? |?? NESTED LOOPS?????? |????????????? |?? 24K |? 361K |??? 53 |? 00:00:01 |
| 3?? |??? TABLE ACCESS FULL | T12_OWNER??? |??? 10 |??? 90 |???? 3 |? 00:00:01 |
| 4?? |??? INDEX RANGE SCAN? | IDX_T12_OWNER|? 2466 |?? 14K |???? 5 |? 00:00:01 |
--------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
4 - access("O"."OWNER"="T"."OWNER")
?
Content of other_xml column
===========================
? db_version???? : 11.2.0.3
? parse_schema?? : TANG
? plan_hash????? : 1246238126
? plan_hash_2??? : 632309206
? Outline Data:
? /*+
??? BEGIN_OUTLINE_DATA
????? IGNORE_OPTIM_EMBEDDED_HINTS
????? OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
????? DB_VERSION('11.2.0.3')
????? ALL_ROWS
????? OUTLINE_LEAF(@"SEL$1")
????? FULL(@"SEL$1" "O"@"SEL$1")
????? INDEX(@"SEL$1" "T"@"SEL$1" ("T12"."OWNER"))
????? LEADING(@"SEL$1" "O"@"SEL$1" "T"@"SEL$1")
????? USE_NL(@"SEL$1" "T"@"SEL$1")
??? END_OUTLINE_DATA
? */
?
從上面的跟蹤日志中可以看到，最后的執行計劃和在SQLPLUS 中是一致的。
--------------------------------------------------------------------------------------------------
4.當統計信息不準確時，CBO可能產生錯誤的執行計劃，請給出這樣的一個例子，
在10053 trace中找到CBO出錯的位置，并給出必要的文字說明。<br>
C:\Users\Administrator>sqlplus tang/tang@orcl
SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 20 13:35:09 2013
Copyright (c) 1982, 2010, Oracle.? All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> drop table t12;
Table dropped.
SQL> create table t12 as select * from dba_objects;
Table created.
SQL> create index idx_t12_id on t12(object_id);
Index created.
SQL> select count(0) from t12;
? COUNT(0)
----------
???? 76438
SQL> update t12 set object_id=1 where object_id<=70000;
69175 rows updated.
SQL> update t12 set object_id=2 where object_id>70000;
7256 rows updated.
SQL> execute dbms_stats.gather_table_stats(user,'T12',CASCADE=>TRUE,ESTIMATE_PERCENT=>NULL,METHOD_OPT=>'for all columns size 254');
PL/SQL procedure successfully completed.
SQL>
SQL> select count(object_name) from t12 where object_id=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 4279859672
---------------------------------------------------------------------------
| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |???? 1 |??? 28 |?? 305?? (1)| 00:00:04 |
|?? 1 |? SORT AGGREGATE??? |????? |???? 1 |??? 28 |??????????? |????????? |
|*? 2 |?? TABLE ACCESS FULL| T12? | 69175 |? 1891K|?? 305?? (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 2 - filter("OBJECT_ID"=1)
SQL> select count(object_name) from t12 where object_id=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 3968484635
-------------------------------------------------------------------------------------------
| Id? | Operation??????????????????? | Name?????? | Rows? | Bytes | Cost (%CPU)| Time???? |
-------------------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT???????????? |??????????? |???? 1 |??? 28 |?? 129?? (0)| 00:00:02 |
|?? 1 |? SORT AGGREGATE????????????? |??????????? |???? 1 |??? 28 |??????????? |????????? |
|?? 2 |?? TABLE ACCESS BY INDEX ROWID| T12??????? |? 7256 |?? 198K|?? 129?? (0)| 00:00:02 |
|*? 3 |??? INDEX RANGE SCAN????????? | IDX_T12_ID |? 7256 |?????? |??? 25?? (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 3 - access("OBJECT_ID"=2)
SQL>
上面表中數據 OBJECT_ID=1 時，數據有69175 ，OBJECT_ID=2 時，數據有7256；
從上面的執行計劃可以看出，第一個使用了全表搜索，第二個查詢使用了索引，是正確的。
因為先從索引中查找 ojbect_id=2 的數據比較少，走索引，再到表中查找數據，速度更快。
下面我們對數據進行一個修正。把OBJECT_ID 的分布進行修改。
SQL> set autot off;
SQL> update t12 set object_id=3 where rownum<=60000;
60000 rows updated.
SQL> select count(0) from t12 where object_id=1;
? COUNT(0)
----------
????? 9207
SQL> set autot trace expl;
SQL> select count(object_name) from t12 where object_id=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 4279859672
---------------------------------------------------------------------------
| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |???? 1 |??? 28 |?? 305?? (1)| 00:00:04 |
|?? 1 |? SORT AGGREGATE??? |????? |???? 1 |??? 28 |??????????? |????????? |
|*? 2 |?? TABLE ACCESS FULL| T12? | 69175 |? 1891K|?? 305?? (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 2 - filter("OBJECT_ID"=1)
SQL>
經修改后，object_id=1 的數據只有 9207，在表中的分布應該是很少了。應該走索引才對。
這就是因為表中的統計信息已不正確的原因。從上面的執行計劃也可以看出，執行計劃中統計出的
數據行數 ：69175 就是在修改前的OBJECT_ID=1 的數量。
我們來跟蹤一下10053的跟蹤日志。查找原因。
**************************
Predicate Move-Around (PM)
**************************
PM:???? PM bypassed: Outer query contains no views.
PM:???? PM bypassed: Outer query contains no views.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
"T12"."OBJECT_ID"=:B1
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "T12"."OBJECT_ID"=:B1
apadrv-start sqlid=7904889763193459797
? :
??? call(in-use=1520, alloc=16344), compile(in-use=55176, alloc=57056), execution(in-use=3296, alloc=4032)
*******************************************
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
?Bind#0
? oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
? oacflg=10 fl2=0100 frm=00 csi=00 siz=24 off=0
? kxsbbbfp=ae8c6460? bln=22? avl=02? flg=09
? value=1
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT("T12"."OBJECT_NAME") "COUNT(OBJECT_NAME)" FROM "TANG"."T12" "T12" WHERE "T12"."OBJECT_ID"=:B1
kkoqbc: optimizing query block SEL$1 (#0)
?????? ?
??????? :
??? call(in-use=1568, alloc=16344), compile(in-use=56104, alloc=57056), execution(in-use=3432, alloc=4032)
kkoqbc-subheap (create addr=0x000000001819A658)
****************
QUERY BLOCK TEXT
****************
select count(object_name) from t12 where object_id=:"SYS_B_0"
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
? fro(0): flg=0 objn=98536 hint_alias="T12"@"SEL$1"
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
? Using NOWORKLOAD Stats
? CPUSPEEDNW: 1720 millions instructions/sec (default is 100)
? IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
? IOSEEKTIM:? 10 milliseconds (default is 10)
? MBRC:?????? NO VALUE blocks (default is 8)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
? Table: T12? Alias: T12
??? #Rows: 76438? #Blks:? 1115? AvgRowLen:? 96.00? ChainCnt:? 0.00
?? ?#行數 塊數 平均行長
Index Stats::
? Index: IDX_T12_ID? Col#: 4
??? LVLS: 1? #LB: 263? #DK: 2? LB/K: 131.00? DB/K: 545.00? CLUF: 1091.00
#索引幾層葉子塊數多少個唯一鍵值每個鍵值有多少個葉塊每個鍵值有多少個數據塊聚簇因子
Access path analysis for T12
***************************************
SINGLE TABLE ACCESS PATH
? Single Table Cardinality Estimation for T12[T12]
? Column (#4):
??? NewDensity:0.047468, OldDensity:0.000007 BktCnt:76431, PopBktCnt:76431, PopValCnt:2, NDV:2
? Column (#4): OBJECT_ID(
??? AvgLen: 3 NDV: 2 Nulls: 7 Density: 0.047468 Min: 1 Max: 2
??? Histogram: Freq? #Bkts: 2? UncompBkts: 76431? EndPtVals: 2
? Table: T12? Alias: T12
??? Card: Original: 76438.000000? Rounded: 69175? Computed: 69175.00? Non Adjusted: 69175.00
#原始行數 近似值 精確值 非修正值
#可以看出，問題就出在這里，ORACLE 認為，現在OBJECT_ID=1 的數據就是： 69175
? Access Path: TableScan 全表掃描代價
??? Cost:? 305.35? Resp: 305.35? Degree: 0
????? Cost_io: 304.00? Cost_cpu: 27814286
????? Resp_io: 304.00? Resp_cpu: 27814286
? Access Path: index (AllEqRange)? 索引全掃描代價
??? Index: IDX_T12_ID
??? resc_io: 1227.00? resc_cpu: 37100607
??? ix_sel: 0.905065? ix_sel_with_filters: 0.905065
?? ?#ix_sel 索引選擇率
?? ?#ix_sel_with_filters帶過濾條件索引選擇率
?? ?#這里也可以看出，ORACLE認為索引選擇率很高
??? Cost: 1228.80? Resp: 1228.80? Degree: 1
? Best:: AccessPath: TableScan
???????? Cost: 305.35? Degree: 1? Resp: 305.35? Card: 69175.00? Bytes: 0
得出結論走全表搜索優于索引
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]:? T12[T12]#0
***********************
Best so far:? Table#: 0? cost: 305.3474? card: 69175.0000? bytes: 1936900
***********************
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000
*********************************
Number of join permutations tried: 1
*********************************
Enumerating distribution method (advanced)
Trying or-Expansion on query block SEL$1 (#0)
Transfer Optimizer annotations for query block SEL$1 (#0)
id=0 frofand predicate="T12"."OBJECT_ID"=:B1
Final cost for query block SEL$1 (#0) - All Rows Plan:
? Best join order: 1
? Cost: 305.3474? Degree: 1? Card: 69175.0000? Bytes: 1936900
? Resc: 305.3474? Resc_io: 304.0000? Resc_cpu: 27814286
? Resp: 305.3474? Resp_io: 304.0000? Resc_cpu: 27814286
kkoqbc-subheap (delete addr=0x000000001819A658, in-use=13600, alloc=16408)
kkoqbc-end:
??????? :
??? call(in-use=29304, alloc=49184), compile(in-use=57072, alloc=61200), execution(in-use=3800, alloc=4032)
kkoqbc: finish optimizing query block SEL$1 (#0)
apadrv-end
????????? :
??? call(in-use=29304, alloc=49184), compile(in-use=57984, alloc=61200), execution(in-use=3800, alloc=4032)
Starting SQL statement dump
user_id=84 user_name=TANG module=SQL*Plus action=
sql_id=6vcygbf9w952p plan_hash_value=-15107624 problem_type=3
----- Current SQL Statement for this session (sql_id=6vcygbf9w952p) -----
?select count(object_name) from t12 where object_id=:"SYS_B_0"
sql_text_length=63
sql= select count(object_name) from t12 where object_id=:"SYS_B_0"
----- Explain Plan Dump -----
----- Plan Table -----
?
============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id? | Operation?????????? | Name??? | Rows? | Bytes | Cost? | Time????? |
--------------------------------------+-----------------------------------+
| 0?? | SELECT STATEMENT??? |???????? |?????? |?????? |?? 305 |?????????? |
| 1?? |? SORT AGGREGATE???? |???????? |???? 1 |??? 28 |?????? |?????????? |
| 2?? |?? TABLE ACCESS FULL | T12???? |?? 68K | 1892K |?? 305 |? 00:00:04 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter("OBJECT_ID"=:SYS_B_0)
?
Content of other_xml column
===========================
? db_version???? : 11.2.0.3
? parse_schema?? : TANG
? plan_hash????? : 4279859672
? plan_hash_2??? : 3898508299
Peeked Binds
============
? Bind variable information
??? position=1
??? datatype(code)=2
??? datatype(string)=NUMBER
??? precision=0
??? scale=0
??? max length=22
??? value=1
? Outline Data:
? /*+
??? BEGIN_OUTLINE_DATA
????? IGNORE_OPTIM_EMBEDDED_HINTS
????? OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
????? DB_VERSION('11.2.0.3')
????? ALL_ROWS
????? OUTLINE_LEAF(@"SEL$1")
????? FULL(@"SEL$1" "T12"@"SEL$1")
??? END_OUTLINE_DATA
? */
?
2.給出B-tree索引 Unique scan的成本計算公式，貼出執行計劃和計算公式。<br>
3.通過10053事件分析一個SQL執行計劃的產生過程，需要貼出trace中的相關信息和必要的文字說明。<br>
4.當統計信息不準確時，CBO可能產生錯誤的執行計劃，請給出這樣的一個例子，在10053 trace中找到CBO出錯的位置，并給出必要的文字說明。<br>
========================================================================================================
1.驗證全表掃描的成本計算公式，貼出執行計劃和計算公式。<br>
答：
C:\Users\Administrator>sqlplus tang/sa@orcl
SQL*Plus: Release 11.2.0.1.0 Production on Thu Dec 19 15:35:55 2013
Copyright (c) 1982, 2010, Oracle.? All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table t12 as select * from dba_objects;
Table created.
SQL>
--對表t12進行統計分析，不做直方圖
SQL> begin
? 2? dbms_stats.gather_table_stats(user,'T12',CASCADE=>TRUE,ESTIMATE_PERCENT=> NULL,
? 3? METHOD_OPT=>'for all columns size 1');
? 4? end;
? 5? /
PL/SQL procedure successfully completed.
SQL>
SQL> set linesize 800;
SQL> set pagesize 400;
--數據庫塊大小8K
SQL>show parameter db_block_size
?
NAME???????????????????????????????? TYPE??????? VALUE
------------------------------------ ----------- ------------------------------
db_block_size??????????????????????? integer???? 8192
--查看一次IO讀多少個塊
SQL> show parameter db_file;
NAME?? ??? ??? ??? ??? ??? ??? ??? ?TYPE?? ??? ??? ??? ??? ??? ??? ??? ?VALUE
-------?? ??? ??? ??? ??? ??? ??? ??? ?-------------?? ??? ??? ??? ??? ??? ?----------------------
db_file_multiblock_read_count??????? integer?????????????????????????????? 128
db_file_name_convert???????????????? string
db_files???????????????????????????? integer?????????????????????????????? 200
SQL>
--查看t表所用塊數
SQL> select blocks from user_tables where table_name='T12';
??? BLOCKS
----------
????? 1115
SQL>
SQL>select * from sys.aux_stats$
? ??? ?SNAME?? ??? ??? ?PNAME?? ??? ?PVAL1?? ??? ??? ?PVAL2
?? ?-----------------------------------------------------
1?? ?SYSSTATS_INFO?? ?STATUS?? ??? ??? ??? ??? ??? ?COMPLETED
2?? ?SYSSTATS_INFO?? ?DSTART?? ??? ??? ??? ??? ??? ?11-03-2011 06:38
3?? ?SYSSTATS_INFO?? ?DSTOP?? ??? ??? ??? ??? ??? ?11-03-2011 06:38
4?? ?SYSSTATS_INFO?? ?FLAGS?? ??? ?1?? ?
5?? ?SYSSTATS_MAIN?? ?CPUSPEEDNW?? ?1720.20725388601?? ?
6?? ?SYSSTATS_MAIN?? ?IOSEEKTIM?? ?10?? ?
7?? ?SYSSTATS_MAIN?? ?IOTFRSPEED?? ?4096?? ?
8?? ?SYSSTATS_MAIN?? ?SREADTIM?? ??? ?
9?? ?SYSSTATS_MAIN?? ?MREADTIM?? ??? ?
10?? ?SYSSTATS_MAIN?? ?CPUSPEED?? ??? ?
11?? ?SYSSTATS_MAIN?? ?MBRC?? ??? ?
12?? ?SYSSTATS_MAIN?? ?MAXTHR?? ??? ?
13?? ?SYSSTATS_MAIN?? ?SLAVETHR?? ??? ?
SQL> set autot trace exp;
SQL> select * from t12;
Execution Plan
----------------------------------------------------------
Plan hash value: 2727786061
--------------------------------------------------------------------------
| Id? | Operation???????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
--------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT? |????? | 76437 |? 7315K|?? 306?? (1)| 00:00:04 |
|?? 1 |? TABLE ACCESS FULL| T12? | 76437 |? 7315K|?? 306?? (1)| 00:00:04 |
--------------------------------------------------------------------------
SQL>
成本計算公式：
?Cost = (
???????? #SRds +
???????? #MRds * mreadtim/sreadtim +
???????? CPUCycles / (cpuspeed*sreadtim)
???????? )
?--注釋：
?#SRDs - number of single block reads?? ?單塊讀 的塊數
?#MRDs - number of multi block reads?? ?多塊多 的塊數
?#CPUCycles - number of CPU Cycles?? ??? ?CPU頻率
?sreadtim? - single block read time?? ??? ?單塊讀的時間
?mreadtim - multi block read time?? ??? ?多塊讀的時間
?cpuspeed? -? CPU cycles per second?? ??? ?CPU當前速度
1.#SRds=0 因為是全表掃描；
2.#MRds=塊數/多塊讀參數值=1115/128 =8.7109375 ?
SELECT TABLE_NAME,T.BLOCKS
FROM DBA_TABLES T WHERE T.TABLE_NAME='T12'
? ??? ?TABLE_NAME?? ?BLOCKS
?? ?-----------------------
1?? ?T12?? ?1115
3.#mreadtim
mreadtim =ioseektim+db_file_multiblock_count*db_block_size/iotftspeed=10+128*8192/4096=266
?? ?相關參數查詢：
?? ?SQL> select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM';
?? ??? ? PVAL1
?? ?----------
?? ??? ??? ?10
?? ?SQL> select value from v$parameter? where name='db_file_multiblock_read_count';
?? ??? ?VALUE
?? ??? ?---------------------------------------------------------------------------------------
?? ??? ?128
?? ?SQL> select value from v$parameter where name='db_block_size';
?? ?VALUE
?? ?--------------------------------------------------------------------------------
?? ?8192
?? ?SQL>? select pval1 from SYS.aux_stats$ where pname='IOTFRSPEED';
?? ??? ? PVAL1
?? ?----------
?? ??? ?? 4096
sreadtim =ioseektim+db_block_size/iotfrspeed=10+8192/4096=12
?相關參數數值：
??? SQL> select pval1 from sys.aux_stats$ where pname = 'IOSEEKTIM';
????? PVAL1
?----------
???????? 10
???? SQL> select value from v$parameter where name='db_block_size';
???? VALUE
???? --------------------------------------------------------------------------------------
???? 8192
???? SQL> select pval1 from sys.aux_stats$ where pname = 'IOTFRSPEED';
????? PVAL1
??? ----------
?????? 4096
?
CPUCycles? = 40808.316
CPUCycles等于 PLAN_TABLE里面的CPU_COST數值。
?? ?SQL> explain plan for select * from t12;
?? ?Explained.
?? ?
?? ?SQL> select cpu_cost from plan_table;
?? ? CPU_COST
?? ? --------------
?? ?40808316
?? ??? ?所以CPUCycles數值為40808316 ms，換算成s為 40808.316。
?? ?
6、cpuspeed
?? ??? ? cpuspeed 等于 CPUSPEEDNW的數值。
?? ??? ? SQL> select pval1 from sys.aux_stats$ where pname='CPUSPEEDNW';
?? ??? ?? PVAL1
?? ? ----------
?? ? 1720.20725
cpuspeed =? 1720.20725
?
Cost =#SRds+#MRds* mreadtim/sreadtim+#CPUCycles/(cpuspeed*sreadtim)
=0+(1115/128) * 266 / 12+? 40808.316/(1720.20725* 12)
?SQL> select ceil(0+1115/128 * 266 / 12+? 40808.316/(1720.20725* 12)) from dual;
?=196
?
196 與執行計劃的306 差異為? 113，差異很大。還得再找原因。
經測試，如果把參數db_file_multiblock_read_count修改成 16以后，
上面的公式計算出的成本 為：
mreadtim =ioseektim+db_file_multiblock_count*db_block_size/iotftspeed=10+16*8192/4096=42
Cost = ceil(0+(1115/16) * 42 / 12+? 40808.316/(1720.20725* 12)) =246
倒是很執行計劃的計算沒有多少差別了。
SQL> alter session set db_file_multiblock_read_count=16;
Session altered.
SQL> show parameter db_file_multi;
NAME?? ??? ??? ??? ??? ??? ??? ??? ?TYPE?? ????? VALUE
------ ------------------------------------------------------
db_file_multiblock_read_count??????? integer????? 16
SQL> set autot trace exp;
SQL> select * from t12;
Execution Plan
----------------------------------------------------------
Plan hash value: 2727786061
--------------------------------------------------------------------------
| Id? | Operation???????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
--------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT? |????? | 76437 |? 7315K|?? 248?? (1)| 00:00:03 |
|?? 1 |? TABLE ACCESS FULL| T12? | 76437 |? 7315K|?? 248?? (1)| 00:00:03 |
--------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------
2.給出B-tree索引 Unique scan的成本計算公式，貼出執行計劃和計算公式。<br>
?? ?SQL>
?? ?SQL> set autot trace exp;
?? ?SQL> select * from t12;
?? ?Execution Plan
?? ?----------------------------------------------------------
?? ?Plan hash value: 2727786061
?? ?--------------------------------------------------------------------------
?? ?| Id? | Operation???????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
?? ?--------------------------------------------------------------------------
?? ?|?? 0 | SELECT STATEMENT? |????? | 76437 |? 7315K|?? 306?? (1)| 00:00:04 |
?? ?|?? 1 |? TABLE ACCESS FULL| T12? | 76437 |? 7315K|?? 306?? (1)| 00:00:04 |
?? ?--------------------------------------------------------------------------
?? ?SQL> show parameter version;
?? ?SQL>
?? ?SQL>
?? ?SQL> create unique index pk_t12_id on t12(object_id);
?? ?Index created.
?? ?SQL> set linesize 200;
?? ?SQL> set pagesize 500;
?? ?SQL> set autot trace exp
?? ?SQL> ;
?? ?? 1* create unique index pk_t12_id on t12(object_id)
?? ?SQL> select * from t12 where object_id=1000;
?? ?Execution Plan
?? ?----------------------------------------------------------
?? ?Plan hash value: 1871783013
?? ?-----------------------------------------------------------------------------------------
?? ?| Id? | Operation?????????????????? | Name????? | Rows? | Bytes | Cost (%CPU)| Time???? |
?? ?-----------------------------------------------------------------------------------------
?? ?|?? 0 | SELECT STATEMENT??????????? |?????????? |???? 1 |??? 98 |???? 2?? (0)| 00:00:01 |
?? ?|?? 1 |? TABLE ACCESS BY INDEX ROWID| T12?????? |???? 1 |??? 98 |???? 2?? (0)| 00:00:01 |
?? ?|*? 2 |?? INDEX UNIQUE SCAN???????? | PK_T12_ID |???? 1 |?????? |???? 1?? (0)| 00:00:01 |
?? ?-----------------------------------------------------------------------------------------
?? ?Predicate Information (identified by operation id):
?? ?---------------------------------------------------
?? ??? 2 - access("OBJECT_ID"=1000)
?? ?SQL> set autot off;
?? ?SQL> select index_name,blevel,leaf_blocks,clustering_factor,num_rows from user_indexes where index_name='PK_T12_ID';
?? ?INDEX_NAME????????? BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR?? NUM_ROWS
?? ?------------------ ---------- ----------- ----------------- ----------
?? ?PK_T12_ID????????? 1???????? 159????????????? 1299????? 76430
?? ?SQL>
?? ?--計算Unique scan成本
?? ? Unique scan的成本計算公式=blevel+1=1+1=2
?? ? --小結：
?? ? 手工計算的Unique scan的成本為1與執行計劃的Cost相同
--------------------------------------------------------------------------------------------------
3.通過10053事件分析一個SQL執行計劃的產生過程，需要貼出trace中的相關信息和必要的文字說明。<br>
3.1為了查看有索引時的執行計劃，先建立一個索引
SQL> create index idx_t12_owner on t12(owner);
Index created.
SQL>
SQL> execute dbms_stats.gather_table_stats(user,'T12',cascade=>true,estimate_percent=>null,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed
SQL>
SQL> create table t12_owner as select * from (select distinct owner from t12 ) where rownum<=10;
Table created.
SQL> execute dbms_stats.gather_table_stats(user,'T12_OWNER',cascade=>true,estimate_percent=>null,method_opt=>'for all columns size 1');
PL/SQL procedure successfully completed
SQL> alter system flush shared_pool;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> set linesize 400;
SQL> set pagesize 400;
SQL> select count(*) from t12_owner o,t12? t where o.owner=t.owner;
Execution Plan
----------------------------------------------------------
Plan hash value: 1246238126
-------------------------------------------------------------------------------------
| Id? | Operation?????????? | Name????????? | Rows? | Bytes | Cost (%CPU)| Time???? |
-------------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT??? |?????????????? |???? 1 |??? 15 |??? 53?? (0)| 00:00:01 |
|?? 1 |? SORT AGGREGATE???? |?????????????? |???? 1 |??? 15 |??????????? |????????? |
|?? 2 |?? NESTED LOOPS????? |?????????????? | 24657 |?? 361K|??? 53?? (0)| 00:00:01 |
|?? 3 |??? TABLE ACCESS FULL| T12_OWNER???? |??? 10 |??? 90 |???? 3?? (0)| 00:00:01 |
|*? 4 |??? INDEX RANGE SCAN | IDX_T12_OWNER |? 2466 | 14796 |???? 5?? (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 4 - access("O"."OWNER"="T"."OWNER")
SQL>
SQL> set autot off;
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> alter session set events '10053 trace name context forever, level 2';
Session altered.
SQL> select count(*) from t12_owner o,t12? t where o.owner=t.owner;
? COUNT(*)
----------
????? 6162
SQL> alter session set events '10053 trace name context off';
Session altered.
SQL>EXIT
trace file contect:
-------------------------------------------------------------------------------------------------
跟蹤文件內容解讀
? ***************************************
? PARAMETERS IN OPT_PARAM HINT
? ****************************
***************************************
Column Usage Monitoring is ON: tracking level = 1
***************************************
Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
JF: Checking validity of join factorization for query block SEL$1 (#0)
JF: Bypassed: not a UNION or UNION-ALL query block.
ST: not valid since star transformation parameter is FALSE
TE: Checking validity of table expansion for query block SEL$1 (#0)
TE: Bypassed: No partitioned table in query block.
CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
CBQT: Validity checks failed for 530jrynjh1h0c.
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE:???? CSE not performed on query block SEL$1 (#0).
OBYE:?? Considering Order-by Elimination from view SEL$1 (#0)
***************************
Order-by elimination (OBYE)
***************************
OBYE:???? OBYE bypassed: no order by to eliminate.
JE:?? Considering Join Elimination on query block SEL$1 (#0)
*************************
Join Elimination (JE)?? ?
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "TANG"."T12_OWNER" "O","TANG"."T12" "T" WHERE "O"."OWNER"="T"."OWNER"
JE:?? cfro: T12_OWNER objn:98415 col#:1 dfro:T12 dcol#:1
JE:?? cfro: T12 objn:98532 col#:1 dfro:T12_OWNER dcol#:1
SQL:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "TANG"."T12_OWNER" "O","TANG"."T12" "T" WHERE "O"."OWNER"="T"."OWNER"
Query block SEL$1 (#0) unchanged
CVM: Considering view merge in query block SEL$1 (#0)
OJE: Begin: find best directive for query block SEL$1 (#0)
OJE: End: finding best directive for query block SEL$1 (#0)
CNT:?? Considering count(col) to count(*) on query block SEL$1 (#0)
*************************
Count(col) to Count(*) (CNT)
*************************
CNT:???? COUNT() to COUNT(*) not done.
JE:?? Considering Join Elimination on query block SEL$1 (#0)
*************************
Join Elimination (JE)?? ?
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "TANG"."T12_OWNER" "O","TANG"."T12" "T" WHERE "O"."OWNER"="T"."OWNER"
JE:?? cfro: T12_OWNER objn:98415 col#:1 dfro:T12 dcol#:1
JE:?? cfro: T12 objn:98532 col#:1 dfro:T12_OWNER dcol#:1
SQL:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "TANG"."T12_OWNER" "O","TANG"."T12" "T" WHERE "O"."OWNER"="T"."OWNER"
Query block SEL$1 (#0) unchanged
query block SEL$1 (#0) unchanged
Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
JF: Checking validity of join factorization for query block SEL$1 (#0)
JF: Bypassed: not a UNION or UNION-ALL query block.
ST: not valid since star transformation parameter is FALSE
TE: Checking validity of table expansion for query block SEL$1 (#0)
TE: Bypassed: No partitioned table in query block.
CBQT bypassed for query block SEL$1 (#0): no complex view, sub-queries or UNION (ALL) queries.
CBQT: Validity checks failed for 530jrynjh1h0c.
CSE: Considering common sub-expression elimination in query block SEL$1 (#0)
*************************
Common Subexpression elimination (CSE)
*************************
CSE:???? CSE not performed on query block SEL$1 (#0).
SU: Considering subquery unnesting in query block SEL$1 (#0)
********************
Subquery Unnest (SU)
********************
SJC: Considering set-join conversion in query block SEL$1 (#0)
*************************
Set-Join Conversion (SJC)
*************************
SJC: not performed
JE:?? Considering Join Elimination on query block SEL$1 (#0)
*************************
Join Elimination (JE)?? ?
*************************
SQL:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "TANG"."T12_OWNER" "O","TANG"."T12" "T" WHERE "O"."OWNER"="T"."OWNER"
JE:?? cfro: T12_OWNER objn:98415 col#:1 dfro:T12 dcol#:1
JE:?? cfro: T12 objn:98532 col#:1 dfro:T12_OWNER dcol#:1
SQL:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "TANG"."T12_OWNER" "O","TANG"."T12" "T" WHERE "O"."OWNER"="T"."OWNER"
Query block SEL$1 (#0) unchanged
PM: Considering predicate move-around in query block SEL$1 (#0)
**************************
Predicate Move-Around (PM)
**************************
PM:???? PM bypassed: Outer query contains no views.
PM:???? PM bypassed: Outer query contains no views.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
"O"."OWNER"="T"."OWNER"
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "O"."OWNER"="T"."OWNER"
apadrv-start sqlid=5873318390038315020
? :
??? call(in-use=2056, alloc=16344), compile(in-use=58144, alloc=62216), execution(in-use=3352, alloc=4032)
*******************************************
Peeked values of the binds in SQL statement
*******************************************
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT(*) "COUNT(*)" FROM "TANG"."T12_OWNER" "O","TANG"."T12" "T" WHERE "O"."OWNER"="T"."OWNER"
kkoqbc: optimizing query block SEL$1 (#0)
?????? ?
??????? :
??? call(in-use=2112, alloc=16344), compile(in-use=59408, alloc=62216), execution(in-use=3352, alloc=4032)
kkoqbc-subheap (create addr=0x000000001F2CFAA0)
****************
QUERY BLOCK TEXT
****************
select count(*) from t12_owner o,t12? t where o.owner=t.owner
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0
? fro(0): flg=0 objn=98532 hint_alias="O"@"SEL$1"
? fro(1): flg=0 objn=98415 hint_alias="T"@"SEL$1"
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
? Using NOWORKLOAD Stats
? CPUSPEEDNW: 1720 millions instructions/sec (default is 100)
? IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
? IOSEEKTIM:? 10 milliseconds (default is 10)
? MBRC:?????? NO VALUE blocks (default is 8)
***************************************
BASE STATISTICAL INFORMATION
基礎統計信息
***********************
Table Stats::
? Table: T12? Alias:? T
#? 表：T12 統計信息中的： 行數：76437，數據塊：1115；平均行長度
#Column? 字段OWNER 的統計信息（直方圖中的字段統計信息）
#AvgLen：6? 平均長度
#NDV: 31 (NUM_DISTINCT 列的不重復值數)
# Nulls: 0 (NUM_NULLS? 列的空行數)
# Density: 0.032258 (DENSITY 列密度，沒有直方圖的情況下= 1/NDV)
??? #Rows: 76437? #Blks:? 1115? AvgRowLen:? 98.00? ChainCnt:? 0.00
? Column (#1): OWNER(
??? AvgLen: 6 NDV: 31 Nulls: 0 Density: 0.032258
索引的統計信息
#LVLS: 1? (BLEVEL BTREE索引高度)
#LB: 180 (LEAF_BLOCKS 索引葉塊數)
#DK: 31 (DISTINCT_KEYS 不重復索引關鍵字)
#LB/K: 5.00 (AVG_LEAF_BLOCKS_PER_KEY 葉塊/關鍵字)
#DB/K: 65.00 (AVG_DATA_BLOCKS_PER_KEY 數據塊/關鍵字)
#CLUF: 2031.00 (CLUSTERING_FACTOR 索引聚合因子)
#主要內容：索引幾層；葉子塊數；多少個唯一鍵值； 每個鍵值有多少個葉塊；每個鍵值有多少個數據塊聚簇因子
Index Stats::
? Index: IDX_T12_OWNER? Col#: 1
??? LVLS: 1? #LB: 180? #DK: 31? LB/K: 5.00? DB/K: 65.00? CLUF: 2031.00
? Index: PK_T12_ID? Col#: 4
??? LVLS: 1? #LB: 159? #DK: 76430? LB/K: 1.00? DB/K: 1.00? CLUF: 1299.00
***********************
表T12_OWNER 的統計信息
Table Stats::
? Table: T12_OWNER? Alias:? O
??? #Rows: 10? #Blks:? 4? AvgRowLen:? 9.00? ChainCnt:? 0.00
關聯字段OWNER 的統計信息（直方圖中的字段統計信息）
? Column (#1): OWNER(
??? AvgLen: 9 NDV: 10 Nulls: 0 Density: 0.100000
Access path analysis for T12_OWNER
***************************************
#分析單表的訪問路徑，有些信息來自于dba_tables
#原始行數 近似值 精確值 非修正值
SINGLE TABLE ACCESS PATH
? Single Table Cardinality Estimation for T12_OWNER[O]
? Table: T12_OWNER? Alias: O
? #(Computed=Card: Original *FF Filter Factor? FF在沒有直方圖的情況下=Density)
??? Card: Original: 10.000000? Rounded: 10? Computed: 10.00? Non Adjusted: 10.00
? Access Path: TableScan
??? Cost:? 3.00? Resp: 3.00? Degree: 0
????? Cost_io: 3.00? Cost_cpu: 29986
????? Resp_io: 3.00? Resp_cpu: 29986
#全表掃描成本
? Best:: AccessPath: TableScan ?
???????? Cost: 3.00? Degree: 1? Resp: 3.00? Card: 10.00? Bytes: 0
Access path analysis for T12
***************************************
SINGLE TABLE ACCESS PATH
? Single Table Cardinality Estimation for T12[T]#單表基數估計
? Table: T12? Alias: T
??? Card: Original: 76437.000000? Rounded: 76437? Computed: 76437.00? Non Adjusted: 76437.00
? Access Path: TableScan?? ??? ??? ??? ??? ??? ?#全表掃描代價
??? Cost:? 304.94? Resp: 304.94? Degree: 0
????? Cost_io: 304.00? Cost_cpu: 19405956
????? Resp_io: 304.00? Resp_cpu: 19405956
? Access Path: index (index (FFS))?? ??? ??? ??? ?#索引快速全掃描
??? Index: IDX_T12_OWNER
??? resc_io: 51.00? resc_cpu: 10454299?? ??? ??? ??? ?#串行訪問代價=51（因為索引是串行存儲的）
??? ix_sel: 0.000000? ix_sel_with_filters: 1.000000? #帶過濾條件索引選擇率
? Access Path: index (FFS)
??? Cost:? 51.51? Resp: 51.51? Degree: 1?? ??? ?#索引并行訪問代價=51.51>51(串行訪問代價)
????? Cost_io: 51.00? Cost_cpu: 10454299
????? Resp_io: 51.00? Resp_cpu: 10454299
? Access Path: index (FullScan)?? ??? ??? ??? ??? ?#索引全掃描
??? Index: IDX_T12_OWNER
??? resc_io: 181.00? resc_cpu: 16576381
??? ix_sel: 1.000000? ix_sel_with_filters: 1.000000 #ix_sel索引選擇率 ix_sel_with_filters帶過濾條件索引選擇率
??? Cost: 181.80? Resp: 181.80? Degree: 1
******** Begin index join costing ********
? ****** trying bitmap/domain indexes ******?? ?#索引關聯掃描
? Access Path: index (FullScan)
??? Index: IDX_T12_OWNER
??? resc_io: 181.00? resc_cpu: 16576381
??? ix_sel: 1.000000? ix_sel_with_filters: 1.000000
??? Cost: 181.80? Resp: 181.80? Degree: 0
? Access Path: index (FullScan)?? ??? ??? ??? ??? ?#全索引掃描
??? Index: IDX_T12_OWNER
??? resc_io: 181.00? resc_cpu: 16576381
??? ix_sel: 1.000000? ix_sel_with_filters: 1.000000
??? Cost: 181.80? Resp: 181.80? Degree: 0
? Bitmap nodes:?? ??? ??? ??? ??? ??? ??? ??? ??? ?#BITMAP 掃描
??? Used IDX_T12_OWNER
????? Cost = 227.253778, sel = 1.000000
? ****** finished trying bitmap/domain indexes ******
******** End index join costing ********
? Best:: AccessPath: IndexFFS
? Index: IDX_T12_OWNER
???????? Cost: 51.51? Degree: 1? Resp: 51.51? Card: 76437.00? Bytes: 0
***************************************
#關聯查詢—驅動表的選擇
#優化器的統計和計算
#把各種情況羅列出來，選擇最優的查詢路徑
#分別用兩個表做驅動表，及各種關聯方式的成本 對比。
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS?? ??? ?選擇執行計劃
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]:? T12_OWNER[O]#0? T12[T]#1 #關聯的表對象
***************
Now joining: T12[T]#1?? ??? ?#1 現在要用T12_OWNER小表關聯T12大表
***************
NL Join?? ??? ??? ??? ??? ??? ?#NL Join嵌套循環關聯? OUTER TABLE 驅動表
#確定一個驅動表(outer table)，另一個表為inner table，驅動表中的每一行與inner表中的相應記錄JOIN
? Outer table: Card: 10.00? Cost: 3.00? Resp: 3.00? Degree: 1? Bytes: 9
Access path analysis for T12?? ??? ?
? Inner table: T12? Alias: T?? ??? ?#inner TABLE 內表 T12
? Access Path: TableScan
??? NL Join:? Cost: 3034.40? Resp: 3034.40? Degree: 1?? ??? ?#全表掃描-嵌套循環關聯COST=3034.40
????? Cost_io: 3025.00? Cost_cpu: 194089542
????? Resp_io: 3025.00? Resp_cpu: 194089542
? Access Path: index (index (FFS))?? ??? ??? ??? ??? ??? ??? ?#快速索引掃描
??? Index: IDX_T12_OWNER
??? resc_io: 48.90? resc_cpu: 10454299?? ??? ??? ??? ??? ??? ?#串行訪問代價
??? ix_sel: 0.000000? ix_sel_with_filters: 1.000000
? Inner table: T12? Alias: T
? Access Path: index (FFS)?? ??? ??? ??? ??? ??? ??? ??? ??? ?#并行訪問代價
??? NL Join:? Cost: 497.07? Resp: 497.07? Degree: 1
????? Cost_io: 492.00? Cost_cpu: 104572978
????? Resp_io: 492.00? Resp_cpu: 104572978
? Access Path: index (AllEqJoinGuess)
??? Index: IDX_T12_OWNER
??? resc_io: 5.00? resc_cpu: 529657
??? ix_sel: 0.032258? ix_sel_with_filters: 0.032258
??? NL Join : Cost: 53.26? Resp: 53.26? Degree: 1
????? Cost_io: 53.00? Cost_cpu: 5326558
????? Resp_io: 53.00? Resp_cpu: 5326558
? Best NL cost: 53.26?? ??? ??? ??? ??? ??? ??? ??? ??? ??? ?#最優NL JOIN? 嵌套循環關聯 成本 ：53.26
????????? resc: 53.26? resc_io: 53.00? resc_cpu: 5326558?? ?#串行方式的代價 IO代價+CPU代價?? ?
????????? resp: 53.26? resp_io: 53.00? resc_cpu: 5326558?? ?#并行方式的代價
Join Card:? 24657.096774 = outer (10.000000) * inner (76437.000000) * sel (0.032258)
Join Card - Rounded: 24657 Computed: 24657.10
? Outer table:? T12_OWNER? Alias: O
??? resc: 3.00? card 10.00? bytes: 9? deg: 1? resp: 3.00
? Inner table:? T12? Alias: T
??? resc: 51.51? card: 76437.00? bytes: 6? deg: 1? resp: 51.51
??? using dmeth: 2? #groups: 1
??? SORT ressource???????? Sort statistics
????? Sort width:???????? 980 Area size:???? 1048576 Max Area size:?? 171642880
????? Degree:?????????????? 1
????? Blocks to Sort: 1 Row size:???? 20 Total Rows:???????????? 10
????? Initial runs:?? 1 Merge passes:? 0 IO Cost / pass:????????? 0
????? Total IO sort cost: 0????? Total CPU sort cost: 20643984
????? Total Temp space used: 0
??? SORT ressource???????? Sort statistics
????? Sort width:???????? 980 Area size:???? 1048576 Max Area size:?? 171642880
????? Degree:?????????????? 1
????? Blocks to Sort: 160 Row size:???? 17 Total Rows:????????? 76437
????? Initial runs:?? 2 Merge passes:? 1 IO Cost / pass:???????? 88
????? Total IO sort cost: 248????? Total CPU sort cost: 80448497
????? Total Temp space used: 1860000
? SM join: Resc: 307.41? Resp: 307.41? [multiMatchCost=0.00]
SM Join?? ?#先排序后合并關聯
? SM cost: 307.41
???? resc: 307.41 resc_io: 302.00 resc_cpu: 111576765
???? resp: 307.41 resp_io: 302.00 resp_cpu: 111576765
? Outer table:? T12_OWNER? Alias: O
??? resc: 3.00? card 10.00? bytes: 9? deg: 1? resp: 3.00
? Inner table:? T12? Alias: T
??? resc: 51.51? card: 76437.00? bytes: 6? deg: 1? resp: 51.51
??? using dmeth: 2? #groups: 1
??? Cost per ptn: 0.87? #ptns: 1
??? hash_area: 256 (max=41905) buildfrag: 1? probefrag: 168? ppasses: 1
? Hash join: Resc: 55.38? Resp: 55.38? [multiMatchCost=0.00]
HA Join?? ?
? HA cost: 55.38 ?
???? resc: 55.38 resc_io: 54.00 resc_cpu: 28450728
???? resp: 55.38 resp_io: 54.00 resp_cpu: 28450728
Best:: JoinMethod: NestedLoop
?????? Cost: 53.26? Degree: 1? Resp: 53.26? Card: 24657.10 Bytes: 15
***********************
Best so far:? Table#: 0? cost: 3.0015? card: 10.0000? bytes: 90
????????????? Table#: 1? cost: 53.2580? card: 24657.0968? bytes: 369855
***********************
Join order[2]:? T12[T]#1? T12_OWNER[O]#0
***************
Now joining: T12_OWNER[O]#0
***************
NL Join?? ?#NL Join 嵌套循環關聯?? ?與T12為驅動表
? Outer table: Card: 76437.00? Cost: 51.51? Resp: 51.51? Degree: 1? Bytes: 6
Access path analysis for T12_OWNER
? Inner table: T12_OWNER? Alias: O
? Access Path: TableScan
??? NL Join:? Cost: 82971.54? Resp: 82971.54? Degree: 1
????? Cost_io: 82860.00? Cost_cpu: 2302475836
????? Resp_io: 82860.00? Resp_cpu: 2302475836
? Best NL cost: 82971.54
????????? resc: 82971.54? resc_io: 82860.00? resc_cpu: 2302475836
????????? resp: 82971.54? resp_io: 82860.00? resc_cpu: 2302475836
Join Card:? 24657.096774 = outer (76437.000000) * inner (10.000000) * sel (0.032258)
Join Card - Rounded: 24657 Computed: 24657.10
? Outer table:? T12? Alias: T
??? resc: 51.51? card 76437.00? bytes: 6? deg: 1? resp: 51.51
? Inner table:? T12_OWNER? Alias: O
??? resc: 3.00? card: 10.00? bytes: 9? deg: 1? resp: 3.00
??? using dmeth: 2? #groups: 1
??? SORT ressource???????? Sort statistics
????? Sort width:???????? 980 Area size:???? 1048576 Max Area size:?? 171642880
????? Degree:?????????????? 1
????? Blocks to Sort: 160 Row size:???? 17 Total Rows:????????? 76437
????? Initial runs:?? 2 Merge passes:? 1 IO Cost / pass:???????? 88
????? Total IO sort cost: 248????? Total CPU sort cost: 80448497
????? Total Temp space used: 1860000
??? SORT ressource???????? Sort statistics
????? Sort width:???????? 980 Area size:???? 1048576 Max Area size:?? 171642880
????? Degree:?????????????? 1
????? Blocks to Sort: 1 Row size:???? 20 Total Rows:???????????? 10
????? Initial runs:?? 1 Merge passes:? 0 IO Cost / pass:????????? 0
????? Total IO sort cost: 0????? Total CPU sort cost: 20643984
????? Total Temp space used: 0
? SM join: Resc: 307.41? Resp: 307.41? [multiMatchCost=0.00]
SM Join
? SM cost: 307.41
???? resc: 307.41 resc_io: 302.00 resc_cpu: 111576765
???? resp: 307.41 resp_io: 302.00 resp_cpu: 111576765
SM Join (with index on outer)
? Access Path: index (FullScan)
??? Index: IDX_T12_OWNER
??? resc_io: 181.00? resc_cpu: 16576381
??? ix_sel: 1.000000? ix_sel_with_filters: 1.000000
??? Cost: 181.80? Resp: 181.80? Degree: 1
? Outer table:? T12? Alias: T
??? resc: 181.80? card 76437.00? bytes: 6? deg: 1? resp: 181.80
? Inner table:? T12_OWNER? Alias: O
??? resc: 3.00? card: 10.00? bytes: 9? deg: 1? resp: 3.00
??? using dmeth: 2? #groups: 1
??? SORT ressource???????? Sort statistics
????? Sort width:???????? 980 Area size:???? 1048576 Max Area size:?? 171642880
????? Degree:?????????????? 1
????? Blocks to Sort: 1 Row size:???? 20 Total Rows:???????????? 10
????? Initial runs:?? 1 Merge passes:? 0 IO Cost / pass:????????? 0
????? Total IO sort cost: 0????? Total CPU sort cost: 20643984
????? Total Temp space used: 0
? SM join: Resc: 185.80? Resp: 185.80? [multiMatchCost=0.00]
? Outer table:? T12? Alias: T
??? resc: 51.51? card 76437.00? bytes: 6? deg: 1? resp: 51.51
? Inner table:? T12_OWNER? Alias: O
??? resc: 3.00? card: 10.00? bytes: 9? deg: 1? resp: 3.00
??? using dmeth: 2? #groups: 1
??? Cost per ptn: 1.06? #ptns: 1
??? hash_area: 256 (max=41905) buildfrag: 168? probefrag: 1? ppasses: 1
? Hash join: Resc: 55.68? Resp: 55.68? [multiMatchCost=0.12]
? Outer table:? T12_OWNER? Alias: O
??? resc: 3.00? card 10.00? bytes: 9? deg: 1? resp: 3.00
? Inner table:? T12? Alias: T
??? resc: 51.51? card: 76437.00? bytes: 6? deg: 1? resp: 51.51
??? using dmeth: 2? #groups: 1
??? Cost per ptn: 0.87? #ptns: 1
??? hash_area: 256 (max=41905) buildfrag: 1? probefrag: 168? ppasses: 1
? Hash join: Resc: 55.38? Resp: 55.38? [multiMatchCost=0.00]
HA Join?? ?哈希關聯
? HA cost: 55.38 swapped?? ?
???? resc: 55.38 resc_io: 54.00 resc_cpu: 28450728
???? resp: 55.38 resp_io: 54.00 resp_cpu: 28450728
Join order aborted: cost > best plan cost
***********************
(newjo-stop-1) k:0, spcnt:0, perm:2, maxperm:2000
*********************************
Number of join permutations tried: 2
*********************************
Consider using bloom filter between O[T12_OWNER] and T[T12] with ??
kkoBloomFilter: join ndv:0 reduction:1.000000 (limit:0.500000)? rejected because not a hash join
Enumerating distribution method (advanced)
--- Distribution method for
join between O[T12_OWNER](serial) and T[T12](serial); jm = 12; right side access path = IndexRange
---- NLJ default -> BROADCAST-LEFT
(newjo-save)??? [1 0 ]
Trying or-Expansion on query block SEL$1 (#0)
Transfer Optimizer annotations for query block SEL$1 (#0)
id=0 frofkks[i] (index start key) predicate="O"."OWNER"="T"."OWNER"
id=0 frofkke[i] (index stop key) predicate="O"."OWNER"="T"."OWNER"
Final cost for query block SEL$1 (#0) - All Rows Plan:?? ??? ??? ??? ??? ?#最后得出最佳成本 執行計劃
? Best join order: 1
? Cost: 53.2580? Degree: 1? Card: 24657.0000? Bytes: 369855
? Resc: 53.2580? Resc_io: 53.0000? Resc_cpu: 5326558
? Resp: 53.2580? Resp_io: 53.0000? Resc_cpu: 5326558
kkoqbc-subheap (delete addr=0x000000001F2CFAA0, in-use=27840, alloc=32840)
kkoqbc-end:
??????? :
??? call(in-use=14400, alloc=65656), compile(in-use=60960, alloc=62216), execution(in-use=3352, alloc=4032)
kkoqbc: finish optimizing query block SEL$1 (#0)
apadrv-end
????????? :
??? call(in-use=14400, alloc=65656), compile(in-use=61944, alloc=62216), execution(in-use=3352, alloc=4032)
Starting SQL statement dump
user_id=84 user_name=TANG module=SQL*Plus action=
sql_id=530jrynjh1h0c plan_hash_value=1246238126 problem_type=3
----- Current SQL Statement for this session (sql_id=530jrynjh1h0c) -----
select count(*) from t12_owner o,t12? t where o.owner=t.owner
sql_text_length=62
sql=select count(*) from t12_owner o,t12? t where o.owner=t.owner
----- Explain Plan Dump -----
----- Plan Table -----
?
============
Plan Table
============
--------------------------------------------+-----------------------------------+
| Id? | Operation??????????? | Name???????? | Rows? | Bytes | Cost? | Time????? |
--------------------------------------------+-----------------------------------+
| 0?? | SELECT STATEMENT???? |????????????? |?????? |?????? |??? 53 |?????????? |
| 1?? |? SORT AGGREGATE????? |????????????? |???? 1 |??? 15 |?????? |?????????? |
| 2?? |?? NESTED LOOPS?????? |????????????? |?? 24K |? 361K |??? 53 |? 00:00:01 |
| 3?? |??? TABLE ACCESS FULL | T12_OWNER??? |??? 10 |??? 90 |???? 3 |? 00:00:01 |
| 4?? |??? INDEX RANGE SCAN? | IDX_T12_OWNER|? 2466 |?? 14K |???? 5 |? 00:00:01 |
--------------------------------------------+-----------------------------------+
Predicate Information:
----------------------
4 - access("O"."OWNER"="T"."OWNER")
?
Content of other_xml column
===========================
? db_version???? : 11.2.0.3
? parse_schema?? : TANG
? plan_hash????? : 1246238126
? plan_hash_2??? : 632309206
? Outline Data:
? /*+
??? BEGIN_OUTLINE_DATA
????? IGNORE_OPTIM_EMBEDDED_HINTS
????? OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
????? DB_VERSION('11.2.0.3')
????? ALL_ROWS
????? OUTLINE_LEAF(@"SEL$1")
????? FULL(@"SEL$1" "O"@"SEL$1")
????? INDEX(@"SEL$1" "T"@"SEL$1" ("T12"."OWNER"))
????? LEADING(@"SEL$1" "O"@"SEL$1" "T"@"SEL$1")
????? USE_NL(@"SEL$1" "T"@"SEL$1")
??? END_OUTLINE_DATA
? */
?
從上面的跟蹤日志中可以看到，最后的執行計劃和在SQLPLUS 中是一致的。
--------------------------------------------------------------------------------------------------
4.當統計信息不準確時，CBO可能產生錯誤的執行計劃，請給出這樣的一個例子，
在10053 trace中找到CBO出錯的位置，并給出必要的文字說明。<br>
C:\Users\Administrator>sqlplus tang/tang@orcl
SQL*Plus: Release 11.2.0.1.0 Production on Fri Dec 20 13:35:09 2013
Copyright (c) 1982, 2010, Oracle.? All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> drop table t12;
Table dropped.
SQL> create table t12 as select * from dba_objects;
Table created.
SQL> create index idx_t12_id on t12(object_id);
Index created.
SQL> select count(0) from t12;
? COUNT(0)
----------
???? 76438
SQL> update t12 set object_id=1 where object_id<=70000;
69175 rows updated.
SQL> update t12 set object_id=2 where object_id>70000;
7256 rows updated.
SQL> execute dbms_stats.gather_table_stats(user,'T12',CASCADE=>TRUE,ESTIMATE_PERCENT=>NULL,METHOD_OPT=>'for all columns size 254');
PL/SQL procedure successfully completed.
SQL>
SQL> select count(object_name) from t12 where object_id=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 4279859672
---------------------------------------------------------------------------
| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |???? 1 |??? 28 |?? 305?? (1)| 00:00:04 |
|?? 1 |? SORT AGGREGATE??? |????? |???? 1 |??? 28 |??????????? |????????? |
|*? 2 |?? TABLE ACCESS FULL| T12? | 69175 |? 1891K|?? 305?? (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 2 - filter("OBJECT_ID"=1)
SQL> select count(object_name) from t12 where object_id=2;
Execution Plan
----------------------------------------------------------
Plan hash value: 3968484635
-------------------------------------------------------------------------------------------
| Id? | Operation??????????????????? | Name?????? | Rows? | Bytes | Cost (%CPU)| Time???? |
-------------------------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT???????????? |??????????? |???? 1 |??? 28 |?? 129?? (0)| 00:00:02 |
|?? 1 |? SORT AGGREGATE????????????? |??????????? |???? 1 |??? 28 |??????????? |????????? |
|?? 2 |?? TABLE ACCESS BY INDEX ROWID| T12??????? |? 7256 |?? 198K|?? 129?? (0)| 00:00:02 |
|*? 3 |??? INDEX RANGE SCAN????????? | IDX_T12_ID |? 7256 |?????? |??? 25?? (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 3 - access("OBJECT_ID"=2)
SQL>
上面表中數據 OBJECT_ID=1 時，數據有69175 ，OBJECT_ID=2 時，數據有7256；
從上面的執行計劃可以看出，第一個使用了全表搜索，第二個查詢使用了索引，是正確的。
因為先從索引中查找 ojbect_id=2 的數據比較少，走索引，再到表中查找數據，速度更快。
下面我們對數據進行一個修正。把OBJECT_ID 的分布進行修改。
SQL> set autot off;
SQL> update t12 set object_id=3 where rownum<=60000;
60000 rows updated.
SQL> select count(0) from t12 where object_id=1;
? COUNT(0)
----------
????? 9207
SQL> set autot trace expl;
SQL> select count(object_name) from t12 where object_id=1;
Execution Plan
----------------------------------------------------------
Plan hash value: 4279859672
---------------------------------------------------------------------------
| Id? | Operation????????? | Name | Rows? | Bytes | Cost (%CPU)| Time???? |
---------------------------------------------------------------------------
|?? 0 | SELECT STATEMENT?? |????? |???? 1 |??? 28 |?? 305?? (1)| 00:00:04 |
|?? 1 |? SORT AGGREGATE??? |????? |???? 1 |??? 28 |??????????? |????????? |
|*? 2 |?? TABLE ACCESS FULL| T12? | 69175 |? 1891K|?? 305?? (1)| 00:00:04 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
?? 2 - filter("OBJECT_ID"=1)
SQL>
經修改后，object_id=1 的數據只有 9207，在表中的分布應該是很少了。應該走索引才對。
這就是因為表中的統計信息已不正確的原因。從上面的執行計劃也可以看出，執行計劃中統計出的
數據行數 ：69175 就是在修改前的OBJECT_ID=1 的數量。
我們來跟蹤一下10053的跟蹤日志。查找原因。
**************************
Predicate Move-Around (PM)
**************************
PM:???? PM bypassed: Outer query contains no views.
PM:???? PM bypassed: Outer query contains no views.
query block SEL$1 (#0) unchanged
FPD: Considering simple filter push in query block SEL$1 (#0)
"T12"."OBJECT_ID"=:B1
try to generate transitive predicate from check constraints for query block SEL$1 (#0)
finally: "T12"."OBJECT_ID"=:B1
apadrv-start sqlid=7904889763193459797
? :
??? call(in-use=1520, alloc=16344), compile(in-use=55176, alloc=57056), execution(in-use=3296, alloc=4032)
*******************************************
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
?Bind#0
? oacdty=02 mxl=22(02) mxlc=00 mal=00 scl=00 pre=00
? oacflg=10 fl2=0100 frm=00 csi=00 siz=24 off=0
? kxsbbbfp=ae8c6460? bln=22? avl=02? flg=09
? value=1
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT COUNT("T12"."OBJECT_NAME") "COUNT(OBJECT_NAME)" FROM "TANG"."T12" "T12" WHERE "T12"."OBJECT_ID"=:B1
kkoqbc: optimizing query block SEL$1 (#0)
?????? ?
??????? :
??? call(in-use=1568, alloc=16344), compile(in-use=56104, alloc=57056), execution(in-use=3432, alloc=4032)
kkoqbc-subheap (create addr=0x000000001819A658)
****************
QUERY BLOCK TEXT
****************
select count(object_name) from t12 where object_id=:"SYS_B_0"
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
? fro(0): flg=0 objn=98536 hint_alias="T12"@"SEL$1"
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
? Using NOWORKLOAD Stats
? CPUSPEEDNW: 1720 millions instructions/sec (default is 100)
? IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
? IOSEEKTIM:? 10 milliseconds (default is 10)
? MBRC:?????? NO VALUE blocks (default is 8)
***************************************
BASE STATISTICAL INFORMATION
***********************
Table Stats::
? Table: T12? Alias: T12
??? #Rows: 76438? #Blks:? 1115? AvgRowLen:? 96.00? ChainCnt:? 0.00
?? ?#行數 塊數 平均行長
Index Stats::
? Index: IDX_T12_ID? Col#: 4
??? LVLS: 1? #LB: 263? #DK: 2? LB/K: 131.00? DB/K: 545.00? CLUF: 1091.00
#索引幾層葉子塊數多少個唯一鍵值每個鍵值有多少個葉塊每個鍵值有多少個數據塊聚簇因子
Access path analysis for T12
***************************************
SINGLE TABLE ACCESS PATH
? Single Table Cardinality Estimation for T12[T12]
? Column (#4):
??? NewDensity:0.047468, OldDensity:0.000007 BktCnt:76431, PopBktCnt:76431, PopValCnt:2, NDV:2
? Column (#4): OBJECT_ID(
??? AvgLen: 3 NDV: 2 Nulls: 7 Density: 0.047468 Min: 1 Max: 2
??? Histogram: Freq? #Bkts: 2? UncompBkts: 76431? EndPtVals: 2
? Table: T12? Alias: T12
??? Card: Original: 76438.000000? Rounded: 69175? Computed: 69175.00? Non Adjusted: 69175.00
#原始行數 近似值 精確值 非修正值
#可以看出，問題就出在這里，ORACLE 認為，現在OBJECT_ID=1 的數據就是： 69175
? Access Path: TableScan 全表掃描代價
??? Cost:? 305.35? Resp: 305.35? Degree: 0
????? Cost_io: 304.00? Cost_cpu: 27814286
????? Resp_io: 304.00? Resp_cpu: 27814286
? Access Path: index (AllEqRange)? 索引全掃描代價
??? Index: IDX_T12_ID
??? resc_io: 1227.00? resc_cpu: 37100607
??? ix_sel: 0.905065? ix_sel_with_filters: 0.905065
?? ?#ix_sel 索引選擇率
?? ?#ix_sel_with_filters帶過濾條件索引選擇率
?? ?#這里也可以看出，ORACLE認為索引選擇率很高
??? Cost: 1228.80? Resp: 1228.80? Degree: 1
? Best:: AccessPath: TableScan
???????? Cost: 305.35? Degree: 1? Resp: 305.35? Card: 69175.00? Bytes: 0
得出結論走全表搜索優于索引
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]:? T12[T12]#0
***********************
Best so far:? Table#: 0? cost: 305.3474? card: 69175.0000? bytes: 1936900
***********************
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000
*********************************
Number of join permutations tried: 1
*********************************
Enumerating distribution method (advanced)
Trying or-Expansion on query block SEL$1 (#0)
Transfer Optimizer annotations for query block SEL$1 (#0)
id=0 frofand predicate="T12"."OBJECT_ID"=:B1
Final cost for query block SEL$1 (#0) - All Rows Plan:
? Best join order: 1
? Cost: 305.3474? Degree: 1? Card: 69175.0000? Bytes: 1936900
? Resc: 305.3474? Resc_io: 304.0000? Resc_cpu: 27814286
? Resp: 305.3474? Resp_io: 304.0000? Resc_cpu: 27814286
kkoqbc-subheap (delete addr=0x000000001819A658, in-use=13600, alloc=16408)
kkoqbc-end:
??????? :
??? call(in-use=29304, alloc=49184), compile(in-use=57072, alloc=61200), execution(in-use=3800, alloc=4032)
kkoqbc: finish optimizing query block SEL$1 (#0)
apadrv-end
????????? :
??? call(in-use=29304, alloc=49184), compile(in-use=57984, alloc=61200), execution(in-use=3800, alloc=4032)
Starting SQL statement dump
user_id=84 user_name=TANG module=SQL*Plus action=
sql_id=6vcygbf9w952p plan_hash_value=-15107624 problem_type=3
----- Current SQL Statement for this session (sql_id=6vcygbf9w952p) -----
?select count(object_name) from t12 where object_id=:"SYS_B_0"
sql_text_length=63
sql= select count(object_name) from t12 where object_id=:"SYS_B_0"
----- Explain Plan Dump -----
----- Plan Table -----
?
============
Plan Table
============
--------------------------------------+-----------------------------------+
| Id? | Operation?????????? | Name??? | Rows? | Bytes | Cost? | Time????? |
--------------------------------------+-----------------------------------+
| 0?? | SELECT STATEMENT??? |???????? |?????? |?????? |?? 305 |?????????? |
| 1?? |? SORT AGGREGATE???? |???????? |???? 1 |??? 28 |?????? |?????????? |
| 2?? |?? TABLE ACCESS FULL | T12???? |?? 68K | 1892K |?? 305 |? 00:00:04 |
--------------------------------------+-----------------------------------+
Predicate Information:
----------------------
2 - filter("OBJECT_ID"=:SYS_B_0)
?
Content of other_xml column
===========================
? db_version???? : 11.2.0.3
? parse_schema?? : TANG
? plan_hash????? : 4279859672
? plan_hash_2??? : 3898508299
Peeked Binds
============
? Bind variable information
??? position=1
??? datatype(code)=2
??? datatype(string)=NUMBER
??? precision=0
??? scale=0
??? max length=22
??? value=1
? Outline Data:
? /*+
??? BEGIN_OUTLINE_DATA
????? IGNORE_OPTIM_EMBEDDED_HINTS
????? OPTIMIZER_FEATURES_ENABLE('11.2.0.3')
????? DB_VERSION('11.2.0.3')
????? ALL_ROWS
????? OUTLINE_LEAF(@"SEL$1")
????? FULL(@"SEL$1" "T12"@"SEL$1")
??? END_OUTLINE_DATA
? */
?
總結
以上是生活随笔為你收集整理的【性能优化】 之 10053 事件的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 【性能优化】 之10046 事件
- 下一篇： 【性能优化】 之性能视图及性能参数