【Oracle】26.使用10046事件查看Oracle执行计划小事牛刀

1.10046 事件的两种玩法。

使用10046事件是在Oracle数据库中查看目标SQL的执行计划的另外一种方法。10046事件的
特别之处在于显示执行步骤消耗的物理读，逻辑读和花费的时间。整个对于诊断复杂
SQL的性能时非常重要,这是其他方法没有的。
当然使用 GATHER_PLAN_STATISTICS(HINT)+DBMS_XPLAN 包也可以达到10046的效果。

2.使用10046事件方法一

SQL> oradebug setmypid
Statement processed.
SQL> oradebug event 10046 trace name context forever,level 12
Statement processed.
SQL> select t1.id,t1.name from test.test01 t1,test.test03 t2 where t1.id=t2.id; 

	ID NAME
---------- ------------------------------
	 1 1xsq1
	 1 1xsq1

SQL> oradebug tracefile_name
/oracle/app/oracle/diag/rdbms/fermdg/fermdg/trace/fermdg_ora_3141.trc
SQL> oradebug event 10046 trace name context off
Statement processed.

3.使用tkprof翻译trace文件
 

tkprof /oracle/app/oracle/diag/rdbms/fermdg/fermdg/trace/fermdg_ora_3141.trc /home/oracle/fermdg_ora_3141.trc

more /home/oracle/fermdg_ora_3141.trc
TKPROF: Release 11.2.0.4.0 - Development on Sat Feb 3 16:35:19 2024

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Trace file: /oracle/app/oracle/diag/rdbms/fermdg/fermdg/trace/fermdg_ora_3141.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing 
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

SQL ID: 9y3dt1pbmwdb1 Plan Hash: 3157290088

select t1.id,t1.name 
from
 test.test01 t1,test.test03 t2 where t1.id=t2.id


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         16          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         16          0           2

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         2          2          2  NESTED LOOPS  (cr=16 pr=0 pw=0 time=357 us cost=9 size=48 card=2)
         2          2          2   NESTED LOOPS  (cr=14 pr=0 pw=0 time=370 us cost=9 size=48 card=2)
         2          2          2    TABLE ACCESS FULL TEST03 (cr=7 pr=0 pw=0 time=214 us cost=3 size=6 card=2)
         2          2          2    INDEX RANGE SCAN IDX_ID (cr=7 pr=0 pw=0 time=147 us cost=2 size=0 card=1)(object id 87368)
         2          2          2   TABLE ACCESS BY INDEX ROWID TEST01 (cr=2 pr=0 pw=0 time=52 us cost=3 size=21 card=1)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2       12.39         12.39

********************************************************************************
OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0         16          0           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0         16          0           2
Misses in library cache during parse: 1
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       4        0.00          0.00
  SQL*Net message from client                     4       30.10         59.04
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0
Misses in library cache during parse: 0

    1  user  SQL statements in session.
    0  internal SQL statements in session.
    1  SQL statements in session.
********************************************************************************
Trace file: /oracle/app/oracle/diag/rdbms/fermdg/fermdg/trace/fermdg_ora_3141.trc
Trace file compatibility: 11.1.0.7
Sort options: default

       1  session in tracefile.
       1  user  SQL statements in trace file.
       0  internal SQL statements in trace file.
       1  SQL statements in trace file.
       1  unique SQL statements in trace file.
      84  lines in trace file.
       0  elapsed seconds in trace file.
	   
cr:逻辑读,内存查找数据。 
pr:物理读,数据文件读取数据。
time:耗费时间 
card:实际返回结果集的行数。	   
pw:物理写 
同时也可以看到执行计划。

10046使用方法二

SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit  --不限制trace文件大小。
Statement processed.
SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> select t1.id,t2.name from test.test01 t1,test.test03 t2 where t1.id=t2.id;

	ID NAME
---------- --------------------
	 1 xsq1
	 1 xsq1

SQL> oradebug tracefile_name 
/oracle/app/oracle/diag/rdbms/fermdg/fermdg/trace/fermdg_ora_3473.trc
SQL> alter session set events '10046 trace name context off';

Session altered.

4.总结

10046事件可以查看如下内容:

cr:逻辑读,内存查找数据。 
pr:物理读,数据文件读取数据。
time:耗费时间 
card:实际返回结果集的行数。       
pw:物理写 
同时也可以看到执行计划,对象ID,解析调用执行情况，SQL_ID,SQL_HASH_VALUE,SQL_TEXT;