Wednesday, January 15, 2014

How to use the sqlplus "autotrace" facility

SET AUTOT ON
Executes the SQL and returns the execution plan and statistics (shorthand for "set autotrace on")

SET AUTOT TRACE
Executes the SQL and returns the execution plan and statistics (shorthand for "set autotrace traceonly")

Note: Using ON or TRACEONLY with no explicit options defaults to EXPLAIN STATISTICS.

SET AUTOT TRACE EXP
Explains the SQL, omits statistics and does not execute the SQL(shorthand for "set autotrace traceonly explain")
The SQL statement is never execute for real, only explained.

SET AUTOT TRACE EXP STAT
Executes the SQL, displays the execution plan, displays statistics. Executes the SQL, but supresses the output (shorthand for "set autotrace traceonly explain statistics")

SET AUTOT OFF
Disables autotraceing (shorthand for "set autotrace off")

If you have trouble getting the autotrace feature to work, make sure you have created the PLUSTRACE role.

Source: Oracle 19c Documentation

No comments:

Post a Comment