REM ---------------------------------------------------------------------------
REM PURPOSE:
REM   This Script show sql statements with high disk reads.
REM
REM ---------------------------------------------------------------------------
REM DISCLAIMER:
REM   This script is provided for educational purposes only. It is NOT
REM   supported by Oracle Support Services.
REM   The script has been tested and appears to work as intended in versions
REM   7.3, 8.0, 8.1.6 , 9.0.1 and 9.2.0 of the database.
REM   You should always run new scripts on a test instance initially.
REM ---------------------------------------------------------------------------
REM
set echo off;
set feedback off
DEFINE blocks_read = 1000 (NUMBER)
COLUMN parsing_user_id  FORMAT 9999999     HEADING 'User Id'
COLUMN executions       FORMAT 9999        HEADING 'Exec'
COLUMN sorts            FORMAT 99999       HEADING 'Sorts'
COLUMN command_type     FORMAT 99999       HEADING 'CmdT'
COLUMN disk_reads       FORMAT 999,999,999 HEADING 'Block Reads'
COLUMN sql_text         FORMAT a40         HEADING 'Statement' WORD_WRAPPED
SET LINES 130

SELECT parsing_user_id, executions, sorts, command_type, disk_reads, sql_text
  FROM v$sqlarea
 WHERE disk_reads > &&blocks_read
 ORDER BY disk_reads;

set echo off;
SET LINES 80