How to use SET AUTOTRACE

Let us see the different forms of using SET AUTOTRACE commands that are offered by
Oracle. First off, what is the syntax of using SET AUTOTRACE, I knew it starts with
SET AUTOTRACE - but what after that. so I did a SET AUTOTRACE and hit enter and I
was surprised to see that Oracle helps in finding the exact syntax of using SET AUTOTRACE.



Later I realized that any character or number followed by SET AUTOTRACE, gives the
above mentioned help menu.



Let us see how each and every option works when used.




Just SET AUTOTRACE ON, returns all the rows of the query followed by the execution
plan and statistics of the query run. Now, SET AUTOTRACE TRACE which is synonymous
to SET AUTOTRACE TRACEONLY does not return the rows of the query but returns the
count of rows (like it always does at the end of every SELECT query) along with the
execution plan and the statistics of the query.



SET AUTOTRACE TRACE works differently if it just takes explain - returns only the
explain plan of the query skipping the statistics.



Now I would like the statistics but no explain plan - run SET AUTOTRACE TRACE STATISTICS



The keywords EXP[LAIN] or STAT[ISTICS] do not work without either ON or TRACE[ONLY]
keyword, as shown below



Just a SET AUTOTRACE ON - returns the rows of the SELECT query, displays the explain
plan along with the statistics. Here are some more situations - playing around with
the keywords in one order or the other.

SET AUTOTRACE ON EXPLAIN - Returns the rows and explain plan, skipping the statistics



SET AUTOTRACE ON STAT - Returns the rows and statistics thereby skipping the execution plan.



However, just the TRACE[ONLY] keyword does not work with SET AUTOTRACE ON, neither
does the EXP[LAIN] or STAT[ISTICS] keywords.



Note : after every run of the query in different situations, the following statements were run to clear the shared pool and buffer cache.




This implies that SET AUTOTRACE ON is really of no use for us, DBAs, since it
returns the result set of the query before it shows up the explain plan and the
statistics of the query - If the result set is of say 10 million records then one
has to wait for all the records to come up before (s)he gets a chance to look at the
explain plan and statistics. It is suggested to use SET AUTOTRACE TRACE as it gives
us the information of number of rows returned, explain plan and statistics.