Home » RDBMS Server » Performance Tuning » How to find Literal SQL statement (Oracle 10g Hp-UX)
How to find Literal SQL statement [message #541265] Mon, 30 January 2012 04:11 Go to next message
pokhraj_d
Messages: 117
Registered: December 2007
Senior Member
Hi Team,
Is there is any view/query from where I can find how many sql using literals.



Thanks-
P
Re: How to find Literal SQL statement [message #541273 is a reply to message #541265] Mon, 30 January 2012 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
v$sql.

Regards
Michel
Re: How to find Literal SQL statement [message #541281 is a reply to message #541273] Mon, 30 January 2012 04:43 Go to previous messageGo to next message
pokhraj_d
Messages: 117
Registered: December 2007
Senior Member
Hi,
I am using the below query :-


SELECT force_matching_signature, COUNT(1)
FROM v$sql
WHERE force_matching_signature > 0
AND force_matching_signature <> exact_matching_signature
GROUP BY force_matching_signature
HAVING COUNT(1) > 10
ORDER BY 2


Am I pointing to right direction?
Please suggest.

Thanks -
P
Re: How to find Literal SQL statement [message #541288 is a reply to message #541281] Mon, 30 January 2012 04:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Am I pointing to right direction?


Not for a good post.

Regards
Michel
Re: How to find Literal SQL statement [message #541294 is a reply to message #541281] Mon, 30 January 2012 05:27 Go to previous message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Your approach is correct. I use the following script for that:

-- 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

[Updated on: Mon, 30 January 2012 05:32] by Moderator

Report message to a moderator

Previous Topic: Performance tuning in View
Next Topic: Materialized view to improve Search queries
Goto Forum:
  


Current Time: Thu Mar 28 06:09:31 CDT 2024