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