Oracle 10053 事件

最新推荐文章于 2021-04-14 14:27:06 发布

iteye_3158

最新推荐文章于 2021-04-14 14:27:06 发布

阅读量63

点赞数

文章标签：数据库操作系统人工智能

一.10053事件

当一个SQL出现性能问题的时候，可以使用SQL_TRACE或者10046事件来跟踪SQL.通过生成的trace来了解SQL的执行过程。

Oracle SQL Trace和10046事件

http://blog.csdn.net/xujinyang/article/details/6831398

Event 10053执行计划绑定变量Bind peeking

http://blog.csdn.net/xujinyang/article/details/6882133

Oracle跟踪事件set event

http://blog.csdn.net/xujinyang/article/details/6829880

现在来看一下10053事件。10053事件也是非公开的，在官网上也找不到相关信息。我们在查看一条SQL的执行计划的时候，只能看到CBO最终告诉我们的执行计划结果，但是不知道CBO是根据什么来做的。如果遇到了执行计划失真，如：一个SQL语句，很明显oracle应该使用索引，但是执行计划却没有使用索引。无法进行分析判断。

而10053事件就提供了这样的功能。它产生的trace文件提供了Oracle如何选择执行计划，为什么会得到这样的执行计划信息。

10053事件生成trace文件目录和SQL_TRACE一样。

在Oracle 10g中，SQL_TRACE生成的trace文件默认路劲是$ORACLE_BASE/admin/SID/udump.

在Oracle 11g，trace默认路径在：$ORACLE_BASE/diag/rdbms/orcl/orcl/trace目录下

对于10053事件的trace文件，我们只能直接阅读原始的trace文件，不能使用tkprof工具来处理，tkprof工具只能用来处理sql_trace和10046事件产生的trace文件。

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

启用10053事件：

ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';

ALTER SESSION SET EVENTS='10053 trace name context forever, level 2';

关闭10053事件：

ALTER SESSION SET EVENTS '10053 trace name context off';

说明：

（1）sqlplus中打开autotrace看到的执行计划实际上是用explain plan命令得到的，explain plan命令不会进行bind peeking。应该通过v$sql_plan查看SQL的真实的执行计划。

（2）10053只对CBO有效，而且如果一个sql语句已经解析过，就不会产生新的trace信息。

二．示例：

1.确定当前的trace文件

1.1设定trace文件标识

SQL> alter session set tracefile_identifier='怀宁';

会话已更改。

设置标识的目的就是方便我们查找生成的trace文件。我们只需要在trace目录查找文件名里带有标识的文件即可。

1.2直接用如下SQL直接查出，当前的trace文件名。

/* Formatted on 2010/9/1 23:56:24 (QP5 v5.115.810.9015) */

SELECTd.VALUE

|| '/'

|| LOWER (RTRIM (i.INSTANCE, CHR (0)))

|| '_ora_'

|| p.spid

|| '.trc'

AS "trace_file_name"

