Oracle Database

--
-- List the most resource hungry SQL statements.
--
 
SET PAUSE ON
SET PAUSE 'Press Return to Continue'
SET PAGESIZE 60
SET LINESIZE 300
 
COLUMN sql_text FORMAT A50
COLUMN reads_per_execution FORMAT 999,999,999
COLUMN buffer_gets FORMAT 999,999,999
COLUMN disk_reads FORMAT 999,999,999
COLUMN executions FORMAT 999,999,999
COLUMN sorts FORMAT 999,999,999
 
SELECT *
FROM   (SELECT Substr(a.sql_text,1,50) sql_text,
               Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution, 
               a.buffer_gets, 
               a.disk_reads, 
               a.executions, 
               a.sorts,
               a.address,
               a.sql_id
        FROM   v$sqlarea a
        ORDER BY 2 DESC)
WHERE  rownum <= &rows_to_by_displayed
/