藉助Oracle的10053事件event，我們可以監控到CBO對SQL進行成本計算和路徑選擇的過程和方法。即SQL為什麼要這樣執行；

10053事件有兩個級別：

Level 2：2級是1級的一個子集，它包含以下內容：

Column statistics

Single Access Paths

Join Costs

Table Joins Considered

Join Methods Considered (NL/MS/HA)

Level 1： 1級比2級更詳細，它包含2級的所有內容，再加如下內容：

Parameters used by the optimizer

Index statistics

啟用10053事件

ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';

ALTER SESSION SET EVENTS='10053 trace name context forever, level 2';

關閉10053事件：

ALTER SESSION SET EVENTS '10053 trace name context off';

說明：

1、sqlplus中開啟autotrace看到的執行計劃實際上是用explain plan 命令得到的，explain plan 命令不會進行bind peeking。應該透過v$sql_plan檢視SQL的真實的執行計劃。

2、10053只對CBO有效，而且如果一個sql語句已經解析過，就不會產生新的trace資訊。

3、10053事件產生的trace檔案不能用tkprof格式化。

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

測試如下：

——-建立測試表t1,t2

SYS@ORA11GR2>create table t1 as select * from all_objects;

Table created.

SYS@ORA11GR2>select count(*) from t1;

COUNT(*)

----------

85019

SYS@ORA11GR2>create table t2 as select * from all_objects where rownum<=100;

Table created.

SYS@ORA11GR2>select count(*) from t2;

COUNT(*)

----------

100

——對T1,T2表進行分析，不包含直方圖（收集統計資訊）

SYS@ORA11GR2>exec dbms_stats.gather_table_stats('SYS','T1',cascade=>true,estimate_percent=>null,method_opt=>'for all columns size 1');（對sys使用者下的t1表全部統計資訊，所有列生成1 bucket，且包含索引）

PL/SQL procedure successfully completed.

SYS@ORA11GR2>exec dbms_stats.gather_table_stats('SYS','T2',cascade=>true,estimate_percent=>null,method_opt=>'for all columns size 1'); （對sys使用者下的t2表全部統計資訊，所有列生成1 bucket，且包含索引）

PL/SQL procedure successfully completed.

——生成10053trace檔案

啟用10053事件：

SYS@ORA11GR2>alter session set events '10053 trace name context forever ,level 1';

Session altered.

進行操作以生成10053trace檔案：

SYS@ORA11GR2>select count(*) from t1,t2 where t1.object_id = t2.object_id;

COUNT(*)

----------

100

關閉10053事件：

SYS@ORA11GR2>alter session set events '10053 trace name context off';

Session altered.

查詢10053trace檔案位置：

SYS@ORA11GR2>select value from v$diag_info where name like '%Default%';

VALUE

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

/u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_12775.trc

——檢視10053trace檔案：

[oracle@wang ~]$ less /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_12775.trc

--trace檔案路徑、資料庫版本、資料庫基本資訊

Trace file /u01/app/oracle/diag/rdbms/ora11gr2/ORA11GR2/trace/ORA11GR2_ora_12775.trc

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

With the Partitioning, OLAP, Data Mining and Real Application Testing options

ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1

System name: Linux

Node name: wang

Release: 2.6.32-100.26.2.el5

Version: #1 SMP Tue Jan 18 20:11:49 EST 2011

Machine: x86_64

VM name: VMWare Version: 6

Instance name: ORA11GR2

Redo thread mounted by this instance: 1

Oracle process number: 19

Unix process pid: 12775, image: oracle@wang (TNS V1-V3)

*** 2016-10-11 22:53:19.330

*** SESSION ID:(1.13) 2016-10-11 22:53:19.330

*** CLIENT ID:() 2016-10-11 22:53:19.330

*** SERVICE NAME:(SYS$USERS) 2016-10-11 22:53:19.330

*** MODULE NAME:(sqlplus@wang (TNS V1-V3)) 2016-10-11 22:53:19.330

*** ACTION NAME:() 2016-10-11 22:53:19.330

Registered qb: SEL$1 0x60860020 (PARSER)

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

QUERY BLOCK SIGNATURE

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

signature (): qb_name=SEL$1 nbfros=2 flg=0

fro(0): flg=4 objn=88759 hint_alias="T1"@"SEL$1"

fro(1): flg=4 objn=88760 hint_alias="T2"@"SEL$1"

SPM: statement not found in SMB

Automatic degree of parallelism (ADOP)

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

Automatic degree of parallelism is disabled: Parameter.

