|
|
|
|
|
|
|
|
|
|
|
Re: how to find the SQL statement which have many hard parse [message #557669 is a reply to message #557645] |
Thu, 14 June 2012 12:41 |
|
LNossov
Messages: 318 Registered: July 2011 Location: Germany
|
Senior Member |
|
|
You should have said from the very beginning, what exactly you needed. If you want to find top sql's with literals, then you can do that with the following script:
-- 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
|
|
|