Quick queries to find the bottlenecks on your system

It’s easy to find SQL queries or transactions that are slowing your system. While Oracle 10g provides its Top SQL feature for this purpose, you can find bottlenecks by using queries if you aren’t on 10g or prefer the precision and control you get from issuing SQL commands.

Get your answer from V$SQL

Simply query the view V$SQL to identify which SQL statements consume too many resources when they run. For instance, here’s a sample query for Oracle 9i that lists SQL code that runs over 60 seconds:

SELECT Elapsed_Time/1000000/60
TIME_MIN, Executions, Buffer_Gets,
Disk_Reads, Sql_Text
FROM V$Sql
WHERE Elapsed_Time &g; 60000000;

We’ve used queries like this countless times to find performance bottlenecks. It’s often the first thing we do when analyzing a database for performance problems. (Just remember that the units for the field Elapsed_Time are in microseconds.)

Besides looking for SQL with excessive runtime, you can also return SQL based on excessive usage of CPU time, logical reads, or disk I/O. Simply modify the SQL code and substitute one of the following conditions:

WHERE CPU_Time &g; 60000000
WHERE Buffer_Gets &g; 1000000
WHERE Disk_Reads &g; 1000000

Note:CPU_Time and Elapsed_Time are new fields for Oracle 9i. If you’re using a version earlier than 9i, you’ll only be able to use the Buffer_Gets and Disk_Reads criteria.

Find resources consumed per execution

Another variation is to list SQL that consumes many resources per each execution—not just in total. Use a clause like this:

WHERE Disk_Reads/(Executions + .1)
&g; 1000000

It’s necessary to add a small number to the denominator to avoid a divide by zero error. This is because the SQL statistics in V$SQL eventually age-out, setting the Executions field to zero.

We used this technique on one system we looked at recently to isolate a query that required billions of reads every time it ran. The query was wrecking the application’s performance, but the programmers didn’t know where to look. It only took a minute or so to point them in the right direction.