Problem solve Get help with specific problems with your technologies, process and projects.

Resource-intensive SQL

This simple SQL script will help you find the most resource-intensive SQL in the database.

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;

This was last published in September 2005

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close