PM: Considering predicate move-around in query block SEL$1 (#0)

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

Predicate Move-Around (PM)

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

OPTIMIZER INFORMATION

--執行的SQL語句

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

----- Current SQL Statement for this session (sql_id=dzvtvzv73q45u) -----

select count(*) from t1,t2 where t1.object_id = t2.object_id

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

--縮寫的解釋

Legend

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

SJC - set join conversion

SU - subquery unnesting

OBYE - order by elimination

OST - old style star transformation

ST - new (cbqt) star transformation

CNT - count(col) to count(*) transformation

JE - Join Elimination

JF - join factorization

CBY - connect by

SLP - select list pruning

DP - distinct placement

qb - query block

LB - leaf blocks

DK - distinct keys

LB/K - average number of leaf blocks per key

DB/K - average number of data blocks per key

CLUF - clustering factor

NDV - number of distinct values

Resp - response cost

Card - cardinality

Resc - resource cost

NL - nested loops (join)

SM - sort merge (join)

HA - hash (join)

CPUSPEED - CPU Speed

IOTFRSPEED - I/O transfer speed

IOSEEKTIM - I/O seek time

SREADTIM - average single block read time

MREADTIM - average multiblock read time

MBRC - average multiblock read count

MAXTHR - maximum I/O system throughput

SLAVETHR - average slave I/O throughput

dmeth - distribution method

1: no partitioning required

2: value partitioned

4: right is random (round-robin)

128: left is random (round-robin)

8: broadcast right and partition left

16: broadcast left and partition right

32: partition left using partitioning of right

64: partition right using partitioning of left

256: run the join in serial

0: invalid distribution method

sel – selectivity

ptn – partition

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

PARAMETERS USED BY THE OPTIMIZER

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

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

PARAMETERS WITH ALTERED VALUES

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

Compilation Environment Dump

Bug Fix Control Environment

--引數的預設值（包括隱含引數）

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

PARAMETERS WITH DEFAULT VALUES

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

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

parallel_ddl_forced_instances = 0

_query_rewrite_fudge = 90

optimizer_features_enable = 11.2.0.4

_optimizer_search_limit = 5

cpu_count = 1

active_instance_count = 1

parallel_threads_per_cpu = 2

hash_area_size = 131072

bitmap_merge_area_size = 1048576

sort_area_size = 65536

sort_area_retained_size = 0

省略………………………………………………………………………………………………………………………………

——Bug修復情況

Bug Fix Control Environment

fix 3834770 = 1

fix 3746511 = enabled

fix 4519016 = enabled

fix 3118776 = enabled

fix 4488689 = enabled

fix 2194204 = disabled

fix 2660592 = enabled

fix 2320291 = enabled

fix 2324795 = enabled

fix 4308414 = enabled

fix 3499674 = disabled

fix 4569940 = enabled

fix 4631959 = enabled

fix 4519340 = enabled

fix 4550003 = enabled

fix 1403283 = enabled

省略………………………………………………………………………………………………………………………………

——引數選擇

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

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 dzvtvzv73q45u.

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 "SYS"."T1" "T1","SYS"."T2" "T2" WHERE "T1"."OBJECT_ID"="T2"."OBJECT_ID"

JE: cfro: T1 objn:88760 col#:4 dfro:T2 dcol#:4

JE: cfro: T2 objn:88759 col#:4 dfro:T1 dcol#:4

SQL:******* UNPARSED QUERY IS *******

SELECT COUNT(*) "COUNT(*)" FROM "SYS"."T1" "T1","SYS"."T2" "T2" WHERE "T1"."OBJECT_ID"="T2"."OBJECT_ID"

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 "SYS"."T1" "T1","SYS"."T2" "T2" WHERE "T1"."OBJECT_ID"="T2"."OBJECT_ID"

JE: cfro: T1 objn:88760 col#:4 dfro:T2 dcol#:4

JE: cfro: T2 objn:88759 col#:4 dfro:T1 dcol#:4

SQL:******* UNPARSED QUERY IS *******

SELECT COUNT(*) "COUNT(*)" FROM "SYS"."T1" "T1","SYS"."T2" "T2" WHERE "T1"."OBJECT_ID"="T2"."OBJECT_ID"

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 dzvtvzv73q45u.

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 "SYS"."T1" "T1","SYS"."T2" "T2" WHERE "T1"."OBJECT_ID"="T2"."OBJECT_ID"

JE: cfro: T1 objn:88760 col#:4 dfro:T2 dcol#:4

JE: cfro: T2 objn:88759 col#:4 dfro:T1 dcol#:4

SQL:******* UNPARSED QUERY IS *******

SELECT COUNT(*) "COUNT(*)" FROM "SYS"."T1" "T1","SYS"."T2" "T2" WHERE "T1"."OBJECT_ID"="T2"."OBJECT_ID"

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.

query block SEL$1 (#0) unchanged

FPD: Considering simple filter push in query block SEL$1 (#0)

"T1"."OBJECT_ID"="T2"."OBJECT_ID"

try to generate transitive predicate from check constraints for query block SEL$1 (#0)

finally: "T1"."OBJECT_ID"="T2"."OBJECT_ID"

apadrv-start sqlid=16136181955459485882

:

call(in-use=1872, alloc=16344), compile(in-use=56728, alloc=56944), execution(in-use=3376, alloc=4032)

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

Peeked values of the binds in SQL statement

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

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT COUNT(*) "COUNT(*)" FROM "SYS"."T1" "T1","SYS"."T2" "T2" WHERE "T1"."OBJECT_ID"="T2"."OBJECT_ID"

kkoqbc: optimizing query block SEL$1 (#0)

:

call(in-use=1928, alloc=16344), compile(in-use=58024, alloc=61088), execution(in-use=3376, alloc=4032)

kkoqbc-subheap (create addr=0x7f5760b450b8)

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

QUERY BLOCK TEXT

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

select count(*) from t1,t2 where t1.object_id = t2.object_id

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

QUERY BLOCK SIGNATURE

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

signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0

fro(0): flg=0 objn=88759 hint_alias="T1"@"SEL$1"

fro(1): flg=0 objn=88760 hint_alias="T2"@"SEL$1"

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

SYSTEM STATISTICS INFORMATION

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

Using NOWORKLOAD Stats

CPUSPEEDNW: 3074 millions instructions/sec (default is 100)

:

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

Peeked values of the binds in SQL statement

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

Final query after transformations:******* UNPARSED QUERY IS *******

SELECT COUNT(*) "COUNT(*)" FROM "SYS"."T1" "T1","SYS"."T2" "T2" WHERE "T1"."OBJECT_ID"="T2"."OBJECT_ID"

kkoqbc: optimizing query block SEL$1 (#0)

:

call(in-use=1928, alloc=16344), compile(in-use=58024, alloc=61088), execution(in-use=3376, alloc=4032)

kkoqbc-subheap (create addr=0x7f5760b450b8)

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

QUERY BLOCK TEXT

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

select count(*) from t1,t2 where t1.object_id = t2.object_id

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

QUERY BLOCK SIGNATURE

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

signature (optimizer): qb_name=SEL$1 nbfros=2 flg=0

fro(0): flg=0 objn=88759 hint_alias="T1"@"SEL$1"

fro(1): flg=0 objn=88760 hint_alias="T2"@"SEL$1"

——系統資訊區

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

SYSTEM STATISTICS INFORMATION

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

Using NOWORKLOAD Stats

CPUSPEEDNW: 3074 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: T2 Alias: T2

#Rows: 100 #Blks: 2 AvgRowLen: 75.00 ChainCnt: 0.00

Column (#4): OBJECT_ID(

AvgLen: 4 NDV: 100 Nulls: 0 Density: 0.010000 Min: 2 Max: 101

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

Table Stats::

Table: T1 Alias: T1

#Rows: 85019 #Blks: 1213 AvgRowLen: 98.00 ChainCnt: 0.00

Column (#4): OBJECT_ID(

AvgLen: 5 NDV: 85019 Nulls: 0 Density: 0.000012 Min: 2 Max: 88759

Access path analysis for T1

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

SINGLE TABLE ACCESS PATH

Single Table Cardinality Estimation for T1[T1]

Table: T1 Alias: T1

Card: Original: 85019.000000 Rounded: 85019 Computed: 85019.00 Non Adjusted: 85019.00

Access Path: TableScan

Cost: 330.72 Resp: 330.72 Degree: 0

Cost_io: 330.00 Cost_cpu: 26492297

Resp_io: 330.00 Resp_cpu: 26492297

Best:: AccessPath: TableScan

Cost: 330.72 Degree: 1 Resp: 330.72 Card: 85019.00 Bytes: 0

Access path analysis for T2

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

SINGLE TABLE ACCESS PATH

Single Table Cardinality Estimation for T2[T2]

Table: T2 Alias: T2

Card: Original: 100.000000 Rounded: 100 Computed: 100.00 Non Adjusted: 100.00

Access Path: TableScan

Cost: 2.00 Resp: 2.00 Degree: 0

Cost_io: 2.00 Cost_cpu: 35243

Resp_io: 2.00 Resp_cpu: 35243

Best:: AccessPath: TableScan

Cost: 2.00 Degree: 1 Resp: 2.00 Card: 100.00 Bytes: 0

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

--最佳化統計和計算

OPTIMIZER STATISTICS AND COMPUTATIONS

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

GENERAL PLANS

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

Considering cardinality-based initial join order.

Permutations for Starting Table :0

Join order[1]: T2[T2]#0 T1[T1]#1

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

Now joining: T1[T1]#1

:

Best:: AccessPath: TableScan

Cost: 330.72 Degree: 1 Resp: 330.72 Card: 85019.00 Bytes: 0

Access path analysis for T2

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

SINGLE TABLE ACCESS PATH

Single Table Cardinality Estimation for T2[T2]

Table: T2 Alias: T2

Card: Original: 100.000000 Rounded: 100 Computed: 100.00 Non Adjusted: 100.00

Access Path: TableScan

Cost: 2.00 Resp: 2.00 Degree: 0

Cost_io: 2.00 Cost_cpu: 35243

Resp_io: 2.00 Resp_cpu: 35243

Best:: AccessPath: TableScan

Cost: 2.00 Degree: 1 Resp: 2.00 Card: 100.00 Bytes: 0

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

--最佳化統計和計算

OPTIMIZER STATISTICS AND COMPUTATIONS

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

GENERAL PLANS

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

Considering cardinality-based initial join order.

Permutations for Starting Table :0

Join order[1]: T2[T2]#0 T1[T1]#1

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

Now joining: T1[T1]#1

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

NL Join

Outer table: Card: 100.00 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 4

Access path analysis for T1

:

Best:: AccessPath: TableScan

Cost: 330.72 Degree: 1 Resp: 330.72 Card: 85019.00 Bytes: 0

Access path analysis for T2

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

SINGLE TABLE ACCESS PATH

Single Table Cardinality Estimation for T2[T2]

Table: T2 Alias: T2

Card: Original: 100.000000 Rounded: 100 Computed: 100.00 Non Adjusted: 100.00

Access Path: TableScan

Cost: 2.00 Resp: 2.00 Degree: 0

Cost_io: 2.00 Cost_cpu: 35243

Resp_io: 2.00 Resp_cpu: 35243

Best:: AccessPath: TableScan

Cost: 2.00 Degree: 1 Resp: 2.00 Card: 100.00 Bytes: 0

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

--最佳化統計和計算

OPTIMIZER STATISTICS AND COMPUTATIONS

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

GENERAL PLANS

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

Considering cardinality-based initial join order.

Permutations for Starting Table :0

Join order[1]: T2[T2]#0 T1[T1]#1

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

Now joining: T1[T1]#1

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

NL Join

Outer table: Card: 100.00 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 4

Access path analysis for T1

Inner table: T1 Alias: T1

Access Path: TableScan

NL Join: Cost: 32927.82 Resp: 32927.82 Degree: 1

Cost_io: 32856.00 Cost_cpu: 2649264915

Resp_io: 32856.00 Resp_cpu: 2649264915

Best NL cost: 32927.82

resc: 32927.82 resc_io: 32856.00 resc_cpu: 2649264915

resp: 32927.82 resp_io: 32856.00 resc_cpu: 2649264915

Join Card: 100.000000 = outer (100.000000) * inner (85019.000000) * sel (0.000012)

Join Card - Rounded: 100 Computed: 100.00

Outer table: T2 Alias: T2

resc: 2.00 card 100.00 bytes: 4 deg: 1 resp: 2.00

Inner table: T1 Alias: T1

resc: 330.72 card: 85019.00 bytes: 5 deg: 1 resp: 330.72

using dmeth: 2 #groups: 1

SORT ressource Sort statistics

Sort width: 329 Area size: 288768 Max Area size: 57880576

Degree: 1

Blocks to Sort: 1 Row size: 15 Total Rows: 100

Initial runs: 1 Merge passes: 0 IO Cost / pass: 0

Total IO sort cost: 0 Total CPU sort cost: 36918822

Total Temp space used: 0

SORT ressource Sort statistics

Sort width: 329 Area size: 288768 Max Area size: 57880576

Degree: 1

Blocks to Sort: 167 Row size: 16 Total Rows: 85019

Initial runs: 2 Merge passes: 1 IO Cost / pass: 92

Total IO sort cost: 259 Total CPU sort cost: 103727680

Total Temp space used: 2073000

SM join: Resc: 595.53 Resp: 595.53 [multiMatchCost=0.00]

SM Join

SM cost: 595.53

resc: 595.53 resc_io: 591.00 resc_cpu: 167174042

resp: 595.53 resp_io: 591.00 resp_cpu: 167174042

Outer table: T2 Alias: T2

resc: 2.00 card 100.00 bytes: 4 deg: 1 resp: 2.00

Inner table: T1 Alias: T1

resc: 330.72 card: 85019.00 bytes: 5 deg: 1 resp: 330.72

using dmeth: 2 #groups: 1

Cost per ptn: 0.25 #ptns: 1

hash_area: 124 (max=14131) buildfrag: 1 probefrag: 177 ppasses: 1

Hash join: Resc: 332.97 Resp: 332.97 [multiMatchCost=0.00]

HA Join

HA cost: 332.97

resc: 332.97 resc_io: 332.00 resc_cpu: 35644440

resp: 332.97 resp_io: 332.00 resp_cpu: 35644440

Best:: JoinMethod: Hash

Cost: 332.97 Degree: 1 Resp: 332.97 Card: 100.00 Bytes: 9

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

Best so far: Table#: 0 cost: 2.0010 card: 100.0000 bytes: 400

Table#: 1 cost: 332.9663 card: 100.0000 bytes: 900

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

Join order[2]: T1[T1]#1 T2[T2]#0

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

Now joining: T2[T2]#0

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

NL Join

Outer table: Card: 85019.00 Cost: 330.72 Resp: 330.72 Degree: 1 Bytes: 5

Access path analysis for T2

Inner table: T2 Alias: T2

Access Path: TableScan

NL Join: Cost: 46465.94 Resp: 46465.94 Degree: 1

Cost_io: 46384.00 Cost_cpu: 3022806711

Resp_io: 46384.00 Resp_cpu: 3022806711

Best NL cost: 46465.94

resc: 46465.94 resc_io: 46384.00 resc_cpu: 3022806711

resp: 46465.94 resp_io: 46384.00 resc_cpu: 3022806711

Join Card: 100.000000 = outer (85019.000000) * inner (100.000000) * sel (0.000012)

Join Card - Rounded: 100 Computed: 100.00

Outer table: T1 Alias: T1

resc: 330.72 card 85019.00 bytes: 5 deg: 1 resp: 330.72

Inner table: T2 Alias: T2

resc: 2.00 card: 100.00 bytes: 4 deg: 1 resp: 2.00

using dmeth: 2 #groups: 1

SORT ressource Sort statistics

Sort width: 329 Area size: 288768 Max Area size: 57880576

Degree: 1

Blocks to Sort: 167 Row size: 16 Total Rows: 85019

Initial runs: 2 Merge passes: 1 IO Cost / pass: 92

Total IO sort cost: 259 Total CPU sort cost: 103727680

Total Temp space used: 2073000

SORT ressource Sort statistics

Sort width: 329 Area size: 288768 Max Area size: 57880576

Degree: 1

Blocks to Sort: 1 Row size: 15 Total Rows: 100

Initial runs: 1 Merge passes: 0 IO Cost / pass: 0

Total IO sort cost: 0 Total CPU sort cost: 36918822

Total Temp space used: 0

SM join: Resc: 595.53 Resp: 595.53 [multiMatchCost=0.00]

SM Join

SM cost: 595.53

resc: 595.53 resc_io: 591.00 resc_cpu: 167174042

resp: 595.53 resp_io: 591.00 resp_cpu: 167174042

Outer table: T1 Alias: T1

resc: 330.72 card 85019.00 bytes: 5 deg: 1 resp: 330.72

Inner table: T2 Alias: T2

resc: 2.00 card: 100.00 bytes: 4 deg: 1 resp: 2.00

using dmeth: 2 #groups: 1

Cost per ptn: 70.38 #ptns: 1

hash_area: 124 (max=14131) buildfrag: 177 probefrag: 1 ppasses: 1

Hash join: Resc: 403.10 Resp: 403.10 [multiMatchCost=0.00]

Outer table: T2 Alias: T2

resc: 2.00 card 100.00 bytes: 4 deg: 1 resp: 2.00

Inner table: T1 Alias: T1

resc: 330.72 card: 85019.00 bytes: 5 deg: 1 resp: 330.72

using dmeth: 2 #groups: 1

Cost per ptn: 0.25 #ptns: 1

hash_area: 124 (max=14131) buildfrag: 1 probefrag: 177 ppasses: 1

Hash join: Resc: 332.97 Resp: 332.97 [multiMatchCost=0.00]

HA Join

HA cost: 332.97 swapped

resc: 332.97 resc_io: 332.00 resc_cpu: 35644440

resp: 332.97 resp_io: 332.00 resp_cpu: 35644440

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 T2[T2] and T1[T1] with ??

kkoBloomFilter: join ndv:0 reduction:1.000000 (limit:0.500000) rejected because no single-tables predicates

Enumerating distribution method (advanced)

--- Distribution method for

join between T2[T2](serial) and T1[T1](serial); jm = 1; right side access path = TableScan

---- serial Hash-Join -> NONE

(newjo-save) [0 1 ]

Trying or-Expansion on query block SEL$1 (#0)

Transfer Optimizer annotations for query block SEL$1 (#0)

id=0 frofkksm[i] (sort-merge/hash) predicate="T1"."OBJECT_ID"="T2"."OBJECT_ID"

id=0 frosand (sort-merge/hash) predicate="T1"."OBJECT_ID"="T2"."OBJECT_ID"

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

Best join order: 1

Cost: 332.9663 Degree: 1 Card: 100.0000 Bytes: 900

Resc: 332.9663 Resc_io: 332.0000 Resc_cpu: 35644440

Resp: 332.9663 Resp_io: 332.0000 Resc_cpu: 35644440

kkoqbc-subheap (delete addr=0x7f5760b450b8, in-use=36144, alloc=49272)

kkoqbc-end:

:

call(in-use=10704, alloc=82128), compile(in-use=61576, alloc=65232), execution(in-use=3376, alloc=4032)

kkoqbc: finish optimizing query block SEL$1 (#0)

apadrv-end

:

call(in-use=10704, alloc=82128), compile(in-use=62560, alloc=65232), execution(in-use=3376, alloc=4032)

Starting SQL statement dump

user_id=0 user_name=SYS module=sqlplus@wang (TNS V1-V3) action=

sql_id=dzvtvzv73q45u plan_hash_value=-20910549 problem_type=3

----- Current SQL Statement for this session (sql_id=dzvtvzv73q45u) -----

select count(*) from t1,t2 where t1.object_id = t2.object_id

sql_text_length=61

sql=select count(*) from t1,t2 where t1.object_id = t2.object_id

----- Explain Plan Dump -----

----- Plan Table -----

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

Plan Table

最終的執行計劃

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

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

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

| 0 | SELECT STATEMENT | | | | 333 | |

| 1 | SORT AGGREGATE | | 1 | 9 | | |

| 2 | HASH JOIN | | 100 | 900 | 333 | 00:00:04 |

| 3 | TABLE ACCESS FULL | T2 | 100 | 400 | 2 | 00:00:01 |

| 4 | TABLE ACCESS FULL | T1 | 83K | 415K | 331 | 00:00:04 |

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

Predicate Information:

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

2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")

Content of other_xml column

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

db_version : 11.2.0.4

parse_schema : SYS

plan_hash : 4274056747

plan_hash_2 : 655528263

Outline Data:

/*+

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE('11.2.0.4')

DB_VERSION('11.2.0.4')

ALL_ROWS

OUTLINE_LEAF(@"SEL$1")

FULL(@"SEL$1" "T2"@"SEL$1")

FULL(@"SEL$1" "T1"@"SEL$1")

LEADING(@"SEL$1" "T2"@"SEL$1" "T1"@"SEL$1")

USE_HASH(@"SEL$1" "T1"@"SEL$1")

END_OUTLINE_DATA

*/

Optimizer state dump:

Compilation Environment Dump

optimizer_mode_hinted = false

optimizer_features_hinted = 0.0.0

parallel_execution_enabled = true

parallel_query_forced_dop = 0

省略…………………………………………………………………………………………………………….

fix 13253977 = enabled

fix 16554552 = enabled

Query Block Registry:

SEL$1 0x60860020 (PARSER) [FINAL]

:

call(in-use=19008, alloc=82128), compile(in-use=93376, alloc=154304), execution(in-use=8648, alloc=12144)

End of Optimizer State Dump

Dumping Hints

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

====================== END SQL Statement Dump ======================

結束！！！！！！！！！！！！

【最佳化】10053事件

相關文章