Toad provides an intuitive and efficient way to write, run, and test your SQL and PL/SQL code. Toad supports efficient code management for a single developer or a whole team of developers.
Toad provides an intuitive and efficient way to write, run, and test your SQL and PL/SQL code. Toad supports efficient code management for a single developer or a whole team of developers.
Toad offers several features to help you optimize queries or view the performance statistics for the server. Although Toad provides access to these statistics and/or Oracle utilities, this section describes only how to use the features within Toad, not how to interpret the results.
For an excellent guide on SQL tuning, we suggest Oracle SQL - High Performance Tuning by Guy Harrison available from Prentice Hall Press.
See the online help for more information about these features.
| Feature | Description |
|---|---|
| Optimize Current SQL |
Use Auto Optimize SQL to quickly optimize a single SQL statement. Toad searches for faster alternatives and allows you to compare them to the original statement and each other. |
| SQL Optimizer |
If you have a Toad Edition that includes the SQL Optimizer package, you can use it to help you optimize your code. |
| Explain Plan |
Explain Plan shows the path and order in which Oracle will process your statement. By processing Explain Plan on variations of a statement, you can see how the adjustments will affect the execution. |
| SQL Trace |
SQL Trace is a server-side trace utility that displays CPU, IO requirements, and resource usage for a statement. SQL Trace is a much more complete utility than Auto Trace; however, viewing the results can be difficult because the output file is created on the server. |
| Auto Trace |
Auto Trace is a mini version of SQL Trace that displays quick results directly on the client. In Toad, the results are displayed beneath the Editor window. |
Toad provides an intuitive and efficient way to write, run, and test your SQL and PL/SQL code. Toad supports efficient code management for a single developer or a whole team of developers.
You can use the DBMS Profiler or the hierarchical profiler, depending on their availability in your database version.
The PL/SQL hierarchical profiler organizes data by subprogram calls, and stores the results in database tables letting you create custom reports.
See Oracle's documentation for more information.
Information provided includes:
The Probe Profiler API profiles existing PL/SQL applications and identifies performance bottlenecks. The collected profiler (performance) data can be used for performance improvement efforts or for determining code coverage for PL/SQL applications. Application developers can use code coverage data to focus their incremental testing efforts.
The profiler API is implemented as a PL/SQL package, DBMS_PROFILER, that provides services for collecting and persistently storing PL/SQL profiler data.
Caution: Statistics may not be collected properly if you are running the profiler on an Oracle server on a Tru64 platform.
Improving application performance is an iterative process. Every iteration involves the following:
To support this process, the PL/SQL profiler supports the notion of a run. A run involves executing specified SQL commands through benchmark tests with profiler data collection enabled.
Collected Data
With the Probe Profiler API, you can generate profiling information for all named library units that are executed in a session. The profiler gathers information at the PL/SQL virtual machine level that includes the total number of times each line has been executed, the total amount of time that has been spent executing that line, and the minimum and maximum times that have been spent on a particular execution of that line.
The profiling information is stored in database tables. This enables the ad-hoc querying on the data: It lets you build customizable reports (summary reports, hottest lines, code coverage data, and so on) and analysis capabilities.