rem highload.sql
rem
rem CALL from sqlplus:
rem  @highload
rem 
rem highload extracts the most time consuming statements from the library
rem cache (V$SQL).
rem By aggregating over the hash value for the execution plan (plan_hash_value)
rem statements without bind varibles 
rem 
rem PARAMETERS:
rem   NONE
rem
rem REQUIREMENTS:
rem  - Oracle version 9i or better - but only tested on 9i release 2
rem  - Select priviledge on V$SQL
rem
rem WRITTEN BY Martin Berg, Martin Berg Consult 
rem Log    Date           Comment
rem        31-JAN-2006    Initial creation
rem        05-FEB-2006    Handling of non-consistent read from V$'s
rem
rem CONTACT the author by email: martin AT berg.dk
rem 
rem COPYING, USAGE, CHANGING RULES
rem You may use this code as you need - in its original form, as part of
rem something else, or just changed to your needs.
rem It would be nice if you kept the credits for the origin along with the code
rem 
rem WARRANTS
rem This code is delivered as is and no guarantees are given for correct
rem results or unwanted sideeffects

prompt ************************************************************************
prompt HIGH LOAD SQL
prompt
prompt 10 most time consuming statements from the library cache
prompt Statements with identical execution plans are grouped
prompt
prompt When multiple statement with identical execution plan are found
prompt only the statement with the largest elapsed time is displayed.
prompt
prompt The most time consuming statement is listed at the top

set heading off
set pagesize 0
set linesize 80

select
  '************************************************************************',
       decode(count, 1, 'Single statement:',
                 to_char(count)|| ' statements with identical execution '||
                 'plan (plan_hash_value='||to_char(plan_hash_value)||'):'),
       '                                                                    ',
       (select sql_text
        from v$sql vs2
        where vs2.plan_hash_value = hs1.plan_hash_value
          and vs2.elapsed_time >= hs1.max_elapsed
          and rownum =1),
       '                                                                    ',
       decode(count, 1, 'Time, elapsed:          ',
                        'Total time, elapsed:    ')||
         to_char(elapsed_time/1000000, '999990.99')|| ' sec',
       decode(count, 1, 'Time, CPU:              ',
                        'Total time, CPU:        ')||
         to_char(cpu_time/1000000, '999990.99') || ' sec',
       decode(count, 1, 'Disk reads:            ',
                        'Total disk reads:      ')||
         to_char(disk_reads, '9999999999'),
       decode(count, 1, 'Buffer gets:         ',
                        'Total buffer gets:   ')||
         to_char(buffer_gets, '999999999999'),
       decode(count, 1, 'Executions:             ',
                        'Total executions:       ')||
         to_char(executions, '999999999'),
       decode(count, 1, 'Rows processed:      ',
                        'Total rows processed:')||
         to_char(rows_processed, '999999999999'),
       'Elapsed time/execution: ' ||
       decode(count, 1,
         to_char(elapsed_time/executions/1000000,'999990.99')||' sec/exec',
         to_char(elapsed_time/executions/1000000,'99990.999') ||' sec/exec'),
       '                                                                    '
from (select plan_hash_value, count(*) count,
             sum(disk_reads) disk_reads,
             sum(buffer_gets) buffer_gets,
             sum(elapsed_time) elapsed_time,
             sum(cpu_time) cpu_time,
             sum(executions) executions,
             sum(rows_processed) rows_processed,
             max(elapsed_time) max_elapsed
      from v$sql
      where command_type not in (35,42,47,49,26,85) /* only DML and DQL */
        and parsing_user_id != 0 /* no SYS */
        and plan_hash_value > 0
      group by plan_hash_value
      order by elapsed_time desc) hs1
where rownum <= 10
/