TOAD Pocket Reference for OracleBy Jim McDaniel, Patrick McGrath
0-596-00337-4, Order Number 3374
128 pages, $12.95 US $20.95 CA
TOAD provides a number of tools for identifying and tuning poorly running SQL--for example, statements that are CPU-intensive or I/O-intensive. These tools include:
- Explain Plans that show how Oracle executes a statement
- Several commands for reviewing statistics after statement execution, including Explain Current SQL, SGA/Trace Optimization, and Kill/Trace Session
- TOAD's optional SQL Tuning Lab module and the Oracle Probe API.
EXPLAIN PLAN is an Oracle command that analyzes a SQL statement for performance without actually executing the statement. The purpose of this command is to determine before execution the plan that Oracle will follow when optimizing and executing a SQL statement. The results of the EXPLAIN PLAN display the order in which Oracle will search/join the tables, the types of access that will be employed (indexed search or full table scan), and the names of indexes that will be used. The display is read from the deepest indentation out vertically.
Explain Plan in the SQL Editor
In the SQL Editor, you can generate an EXPLAIN PLAN command before executing a valid SQL statement by clicking on the "Run Explain Plan for current statement" button (the ambulance ) on the SQL Edit toolbar. You can also press CTRL-E to generate the plan.
The generated plan displays on the Explain Plan tab available from the Results panel. If you wish, you can display this information from the right mouse menu. The Run Explain Plan for current statement command is also available in the SQL Modeler.
TOAD expects to find an Explain Plan table with columns matching the most recent specification from Oracle. If you get Invalid Column errors when executing the EXPLAIN PLAN command, check in the TOADPREP.SQL script for the columns you may be missing. The TOAD Explain Plan table is backward-compatible with earlier Oracle releases.
You can set the name of the Explain Plan table in the View Options Oracle window. Adjust the information in the dialog and enter your Explain Plan table name and the username for the Explain Plan.
Previous Explain Plan results
TOAD stores previously generated Explain Plans for review and comparison. You can access these from View Explain Plan or by clicking on the "Show Previous Explain Plan Results" button (the ambulance button with the blue line above it ) on the TOAD Standard toolbar.
The resulting multi-paneled Explain Plan window displays previously generated plans. You can compare the generated plans for variations of the same queries, different queries, and so on. This window does not have any editing capabilities, so if you decide to make changes to the displayed SQL statement, you must do so in the SQL Editor. Once you've made these changes, you can generate a new Explain Plan while in the SQL Editor and then return to the Explain Plan window and click the Refresh button to update the display.
You may occasionally need to perform some maintenance to clear Explain Plan results that are no longer needed. Select the obsolete Explain Plan results and click Clear.
Before you can use the Previous Explain Plan Results feature, you must go to View Options Oracle and turn on the Save previous Explain Plan results (requires TOAD tables) option. You will need to create the necessary tables and their related objects by executing either the TOADPREP.SQL or the NOTOAD.SQL script. Both scripts are located in the TOAD\temps folder.
SQL Tuning Commands
The following sections describe a variety of TOAD commands used to review statistics and tune SQL statement execution.
TOAD's AutoTrace feature lets you review resource usage for a particular query in the SQL Editor. AutoTrace is a mini-version of Oracle's SQL Trace (described in the next section). Unlike TOAD's Run Explain Plan for current statement command, which can be generated without executing a statement, AutoTrace requires that the statement be executed in order to generate its results.
AutoTrace displays information such as Recursive Calls, Physical Reads, Consistent Gets, Index Scans, etc. The results are displayed in the AutoTrace tab of the Results panel.
If AutoTrace is not enabled when you click the AutoTrace button in the Results panel, TOAD prompts you to enable it. You can enable or disable AutoTrace from SQL Editor Right Mouse AutoTrace. Once enabled, AutoTrace remains enabled until you disable or until the TOAD session is terminated.
NOTE: Because AutoTrace forces a read of all data resulting from your query, you may notice that it adds overhead to Oracle.
SQL Trace (TKPROF)
SQL Trace (TKPROF) is a server-side Oracle trace utility that captures CPU, I/O, and resource usage during statement execution. SQL Trace is a much more complete utility than AutoTrace. The output file is created on your Oracle server in the directory specified in the USER_DUMP_DEST parameter of your INIT.ORA file (containing Oracle initialization parameters). You can view this file from Tools TKPROF Interface.
The TKPROF wizard-driven interface prompts you for the trace file(s) you want to view, lets you choose sort options and data elements to view, and then displays the results in a separate window.
To enable the TKPROF interface, select View Options Executables. If the path for your TKPROF executable is not identified, click on the flashlight icon to have TOAD locate the path for you.
SGA Trace Optimization
You can use the Tools SGA Trace Optimization command to view information about SQL statements that have been executed and the resources they used. Whereas Auto Trace and TKPROF information is specific to a single statement, SGA Trace Optimization displays statistics from multiple SQL statements currently present in Oracle's SGA (System Global Area).
Go to Tools SGA Trace Optimization to open the SGA Trace window. You can set several options from this screen in order to search for SQL statements. The default settings are for ALL Statements for ALL Users, but you can click on the corresponding drop-down box and choose another option to change these choices. You can limit the selection to a single statement type (e.g., SELECT statements, UPDATE statements, anonymous PL/SQL, etc.) or to a specific user. You can also enter a text string in the SQL Search Text box to limit the rows returned to statements containing that text string.
Click on the "Refresh the List of Statements" button ( ) to retrieve the most resource-intensive SQL from the SGA. This returns all of the queries that match your criteria. The screen is divided into two parts:
- The query results grid on the top half of the screen shows the query that was executed and the associated resources used (memory, disk reads, loads, etc.).
- The bottom half of the screen displays the full SQL statement, execution statistics from the Oracle shared pool, and the Explain Plan for the query.
When necessary, 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.
This toolbar also contains a button to "Flush the SGA." Your Oracle privileges dictate your logged-in user's ability to use this function.
NOTE: Tools SGA Trace Optimization requires access to a number of Oracle V$ objects. For a current listing of the Oracle access required to utilize this feature, go to Help Contents. From the Table of Contents, select TOAD Basics V$ Tables Required.
The DBA Kill/Trace Session option, like Tools SGA Trace Optimization, requires access to a number of Oracle V$ objects. The Kill/Trace window displays session information on the use of locks, blocking locks, and rollback segments. The Access tab lists objects by each user in the current session. You can also review the current statement and open cursors for each user. When necessary, you can selectively kill sessions using the Kill the selected session command from the Kill/Trace toolbar. Your ability to execute a kill session is governed by the logged-in user's Oracle privileges.
Selecting Start trace for this session from the Kill/Trace toolbar enables traces for selected user sessions. Selecting End trace for this session disables traces for these sessions.
Oracle Probe API
TOAD uses the Oracle Probe API to collect performance data on PL/SQL applications. To use this feature, you must first verify that you have the DBMS_PROFILER package (created by Oracle's PROFLOAD.SQL script). Then use the TOAD user (created by TOADPREP.SQL) to run TOADPROFILER.SQL.
Toggle Database PL/SQL Profiling on and then execute your stored program. The Profiler prompts you to name each run (execution). As each run completes, the Profiler Analysis window displays performance data for each line of code. Because this information is stored in database tables, you will be able to run ad hoc queries and customize your own reports.
TOAD provides an add-on module, the SQL Tuning Lab, that lets you perform additional SQL statement tuning. When necessary, you can pass a SQL statement from the SQL Editor or the Procedure Editor directly into the SQL Tuning Lab. See the description in the "SQL Tuning Lab" section.
Back to: TOAD Pocket Reference for Oracle
© 2001, O'Reilly & Associates, Inc.