Home » RDBMS Server » Performance Tuning » how to find the SQL statement which have many hard parse
how to find the SQL statement which have many hard parse [message #556214] Thu, 31 May 2012 22:38 Go to next message
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
Member
Hi,all
how to find the SQL statement which have many hard parse?
Re: how to find the SQL statement which have many hard parse [message #556223 is a reply to message #556214] Fri, 01 June 2012 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
First idea:
sum(v$sql.parse_calls) group by sql_id

Regards
Michel
Re: how to find the SQL statement which have many hard parse [message #556293 is a reply to message #556223] Fri, 01 June 2012 08:43 Go to previous messageGo to next message
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
Member
hi,Michel .only this method for that ?

thanks advance.
Re: how to find the SQL statement which have many hard parse [message #556296 is a reply to message #556293] Fri, 01 June 2012 09:01 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>hi,Michel .only this method for that ?
Yes

post SQL & results that have you suspect that hard parse is a problem for you.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: how to find the SQL statement which have many hard parse [message #557537 is a reply to message #556223] Wed, 13 June 2012 10:20 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Hi Michel

parse_call in v$sql are SOFT parse calls. At each hard parse call a child cursor will be created. So the number of child cursors (i.e. the max. number of child cursor) is the number of hard parse calls. With the following sql one could find the top sql's for hard parse calls:

select * from (select sql_id,max(child_number) from v$sql group by sql_id order by 2 desc) where rownum <= 10;


But I cannot understand for what purpose.

Regards
Leonid
Re: how to find the SQL statement which have many hard parse [message #557544 is a reply to message #557537] Wed, 13 June 2012 11:40 Go to previous messageGo to next message
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
Member
parse_call in v$sql are SOFT parse calls.
>>pase_call = HARD parse calls + SOFT parse calls ,isn't it ?
Re: how to find the SQL statement which have many hard parse [message #557545 is a reply to message #557544] Wed, 13 June 2012 11:44 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>But I cannot understand for what purpose.
Re: how to find the SQL statement which have many hard parse [message #557549 is a reply to message #557544] Wed, 13 June 2012 13:40 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Generally yes, but in this special case no.

[Updated on: Wed, 13 June 2012 13:42]

Report message to a moderator

Re: how to find the SQL statement which have many hard parse [message #557645 is a reply to message #557545] Thu, 14 June 2012 08:52 Go to previous messageGo to next message
lonion
Messages: 97
Registered: August 2011
Location: shenzhen,China
Member
when the system has many hard parses ,it will cost more cpu resource . so ,finding out the hard parse SQL statement and using the bind variable.
Re: how to find the SQL statement which have many hard parse [message #557646 is a reply to message #557645] Thu, 14 June 2012 08:54 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
If SQL parsing consume 5% of CPU cycles, it might be more productive to investigate where other 95% is being consumed
Re: how to find the SQL statement which have many hard parse [message #557653 is a reply to message #557645] Thu, 14 June 2012 10:57 Go to previous messageGo to next message
Michel Cadot
Messages: 67946
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
lonion wrote on Thu, 14 June 2012 15:52
when the system has many hard parses ,it will cost more cpu resource . so ,finding out the hard parse SQL statement and using the bind variable.


Run Statspack/AWR, it is better than any SQL statement which is a Oracle version 7 behaviour.

Regards
Michel
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 Go to previous message
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

Previous Topic: query for optimisation
Next Topic: Passing record to a variable - Performance Issue
Goto Forum:
  


Current Time: Fri Sep 17 18:07:30 CDT 2021