Re: How do I get a list of most resource consuming SQL statements

Query the V$SQLAREA view.

For each SQL statement in the cache, it records: the SQL text,
the memory used by the cursor,
the number of times the statement executed, the number of disk reads,
the number of buffer gets,
a sort indicator (sometimes ORDER BY doesn't need to sort!) the number of rows processed.

You can also compute ratios:
rows per execution,
buffer gets per row,
etc.

While this does not record CPU usage, buffer gets are proportional to CPU time.

Disk reads is proportional to I/O time.

Disk reads is also related to buffer hit ratio -- if the statement is repeated many times, the disk reads per row will be quite low. This is good, right? But why re-execute the same statement many times?

High buffer gets per row indicates that the index used is not selective for the given where clause. You will find that this is the number one cause of bad performance. I have seen queries that used a non-selective index perform much worse than a full-table scan.

HTH Cor Kooij wrote:

> I'm looking for a way to find out which SQL-statements consumed the most
> resources in terms of CPU and/or memory, the last hours/day/week.
> A top 10 or whatever number of SQL-statements would enable me to focus
> on the worst cases first.
Received on Tue Apr 13 1999 - 10:08:59 CDT