Using Third-Party Tools to
Locate Offensive SQL
Oracle Tips by Burleson Consulting
There are several third-party tools that can
be used to quickly identify and tune suspect SQL statements. In a
busy environment where the Remote DBA must tune many SQL statements each
day, the GUI interfaces of many third-party tools greatly speed up
the tuning process. Another nice feature of the tools is their
ability to allow push-button hints. This eliminates the problem of
syntax errors within hints that sometimes happen with the manual
Caution: Remember that adding an index to
tune one query may cause the execution plan for other queries to
change. It is always a good idea to make your execution plans
permanent (using optimizer plan stability or hints) to ensure that a
new index does not inadvertently un-tune existing queries.
The following examples are from the Q
Diagnostic Center software by Precise Software. This product, which
does a very good job at quickly identifying suspect SQL, was written
by John Beresniewicz, one of the best Oracle internals gurus in the
The following example shows the Q Diagnostic
Center displaying all of the SQL from v$sqlarea. The GUI tools allow
the Remote DBA to resort the list of SQL statements by any parameter. In
the example that follows, the Remote DBA sorts the list of SQL by
rows_processed (see Figure 7-2).
Figure 2: Sorting SQL
With a click of the Sorts button, the entire
list can be redisplayed in descending order of the number of sorts
(see Figure 7-3).
Figure 3: Resorting
the SQL list by number of sorts
By clicking the sorting bar at the top of the
list of SQL statements, the Remote DBA can quickly identify those SQL
statements that are frequently executed and place a processing
burden on the database.
Let’s move on to look at how to extract and
explain suspect SQL statements.
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.