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

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

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 by rows_processed

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.