 |
|
Oracle Tips by Burleson |
OCP Instructors Guide for
Oracle DBA Certification
Chapter 7 - Tuning and Performance
Explain Plan Output
The Oracle Database Performance Tuning (1Z0-033) certification test
will have multiple questions on Explain. Test candidates should
have a firm understanding of:
-
What explain tables are used for and how the
simplify SQL statement tuning
-
How to create an explain table
-
The basic columns contained in the explain
table
-
How to retrieve the contents of the explain
table
-
How to interpret the information retrieved from
the explain table
SQL*PLUS AUTOTRACE
The SQL*PLUS AUTOTRACE command can be used to
create a report of the execution path used by a particular SQL
statement. Statement execution statistics can also be generated if
desired. This is my favorite explain tool because it’s a quick and
easy way to display the access path the query is taking. The
following parameters are used as input to AUTOTRACE to generate
different report output:
-
SET AUTOTRACE OFF- No AUTOTRACE report is
generated. This is the default.
-
SET AUTOTRACE ON EXPLAIN - The AUTOTRACE report
shows only the optimizer execution path.
-
SET AUTOTRACE ON STATISTICS - The AUTOTRACE
report shows only the SQL statement execution statistics.
-
SET AUTOTRACE ON - The AUTOTRACE report
includes both the optimizer execution path and the SQL statement
execution statistics.
-
SET AUTOTRACE TRACEONLY - Like SET AUTOTRACE
ON, but suppresses the printing of the user's query output, if any.
The above text is
an excerpt from:
OCP Instructors Guide for Oracle DBA Certification
A Study Guide to Advanced Oracle Certified Professional Database
Administration Techniques
ISBN 0-9744355-3-8
by Christopher T. Foot
http://www.rampant-books.com/book_2003_2_OCP_print.htm
|
Download your Oracle scripts now:
www.oracle-script.com
The
definitive Oracle Script collection for every Oracle professional DBA
|
|
