Home » RDBMS Server » Performance Tuning » Reducing Hard parses (Oracle 10g, 10.2.0.3.0, Linux)
Reducing Hard parses [message #529744] Wed, 02 November 2011 13:58 Go to next message
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
Member
Hi,
The hard parse that happens in our database is typically around 140 per second and it goes up to 200 per second during peak load period. THe cursor_sharing is set to SIMILAR.

Is there a way to identify the SQLs which contribute to the hard parsing? I do have an sql blacklist but I need some way to check if they contribute to hard parsing.

I did google a lot but could not quite find many useful pointers.

Is it correct if I say that multiple entries of the same sql_id in v$sql indicates that the particular sql is being hard parsed everytime?

Any help would be appreciated.
Re: Reducing Hard parses [message #529748 is a reply to message #529744] Wed, 02 November 2011 14:07 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311

http://www.lmgtfy.com/?q=oracle+hard+parse
Re: Reducing Hard parses [message #529750 is a reply to message #529748] Wed, 02 November 2011 14:18 Go to previous messageGo to next message
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
Member
Hi BlackSwan,
I did google it and could not find much. There is lot of info about what hard parse is and how to avoid it. But what I need now is a retrospective analysis on the database.
Re: Reducing Hard parses [message #529752 is a reply to message #529744] Wed, 02 November 2011 14:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Oracle parses ONLY when you ask it to do so.
So modify your application to not ask Oracle to parse and the first step is to always use bind variables.

Regards
Michel

Re: Reducing Hard parses [message #529755 is a reply to message #529752] Wed, 02 November 2011 14:27 Go to previous messageGo to next message
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
Member
The catch is the application is a third party vendor product. So I have no control on the code. However if I point out the specific issues, the product owner will fix it for us. Thats the reason, I will have to gather the list of SQLs which are being hard parsed extensively!
Re: Reducing Hard parses [message #529759 is a reply to message #529755] Wed, 02 November 2011 14:54 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Hi - I think this is not the first question you have raised about this. Did you ever set CURSOR_SHARING=FORCE as was suggested before?

update: and, of course, why do you think the hard parsing is a problem?

[Updated on: Wed, 02 November 2011 14:54]

Report message to a moderator

Re: Reducing Hard parses [message #529871 is a reply to message #529759] Thu, 03 November 2011 08:34 Go to previous messageGo to next message
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
Member
Hi John,
Yes it is just a continuation of my post sometime back. I could not try the CURSOR_SHARING=FORCE because I could not provide enough evidence and that is the reason I have raised this new topic. The idea right now is not to solve the problem (sorry if the subject was misleading) rather to gather enough evidence to say that certain SQLs are contributing to hard parse. I need to spot the SQLs that are being hard parsed everytime to convince the team to try the FORCE option.

There is a lot of hard parse that happen in our db. And I have a list of bad performing SQL and want to validate if these SQL contribute to the hard parse.
Re: Reducing Hard parses [message #529874 is a reply to message #529871] Thu, 03 November 2011 08:40 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I can only repeat myself: why do you think parsing is a problem? The evidence (if there is any) will be in your AWR reports: in the Time Model statistics you have the proportion of DB Time spent on hard parsing.
Re: Reducing Hard parses [message #529877 is a reply to message #529871] Thu, 03 November 2011 08:46 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
select * from (select sql_id,count(*) from v$sql where is_obsolete != 'Y' group by sql_id order by 2 desc) where rownum <= 10;
Re: Reducing Hard parses [message #529879 is a reply to message #529877] Thu, 03 November 2011 08:48 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
I think like John, cursor_sharing=similar is the reason.
Re: Reducing Hard parses [message #529905 is a reply to message #529879] Thu, 03 November 2011 09:51 Go to previous messageGo to next message
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
Member
Hi John,
I dont know if hard parsing is the problem. And I want to validate if that is the problem before we think of solving it.

From the AWR report, I am aware that there is heavy hard parse that happens in our db (around 150 per second)

As per time model statistics %db of hard parse is 5.67.

Lnossov,
I executed the query and just posting the first 2 rows.

ayyfcu5tq5zkg	201
8u1c0csm9ungb	189

[Updated on: Thu, 03 November 2011 09:53]

Report message to a moderator

Re: Reducing Hard parses [message #529906 is a reply to message #529905] Thu, 03 November 2011 09:57 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Do you think 5.67% is worth attempting to tune? Is it really the worst problem? Probably not. Forget about it until you have fixed more important issues.
Re: Reducing Hard parses [message #529912 is a reply to message #529906] Thu, 03 November 2011 10:12 Go to previous messageGo to next message
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
Member
Yes John, thanks, I felt the same when I saw the figure as 5.67.

That was very useful. Thanks all.
Re: Reducing Hard parses [message #529928 is a reply to message #529912] Thu, 03 November 2011 11:15 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
You have a potential issue with 150 hard parse calls per second. If the system load increases, you can run into serious problem.

You sent only 2 sql's from the top 10. Didn't you want to clarify the reason of hard parse calls? Look into v$sql_shared_cursors at least for these 2 sql's:

select sql_text from v$sql where sql_id='<sql_id>';

select * from v$sql_shared_cursor where sql_id='<sql_id>' order by child_number;
Re: Reducing Hard parses [message #529946 is a reply to message #529928] Thu, 03 November 2011 11:52 Go to previous messageGo to next message
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
Member
Hi LNossov,
Yes of course I would want to improve things if possible.

I checked the V$sql_Shared_Cursor for the top 10 SQLs. For majority them the reason is INCOMPLETE_CURSOR. I googled about it and got to know it means "Cursor is incomplete: typecheck heap came from call memory" but couldnt quite catch that!

[Updated on: Thu, 03 November 2011 12:01]

Report message to a moderator

Re: Reducing Hard parses [message #529952 is a reply to message #529946] Thu, 03 November 2011 12:14 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
If all fields of v$sql_shared_cursor are 'N' in 10g, it is a sign, that cursor_sharing=similar is a reason for opening the new cursors. As we already assumed Smile . You can get different execution plans, because of different values of bind variables (literals).

[Updated on: Thu, 03 November 2011 12:15]

Report message to a moderator

Re: Reducing Hard parses [message #529955 is a reply to message #529952] Thu, 03 November 2011 12:20 Go to previous messageGo to next message
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
Member
Hmm.. interesting, I am little concerned about making the cursor_sharing as FORCE because recently we ran into multiple bind variable peeking issues. In those conditions the SIMILAR option is what saved us from a disaster!
Re: Reducing Hard parses [message #529957 is a reply to message #529955] Thu, 03 November 2011 12:21 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
In that case you really need to get the vendor to fix their code so that it uses bind variables.
Re: Reducing Hard parses [message #529958 is a reply to message #529955] Thu, 03 November 2011 12:22 Go to previous messageGo to next message
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
Member
Will it help if I change the application in any way?

And what about the INCOMPLETE_CURSOR?
Re: Reducing Hard parses [message #529959 is a reply to message #529958] Thu, 03 November 2011 12:23 Go to previous messageGo to next message
Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom
Member
Thanks Cookiemonster, yes I will get that fixed. Thanks a lot.

Re: Reducing Hard parses [message #529963 is a reply to message #529955] Thu, 03 November 2011 12:36 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Regarding cursor_sharing=similar. Try this testcase in 10g please:

set echo on

col sss noprint new_value sss
col sqlid new_value sqlid

select sid||'_'||serial# sss from v$session where sid in (select distinct sid from v$mystat);

create table t1(c1 number, c2 number);

alter session set cursor_sharing=similar;

select /* &sss */ count(c1) from t1 where c1  = 1;
select /* &sss */ count(c1) from t1 where c1  = 2;
select /* &sss */ count(c1) from t1 where c1  = 3;
select /* &sss */ count(c1) from t1 where c1  = 4;
select /* &sss */ count(c1) from t1 where c1  = 5;
select /* &sss */ count(c1) from t1 where c1  = 6;
select /* &sss */ count(c1) from t1 where c1  = 7;
select /* &sss */ count(c1) from t1 where c1  = 8;
select /* &sss */ count(c1) from t1 where c1  = 9;
select /* &sss */ count(c1) from t1 where c1  = 10;
select /* &sss */ count(c1) from t1 where c1  = 11;
select /* &sss */ count(c1) from t1 where c1  = 12;
select /* &sss */ count(c1) from t1 where c1  = 13;
select /* &sss */ count(c1) from t1 where c1  = 14;
select /* &sss */ count(c1) from t1 where c1  = 15;
select /* &sss */ count(c1) from t1 where c1  = 16;
select /* &sss */ count(c1) from t1 where c1  = 17;
select /* &sss */ count(c1) from t1 where c1  = 18;
select /* &sss */ count(c1) from t1 where c1  = 19;
select /* &sss */ count(c1) from t1 where c1  = 20;
select /* &sss */ count(c1) from t1 where c1  = 21;
select /* &sss */ count(c1) from t1 where c1  = 22;
select /* &sss */ count(c1) from t1 where c1  = 23;
select /* &sss */ count(c1) from t1 where c1  = 24;
select /* &sss */ count(c1) from t1 where c1  = 25;
select /* &sss */ count(c1) from t1 where c1  = 26;
select /* &sss */ count(c1) from t1 where c1  = 27;
select /* &sss */ count(c1) from t1 where c1  = 28;
select /* &sss */ count(c1) from t1 where c1  = 29;
select /* &sss */ count(c1) from t1 where c1  = 30;
select /* &sss */ count(c1) from t1 where c1  = 31;
select /* &sss */ count(c1) from t1 where c1  = 32;
select /* &sss */ count(c1) from t1 where c1  = 33;
select /* &sss */ count(c1) from t1 where c1  = 34;
select /* &sss */ count(c1) from t1 where c1  = 35;
select /* &sss */ count(c1) from t1 where c1  = 36;
select /* &sss */ count(c1) from t1 where c1  = 37;
select /* &sss */ count(c1) from t1 where c1  = 38;
select /* &sss */ count(c1) from t1 where c1  = 39;
select /* &sss */ count(c1) from t1 where c1  = 40;
select /* &sss */ count(c1) from t1 where c1  = 41;
select /* &sss */ count(c1) from t1 where c1  = 42;
select /* &sss */ count(c1) from t1 where c1  = 43;
select /* &sss */ count(c1) from t1 where c1  = 44;
select /* &sss */ count(c1) from t1 where c1  = 45;
select /* &sss */ count(c1) from t1 where c1  = 46;
select /* &sss */ count(c1) from t1 where c1  = 47;
select /* &sss */ count(c1) from t1 where c1  = 48;
select /* &sss */ count(c1) from t1 where c1  = 49;
select /* &sss */ count(c1) from t1 where c1  = 50;
select /* &sss */ count(c1) from t1 where c1  = 51;



select sql_id sqlid, sql_text, plan_hash_value, count(*) from v$sql where sql_text like '%/* &sss */%' group by sql_id, sql_text, plan_hash_value;

select * from v$sql_shared_cursor where sql_id = '&sqlid' order by child_number;


drop table t1;



With this testcase you'll get 51 child cursors with the same execution plan.

You can think once more about parameter setting cursor_sharing=force.

I would't say, cursor_sharing=similar is generally bad. It depends on your application (s. my testcase Wink).
Re: Reducing Hard parses [message #529968 is a reply to message #529963] Thu, 03 November 2011 13:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
You can think once more about parameter setting cursor_sharing=force.

Just search search for "cursor_sharing force" on Metalink bug database and you will more
than one hundred bugs with database crash, wrong result and so forth.

Never use it. It is better to have bad performances than to have wrong results.

This remember me one of my math teacher which once told us: "I calculate fast but I calculate wrong".

Regards
Michel

[Updated on: Thu, 03 November 2011 13:21]

Report message to a moderator

Re: Reducing Hard parses [message #529970 is a reply to message #529968] Thu, 03 November 2011 13:46 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
I just tried this and got 80 bugs totally and 40 for 2011 - 2008. I searched for "wrong results" "cursor_sharing" "force" and didn't investigate, if cursor_sharing=force is really the reason for wrong results. So the number of bugs should be even less.

I think, it is generally a very bad criteria. If you search for "wrong results" in MetaLink, you can decide not to use Oracle at all.

Re: Reducing Hard parses [message #529971 is a reply to message #529970] Thu, 03 November 2011 14:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So it seems they remove some of them.
It is just a fact that cursor_sharing force is the root of many bugs.
Just remember the optimizer was not written for this but for cursor_sharing exact.
The other values are just patches in the original code.

Regards
Michel
Re: Reducing Hard parses [message #529973 is a reply to message #529971] Thu, 03 November 2011 14:25 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Michel, I have a lot of customers, that use cursor_sharing=force or similar without any bad impact. As I wrote, it depends on application and should be tested carefully before going into production.

Don't be so serious, take it easy Wink.

Best regards
Leonid
Re: Reducing Hard parses [message #529974 is a reply to message #529973] Thu, 03 November 2011 14:34 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
without any bad impact.

Wrong results may not be seen this is the nasty side of this kind of bugs.

Quote:
Don't be so serious, take it easy

I always thought I have a duty to advice people with my experience
but I have no responsability they do not follow what I say.
So I can't take it in other way than easy.
By the way, it is a professional forum so being serious on technical points should be the rule.

Regards
Michel


Re: Reducing Hard parses [message #529975 is a reply to message #529974] Thu, 03 November 2011 14:59 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Quote:
I always thought I have a duty to advice people with my experience


Do you think, I don't do the same???

I think, the position

Quote:
it depends on application and should be tested carefully before going into production


is much more adequate than

Quote:

Never use it


Regards
Leonid
Re: Reducing Hard parses [message #529976 is a reply to message #529975] Thu, 03 November 2011 15:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
My experience learn me that for many people (most of them?)
"it depends on application and should be tested carefully before going into production"
means "do it"
and
"Never use it"
leads to "if it exists then it can be used sometimes".

You can write in bold red blink the danger, once you write "it can be used but before..." many read "use it".
Just read the many warnings I wrote in SQL & PL/SQL forum about WHEN OTHERS or triggers or other stuff
and how many times you still see it even when you clearly explain the danger, just because
someone posted a "solution" that "works".

I say if you need cursor_sharing=force then modify the application, the wrong point is there.
I know you will say it is not possible to change some applications, nevertheless I will keep my answer.
I think it is better to frustate some than to lead to dangerous ways many.

Regards
Michel
Re: Reducing Hard parses [message #529979 is a reply to message #529976] Thu, 03 November 2011 16:18 Go to previous messageGo to next message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
It sounds better. You don't want to cancel completely the Oracle feature cursor_sharing anymore.

Let us return to our concrete problem. Rgs123 is using cursor_sharing=similar. From your experiences you should know, that the most of issues with cursor_sharing (practically all of wrong results issues) occur if cursor_sharing <> exact regardless of similar or force value. So, if I suggest to use force instead of similar, I can only improve the situation.

Any objection?

Regards
Leonid


[Updated on: Thu, 03 November 2011 16:42]

Report message to a moderator

Re: Reducing Hard parses [message #530033 is a reply to message #529979] Fri, 04 November 2011 04:36 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
Just to throw a spanner in the works: Oracle think force is better than similar and are discontinuing similar from oracle 12 and advise you stop using it in 11.
Have a look at metalink note 1169017.1
The OP is on 10 so might not matter to them.
Re: Reducing Hard parses [message #530039 is a reply to message #530033] Fri, 04 November 2011 05:00 Go to previous message
LNossov
Messages: 318
Registered: July 2011
Location: Germany
Senior Member
Yes, but it is mainly because of advanced cursor sharing, that doesn't work with similar.
Previous Topic: Doubt about Tuning SQL
Next Topic: SQL tuning
Goto Forum:
  


Current Time: Thu Mar 28 09:18:16 CDT 2024