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

From: <pjackson_at_hilliard.com>
Date: Tue, 13 Apr 1999 15:34:48 GMT
Message-ID: <7evo6o$43p$1@nnrp1.dejanews.com>

I found this statement on the TUSC web site It will show you the statments that are hitting you the hardest. Note that the statement will take a few seconds to run.

Hope it helps,
Paul

Select a.username, b.Executions, ((b.Disk_Reads + b.Buffer_Gets) /

Decode(b.Executions, 0, 1, b.Executions)) Avg_Buffers,	b.Disk_Reads, 
b.Buffer_Gets,	b.first_load_time  Load_Time,  b.SQL_Text From dba_users a,

V$SQLArea b Where a.user_id = b.parsing_user_id And((Disk_Reads + Buffer_Gets) / Decode(Executions, 0, 1, Executions)) > 10000 Or (Executions
> 20000 And ((Disk_Reads + Buffer_Gets) / Decode(Executions, 0, 1,
Executions)) > 500) Order By ((Disk_Reads + Buffer_Gets) / Decode(Executions, 0, 1, Executions)) Desc

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Tue Apr 13 1999 - 10:34:48 CDT