The autotrace-generated output will
not be displayed until after the query has completed, whereas the explain plan output is generated
without running the command. To enable autotrace-generated output, a plan table must either be
created in the schema in which the autotrace utility will be used or created in the SYSTEM schema
with access granted to the schema that will use the autotrace utility. The script plustrce.sql, located
in the $ORACLE_HOME/sqlplus/admin directory, must also be run as SYS before you can set
autotrace on. Users must have the PLUSTRACE role enabled prior to executing set autotrace on.
For an installation or upgrade to Oracle Database 10g or later, this script is run automatically.
NOTE
To show the explain plan output without running the query, use the
set autotrace traceonly explain command.
If you use the parallel query options or query remote databases, an additional section of the
set autotrace on output will show the text of the queries executed by the parallel query server
processes or the query executed within the remote database.
To disable the autotrace feature, use the set autotrace off command.
The following listing shows how to turn on autotrace and generate an explain plan:
set autotrace traceonly explain
select *
from BOOKSHELF
where Title like 'M%';
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=2 Bytes=80)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'BOOKSHELF' (TABLE) (Cost
=3 Card=2 Bytes=80)
2 1 INDEX (RANGE SCAN) OF 'SYS_C004834' (INDEX (UNIQUE)) (Co
st=1 Card=2)
252 Oracle Database 11g DBA Handbook
To understand the explain plan, read the order of operations within the hierarchy from inside
out, until you come to a set of operations at the same level of indentation; then read from top to
bottom.
Pages:
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423