Chat now with support
Chat with Support

Toad for Oracle 12.12 - Getting Started Guide

Table of Contents Welcome to Toad Toad Resources Required Privileges Create and Manage Database Connections Editor Basics Schema Browser Basics Data Grid Basics Work with Data Work with Database Objects Work with Code
Write Statements and Scripts Work with the Query Builder Execute Statements and Scripts Save Statements (SQL Recall) Work with PL/SQL Objects Debug Analyze Code Optimize SQL
Customize your Toad Environment Use Other Quest Software Products with Toad

Optimize SQL

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.

Optimizer Mode

You can set the optimizer mode for the current session. This will affect all queries (including Toad's own) for the duration of the session or optimizer setting.

Note: Optimizer mode is not available in Oracle 10g databases. Therefore Toad disables this option when it is connected to a 10g database.


Profile PL/SQL

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.


About the Profilers

You can use the DBMS Profiler or the hierarchical profiler, depending on their availability in your database version.

Hierarchical Profiler

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:

  • Number of calls to the subprogram
  • Time spent in the subprogram
  • Time spent in the subprogram and descendent subprograms
  • Detailed parent-child information

DBMS Profiler

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:

  • Exercising the application with one or more benchmark tests, with profiler data collection enabled.
  • Analyzing the profiler data, and identifying performance problems.
  • Fixing the problems.

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.


Related Documents

The document was helpful.

Select Rating

I easily found the information I needed.

Select Rating