本文主要是介绍10053事件分析，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
10053内容：参数区=>初始化参数，隐含参数，这些参数可以左右oracle工作方式
sql区=>执行的sql语句，是否使用绑定变量，是否进行了转换操作
系统信息区=>操作系统统计信息，cpu主频cpu执行事件io寻址时间、单块读时间、多块读时间
数据访问方式=>访问方式不一样计算代价的方法也不一样，全表扫描走索引多表关联代价都不同
关联查询=>把每张表都作为驱动表去组合，择优选择“代价”最小的关联方式，与哪个表在前无关系
代价的最后修正=>oracle会对选择出来的代价再进行最后的修正，使其更准确一些，更合理一些
10053event里常见名词解释：
he 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
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
TABLE: Table Name
ALIAS: Table Alias
QBS: Query Block Signature
#ROWS: Number of Rows
#BLKS: Number of Blocks
ARL: Average Row Length
COR: Cardinality Original
CRD: Cardinality Rounded
CCM: Cardinality Computed
CNA: Cardinality Non Adjusted
AVGLEN: Average Column Length
NDV: Number of Distinct Values
NULLS: Number of Nulls in Column
DEN: Column Density
MIN: Minimum Column Value
MAX: Maximum Column Value
TYPE: Histogram Type
#BKTS: Histogram Buckets
UNCOMPBKTS: Histogram Uncompressed Buckets
ENDPTVALS: Histogram End Point Values
OOR: Out-of-Range Predicate
TABLE: Table Name
ALIAS: Table Alias
INDEX: Index Name
QBS: Query Block Signature
LVLS: Index Levels
#LB: Number of Leaf Blocks
#DK: Number of Distinct Keys
LB/K: Average Number of Leaf Blocks Per Key
DB/K: Average Number of Data Blocks Per Key
CLUF: Clustering Factor
INDEX_COLS: Index Column Numbers
COST: Cost of the Join
CARD: Cardinality of the Join
BC: Best Cost
LINE#: Line Number in the 10053 Trace File Where Cost Value is Located
JOIN#: Join Number in the 10053 Trace File Associated With Key
STATUS: If Permutation was Computed for all Table Joins the Status = COMPL. If Not, status = ABORT
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
案例：
创建实验环境：
SQL> create table trsen1 as select * from sh.customers;
Table created.
SQL> create table trsen2 as select * from sh.customers where rownum<=100;
Table created.
SQL> create index idx_trsen1_cid on trsen1(cust_id);
Index created.
SQL> create index idx_trsen2_cid on trsen2(cust_id);
Index created.
SQL> begin
2 dbms_stats.gather_table_stats(
3 ownname=>'trsen',
4 tabname=>'trsen2',
5 cascade=>true,
6 estimate_percent=>null,
7 method_opt=>'for all columns size 1');
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_stats.gather_table_stats(
3 ownname=>'trsen',
4 tabname=>'trsen1',
5 cascade=>true,
6 estimate_percent=>null,
7 method_opt=>'for all columns size 1');
8 end;
9 /
PL/SQL procedure successfully completed.
分析10053的trc文件：
-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
Using NOWORKLOAD Stats==>基于非工作模式下的系统统计信息
CPUSPEEDNW: 3137 millions instructions/sec (default is 100)==>系统上每个CPU每秒钟可以执行的标准操作的次数。后缀NW表明这是个非工作负载下的估计
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)==>磁盘的IO传输速率（单位字节/毫秒）
IOSEEKTIM: 9 milliseconds (default is 10)==>在磁盘上定位数据的平均时间。但实际上是从磁盘检索一个数据块的总时间，包含磁盘的旋转时延、传输时延与寻道时间
MBRC: NO VALUE blocks (default is 8)==>oracle收集完统计信息后评估出的一次多块读可以读几个数据块db_file_multiblock_read_count
***************************************
BASE STATISTICAL INFORMATION==>基本统计信息
***********************
Table Stats::
Table: TRSEN2 Alias: TRSEN2
#Rows: 100 #Blks: 6 AvgRowLen: 184.00 ChainCnt: 0.00==>行数、块数、行平均长度、涉及行链接和行迁移的总行数（dbms_stats不计算这个值，被设置为0）=>dba_tables
Column (#1): CUST_ID(
AvgLen: 5 NDV: 100 Nulls: 0 Density: 0.010000 Min: 1449 Max: 50561=>列平均长度、非重复值、空值数、密度、最小值、最大值=>dba_tab_columns
Index Stats::
Index: IDX_TRSEN2_CID Col#: 1
LVLS: 0 #LB: 1 #DK: 100 LB/K: 1.00 DB/K: 1.00 CLUF: 76.00==>索引高度、叶子块数、非重复键值、占据块数/每个索引、数据块数/每个索引键值、索引的聚合因子=>dba_indexs
***********************
Table Stats::
Table: TRSEN1 Alias: TRSEN1
#Rows: 55500 #Blks: 1485 AvgRowLen: 181.00 ChainCnt: 0.00
Column (#1): CUST_ID(
AvgLen: 5 NDV: 55500 Nulls: 0 Density: 0.000018 Min: 1 Max: 104500
Index Stats::
Index: IDX_TRSEN1_CID Col#: 1
LVLS: 1 #LB: 123 #DK: 55500 LB/K: 1.00 DB/K: 1.00 CLUF: 54345.00==>最佳的索引因子是cluf=blks，最坏的索引因子是cluf=rows
Access path analysis for TRSEN1
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TRSEN1[TRSEN1]
Table: TRSEN1 Alias: TRSEN1
Card: Original: 55500.000000 Rounded: 55500 Computed: 55500.00 Non Adjusted: 55500.00==>原始行、近似值、精确值、非修正值
Access Path: TableScan
Cost: 424.55 Resp: 424.55 Degree: 0==>全表扫描成本
Cost_io: 424.00 Cost_cpu: 18900338
Resp_io: 424.00 Resp_cpu: 18900338
Access Path: index (index (FFS))
Index: IDX_TRSEN1_CID
resc_io: 37.00 resc_cpu: 7535937
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 37.22 Resp: 37.22 Degree: 1==>快速全表扫描的成本
Cost_io: 37.00 Cost_cpu: 7535937
Resp_io: 37.00 Resp_cpu: 7535937
Access Path: index (FullScan)
Index: IDX_TRSEN1_CID
resc_io: 124.00 resc_cpu: 11983059
ix_sel: 1.000000 ix_sel_with_filters: 1.000000
Cost: 124.35 Resp: 124.35 Degree: 1==>索引全扫描的成本
Best:: AccessPath: IndexFFS==>选择最佳的快速全表扫描来做访问路径
Index: IDX_TRSEN1_CID
Cost: 37.22 Degree: 1 Resp: 37.22 Card: 55500.00 Bytes: 0
Access path analysis for TRSEN2
***************************************
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for TRSEN2[TRSEN2]
Table: TRSEN2 Alias: TRSEN2
Card: Original: 100.000000 Rounded: 100 Computed: 100.00 Non Adjusted: 100.00
Access Path: TableScan
Cost: 3.00 Resp: 3.00 Degree: 0
Cost_io: 3.00 Cost_cpu: 57729
Resp_io: 3.00 Resp_cpu: 57729
Access Path: index (index (FFS))
Index: IDX_TRSEN2_CID
resc_io: 2.00 resc_cpu: 19121
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Access Path: index (FFS)
Cost: 2.00 Resp: 2.00 Degree: 1
Cost_io: 2.00 Cost_cpu: 19121
Resp_io: 2.00 Resp_cpu: 19121
Access Path: index (FullScan)
Index: IDX_TRSEN2_CID
resc_io: 1.00 resc_cpu: 27121
ix_sel: 1.000000 ix_sel_with_filters: 1.000000
Cost: 1.00 Resp: 1.00 Degree: 1
Best:: AccessPath: IndexRange
Index: IDX_TRSEN2_CID
Cost: 1.00 Degree: 1 Resp: 1.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]: TRSEN2[TRSEN2]#0 TRSEN1[TRSEN1]#1
***************
Now joining: TRSEN1[TRSEN1]#1
***************
NL Join
Outer table: Card: 100.00 Cost: 1.00 Resp: 1.00 Degree: 1 Bytes: 5
Access path analysis for TRSEN1
Inner table: TRSEN1 Alias: TRSEN1
Access Path: TableScan
NL Join: Cost: 42244.77 Resp: 42244.77 Degree: 1==>cost=42190+Cost_cpu/3137/11/1000=42244.77326226563
Cost_io: 42190.00 Cost_cpu: 1890060961
Resp_io: 42190.00 Resp_cpu: 1890060961
Access Path: index (index (FFS))
Index: IDX_TRSEN1_CID
resc_io: 34.96 resc_cpu: 7535937
ix_sel: 0.000000 ix_sel_with_filters: 1.000000
Inner table: TRSEN1 Alias: TRSEN1
Access Path: index (FFS)
NL Join: Cost: 3518.84 Resp: 3518.84 Degree: 1
Cost_io: 3497.00 Cost_cpu: 753620833
Resp_io: 3497.00 Resp_cpu: 753620833
Access Path: index (AllEqJoinGuess)
Index: IDX_TRSEN1_CID
resc_io: 1.00 resc_cpu: 8171
ix_sel: 0.000018 ix_sel_with_filters: 0.000018
NL Join (ordered): Cost: 101.02 Resp: 101.02 Degree: 1
Cost_io: 101.00 Cost_cpu: 844265
Resp_io: 101.00 Resp_cpu: 844265
Best NL cost: 101.02
resc: 101.02 resc_io: 101.00 resc_cpu: 844265
resp: 101.02 resp_io: 101.00 resc_cpu: 844265
Join Card: 100.000000 = outer (100.000000) * inner (55500.000000) * sel (0.000018)
Join Card - Rounded: 100 Computed: 100.00
Outer table: TRSEN2 Alias: TRSEN2
resc: 1.00 card 100.00 bytes: 5 deg: 1 resp: 1.00
Inner table: TRSEN1 Alias: TRSEN1
resc: 37.22 card: 55500.00 bytes: 5 deg: 1 resp: 37.22
using dmeth: 2 #groups: 1
SORT ressource Sort statistics
Sort width: 391 Area size: 343040 Max Area size: 68786176
Degree: 1
Blocks to Sort: 109 Row size: 16 Total Rows: 55500
Initial runs: 2 Merge passes: 1 IO Cost / pass: 64
Total IO sort cost: 173 Total CPU sort cost: 76600063
Total Temp space used: 1352000
SM join: Resc: 213.44 Resp: 213.44 [multiMatchCost=0.00]==>cost=inner_table_cost+outer_table_cost+inner_table_sort_cost+outer_table_sort_cost
SM Join
SM cost: 213.44 ==>cost=1.00+37.22+173+76600063/3137/(9+8192/4096)/1000=38.22+175.2198412785812=213.4398412785812==>213.44
resc: 213.44 resc_io: 211.00 resc_cpu: 84163121
resp: 213.44 resp_io: 211.00 resp_cpu: 84163121
Outer table: TRSEN2 Alias: TRSEN2
resc: 1.00 card 100.00 bytes: 5 deg: 1 resp: 1.00
Inner table: TRSEN1 Alias: TRSEN1
resc: 37.22 card: 55500.00 bytes: 5 deg: 1 resp: 37.22
using dmeth: 2 #groups: 1
Cost per ptn: 0.66 #ptns: 1
hash_area: 124 (max=16794) buildfrag: 1 probefrag: 116 ppasses: 1
Hash join: Resc: 38.88 Resp: 38.88 [multiMatchCost=0.00]==>小表驱动大表时的成本
HA Join
HA cost: 38.88 ==>cost=cost_outer_table_access+cost_building_bash_table+cost_inner_table_access=37.22+0.66+1=38.88
resc: 38.88 resc_io: 38.00 resc_cpu: 30381559
resp: 38.88 resp_io: 38.00 resp_cpu: 30381559
Best:: JoinMethod: Hash
Cost: 38.88 Degree: 1 Resp: 38.88 Card: 100.00 Bytes: 10
***********************
Best so far: Table#: 0 cost: 1.0008 card: 100.0000 bytes: 500
Table#: 1 cost: 38.8804 card: 100.0000 bytes: 1000
***********************
Join order[2]: TRSEN1[TRSEN1]#1 TRSEN2[TRSEN2]#0
***************
........
省略大表做驱动表的排序连接信息
........
Outer table: TRSEN1 Alias: TRSEN1
resc: 37.22 card 55500.00 bytes: 5 deg: 1 resp: 37.22
Inner table: TRSEN2 Alias: TRSEN2
resc: 1.00 card: 100.00 bytes: 5 deg: 1 resp: 1.00
using dmeth: 2 #groups: 1
Cost per ptn: 0.74 #ptns: 1
hash_area: 124 (max=16794) buildfrag: 116 probefrag: 1 ppasses: 1
Hash join: Resc: 38.96 Resp: 38.96 [multiMatchCost=0.00]==>大表驱动小表时的cost成本
Outer table: TRSEN2 Alias: TRSEN2
resc: 1.00 card 100.00 bytes: 5 deg: 1 resp: 1.00
Inner table: TRSEN1 Alias: TRSEN1
resc: 37.22 card: 55500.00 bytes: 5 deg: 1 resp: 37.22
using dmeth: 2 #groups: 1
Cost per ptn: 0.66 #ptns: 1
hash_area: 124 (max=16794) buildfrag: 1 probefrag: 116 ppasses: 1
Hash join: Resc: 38.88 Resp: 38.88 [multiMatchCost=0.00]==>小表驱动大表时的cost成本
HA Join
HA cost: 38.88 swapped
resc: 38.88 resc_io: 38.00 resc_cpu: 30381559
resp: 38.88 resp_io: 38.00 resp_cpu: 30381559
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 TRSEN2[TRSEN2] and TRSEN1[TRSEN1] 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 TRSEN2[TRSEN2](serial) and TRSEN1[TRSEN1](serial); jm = 1; right side access path = IndexFFS
---- 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="TRSEN1"."CUST_ID"="TRSEN2"."CUST_ID"
id=0 frosand (sort-merge/hash) predicate="TRSEN1"."CUST_ID"="TRSEN2"."CUST_ID"
Final cost for query block SEL$1 (#0) - All Rows Plan:
Best join order: 1==>选择顺序1，来生成执行计划
Cost: 38.8804 Degree: 1 Card: 100.0000 Bytes: 1000
Resc: 38.8804 Resc_io: 38.0000 Resc_cpu: 30381559
Resp: 38.8804 Resp_io: 38.0000 Resc_cpu: 30381559
这篇关于10053事件分析的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
