-----------------------------------------------------------
-- Description: simple SQL*Plus script to read some useful 
-- performance and efficiency indicators for the SQL 
-- statements found in v$sql. The script takes one  
-- argument to specify the maximum number of statements to
-- report, which defaults to 5. The statements are sorted
-- by elapsed time descending. The argument is a single-
-- ampersand arg, so it won't get set for the whole 
-- session.
-- This version attempts to filter out recursive SQL by 
-- means of a 'NOT LIKE.'
--
-- Author: David Clement
-----------------------------------------------------------
accept txtmatch char -
    prompt 'String to match in the SQL statement: '
accept limit number default 5 -
    prompt 'Number of SQL statements to report: '
        
set head off
set lines 80
set verify off
        
declare
    loop_counter number := 1;

    -- Thanks, Mr Kyte
    procedure p (p_string in varchar2)
    is 
        l_string long default p_string;
    begin
        loop
            exit when l_string is null;
            dbms_output.put_line( substr( l_string, 1,
                248 ) );
            l_string := substr( l_string, 251 );
        end loop;
    end;
begin
    dbms_output.enable(1000000);
    for cc in
        (select to_char (sql_fulltext) as sql_fulltext,
            lpad (elapsed_time, 12) elapsed_time,
            lpad (cpu_time, 12) cpu_time,
            lpad (executions, 12) executions,
            lpad (sorts, 12) sorts,
            lpad (sharable_mem + persistent_mem 
              + runtime_mem, 12) memory,
            lpad (round (disk_reads / decode (executions,
              null, 1, 0, 1,
              executions), 2), 12) reads_per_run,
            lpad (decode (sign (buffer_gets - disk_reads),
              1, round ((buffer_gets - disk_reads) 
              / decode (buffer_gets,
              null, 1, 0, 1, buffer_gets) * 100, 2), 0),   
              12) buffer_hit_pct,
            lpad (round (rows_processed / decode 
              (executions, null, 1, 0, 1,
              executions), 2), 12) rows_procd_per_run,
            lpad (round (parse_calls / decode (executions,
              null, 1, 0, 1,
              executions), 2), 12) parse_calls_per_run,
            lpad (round (buffer_gets / decode (executions, 
              null, 1, 0, 1,
              executions), 2), 12) buffer_gets_per_run,
            lpad (rows_processed, 12) rows_processed
        from v$sql
        where rows_processed > 0 and buffer_gets > 0
        and lower (sql_text) not like '%"."%'
        order by elapsed_time desc
        )
        loop
          if loop_counter > &limit
          then
             exit;
          end if;
          dbms_output.put (chr (10));
          dbms_output.put_line ('Statement ' 
             || to_char (loop_counter)
             || '..........');
          p( cc.sql_fulltext );
          dbms_output.put_line ('..........');
          dbms_output.put_line
            ('Elapsed Time              '
            || cc.elapsed_time);
          dbms_output.put_line
            ('CPU Time                  '
            || cc.cpu_time);
          dbms_output.put_line 
            ('Memory Used               '
            || cc.memory);
          dbms_output.put_line
            ('Sorts                     '
            || cc.sorts);
          dbms_output.put_line
            ('Executions                '
            || cc.executions);
          dbms_output.put_line 
            ('Rows Processed            '
            || cc.rows_processed);
          dbms_output.put_line 
            ('Rows Procd per Execution  '
            || cc.rows_procd_per_run);
          dbms_output.put_line 
            ('Disk Reads per Execution  '
            || cc.reads_per_run);
          dbms_output.put_line 
            ('Buffer Gets per Execution '
            || cc.buffer_gets_per_run);
          dbms_output.put_line 
            ('Buffer Hit Percentage     '
            || cc.buffer_hit_pct);
          dbms_output.put_line 
            ('Parse Calls per Execution '
            || cc.parse_calls_per_run);
          loop_counter := loop_counter + 1;
         end loop;
        end;
/
 
set head on
set verify on