一. 10053事件

當一個SQL出現效能問題的時候，可以使用SQL_TRACE 或者 10046事件來跟蹤SQL. 透過生成的trace來了解SQL的執行過程。

Oracle SQL Trace 和 10046 事件

http://blog.csdn.net/tianlesoftware/archive/2010/09/02/5857023.aspx

Event 10053 執行計劃繫結變數 Bind peeking

http://blog.csdn.net/tianlesoftware/archive/2010/04/30/5544307.aspx

Oracle 跟蹤事件 set event

http://blog.csdn.net/tianlesoftware/archive/2009/12/13/4977827.aspx

現在來看一下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) */

SELECT d.VALUE

|| '/'

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

|| '_ora_'

|| p.spid

|| '.trc'

AS "trace_file_name"

FROM (SELECT p.spid

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

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

(SELECT t.INSTANCE

FROM v$thread t, v$parameter v

WHERE v.NAME = 'thread'

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

(SELECT VALUE

FROM v$parameter

WHERE NAME = '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是如何工作的，根據什麼依據得出最終的執行計劃。

Oracle 10053 事件

相關文章