Resource-intensive SQL

Vasan Srinivasan

09.14.2005 SearchOracle.com


Here is a simple script to find the most resource-intensive SQL in the database. It has been of immense help to me several times. It has been used on 8.1.7.4 and 9.2.0.5. However, there may be a better way of doing this in 9i that I have yet to learn.

In the SQL below, I am ordering the results by the descending number of executions, but by changing the order to refer to the dre or bge columns, you can find the SQLs with the most disk reads or buffer gets respectively.

select a.executions,
a.disk_reads,
a.disk_reads/a.executions dre,
a.buffer_gets,
a.buffer_gets/a.executions bge,
b.username,
a.first_load_time,
a.sql_text
from v$sql a, all_users b
where a.executions > 0
and a.parsing_user_id = b.user_id
order by 1 desc;