浅析10053事件

原创谢辉元 2020-03-01

1641

一、概述

当一个SQL出现性能问题的时候，可以使用10046事件来跟踪SQL. 通过生成的trace来了解SQL在执行过程（解析、执行、获取）中收集的统计数据、绑定变量信息、等待事件，执行计划信息。但通过10046事件我们只能看到的是优化器已经执行的结果，但是优化器是如何做出这样的选择的呢，这个最终的执行计划生成过程中考虑了哪些因素呢，如果说一个执行计划感觉明显不对，那到底是哪个环节让优化器给错误计算而误判了呢，那么10053事件就可以很好的告诉我们优化器在生成执行计划中用到的计算因子、中间值以及计算过程，当然，前提是你的优化器是基于cost的（CBO）。

二、使用方法介绍

与10053事件有关的两个参数：
TRACE_ENABLED：由于要做跟踪，因此TRACE_ENABLED参数需要设置为TRUE（默认值）
max_dump_file_size：限制跟踪文件的最大大小，通常设置为unlimited
生成10053事件跟踪文件的四种方法：
第一种：
在11.2版本之后，可以使用DBMS_SQLDIAG包非常方便地生成10053跟踪文件，只要sql还存在内存中，不需要重新执行sql也可以生成。
在调用这个过程时，会自动触发一次硬解析来生成跟踪文件。调用方法：
execute DBMS_SQLDIAG.DUMP_TRACE(
p_sql_id=>‘cjk13xfm8ybh7’,
p_child_number=>0,
p_component=>‘Optimizer’,
p_file_id=>‘TRACE_10053’);

第二种：
可以在会话级别设置只抓取某一个SQL的跟踪文件，这样可以将作用范围缩到最小
ALTER SESSION SET EVENTS ‘trace[rdbms.SQL_Optimizer.][sql:cjk13xfm8ybh7]’;
–执行程序段
ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.] off’;

第三种：
传统方法就是在会话级设置，然后执行sql，获取跟踪文件
ALTER SESSION SET EVENTS ‘10053 trace name context forever, level 1’;
EXPLAIN PLAN FOR --SQL STATEMENT–;
–最好能实际执行一次，而不是仅仅是调用explain plan，避免绑定变量和SQL*Plus的限制，导致抓取的执行计划与应用实际运行的有差异
ALTER SESSION SET EVENTS ‘10053 trace name context off’;
前面三种跟踪文件都是在本会话默认跟踪文件
SELECT value FROM v$diag_info WHERE name=‘Default Trace File’;
第四种：
使用oradebug 来跟踪特定会话
–1.查找会话的相应服务器进程ID

select s.sid,s.serial#, p.spid from v$process p,v$session s where p.addr=s.paddr and s.sid=254;
       SID    SERIAL# SPID
---------- ---------- ------------------------
       254         85 2397

–2.设置追踪服务器进程ID
oradebug setospid 2397
–3.设置追踪文件大小无限制
oradebug unlimit
–开启10053事件
oradebug event 10053 trace name context forever,level 1;
–查看追踪文件
select tracefile from v$process where spid=2397;
–关闭10053事件
oradebug event 10053 trace name context off;
10053事件的两个级别：
Level 2：2级是1级的一个子集，它包含以下内容：
Column statistics
Single Access Paths
Join Costs
Table Joins Considered
Join Methods Considered (NL/MS/HA)
Level 1： 1级比2级更详细，它包含2级的所有内容，再加如下内容：
Parameters used by the optimizer
Index statistics

三追踪文件主要内容解析

1）跟踪文件头部信息
记录数据库、操作系统、实例、进程等基本信息
ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1
System name: Linux
Node name: ora11g1
Release: 2.6.32-431.el6.x86_64
Version: #1 SMP Sun Nov 10 22:19:54 EST 2013
Machine: x86_64
Instance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 19
Unix process pid: 3399, image: oracle@ora11g1 (TNS V1-V3)
2）参数区
包含初始化参数和隐含参数等
-OPTIMIZER INFORMATION
跟踪会话当前的SQL信息（SQLID，SQL文本）

----- Current SQL Statement for this session (sql_id=196wxz41zcpdd) -----
select e.employee_id,e.first_name||’ '||e.last_name name,d.department_name,l.city
from employees e, departments d, jobs j, locations l
where e.department_id = d.department_id
and e.job_id = j.job_id
and d.location_id = l.location_id
and e.employee_id =:y

