Explain Plan/AutoTrace/tkprof using Toad
Using Toad you can easily view the explain plan feature of
oracle. To run this feature you have to execute either TOADPREP.SQL
script or UTLXPLAN.SQL (If you choose to use oracle plan table) then you
have to run UTLXPLAN.SQL in each schema from the location
<ORACLE_HOME>/RDBMS/ADMIN and also you need to change the TOAD_PLAN_TABLE
name to “PLAN_TABLE” from the Toad option button under Oracle –>General
option. The below screen shot will help to change the explain plan table
for your schema.
Now simply write your query on Toad editor and press CLT+E
to check or display explain plan. You can change the optimizer mode just right
click on the statement and choosing the optimizer mode. More about the
optimizer mode, you can search the separate topics on this blog.
Toad stores previously generated explain plan for review and
comparison. You can access these by check marking on the “save previous explain
plan results” option of the above screen shot. Thus you can compare the
generated plan for the variation of same queries or different queries. You can
clear the previous generated explain plan results that are no longer needed or
obsolete.
Auto Trace:
Through this feature you can review the resource usage for
the particular query in SQL Editor. Unlike toad explain plan for current
statement, which can be generated without executing the statement, auto trace
requires statement must be executed in order to generated its result. You can
review the result of auto trace in below figure.
If the auto trace is not enabled when you click the auto
trace button in results panel, TOAD prompts you to enable it. Otherwise you can
enable or disable it with the right mouse click on the SQL editor and selecting
auto trace. It will remain enabled until you disable or the Toad session is
terminated.
SQL Trace (TKPROF)
SQL Trace (TKPROF) is a server-side Oracle trace utility
that captures CPU, I/O, and resource usage during statement execution. The
output file is created on your Oracle server in the directory USER_DUMP_DEST. You
can view this file from Database –> diagnose –>TKPROF Interface.
To enable the TKPROF interface, select View –> Toad Options
–>Executables option. Check the below screen shot for more details.
SGA Trace Optimization:
Instead of using Auto Trace and TKPROF, you can use SGA
Trace Optimization from Database –> Monitor to displays statistics
from multiple SQL statements currently present in Oracle's SGA.
You can set
several options from this screen in order to search for SQL statements. You can
pass a SQL statement into the SQL Editor from the SGA Trace window. Highlight
the desired statement then click on the “Load selected statement in a SQL Editor”
button on the SGA Trace toolbar.
