Shulou(Shulou.com)06/01 Report--

Use the 10046 event to view the real execution plan

Do the following:

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

Execute 1 0.00 0.00 00 00

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

Execute 1 0.00 0.00 00 00

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

Execute 0 0.00 0.00 00 00

Fetch 0 0.00 0.00 00 00

--

Total 0 0.00 0.00 00 00

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.