介绍：

110053 trace文件用来查看Oracle如何根据成本选择执行计划。

1. 打开10053 trace
alter session set max_dump_file_size = unlimited;
ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
或者执行：
SYS.DBMS_SYSTEM.SET_EV (770, 57, 10053, 2, '');
如果没有$ORACLE_HOME/rdbms/admin/prvutil.plb，并授权grant execute on dbms_system to scott;
variable b1 varchar2(30);

exec :b1:='MARTIN';

EXPLAIN PLAN FOR
select /*+ test */ dname,ename
from emp, dept
where emp.deptno = dept.deptno
and ename = :b1
/

2. 解读10053 trace文件

基本统计信息：

表：
Table Stats::
Table: DEPT Alias: DEPT
#Rows: 4 #Blks: 5 AvgRowLen: 20.00 ChainCnt: 0.00
Column (#1): DEPTNO(
AvgLen: 3 NDV: 4 Nulls: 0 Density: 0.250000 Min: 10 Max: 40

索引：
Index Stats::
Index: PK_DEPT Col#: 1
LVLS: 0 #LB: 1 #DK: 4 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00

LB - LEAF_BLOCKS
DK - DISTINCT_KEYS
LB/K - AVG_LEAF_BLOCKS_PER_KEY
DB/K - AVG_DATA_BLOCKS_PER_KEY
CLUF - CLUSTERING_FACTOR

单表访问成本：
Table Stats::
Table: DEPT Alias: DEPT
    #Rows: 4 #Blks: 5 AvgRowLen: 20.00 ChainCnt: 0.00
Column (#1): DEPTNO(
    AvgLen: 3 NDV: 4 Nulls: 0 Density: 0.250000 Min: 10 Max: 40
Index Stats::
Index: PK_DEPT Col#: 1
    LVLS: 0 #LB: 1 #DK: 4 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for EMP[EMP]
Column (#2): ENAME(
    AvgLen: 6 NDV: 14 Nulls: 0 Density: 0.071429
Density：列的密度 = 1/NDV (没有直方图的情况)。
Table: EMP Alias: EMP
    Card: Original: 14.000000 Rounded: 1 Computed: 1.00 Non Adjusted: 1.00
Access Path: TableScan
    Cost: 3.00 Resp: 3.00 Degree: 0
      Cost_io: 3.00 Cost_cpu: 38807
      Resp_io: 3.00 Resp_cpu: 38807
Best:: AccessPath: TableScan
         Cost: 3.00 Degree: 1 Resp: 3.00 Card: 1.00 Bytes: 0
Card：Original：表的行数
Rounded和Computed：评估的返回行数 = 表的行数 × 列密度
Cost：表扫描成本 = 表的块数/db_file_multi_block_read_count，本案例emp只有一个块，由于ASSM至少要扫描3个块。

索引访问成本：
    Index: DEPT_NO_IX
    resc_io: 2.00 resc_cpu: 21803
    ix_sel: 1.000000 ix_sel_with_filters: 1.000000
    Cost: 2.00 Resp: 2.00 Degree: 1

关联：
Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: EMP[EMP]#0 DEPT[DEPT]#1

emp作为驱动表。

***************
Now joining: DEPT[DEPT]#1
***************
NL Join
Outer table: Card: 1.00 Cost: 3.00 Resp: 3.00 Degree: 1 Bytes: 9
Access path analysis for DEPT
Inner table: DEPT Alias: DEPT
Access Path: TableScan
    NL Join: Cost: 6.00 Resp: 6.00 Degree: 1
      Cost_io: 6.00 Cost_cpu: 75094
      Resp_io: 6.00 Resp_cpu: 75094
Access Path: index (UniqueScan)
    Index: PK_DEPT
    resc_io: 1.00 resc_cpu: 8341
    ix_sel: 0.250000 ix_sel_with_filters: 0.250000
    NL Join : Cost: 4.00 Resp: 4.00 Degree: 1
      Cost_io: 4.00 Cost_cpu: 47149
      Resp_io: 4.00 Resp_cpu: 47149
Access Path: index (AllEqUnique)
    Index: PK_DEPT
    resc_io: 1.00 resc_cpu: 8341
    ix_sel: 0.250000 ix_sel_with_filters: 0.250000
    NL Join : Cost: 4.00 Resp: 4.00 Degree: 1
      Cost_io: 4.00 Cost_cpu: 47149
      Resp_io: 4.00 Resp_cpu: 47149

Best NL cost: 4.00
resc: 4.00 resc_io: 4.00 resc_cpu: 47149
resp: 4.00 resp_io: 4.00 resc_cpu: 47149
Join Card: 1.000000 = outer (1.000000) * inner (4.000000) * sel (0.250000)
关联后的记录数=外部表（1）×内部表（4）×选择性（0.25）

ITPub博客

10053 trace事件

最新文章