Thursday, September 29, 2005

Resource-intensive SQL

Vasan Srinivasan


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 and 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.executions dre,
a.buffer_gets/a.executions bge,
from v$sql a, all_users b
where a.executions > 0
and a.parsing_user_id = b.user_id
order by 1 desc;

No comments: