Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Monday, 17 December 2012

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) 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.


Post a Comment