-- E. Nossova, Product TuTool : www.tutool.de

set pagesize 0
set feedback on
set feedback off
set linesize 98
set verify off
set linesize 180

col nline print newline
col force_match_sig format 9999999999999999999999999
col pct format 99990.99

/* reports top SQL's with literals from the sqlarea,
input parameters:
min_first_load_time in 'dd.mm.yyyy hh24:mi:ss'
                        format,
                    default: sysdate - 1 hour,
max_first_load_time in 'dd.mm.yyyy hh24:mi:ss'
                        format,
                    default: sysdate,
top_n - the number of the top sql's 
        (default: 10) */

define min_first_load_time='&min_first_load_time'
define max_first_load_time='&max_first_load_time'
define top_n='&top_n'

select 'Force Matching Signature='||t.force_match_sig||', Count='||max(t.cnt)||', PCT='||max(t.pct)||
'%, Min. Username='||min(s.username)||', Max. Username='||
max(s.username)||', Min. First Load Time='||max(min_first_load_time)||
', Max. First Load Time='||max(max_first_load_time), 
max(sql_text) nline from
(select u.username, a.force_matching_signature force_match_sig, a.sql_text 
 from sys.v_$sql a, sys.dba_users u 
 where 
 a.parsing_user_id = u.user_id and
 to_date(a.first_load_time,'yyyy-mm-dd/hh24:mi:ss') between 
 to_date(nvl('&min_first_load_time',
to_char(sysdate - 1/24,'dd.mm.yyyy hh24:mi:ss')), 
'dd.mm.yyyy hh24:mi:ss') and
 to_date(nvl('&max_first_load_time',to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')), 
'dd.mm.yyyy hh24:mi:ss') and
 a.force_matching_signature != 0 and
 a.exact_matching_signature != 0 and
 a.force_matching_signature != a.exact_matching_signature ) s,
(select * from 
      (select 
           force_matching_signature force_match_sig, 
           count(*) cnt,
           min(first_load_time) min_first_load_time,
           max(first_load_time) max_first_load_time,
	   round((ratio_to_report(count(*)) over ())*100, 2)  pct 
       from sys.v_$sql 
       where 
       to_date(first_load_time,'yyyy-mm-dd/hh24:mi:ss') between 
       to_date(nvl('&min_first_load_time',
to_char(sysdate - 1/24,'dd.mm.yyyy hh24:mi:ss')), 'dd.mm.yyyy hh24:mi:ss') and 
       to_date(nvl('&max_first_load_time',
to_char(sysdate,'dd.mm.yyyy hh24:mi:ss')), 'dd.mm.yyyy hh24:mi:ss') and
       force_matching_signature != 0 and
       exact_matching_signature != 0 and
       force_matching_signature != exact_matching_signature 
       group by force_matching_signature
       order by 2 desc) 
where
rownum <= nvl(abs('&top_n'),10)) t
where
s.force_match_sig = t.force_match_sig
group by t.force_match_sig
order by max(t.cnt) desc
/
 



undefine min_first_load_time
undefine max_first_load_time
undefine top_n

set linesize 80