使用10046事件查看真实的执行计划

操作如下：

SQL> conn / as sysdba

Connected.

SQL>

SQL> oradebug setmypid 

Statement processed.

SQL>

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

Statement processed.

SQL> select count(object_id) from roidba.tt;

 

COUNT(OBJECT_ID)

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

         5524288

 

SQL> oradebug event 10046 trace name context off;

Statement processed.

SQL> oradebug tracefile_name;

/u01/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_ora_29019.trc

SQL> !ls -l /u01/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_ora_29019.trc

-rw-r----- 1 oracle asmadmin 3478 Apr 17 10:32 /u01/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_ora_29019.trc

 

SQL>

 

 

<roidb2:orcl2:/home/oracle>$tkprof /u01/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_ora_29019.trc trace.out

 

TKPROF: Release 11.2.0.4.0 - Development on Tue Apr 17 10:37:19 2018

 

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

 

 

<roidb2:orcl2:/home/oracle>$more trace.out

 

TKPROF: Release 11.2.0.4.0 - Development on Tue Apr 17 10:37:19 2018

 

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

 

Trace file: /u01/app/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_ora_29019.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: fp42r7m3kgabz Plan Hash: 1131838604

 

select count(object_id)

from

 roidba.tt

 

 

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.59       0.63          0      12279          0           1

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

total        4      0.59       0.63          0      12279          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  SORT AGGREGATE (cr=12279 pr=0 pw=0 time=631302 us)

   5524288    5524288    5524288   INDEX FAST FULL SCAN IDX_OBJECT_ID (cr=12279 pr=0 pw=0 time=2086333 us cost=3335 size=

13810720 card=2762144)(object id 87785)

 

 

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.62          7.62

 

 

 

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

 

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.59       0.63          0      12279          0           1

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

total        4      0.59       0.63          0      12279          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                       3        0.00          0.00

  SQL*Net message from client                     3        7.62         13.91

 

 

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/orcl/orcl2/trace/orcl2_ora_29019.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.

      69  lines in trace file.

       0  elapsed seconds in trace file.