FROM(SELECTp.spid

FROMv$mystat m, v$session s, v$process p

WHEREm.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p,

(SELECTt.INSTANCE

FROMv$thread t, v$parameter v

WHEREv.NAME = 'thread'

AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i,

(SELECTVALUE

FROMv$parameter

WHERENAME = 'user_dump_dest') d;

2.启动10053事件

SQL> ALTER SESSION SET EVENTS='10053 trace name context forever, level 1';

3.执行事务

SQL> select * from all_tables where table_name='T';

4.关闭10053事件

SQL> ALTER SESSION SET EVENTS '10053 trace name context off';

三．查看生成的trace文件

因为我们在做之前设置了标识，所以直接进入trace目录，找到含有‘怀宁’标识的trace文件。

Trace file d:/app/administrator/diag/rdbms/orcl/orcl/trace/orcl_ora_3756_怀宁.trc

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Windows NT Version V6.1

CPU: 2 - type 586, 2 Physical Cores

Process Affinity: 0x0x00000000

Memory (Avail/Total): Ph:1570M/4095M, Ph+PgF:4126M/8188M, VA:2874M/4095M

Instance name: orcl

Redo thread mounted by this instance: 1

Oracle process number: 32

Windows thread id: 3756, image: ORACLE.EXE (SHAD)

*** 2010-09-02 15:09:16.677

*** SESSION ID:(23.3388) 2010-09-02 15:09:16.677

*** CLIENT ID:() 2010-09-02 15:09:16.677

*** SERVICE NAME:(SYS$USERS) 2010-09-02 15:09:16.677

*** MODULE NAME:(sqlplus.exe) 2010-09-02 15:09:16.677

*** ACTION NAME:() 2010-09-02 15:09:16.677

*** TRACE CONTINUED FROM FILE d:/app/administrator/diag/rdbms/orcl/orcl/trace/orcl_ora_3756.trc ***

Registered qb: SEL$1 0xdab3a30 (PARSER)

---------------------

QUERY BLOCK SIGNATURE

---------------------

signature (): qb_name=SEL$1 nbfros=1 flg=0

fro(0): flg=5 objn=3232 hint_alias="ALL_TABLES"@"SEL$1"

Registered qb: SEL$2 0xdab1a9c (PARSER)

---------------------

QUERY BLOCK SIGNATURE

---------------------

signature (): qb_name=SEL$2 nbfros=11 flg=0

fro(0): flg=4 objn=18 hint_alias="CO"@"SEL$2"

fro(1): flg=4 objn=22 hint_alias="CU"@"SEL$2"

fro(2): flg=4 objn=18 hint_alias="CX"@"SEL$2"

fro(3): flg=4 objn=132 hint_alias="DS"@"SEL$2"

fro(4): flg=4 objn=4294951198 hint_alias="KSPPCV"@"SEL$2"

fro(5): flg=4 objn=4294950998 hint_alias="KSPPI"@"SEL$2"

fro(6): flg=4 objn=18 hint_alias="O"@"SEL$2"

fro(7): flg=4 objn=14 hint_alias="S"@"SEL$2"

fro(8): flg=4 objn=4 hint_alias="T"@"SEL$2"

fro(9): flg=4 objn=16 hint_alias="TS"@"SEL$2"

fro(10): flg=4 objn=22 hint_alias="U"@"SEL$2"

Registered qb: SEL$3 0xf8a701c (PARSER)

---------------------

QUERY BLOCK SIGNATURE

---------------------

signature (): qb_name=SEL$3 nbfros=1 flg=0

fro(0): flg=4 objn=61 hint_alias="OA"@"SEL$3"

Registered qb: SEL$4 0xf8a6acc (PARSER)

---------------------

QUERY BLOCK SIGNATURE

---------------------

signature (): qb_name=SEL$4 nbfros=1 flg=0

fro(0): flg=4 objn=4294951024 hint_alias="X$KZSRO"@"SEL$4"

Registered qb: SEL$5 0xf8a6384 (PARSER)

---------------------

QUERY BLOCK SIGNATURE

---------------------

signature (): qb_name=SEL$5 nbfros=1 flg=0

fro(0): flg=5 objn=4294950942 hint_alias="V$ENABLEDPRIVS"@"SEL$5"

Registered qb: SEL$6 0xf8ba570 (PARSER)

---------------------

QUERY BLOCK SIGNATURE

---------------------

signature (): qb_name=SEL$6 nbfros=1 flg=0

fro(0): flg=5 objn=4294951295 hint_alias="GV$ENABLEDPRIVS"@"SEL$6"

Registered qb: SEL$7 0xf8b9c50 (PARSER)

---------------------

QUERY BLOCK SIGNATURE

---------------------

signature (): qb_name=SEL$7 nbfros=1 flg=0

fro(0): flg=4 objn=4294951025 hint_alias="X$KZSPR"@"SEL$7"

SPM: statement not found in SMB

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

Automatic degree of parallelism (ADOP)

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

Automatic degree of parallelism is disabled: Parameter.

PM: Considering predicate move-around in query block SEL$1 (#0)

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

Predicate Move-Around (PM)

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

OPTIMIZER INFORMATION

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

----- Current SQL Statement for this session (sql_id=57m60mu8c3w33) -----

select * from all_tables where table_name='T'

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

Legend

The 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

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

….

Starting SQL statement dump

user_id=0 user_name=SYS module=sqlplus.exe action=

sql_id=57m60mu8c3w33 plan_hash_value=-564731517 problem_type=3

----- Current SQL Statement for this session (sql_id=57m60mu8c3w33) -----

select * from all_tables where table_name='T'

sql_text_length=46

sql=select * from all_tables where table_name='T'

----- Explain Plan Dump -----

----- Plan Table -----

============

Plan Table

============

-----------------------------------------------------------------+-----------------------------------+

-----------------------------------------------------------------+-----------------------------------+

| 0| SELECT STATEMENT||||124 ||

| 1|FILTER||||||

| 2|HASH JOIN||2 |4704 |124 |00:00:02 |

| 3|MERGE JOIN CARTESIAN||2 |674 |124 |00:00:02 |

| 4|NESTED LOOPS OUTER||2 |564 |124 |00:00:02 |

| 5|NESTED LOOPS OUTER||2 |530 |122 |00:00:02 |

| 6|NESTED LOOPS OUTER||2 |514 |120 |00:00:02 |

| 7|NESTED LOOPS OUTER||2 |454 |118 |00:00:02 |

| 8|NESTED LOOPS OUTER||2 |364 |116 |00:00:02 |

| 9|NESTED LOOPS||2 |318 |114 |00:00:02 |

| 10|NESTED LOOPS||2 |280 |112 |00:00:02 |

| 11|NESTED LOOPS||2 |106 |111 |00:00:02 |

| 12|TABLE ACCESS BY INDEX ROWID | OBJ$|2 |72 |109 |00:00:02 |

| 13|INDEX SKIP SCAN| I_OBJ2|2 ||107 |00:00:02 |

| 14|TABLE ACCESS CLUSTER| USER$|1 |17 |1 |00:00:01 |

| 15|INDEX UNIQUE SCAN| I_USER#|1 ||0 ||

| 16|TABLE ACCESS CLUSTER| TAB$|1 |87 |1 |00:00:01 |

| 17|INDEX UNIQUE SCAN| I_OBJ#|1 ||0 ||

| 18|TABLE ACCESS CLUSTER| TS$|1 |19 |1 |00:00:01 |

| 19|INDEX UNIQUE SCAN| I_TS#|1 ||0 ||

| 20|TABLE ACCESS BY INDEX ROWID| DEFERRED_STG$|1 |23 |1 |00:00:01 |

| 21|INDEX UNIQUE SCAN| I_DEFERRED_STG1|1 ||0 ||

| 22|TABLE ACCESS CLUSTER| SEG$|1 |45 |1 |00:00:01 |

| 23|INDEX UNIQUE SCAN| I_FILE#_BLOCK# |1 ||0 ||

| 24|TABLE ACCESS BY INDEX ROWID| OBJ$|1 |30 |2 |00:00:01 |

| 25|INDEX RANGE SCAN| I_OBJ1|1 ||1 |00:00:01 |

| 26|INDEX RANGE SCAN| I_OBJ1|1 |8 |1 |00:00:01 |

| 27|TABLE ACCESS CLUSTER| USER$|1 |17 |1 |00:00:01 |

| 28|INDEX UNIQUE SCAN| I_USER#|1 ||0 ||

| 29|BUFFER SORT||1 |55 |123 |00:00:02 |

| 30|FIXED TABLE FULL| X$KSPPI|1 |55 |0 ||

| 31|FIXED TABLE FULL| X$KSPPCV|100 |197K |0 ||

| 32|NESTED LOOPS||1 |21 |2 |00:00:01 |

| 33|INDEX RANGE SCAN| I_OBJAUTH1|1 |8 |2 |00:00:01 |

| 34|FIXED TABLE FULL| X$KZSRO|1 |13 |0 ||

| 35|FIXED TABLE FULL| X$KZSPR|1 |26 |0 ||

-----------------------------------------------------------------+-----------------------------------+

…..

小结：

CBO一定是选择代价最低的数据访问路径作为SQL的执行计划。如果觉得CBO做出的执行计划不是最优的，就应该去分析。这时就用到了10053事件。我们需要关注CBO选择的每一个代价最低的数据访问方式，以及提供给CBO的分析信息是否真实。

CBO只是一个数学模型，它只是机械地将收集到的各种信息通过固定的方式进行计算，如果我们提供给CBO的信息是准确的，那么CBO就能计算出最优的执行计划（排除bug）。

10053事件没有10046事件用的多，比如我们分析SQL，主要还是看SQL的执行计划和SQL_TRACE信息。但是如果想了解CBO的内部，还得通过10053事件，从10053事件的trace文件中，我们可以深入的了解CBO的内部，了解CBO是如何工作的，根据什么依据得出最终的执行计划。

整理自网络

------------------------------------------------------------------------------

iteye_3158

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
复制链接

分享到 QQ

分享到新浪微博

扫一扫