Oracle

REM:**********************************************************************************************
REM: Script : Max 50 I/O Informations
REM: Author: Kumar Menon
REM: Date Submitted: 16-July-2009
REM:FileName: 50maxIO.sql
REM:
REM: NOTE: PLEASE TEST THIS SCRIPT BEFORE USE.
REM: Author will not be responsible for any damage that may be cause by this script.
****************************************************************************************************

spool D:\top50&no.lst
set termout off

declare
    cursor c1 is
    select     executions,
               disk_reads,
        buffer_gets,
        first_load_time,
               sql_text
    from    v$sqlarea
    order     by disk_reads / decode(executions,0,1,executions) desc;

    statement_ctr    number;
    i        number;

begin
    dbms_output.enable(50000);
    statement_ctr := 0;

    for inrec in c1 loop

       statement_ctr := statement_ctr + 1;
       if statement_ctr >= 51 then
          exit;
       end if;

       dbms_output.put_line('Statement Number: ' || to_char(statement_ctr));
       dbms_output.put_line('--------------------------------------------');
       dbms_output.put_line('Executions     : ' ||
                to_char(inrec.executions));
       dbms_output.put_line('Disk Reads     : ' ||
                to_char(inrec.disk_reads));
       dbms_output.put_line('Buffer Gets    : ' ||
                to_char(inrec.buffer_gets));
       dbms_output.put_line('First Load Time: ' ||
                inrec.first_load_time);
       dbms_output.put_line('SQL Statement-------->');


       i := 1;
           while i <= ceil(length(inrec.sql_text) / 72) loop

          dbms_output.put_line('.....' ||
                    substr(inrec.sql_text,((i-1)*72)+1,72));
              i := i + 1;


           end loop;


       dbms_output.put_line('--------------------------------------------');


    end loop;
end;
/

spool off
set termout on