Legend --跟踪文件使用的缩写说明
The following abbreviations are used by optimizer trace.
CBQT - cost-based query transformation
。。。中间省略
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_cluster_by_rowid_control = 3

Column Usage Monitoring is ON: tracking level = 1 --标识10053事件级别： level 1
（11g环境中即使开始设置级别为2，但最终执行的还是级别1）

3）SQL区
包括 SQL查询转换，合并块，计数统计

Query transformations (QT) --查询转换

Peeked values of the binds in SQL statement

----- Bind Info (kkscoacd) ----- 绑定变量信息
Bind#0
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=03 fl2=1000000 frm=00 csi=00 siz=24 off=0
kxsbbbfp=7fab38200698 bln=22 avl=03 flg=05
value=101
BIND ：Variables bound to a cursor，游标号
bind N ：The bind position being bound，绑定游标的位置，从0开始，0是第一个游标
oacdty ：Data type，数据类型
mxl ：Maximum length of the bind variable (private max len in paren)，绑定变量的最大长度
mal ：Array length，最大数组长度(当用到绑定变量数组或批量操作时)
scl ：Scale，比例
pre ：Precision，精度
oacflg ：Special flag indicating bind options，内部标记，若是奇数，则绑定变量为空值，允许有空值。
fl2 ：Continuation of oacflg，内部标记的扩展
size ：Amount of memory to be allocated for this chunk，缓冲区的大小
offset ：Offset into this chunk for this bind buffer，缓冲区的chunk大小
kxsbbbfp ：Bind address，绑定变量地址
bln ：Bind buffer length，绑定变量缓冲区长度
avl ：Actual value length (array length too)，实际值的长度
flg ：Special flag indicating bind status，内部标记
value ：The actual value of the bind variable，绑定变量的实际值
。。。

QUERY BLOCK TEXT --查询块文本

select e.employee_id,e.first_name||’ '||e.last_name name,d.department_name,l.city
from employees e, departments d, jobs j, locations l
where e.department_id = d.department_id
and e.job_id = j.job_id
and d.location_id = l.location_id
and e.employee_id =:y

SYSTEM STATISTICS INFORMATION --系统统计信息

Using NOWORKLOAD Stats --基于非负载统计模式
CPUSPEEDNW: 3074 millions instructions/sec (default is 100)
IOTFRSPEED: 4096 bytes per millisecond (default is 4096)
IOSEEKTIM: 10 milliseconds (default is 10)
MBRC: NO VALUE blocks (default is 8)

BASE STATISTICAL INFORMATION ----对象统计信息

