REM
REM DBAToolZ NOTE:
REM	This script was obtained from DBAToolZ.com
REM	It's configured to work with SQL Directory (SQLDIR).
REM	SQLDIR is a utility that allows easy organization and
REM	execution of SQL*Plus scripts using user-friendly menu.
REM	Visit DBAToolZ.com for more details and free SQL scripts.
REM
REM 
REM File:
REM 	s_sga_high_sql2.sql
REM
REM <SQLDIR_GRP>SGA STATS</SQLDIR_GRP>
REM 
REM Author:
REM 	Vitaliy Mogilevskiy 
REM	VMOGILEV
REM	(vit100gain@earthlink.net)
REM 
REM Purpose:
REM	<SQLDIR_TXT>
REM	(uses PL/SQL block)
REM	Reports expensive SQL from V$SQLAREA, V$SQLTEXT view
REM	Joins to V$SQLTEXT using ADDRESS column
REM	Prompts	for number of DISK_READS and
REM	PARSING_USER
REM	</SQLDIR_TXT>
REM	
REM Usage:
REM	s_sga_high_sql2.sql
REM 
REM Example:
REM	s_sga_high_sql2.sql
REM
REM
REM History:
REM	08-01-1998	VMOGILEV	Created
REM
REM

set serveroutput ON size 1000000

DECLARE
   CURSOR sqlarea_cur IS
      SELECT
        address
      , username parsing_user
      , executions
      , loads
      , DISK_READS
      , BUFFER_GETS    
      , ROWS_PROCESSED 
      from v$sqlarea  a
      ,    dba_users  b
      where a.PARSING_USER_ID = b.user_id
      and   DISK_READS > &number_of_expensive_DISK_READS
      and   b.username = upper('&parsing_username')
      order by DISK_READS desc
      ,        ROWS_PROCESSED desc
      ,        BUFFER_GETS desc
      ,        username;
   
   CURSOR sqltext_cur(p_address IN RAW) IS
      SELECT sql_text
      FROM   v$sqltext
      WHERE  address = p_address
      ORDER BY piece;
BEGIN
   FOR sqlarea IN sqlarea_cur 
   LOOP
      dbms_output.put_line('==>>                                          <<==');         
      dbms_output.put_line('==================================================');         
      dbms_output.put_line('Parsing User ......... :' ||sqlarea.parsing_user   );
      dbms_output.put_line('Number Of Loads ...... :' ||sqlarea.executions     );         
      dbms_output.put_line('Number Of Executions . :' ||sqlarea.loads          );         
      dbms_output.put_line('DISK_READS ........... :' ||sqlarea.DISK_READS     );         
      dbms_output.put_line('BUFFER GETS .......... :' ||sqlarea.BUFFER_GETS    );         
      dbms_output.put_line('ROWS_PROCESSED ....... :' ||sqlarea.ROWS_PROCESSED );         
      dbms_output.put_line('==================================================');         
      
      FOR sqltext IN sqltext_cur(sqlarea.address) 
      LOOP
         dbms_output.put_line(sqltext.sql_text);
      END LOOP;
   END LOOP;
END;
/