Oracle查看执行计划（五）

如果想查看执行计划中每一步耗费的逻辑读、物理读和花费的时间，那么就需要使用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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28536251/viewspace-2139672/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28536251/viewspace-2139672/