Table Stats:: --表统计信息
Table: LOCATIONS Alias: L
#Rows: 23 #Blks: 5 AvgRowLen: 49.00 ChainCnt: 0.00
行数块数平均行长度
Column (#1): LOCATION_ID(
AvgLen: 3 NDV: 23 Nulls: 0 Density: 0.043478 Min: 1000 Max: 3200
连接列信息：平均长度唯一值数量空值数量密度最小值最大值
Index Stats:: --索引统计信息
Index: LOC_CITY_IX Col#: 4
LVLS: 0 #LB: 1 #DK: 23 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
Index: LOC_COUNTRY_IX Col#: 6
LVLS: 0 #LB: 1 #DK: 14 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
Index: LOC_ID_PK Col#: 1
LVLS: 0 #LB: 1 #DK: 23 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
Index: LOC_STATE_PROVINCE_IX Col#: 5
LVLS: 0 #LB: 1 #DK: 17 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
索引的高度叶子块数唯一值数量每个键值叶子块数每个键值数据块数聚簇因子

Access path analysis for EMPLOYEES --访问路径分析

SINGLE TABLE ACCESS PATH
Single Table Cardinality Estimation for EMPLOYEES[E]
Column (#1): EMPLOYEE_ID(
AvgLen: 4 NDV: 107 Nulls: 0 Density: 0.009346 Min: 100 Max: 206
Table: EMPLOYEES Alias: E
Card: Original: 107.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: 57207
Resp_io: 3.00 Resp_cpu: 57207
Access Path: index (UniqueScan) --索引唯一扫描
Index: EMP_EMP_ID_PK
resc_io: 1.00 resc_cpu: 8521
ix_sel: 0.009346 ix_sel_with_filters: 0.009346
ix_sel=1/DK=1/107=0.009346 索引选择率 ix_sel_with_filters 带过滤条件索引选择率
Cost: 1.00 Resp: 1.00 Degree: 1
Access Path: index (AllEqUnique)
Index: EMP_EMP_ID_PK
resc_io: 1.00 resc_cpu: 8521
ix_sel: 0.009346 ix_sel_with_filters: 0.009346
Cost: 1.00 Resp: 1.00 Degree: 1
******** Begin index join costing ********
****** trying bitmap/domain indexes ******
Access Path: index (AllEqUnique)
Index: EMP_EMP_ID_PK
resc_io: 0.00 resc_cpu: 1050
ix_sel: 0.009346 ix_sel_with_filters: 0.009346
Cost: 0.00 Resp: 0.00 Degree: 0
Bitmap nodes:
Used EMP_EMP_ID_PK
Cost = 0.000032, sel = 0.009346
****** finished trying bitmap/domain indexes ******
Access Path: index (FullScan)
Index: EMP_NAME_IX
resc_io: 1.00 resc_cpu: 28521
ix_sel: 1.000000 ix_sel_with_filters: 1.000000
Cost: 1.00 Resp: 1.00 Degree: 0
Access Path: index (FullScan)
Index: EMP_DEPARTMENT_IX
resc_io: 1.00 resc_cpu: 28321
ix_sel: 1.000000 ix_sel_with_filters: 1.000000
Cost: 1.00 Resp: 1.00 Degree: 0

******** Cost index join ******** --索引连接cost分析,选择用来连接的索引

Index join: Joining index EMP_EMP_ID_PK
Index join: Joining index EMP_NAME_IX
Ix HA Join
Outer table: EMPLOYEES Alias: E
resc: 0.00 card 1.00 bytes: 14 deg: 1 resp: 0.00
Inner table: Alias:
resc: 1.00 card: 107.00 bytes: 25 deg: 1 resp: 1.00
using dmeth: 2 #groups: 1
Cost per ptn: 0.02 #ptns: 1
hash_area: 135 (max=25600) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join: Resc: 1.02 Resp: 1.02 [multiMatchCost=0.00]
Index join: Joining index EMP_DEPARTMENT_IX
Ix HA Join
Outer table: EMPLOYEES Alias: E
resc: 0.00 card 1.00 bytes: 39 deg: 1 resp: 0.00
Inner table: Alias:
resc: 1.00 card: 107.00 bytes: 13 deg: 1 resp: 1.00
using dmeth: 2 #groups: 1
Cost per ptn: 0.02 #ptns: 1
hash_area: 135 (max=25600) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join: Resc: 1.02 Resp: 1.02 [multiMatchCost=0.00]

******** Index join cost ********

Cost: 2.03
******** End index join costing ********
One row Card: 1.000000
Best:: AccessPath: IndexUnique
Index: EMP_EMP_ID_PK
Cost: 1.00 Degree: 1 Resp: 1.00 Card: 1.00 Bytes: 0
Access path analysis for DEPARTMENTS
…
Access path analysis for LOCATIONS
…
选好用来连接的索引，下面就开始做连接了（连接顺序、连接方式）
OPTIMIZER STATISTICS AND COMPUTATIONS --优化器的统计和计算

GENERAL PLANS --选择执行计划

Considering cardinality-based initial join order.
Permutations for Starting Table :0
Join order[1]: EMPLOYEES[E]#0 DEPARTMENTS[D]#1 LOCATIONS[L]#2 --连接顺序1

-***************
Now joining: DEPARTMENTS[D]#1 --开始连接表DEPARTMENTS
-***************
NL Join --使用nest loop 连接方式分析
Outer table: Card: 1.00 Cost: 1.00 Resp: 1.00 Degree: 1 Bytes: 22
–内部表(被驱动表）DEPARTMENTS 访问路径分析
Access path analysis for DEPARTMENTS
Inner table: DEPARTMENTS Alias: D
Access Path: TableScan --计算全表扫描开销
NL Join: Cost: 4.00 Resp: 4.00 Degree: 1
Cost_io: 4.00 Cost_cpu: 49589
Resp_io: 4.00 Resp_cpu: 49589
Access Path: index (UniqueScan) --计算索引唯一扫描开销
Index: DEPT_ID_PK
resc_io: 1.00 resc_cpu: 8381
ix_sel: 0.038462 ix_sel_with_filters: 0.038462
NL Join (ordered): Cost: 2.00 Resp: 2.00 Degree: 1
Cost_io: 2.00 Cost_cpu: 16903
Resp_io: 2.00 Resp_cpu: 16903
Access Path: index (AllEqUnique)
Index: DEPT_ID_PK
resc_io: 1.00 resc_cpu: 8381
ix_sel: 0.038462 ix_sel_with_filters: 0.038462
NL Join (ordered): Cost: 2.00 Resp: 2.00 Degree: 1
Cost_io: 2.00 Cost_cpu: 16903
Resp_io: 2.00 Resp_cpu: 16903

Best NL cost: 2.00 --最好的nest loop 连接cost=2
resc: 2.00 resc_io: 2.00 resc_cpu: 16903
resp: 2.00 resp_io: 2.00 resc_cpu: 16903
Join Card: 1.000000 = outer (1.000000) * inner (1.000000) * sel (0.038102)
Join Card - Rounded: 1 Computed: 1.00
Outer table: EMPLOYEES Alias: E
resc: 1.00 card 1.00 bytes: 22 deg: 1 resp: 1.00
Inner table: DEPARTMENTS Alias: D
resc: 3.00 card: 26.00 bytes: 19 deg: 1 resp: 3.00
using dmeth: 2 #groups: 1
SORT ressource Sort statistics
Sort width: 598 Area size: 552960 Max Area size: 104857600
Degree: 1
Blocks to Sort: 1 Row size: 31 Total Rows: 26
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 36894395
Total Temp space used: 0
SM join: Resc: 5.00 Resp: 5.00 [multiMatchCost=0.00]
SM Join --sort merge 排序合并连接
SM cost: 5.00
resc: 5.00 resc_io: 4.00 resc_cpu: 36943984
resp: 5.00 resp_io: 4.00 resp_cpu: 36943984
Outer table: EMPLOYEES Alias: E
resc: 1.00 card 1.00 bytes: 22 deg: 1 resp: 1.00
Inner table: DEPARTMENTS Alias: D
resc: 3.00 card: 26.00 bytes: 19 deg: 1 resp: 3.00
using dmeth: 2 #groups: 1
Cost per ptn: 0.02 #ptns: 1
hash_area: 135 (max=25600) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join: Resc: 4.02 Resp: 4.02 [multiMatchCost=0.00]
HA Join --HASH连接
HA cost: 4.02
resc: 4.02 resc_io: 4.00 resc_cpu: 652339
resp: 4.02 resp_io: 4.00 resp_cpu: 652339
Best:: JoinMethod: NestedLoop --最好的连接方式
Cost: 2.00 Degree: 1 Resp: 2.00 Card: 1.00 Bytes: 41

-**************
Now joining: LOCATIONS[L]#2 --连接表LOCATIONS，执行过程和连接表DEPARTMENTS一样
-**************
NL Join
Outer table: Card: 1.00 Cost: 2.00 Resp: 2.00 Degree: 1 Bytes: 41
Access path analysis for LOCATIONS
Inner table: LOCATIONS Alias: L
Access Path: TableScan
NL Join: Cost: 5.00 Resp: 5.00 Degree: 1
Cost_io: 5.00 Cost_cpu: 57340
Resp_io: 5.00 Resp_cpu: 57340
Access Path: index (UniqueScan)
Index: LOC_ID_PK
resc_io: 1.00 resc_cpu: 8381
ix_sel: 0.043478 ix_sel_with_filters: 0.043478
NL Join : Cost: 3.00 Resp: 3.00 Degree: 1
Cost_io: 3.00 Cost_cpu: 25284
Resp_io: 3.00 Resp_cpu: 25284
Access Path: index (AllEqUnique)
Index: LOC_ID_PK
resc_io: 1.00 resc_cpu: 8381
ix_sel: 0.043478 ix_sel_with_filters: 0.043478
NL Join : Cost: 3.00 Resp: 3.00 Degree: 1
Cost_io: 3.00 Cost_cpu: 25284
Resp_io: 3.00 Resp_cpu: 25284

Best NL cost: 3.00
resc: 3.00 resc_io: 3.00 resc_cpu: 25284
resp: 3.00 resp_io: 3.00 resc_cpu: 25284
Join Card: 1.000000 = outer (1.000000) * inner (1.000000) * sel (0.043478)
Join Card - Rounded: 1 Computed: 1.00
Outer table: DEPARTMENTS Alias: D
resc: 2.00 card 1.00 bytes: 41 deg: 1 resp: 2.00
Inner table: LOCATIONS Alias: L
resc: 2.02 card: 23.00 bytes: 12 deg: 1 resp: 2.02
using dmeth: 2 #groups: 1
SORT ressource Sort statistics
Sort width: 598 Area size: 552960 Max Area size: 104857600
Degree: 1
Blocks to Sort: 1 Row size: 24 Total Rows: 23
Initial runs: 1 Merge passes: 0 IO Cost / pass: 0
Total IO sort cost: 0 Total CPU sort cost: 36893576
Total Temp space used: 0
SM join: Resc: 5.02 Resp: 5.02 [multiMatchCost=0.00]
SM Join
SM cost: 5.02
resc: 5.02 resc_io: 4.00 resc_cpu: 37539672
resp: 5.02 resp_io: 4.00 resp_cpu: 37539672
Outer table: DEPARTMENTS Alias: D
resc: 2.00 card 1.00 bytes: 41 deg: 1 resp: 2.00
Inner table: LOCATIONS Alias: L
resc: 2.02 card: 23.00 bytes: 12 deg: 1 resp: 2.02
using dmeth: 2 #groups: 1
Cost per ptn: 0.02 #ptns: 1
hash_area: 135 (max=25600) buildfrag: 1 probefrag: 1 ppasses: 1
Hash join: Resc: 4.03 Resp: 4.03 [multiMatchCost=0.00]
HA Join
HA cost: 4.03
resc: 4.03 resc_io: 4.00 resc_cpu: 1248546
resp: 4.03 resp_io: 4.00 resp_cpu: 1248546
Best:: JoinMethod: NestedLoop
Cost: 3.00 Degree: 1 Resp: 3.00 Card: 1.00 Bytes: 53

Best so far: Table#: 0 cost: 1.0002 card: 1.0000 bytes: 22
Table#: 1 cost: 2.0005 card: 1.0000 bytes: 41
Table#: 2 cost: 3.0007 card: 1.0000 bytes: 53

(newjo-stop-1) k:0, spcnt:0, perm:1, maxperm:2000

----- Plan Table -----得出执行计划

----- Plan Table -----
============
Plan Table
============
------------------------------------------------------±----------------------------------+
| Id | Operation | Name | Rows | Bytes | Cost | Time |
------------------------------------------------------±----------------------------------+
| 0 | SELECT STATEMENT | | | | 3 | |
| 1 | NESTED LOOPS | | 1 | 53 | 3 | 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 41 | 2 | 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 22 | 1 | 00:00:01 |
| 4 | INDEX UNIQUE SCAN | EMP_EMP_ID_PK| 1 | | 0 | |
| 5 | TABLE ACCESS BY INDEX ROWID | DEPARTMENTS | 1 | 19 | 1 | 00:00:01 |
| 6 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 | |
| 7 | TABLE ACCESS BY INDEX ROWID | LOCATIONS | 1 | 12 | 1 | 00:00:01 |
| 8 | INDEX UNIQUE SCAN | LOC_ID_PK | 1 | | 0 | |
------------------------------------------------------±----------------------------------+
Predicate Information:
、- ---------------------
4 - access(“E”.“EMPLOYEE_ID”=:Y)
6 - access(“E”.“DEPARTMENT_ID”=“D”.“DEPARTMENT_ID”)
8 - access(“D”.“LOCATION_ID”=“L”.“LOCATION_ID”)

由上可以大概总结下优化器生成执行计划所要的依据和过程：
依据（输入）：系统参数+优化器参数+系统统计信息+对象统计信息+绑定变量值
过程（计算）：计算单表访问最优方法->多表连接最优顺序->多表连接最优方法
结果（输出）：最佳执行计划（cost值）
所以说如果一个执行计划发生变化，那么就从以上来考虑吧

oracle

最后修改时间：2020-03-01 17:17:52

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

文章被以下合辑收录

Oracle（共4篇）

Oracle

浅析10053事件

一、概述

二、使用方法介绍

三 追踪文件主要内容解析

文章被以下合辑收录

评论

三追踪文件主要内容解析