OraFAQ Forum: Performance Tuning » Reducing Hard parses

Home » RDBMS Server » Performance Tuning » Reducing Hard parses (Oracle 10g, 10.2.0.3.0, Linux)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Reducing Hard parses [message #529744]

Wed, 02 November 2011 13:58

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.

Report message to a moderator

Re: Reducing Hard parses [message #529748 is a reply to message #529744]

Wed, 02 November 2011 14:07

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

Report message to a moderator

Re: Reducing Hard parses [message #529750 is a reply to message #529748]

Wed, 02 November 2011 14:18

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.

Report message to a moderator

Re: Reducing Hard parses [message #529752 is a reply to message #529744]

Wed, 02 November 2011 14:23

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Reducing Hard parses [message #529755 is a reply to message #529752]

Wed, 02 November 2011 14:27

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!

Report message to a moderator

Re: Reducing Hard parses [message #529759 is a reply to message #529755]

Wed, 02 November 2011 14:54

John Watson
Messages: 8960
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

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.

Report message to a moderator

Re: Reducing Hard parses [message #529874 is a reply to message #529871]

Thu, 03 November 2011 08:40

John Watson
Messages: 8960
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.

Report message to a moderator

Re: Reducing Hard parses [message #529877 is a reply to message #529871]

Thu, 03 November 2011 08:46

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;

Report message to a moderator

Re: Reducing Hard parses [message #529879 is a reply to message #529877]

Thu, 03 November 2011 08:48

LNossov
Messages: 318
Registered: July 2011
Location: Germany

Senior Member

I think like John, cursor_sharing=similar is the reason.

Report message to a moderator

Re: Reducing Hard parses [message #529905 is a reply to message #529879]

Thu, 03 November 2011 09:51

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

John Watson
Messages: 8960
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.

Report message to a moderator

Re: Reducing Hard parses [message #529912 is a reply to message #529906]

Thu, 03 November 2011 10:12

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.

Report message to a moderator

Re: Reducing Hard parses [message #529928 is a reply to message #529912]

Thu, 03 November 2011 11:15

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;

Report message to a moderator

Re: Reducing Hard parses [message #529946 is a reply to message #529928]

Thu, 03 November 2011 11:52

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

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

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

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!

Report message to a moderator

Re: Reducing Hard parses [message #529957 is a reply to message #529955]

Thu, 03 November 2011 12:21

cookiemonster
Messages: 13958
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.

Report message to a moderator

Re: Reducing Hard parses [message #529958 is a reply to message #529955]

Thu, 03 November 2011 12:22

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?

Report message to a moderator

Re: Reducing Hard parses [message #529959 is a reply to message #529958]

Thu, 03 November 2011 12:23

Rags123
Messages: 39
Registered: July 2011
Location: United Kingdom

Member

Thanks Cookiemonster, yes I will get that fixed. Thanks a lot.

Report message to a moderator

Re: Reducing Hard parses [message #529963 is a reply to message #529955]

Thu, 03 November 2011 12:36

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

Wink

).

Report message to a moderator

Re: Reducing Hard parses [message #529968 is a reply to message #529963]

Thu, 03 November 2011 13:19

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

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.

Report message to a moderator

Re: Reducing Hard parses [message #529971 is a reply to message #529970]

Thu, 03 November 2011 14:11

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Reducing Hard parses [message #529973 is a reply to message #529971]

Thu, 03 November 2011 14:25

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

Wink

.

Best regards
Leonid

Report message to a moderator

Re: Reducing Hard parses [message #529974 is a reply to message #529973]

Thu, 03 November 2011 14:34

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Reducing Hard parses [message #529975 is a reply to message #529974]

Thu, 03 November 2011 14:59

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

Report message to a moderator

Re: Reducing Hard parses [message #529976 is a reply to message #529975]

Thu, 03 November 2011 15:11

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Reducing Hard parses [message #529979 is a reply to message #529976]

Thu, 03 November 2011 16:18

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

cookiemonster
Messages: 13958
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.

Report message to a moderator

Re: Reducing Hard parses [message #530039 is a reply to message #530033]

Fri, 04 November 2011 05:00

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.

Report message to a moderator

Previous Topic:	Doubt about Tuning SQL
Next Topic:	SQL tuning

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Nov 24 12:30:06 CST 2024