Sometimes the explain plan by
itself does not provide sufficient information
because although there is now an idea of the
procedural logic or algorithm used to process
the query, the plan itself does not provide any
realistic work execution context or background
by which to measure. How can one actually tell
if a full table scan is really better than an
indexed search if the work actual performed for
each is unknown? What is needed is an extended
“explain plan” capability – one where both the
estimated algorithm and its net resulting
workload are displayed. Fortunately, Oracle has
built that exact feature into SQL*Plus, and it
is one of the most useful ways to work with
and/or measure explain plan effectiveness. It is
called SQL*Plus AUTOTRACE.
SQL*Plus AUTOTRACE requires
some initial setup before it can be used, much
like explain plans, meaning that some scripts
need to be run. The user must be granted a
special role known as PLUSTRC in order to use
AUTOTRACE, or they will see the error message
shown here:
Figure 5.9:
Error Message without PLUSTRC
In
order to setup AUTOTRACE, the DBA must perform
the following two steps. First, they must run
the $ORACLE_HOME/sqlplus/admin/plustrce.sql
script shown below to create the PLUSTRACE role.
Then that PLUSTRACE role simply needs to be
granted as a default enabled role to any users
who want to utilize this facility.
set echo on
drop role plustrace;
create role plustrace;
grant select on v_$sesstat
to plustrace;
grant select on
v_$statname to plustrace;
grant select on v_$mystat
to plustrace;
grant plustrace to dba
with admin option;
set echo off
Once the proper setup steps
have been completed, there is now a much
improved explain plan output, one with both the
estimated algorithm and the actual work
performed to run it as shown in Figure 5.10. The
SQL*Plus user merely has to include a SET
AUTOTRACE ON command. Now information can be
received about the actual work performs like
logical reads, physical reads, sorts and such.
Figure 5.
10:
SQL*Plus Auto-Trace Output
Note: Although the SQL*Plus
AUTOTRACE facility is easy to use and generates
a lot of useful information, the DBA or
developer can gather much more detailed
information via the session level trace
facility. This is a quick and dirty approach.
When better information is needed, favor
session-level trace output.
 |
Fo
r more details on Oracle utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.
You can buy it direct from the publisher for 30% off directly from
Rampant TechPress.
|
