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

操作如下：

sql> conn / as sysdba

Connected.

sql>

sql> oradebug setmypid

Statement processed.

oradebug event 10046 trace name context forever,level 12;

select count(object_id) from roidba.tt;

COUNT(OBJECT_ID)

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

5524288

oradebug event 10046 trace name context off;

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

<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

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

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

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

1 user sql statements in session.

0 internal sql statements in session.

1 sql statements in session.

Trace file compatibility: 11.1.0.7

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.