通过对 sql 做10053 事件，可以发现优化器为什么选择我们看到的最终的执行计划。10053 事件的部分说明。
第一部分 包含了操作系统，内存，cpu ，数据库和会话信息
Trace file f:\orcl\diag\rdbms\oracl\oracl\trace\oracl_ora_5080.trc
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Windows NT Version V5.2 Service Pack 2
CPU : 2 - type 586, 1 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:285M/1022M, Ph+PgF:1914M/2977M, VA:1379M/2047M
Instance name: oracl
Redo thread mounted by this instance: 1
Oracle process number: 26
Windows thread id: 5080, image: ORACLE.EXE (SHAD)
*** 2010-10-19 14:32:23.765
*** SESSION ID:(164.65) 2010-10-19 14:32:23.765
*** CLIENT ID:() 2010-10-19 14:32:23.765
*** SERVICE NAME:(SYS$USERS) 2010-10-19 14:32:23.765
*** MODULE NAME:(sqlplus.exe) 2010-10-19 14:32:23.765
*** ACTION NAME:() 2010-10-19 14:32:23.765
Registered qb: SEL$1 0x25356174 (PARSER)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=4 bjn=69812 hint_alias="T"@"SEL$1"
SPM: statement not found in SMB
DOP: Automatic degree of parallelism is disabled: Parameter.
PM: Considering predicate move-around in query block SEL$1 (#0)
从这里开始10053 的信息部分。cbo 主要对sql语句的谓词进行分析，重写，改写sql为符合逻辑的sql语句。
**************************
Predicate Move-Around (PM)
**************************
OPTIMIZER INFORMATION
******************************************
----- Current SQL Statement for this session (sql_id=a4a1q4c86ghkq) -----
EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR select owner,object_name,object_id, count(*)
from t group by owner ,object_name,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
ST - star transformation
CNT - count(col) to count(*) transformation
JE - Join Elimination
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
--以上为对trace文件的一些缩写的解释。
***************************************
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.1.0.6
_optimizer_search_limit = 5
......
_pred_move_around = true
_full_pwise_join_enabled = true
_partial_pwise_join_enabled = true
_left_nested_loops_random = true
_improved_row_length_enabled = true
_index_join_enabled = true
_enable_type_dep_selectivity = true
_improved_outerjoin_card = true
_optimizer_adjust_for_nulls = true
_optimizer_degree = 0
_use_column_stats_for_function = true
_subquery_pruning_enabled = true
_subquery_pruning_mv_enabled = false
_or_expand_nvl_predicate = true
_like_with_bind_as_equality = false
_table_scan_cost_plus_one = true
_cost_equality_semi_join = true
_default_non_equality_sel_check = true
_new_initial_join_orders = true
_oneside_colstat_for_equijoins = true
_optim_peek_user_binds = true
_minimal_stats_aggregation = true
_force_temptables_for_gsets = false
workarea_size_policy = auto
_optimizer_or_expansion_subheap = true
_optimizer_free_transformation_heap = true
_optimizer_reuse_cost_annotations = true
_result_cache_auto_size_threshold = 100
_result_cache_auto_time_threshold = 1000
_optimizer_nested_rollup_for_gset = 100
_nlj_batching_enabled = 1
parallel_query_default_dop = 0
is_recur_flags = 0
optimizer_use_invisible_indexes = false
flashback_data_archive_internal_cursor = 0
_optimizer_extended_stats_usage_control = 240
Bug Fix Control Environment---bug 的修复信息。
fix 3834770 = 1
fix 3746511 = enabled
fix 4519016 = enabled
fix 3118776 = enabled
......
---cbo 进行计算的一些信息，这个有点难看懂 。
***************************************
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)
**************************
CBQT: Validity checks passed for a4a1q4c86ghkq.
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.
CNT: Considering count(col) to count(*) on query block SEL$1 (#0)
*************************
Count(col) to Count(*) (CNT)
*************************
CNT: COUNT() to COUNT(*) not done.
query block SEL$1 (#0) unchanged
Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
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).
query block SEL$1 (#0) unchanged
apadrv-start sqlid=11684648757439742550
:
call(in-use=376, alloc=16360), compile(in-use=58852, alloc=60944), execution(in-use=66496, alloc=69396)
*******************************************
Peeked values of the binds in SQL statement
*******************************************
CBQT: Considering cost-based transformation on query block SEL$1 (#0)
********************************
COST-BASED QUERY TRANSFORMATIONS
********************************
FPD: Considering simple filter push (pre rewrite) in query block SEL$1 (#0)
FPD: Current where clause predicates ??
OBYE: Considering Order-by Elimination from view SEL$1 (#0)
***************************
Order-by elimination (OBYE)
***************************
OBYE: OBYE bypassed: no order by to eliminate.
Considering Query Transformations on query block SEL$1 (#0)
**************************
Query transformations (QT)
**************************
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).
kkqctdrvTD-start on query block SEL$1 (#0)
kkqctdrvTD-start: :
call(in-use=376, alloc=16360), compile(in-use=120876, alloc=122192), execution(in-use=127924, alloc=130824)
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
call(in-use=376, alloc=16360), compile(in-use=121200, alloc=122192), execution(in-use=127924, alloc=130824)
kkqctdrvTD-end:
call(in-use=376, alloc=16360), compile(in-use=121328, alloc=122192), execution(in-use=127924, alloc=130824)
SJC: Considering set-join conversion in query block SEL$1 (#1)
*************************
Set-Join Conversion (SJC)
*************************
SJC: not performed
CNT: Considering count(col) to count(*) on query block SEL$1 (#1)
*************************
Count(col) to Count(*) (CNT)
*************************
CNT: COUNT() to COUNT(*) not done.
PM: Considering predicate move-around in query block SEL$1 (#1)
**************************
Predicate Move-Around (PM)
**************************
PM: PM bypassed: Outer query contains no views.
PM: PM bypassed: Outer query contains no views.
kkqctdrvTD-start on query block SEL$1 (#1)
kkqctdrvTD-start: :
call(in-use=520, alloc=16360), compile(in-use=121368, alloc=122192), execution(in-use=127924, alloc=130824)
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
call(in-use=520, alloc=16360), compile(in-use=121660, alloc=126248), execution(in-use=132000, alloc=134900)
kkqctdrvTD-end:
call(in-use=520, alloc=16360), compile(in-use=121780, alloc=126248), execution(in-use=132000, alloc=134900)
kkqctdrvTD-start on query block SEL$1 (#1)
kkqctdrvTD-start: :
call(in-use=520, alloc=16360), compile(in-use=121780, alloc=126248), execution(in-use=132000, alloc=134900)
Registered qb: SEL$1 0x22ae1df4 (COPY SEL$1)
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature(): NULL
***********************************
Cost-Based Group By Placement
***********************************
GBP: Checking validity of GBP for query block SEL$1 (#1)
GBP: Checking validity of group-by placement for query block SEL$1 (#1)
GBP: Bypassed: Query has invalid constructs.
kkqctdrvTD-cleanup: transform(in-use=1852, alloc=4072) :
call(in-use=520, alloc=16360), compile(in-use=128324, alloc=146580), execution(in-use=152432, alloc=155332)
kkqctdrvTD-end:
call(in-use=520, alloc=16360), compile(in-use=124336, alloc=146580), execution(in-use=152432, alloc=155332)
GBP: Applying transformation directives
GBP: Checking validity of group-by placement for query block SEL$1 (#1)
GBP: Bypassed: Query has invalid constructs.
JPPD: Considering Cost-based predicate pushdown from query block SEL$1 (#1)
************************************
Cost-based predicate pushdown (JPPD)
************************************
kkqctdrvTD-start on query block SEL$1 (#1)
kkqctdrvTD-start: :
call(in-use=520, alloc=16360), compile(in-use=124336, alloc=146580), execution(in-use=152432, alloc=155332)
kkqctdrvTD-cleanup: transform(in-use=0, alloc=0) :
call(in-use=520, alloc=16360), compile(in-use=124612, alloc=146580), execution(in-use=152432, alloc=155332)
kkqctdrvTD-end:
call(in-use=520, alloc=16360), compile(in-use=124732, alloc=146580), execution(in-use=152432, alloc=155332)
JPPD: Applying transformation directives
query block SEL$1 (#1) unchanged
FPD: Considering simple filter push in query block SEL$1 (#1)
??
kkoqbc: optimizing query block SEL$1 (#1)
:
call(in-use=520, alloc=16360), compile(in-use=125392, alloc=146580), execution(in-use=152432, alloc=155332)
kkoqbc-subheap (create addr=0x0A69B0A0)
****************
QUERY BLOCK TEXT ---执行的sql语句文本。
****************
select owner,object_name,object_id, count(*)
from t group by owner ,object_name,object_id
---------------------
QUERY BLOCK SIGNATURE
---------------------
signature (optimizer): qb_name=SEL$1 nbfros=1 flg=0
fro(0): flg=0 bjn=69812 hint_alias="T"@"SEL$1"
系统的统计信息
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats
CPUSPEED: 1846 millions instructions/sec
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
下面是表和索引的统计信息。
***************************************
BASE STATISTICAL INFORMATION -
***********************
Table Stats::
Table: T Alias: T
#Rows: 203010 #Blks: 2973 AvgRowLen: 101.00
Index Stats::
Index: IDX_T Col#: 1 2 4
LVLS: 2 #LB: 1306 #DK: 67670 LB/K: 1.00 DB/K: 3.00 CLUF: 203010.00
-----这是最重要的部分，关于路径的选择的部分。各种路径的cost 值都会在这里列出。优化器选择最终的表的访问路径也在这里显示。
Access path analysis for T
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T[T]
Table: T Alias: T
Card: Original: 203010.000000 Rounded: 203010 Computed: 203010.00 Non Adjusted: 203010.00
Access Path: TableScan
Cost: 809.88 Resp: 809.88 Degree: 0
Cost_io: 807.00 Cost_cpu: 63804141
Resp_io: 807.00 Resp_cpu: 63804141
Access Path: index (index (FFS))
Index: IDX_T
resc_io: 355.00 resc_cpu: 33661801
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 356.52 Resp: 356.52 Degree: 1
Cost_io: 355.00 Cost_cpu: 33661801
Resp_io: 355.00 Resp_cpu: 33661801
Access Path: index (FullScan)
Index: IDX_T
resc_io: 1308.00 resc_cpu: 49916844
ix_sel: 1.000000 ix_sel_with_filters: 1.000000
Cost: 1310.25 Resp: 1310.25 Degree: 1
Best:: AccessPath: IndexFFS ---最佳路径。
Index: IDX_T
Cost: 356.52 Degree: 1 Resp: 356.52 Card: 203010.00 Bytes: 0
Grouping column cardinality [ OWNER] 22
Grouping column cardinality [OBJECT_NAM] 40356
Grouping column cardinality [ OBJECT_ID] 68480
***************************************
OPTIMIZER STATISTICS AND COMPUTATIONS
***************************************
GENERAL PLANS
***************************************
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: T[T]#0
GROUP BY sort
ColGroup (#1, Index) IDX_T
Col#: 1 2 4 CorStregth: 898459.22
ColGroup Usage:: PredCnt: 3 Matches Full: #0 Partial: Sel: 0.0000
GROUP BY adjustment factor: 0.000001
GROUP BY cardinality: 67670.000000, TABLE cardinality: 203010.000000
SORT ressource Sort statistics
Sort width: 118 Area size: 131072 Max Area size: 20971520
Degree: 1
Blocks to Sort: 1243 Row size: 50 Total Rows: 203010
Initial runs: 2 Merge passes: 1 IO Cost / pass: 674
Total IO sort cost: 1089 Total CPU sort cost: 203830045
Total Temp space used: 13067000
***********************
Best so far: Table#: 0 cost: 1454.7215 card: 203010.0000 bytes: 7308360
***********************
****** Recost for ORDER BY (using index) ************
Access path analysis for T
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for T[T]
Table: T Alias: T
Card: Original: 203010.000000 Rounded: 203010 Computed: 203010.00 Non Adjusted: 203010.00
Access Path: TableScan
Cost: 809.88 Resp: 809.88 Degree: 0
Cost_io: 807.00 Cost_cpu: 63804141
Resp_io: 807.00 Resp_cpu: 63804141
Access Path: index (FullScan)
Index: IDX_T
resc_io: 1308.00 resc_cpu: 49916844
ix_sel: 1.000000 ix_sel_with_filters: 1.000000
Cost: 1310.25 Resp: 1310.25 Degree: 1
Best:: AccessPath: IndexRange
Index: IDX_T
Cost: 1310.25 Degree: 1 Resp: 1310.25 Card: 203010.00 Bytes: 36
Join order[1]: T[T]#0
***********************
Best so far: Table#: 0 cost: 1310.2535 card: 203010.0000 bytes: 7308360
***********************
(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000
*********************************
Number of join permutations tried: 1
*********************************
ColGroup Usage:: PredCnt: 3 Matches Full: #0 Partial: Sel: 0.0000
GROUP BY adjustment factor: 0.000001
GROUP BY cardinality: 67670.000000, TABLE cardinality: 203010.000000
SORT ressource Sort statistics
Sort width: 118 Area size: 131072 Max Area size: 20971520
Degree: 1
Blocks to Sort: 1243 Row size: 50 Total Rows: 203010
Initial runs: 2 Merge passes: 1 IO Cost / pass: 674
Total IO sort cost: 1089 Total CPU sort cost: 203830045
Total Temp space used: 13067000
Trying or-Expansion on query block SEL$1 (#1)
Transfer Optimizer annotations for query block SEL$1 (#1)
ColGroup Usage:: PredCnt: 3 Matches Full: #0 Partial: Sel: 0.0000
GROUP BY adjustment factor: 0.000001
Final cost for query block SEL$1 (#1) - All Rows Plan:
Best join order: 1
Cost: 1310.2535 Degree: 1 Card: 203010.0000 Bytes: 7308360
Resc: 1310.2535 Resc_io: 1308.0000 Resc_cpu: 49916844
Resp: 1310.2535 Resp_io: 1308.0000 Resc_cpu: 49916844
kkoqbc-subheap (delete addr=0x0A69B0A0, in-use=9956, alloc=12132)
kkoqbc-end:
:
call(in-use=7804, alloc=32736), compile(in-use=132696, alloc=146580), execution(in-use=152432, alloc=155332)
kkoqbc: finish optimizing query block SEL$1 (#1)
apadrv-end
:
call(in-use=7804, alloc=32736), compile(in-use=133392, alloc=146580), execution(in-use=152432, alloc=155332)
Starting SQL statement dump
user_id=0 user_name=SYS module=sqlplus.exe action=
sql_id=a4a1q4c86ghkq plan_hash_value=-1110490754 problem_type=3
----- Current SQL Statement for this session (sql_id=a4a1q4c86ghkq) -----
EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR select owner,object_name,object_id, count(*)
from t group by owner ,object_name,object_id
sql_text_length=141
sql=EXPLAIN PLAN SET STATEMENT_ID='PLUS4294967295' FOR select owner,object_name,object_id, count(*)
from t group by owner ,object_name,object_id
----- Explain Plan Dump -----
----- Plan Table -----
============
Plan Table
============
----------------------------------------+-----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
----------------------------------------+-----------------------------------+
| 0 | SELECT STATEMENT | | | | 1310 | |
| 1 | SORT GROUP BY NOSORT | | 66K | 2379K | 1310 | 00:00:16 |
| 2 | INDEX FULL SCAN | IDX_T | 198K | 7137K | 1310 | 00:00:16 |
----------------------------------------+-----------------------------------+
Predicate Information:
----------------------
Content of other_xml column
===========================
db_version : 11.1.0.6
parse_schema : SYS
plan_hash : 3184476542
plan_hash_2 : 1989369395
Outline Data:
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.1.0.6')
DB_VERSION('11.1.0.6')
ALL_ROWS
OUTLINE_LEAF(@"SEL$1")
INDEX(@"SEL$1" "T"@"SEL$1" ("T"."OWNER" "T"."OBJECT_NAME" "T"."OBJECT_ID"))
END_OUTLINE_DATA
*/
:
call(in-use=9324, alloc=32736), compile(in-use=157848, alloc=256772), execution(in-use=264992, alloc=266064)
End of Optimizer State Dump
====================== END SQL Statement Dump ======================
*** 2010-10-19 14:32:30.703
KGX cleanup...
KGX Atomic Operation Log 24E02DCC
Mutex 28DB24D8(164, 0) idn 425c9e31 oper EXAM
Cursor Parent uid 164 efd 5 whr 26 slp 0
oper=OPERATION_DEFAULT pt1=00000000 pt2=00000000 pt3=00000000
pt4=00000000 u41=0 stt=0
