如果想檢視執行計劃中每一步耗費的邏輯讀、物理讀和花費的時間，那麼就需要使用10046事件來得到目標SQL的執行計劃。

步驟如下：
（1）設定生成的trace檔案標識
SQL> alter session set tracefile_identifier='stone';

Session altered.

（2）啟用10046事件
SQL> oradebug setmypid
Statement processed.

SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.

（3）執行目標SQL
SQL> select employee_id,last_name,salary from hr.employees where employee_id=100;

EMPLOYEE_ID LAST_NAME                     SALARY
----------- ------------------------- ----------
        100 King                           24000

（4）確定生成的trace檔案
SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/stone1/stone1/trace/stone1_ora_2792_stone.trc

（5）關閉10046事件
SQL> oradebug event 10046 trace name context off;
Statement processed.

也可以使用alter session set events ‘10046 trace name context forever,level 12'和alter session set events '10046 trace name context off'來啟用和關閉10046事件。

生成的trace檔案不易讀，需要使用tkprof工具來翻譯一下：
[oracle@oeldb1 ~]$ tkprof /u01/app/oracle/diag/rdbms/stone1/stone1/trace/stone1_ora_2792_stone.trc stone1_ora_2792_stone.trc

TKPROF: Release 11.2.0.4.0 - Development on Sat May 20 21:56:07 2017

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

[oracle@oeldb1 ~]$ more stone1_ora_2792_stone.trc

TKPROF: Release 11.2.0.4.0 - Development on Sat May 20 21:56:07 2017

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

Trace file: /u01/app/oracle/diag/rdbms/stone1/stone1/trace/stone1_ora_2792_stone.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: a6myd6hd4nxwd Plan Hash: 1833546154

select employee_id,last_name,salary
from
hr.employees where employee_id=100

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          2          0           1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        4      0.00       0.00          0          2          0           1

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

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
         1          1          1 TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=2 pr=0 pw=0 time=358 us cost=1 size=16 card=1)
         1          1          1   INDEX UNIQUE SCAN EMP_EMP_ID_PK (cr=1 pr=0 pw=0 time=221 us cost=0 size=0 card=1)(object id 88137)

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        7.49          7.49

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

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          2          0           1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        4      0.00       0.00          0          2          0           1

Misses in library cache during parse: 0

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       11.64         27.54

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: /u01/app/oracle/diag/rdbms/stone1/stone1/trace/stone1_ora_2792_stone.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.
      71 lines in trace file.
       0 elapsed seconds in trace file

Oracle檢視執行計劃（五）

相關文章