使用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>

$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.

$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.