SET AUTOTRACE COMMAND


SET AUTOTRACE COMMAND:
======================
The SET AUTOTRACE command allows you to automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics in SQL*Plus. The report is generated after successful SQL DML (Data Manipulation Language – that is, SELECT, DELETE, UPDATE and INSERT) statements. It is useful for monitoring and tuning the performance of these statements.
SET AUTOTRACE command options:
==============================
SET AUTOTRACE OFF
SET AUTOTRACE ON EXPLAIN
SET AUTOTRACE ON STATISTICS
SET AUTOTRACE ON
SET AUTOTRACE TRACEONLY
SHOW PARAMETER COMMAND:
======================
The SHOW PARAMETER Command is used to display the name,type and the value of the desired initialization parameters.
For example:
SQL> SHOW PARAMETER SERIAL
NAME TYPE VALUE
———————————— ——- —————–
serial_reuse string DISABLE
serializable boolean FALSE
Influence of SET AUTOTRACE TRACEONLY on SHOW PARAMETER
======================================================
If you set use any of the SET AUTOTRACE ON options (such as STATISTICS or EXPLAIN)you will be able to view the output of the SHOW PARAMETER command. But
if you use the command SET AUTOTRACE TRACEONLY and then try to execute the SHOW PARAMETER command, the output of this will not be displayed.
For example:
SQL> SET AUTOTRACE ON
SQL> SHOW PARAMETER SERIAL
NAME TYPE VALUE
———————————— ——- —————–
serial_reuse string DISABLE
serializable boolean FALSE
SQL> SET AUTOTRACE TRACEONLY
SQL> SHOW PARAMETER SERIAL
Nothing will be displayed here !!
EXPLANATION
============
The TRACEONLY option of SET AUTOTRACE suppresses the printing of the user’s query output, if any. In the case of the SHOW PARAMETER command, Oracle internally queries the V$PARAMETER view and then displays the formatted output. Therefore, the TRACEONLY option is suppressing the output of the SHOW PARAMETER command.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: