10053事件

10053事件是非官方的，用来输出oracle计算执行计划的过程输出到trace文件中。官方文档没有10053的介绍。

Oracle 8i时，CBO代价只和I/O有关。Oracle 9i之后，CBO代价除了I/O，还考虑了CPU代价。

当set autotrace 或者 explain plan显示错误的执行计划，而又找不到原因的时候，可以使用10053事件。但是10053事件只会列出每一种方式oracle估算的代价，具体计算出这种错误代价的原因还是需要自己对cost分析。

System Statistics

可以通过sys用户的aux_stats$查看到oracle收集的部分系统信息：

select * from sys.aux_stats$;

当系统没有工作量统计信息时，CBO使用db_file_multiblock_read_count计算全表扫描的IO Cost；收集工作量统计信息后，则使用mbrc代替。

手工开始收集系统信息：

exec dbms_stats.gather_system_stats(gathering_mode => 'start');

代价的计算

如果没有系统统计信息，全表扫描代价 = blocks / k。k是一个修正值，根据初始化参数db_file_multiblock_read_count的值而调整。

k值和初始化参数的关系：

全表扫描+系统统计信息：

Cost = (
    #SRds +     // 单块读的时间
    #MRds * mreadtim / sreadtim +    // 多块读的时间
    #CPUCycles / (cpuspeed * sreadtim)   // cpu信息
)

单块读、多块读是IO，占很大一部分。

单块读：sreadtim = IOSEEKTIME + (size of one block / IOTFRSPEED)

多块读：mreadtim = IOSEEKTIME + ((MBRC * Size of one block) / IOTFRSPEED)

IO_COST = (table blocks)/MBRC * (mreadtim / sreadtim)

cpu_cost = cpucycles / (cpuspeed * sreadtim)

cost = io_cost + cpu_cost

索引代价的计算：

unique scan： blevel + 1
Fast full scan：leaf_blocks / k
Index-only：blevel + FF * leaf_blocks（FF是选择率）
Range Scan：blevel + FF * leaf_blocks + FF * clustering_factor

10053事件

了解Oracle执行计划的生成过程，无法获知代价的计算公式。

10053不易阅读，只有在auto trace解决不了的时候才考虑10053事件。

示例：

-- 开启10053事件
alter session set events '10053 trace name context forever,level 1';
-- 关闭10053事件
alter session set events '10053 trace name context off';

监控其他会话：

-- 开启追踪
sys.dbms_system.set_ev(<sid>, <serial#>, 10053, {1|2}, '');
-- 关闭追踪
sys.dbms_system.set_ev(<sid>, <serial#>, 10053, 0, '');

10053的trace内容

因为10053事件和优化器有关，优化器和oracle版本有关，所以每个oracle版本的10053事件trace都不一样，内容越来越多。

大致有以下内容：

参数区
SQL区
系统信息区（SYSTEM STATISTICS INFORMATION）
基本统计信息（BASE STATISTICAL INFORMATION）
数据访问（ACCESS PATH）
关联查询（JOIN ORDER）
代价的最后修正
最终执行计划

开启10053事件，发出一条sql：

select * from INFECTION_PROBABILITY where personname1 like 'R1%';

开头是部分缩写的解释：

******************************************
----- Current SQL Statement for this session (sql_id=72ayd4ba2sxx7) -----
select * from INFECTION_PROBABILITY where personname1 like 'R1%'
*******************************************
Legend
The following abbreviations are used by optimizer trace.


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
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   下划线开头的参数表示oracle不希望用户去更改的参数
optimizer_features_enable           = 11.2.0.1
_optimizer_search_limit             = 5   
cpu_count                           = 8

..............

当前版本修复的bug
Bug Fix Control Environment
    fix  3834770 = 1       
    fix  3746511 = enabled
    fix  4519016 = enabled
    fix  3118776 = enabled
    fix  4488689 = enabled
............

系统信息区：

-----------------------------
SYSTEM STATISTICS INFORMATION
-----------------------------
  Using NOWORKLOAD Stats  非工作量模式下收集的信息(没有进行过手工收集，oracle会使用一些系统默认的值)
  （工作量模式是：发出手工收集系统信息的指令，oracle会评价后续发出的sql执行过程中各种参数的变化）
  （一般情况下不需要使用工作量模式，对象级的统计信息已经足够）
  CPUSPEEDNW: 1684 millions instructions/sec (default is 100)
  IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
  IOSEEKTIM: 10 milliseconds (default is 10)
  MBRC: -1 blocks (default is 8)

对象的基本统计信息：sql中用到的对象的信息，来自于user_tables、user_indexes等统计视图


***************************************
BASE STATISTICAL INFORMATION
***********************
用到的INFECTION_PROBABILITY表信息
Table Stats::
  Table: INFECTION_PROBABILITY  Alias: INFECTION_PROBABILITY
  表的行数、数据块数量、平均行长，来自于user_tables视图：
    #Rows: 225  #Blks:  5  AvgRowLen:  13.00   
Index Stats::
  表的索引PK_INFECTION_PROBABILITY信息
  Index: PK_INFECTION_PROBABILITY  Col#: 1 2
   索引的高度（层数）、叶的块数、唯一键值的数量、每个key对应的叶块、每个key对应的数据块、索引因子
    LVLS: 0  #LB: 1  #DK: 225  LB/K: 1.00  DB/K: 1.00  CLUF: 30.00


INFECTION_PROBABILITY表的不同访问路径的代价分析：
（会把每种访问路径都分别分析，输出分析结果，最终选择最优访问路径）

Access path analysis for INFECTION_PROBABILITY
***************************************
表的访问路径：
SINGLE TABLE ACCESS PATH 
  Single Table Cardinality Estimation for INFECTION_PROBABILITY[INFECTION_PROBABILITY] 
  Column (#1): 
    NewDensity:0.033333, OldDensity:0.002222 BktCnt:225, PopBktCnt:225, PopValCnt:15, NDV:15
  Table: INFECTION_PROBABILITY  Alias: INFECTION_PROBABILITY

    预估的表数据：表中原始数据条数、关联之后的数据条数近似值、关联之后的数据条数精确值
    Card: Original: 225.000000  Rounded: 121  Computed: 120.50  Non Adjusted: 120.50

  全表扫描方式的代价：
  Access Path: TableScan
    Cost:  3.00  Resp: 3.00  Degree: 0
      IO的代价、cpu的代价
      Cost_io: 3.00  Cost_cpu: 96697
      resp表示并行方式访问，resc表示串行的方式访问。
      Resp_io: 3.00  Resp_cpu: 96697
kkofmx: index filter:"INFECTION_PROBABILITY"."PERSONNAME1" LIKE 'R1%'


  索引扫描的代价：
  Access Path: index (RangeScan)
    Index: PK_INFECTION_PROBABILITY
    索引使用的串行方式
    resc_io: 18.00  resc_cpu: 175376
    索引的选择率、加上where条件中其他谓词条件过滤之后的选择率
    ix_sel: 0.535556  ix_sel_with_filters: 0.535556 
    Cost: 18.01  Resp: 18.01  Degree: 1

  选择代价最少的一种访问路径
  Best:: AccessPath: TableScan
         Cost: 3.00  Degree: 1  Resp: 3.00  Card: 120.50  Bytes: 0

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

关联查询：会使用每一种表作为驱动表分析不同关联方式产生的代价，最后选择一条最优的关联方式。

数据库学习笔记

12-10053事件

10053事件

System Statistics

代价的计算

10053事件

10053的trace内容