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 18.104.22.168 and 22.214.171.124. 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;