OraFAQ Forum: Performance Tuning » Identifying the parsing of the query (merged by MC)

Home » RDBMS Server » Performance Tuning » Identifying the parsing of the query (merged by MC) (Oracle 11g)

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

Identifying the parsing of the query (merged by MC) [message #658789]

Tue, 27 December 2016 05:46

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Hi All,

How can I know the parsing type of the query. If the same query with different bind variables are executing more than onetime I want to check the weather it is going for HARD/SOFT Parsing

Pleae help me .

Report message to a moderator

How to identify the parsing of the query [message #658790 is a reply to message #658789]

Tue, 27 December 2016 05:50

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Hi All ,

I Want to know the parsing type of the query. If the same query with different bind variables is executing more than one time I want
to verify weather it is going for SOFT/HARD Parsing.

Please help me to understand.

Thanks
SaiPradyumn

Report message to a moderator

Re: Identifying the parsing of the query [message #658791 is a reply to message #658789]

Tue, 27 December 2016 05:54

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

Senior Member
Account Moderator

When launching a query you can have hard parse, soft parse, softer soft parse and no parse, this only depends on your application(s), how it/they call the query.

SQL> select name from v$statname where name like 'parse%';
NAME
------------------------------------------------------------
parse time cpu
parse time elapsed
parse count (total)
parse count (hard)
parse count (failures)
parse count (describe)

In short, soft parse = total - hard but as I said this does not cover all the cases.

Report message to a moderator

Re: How to identify the parsing of the query [message #658792 is a reply to message #658790]

Tue, 27 December 2016 05:58

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

Senior Member
Account Moderator

In addition, have a look at V$SQL.LOADED_VERSIONS to know how many versions have been created (and so parsed) for the same query.

Report message to a moderator

Re: How to identify the parsing of the query [message #658794 is a reply to message #658792]

Tue, 27 December 2016 06:45

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Thanks

Report message to a moderator

Re: How to identify the parsing of the query [message #658795 is a reply to message #658794]

Tue, 27 December 2016 07:20

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Hi Michel ,

Now we identified some of the queries are going for the hard parsing even though it is previously executed.
How can we make surethat it has go for the soft parse ,softer soft parse,No Parse

Are there any parameters which will impact these parsing execution ?

Report message to a moderator

Re: How to identify the parsing of the query [message #658797 is a reply to message #658795]

Tue, 27 December 2016 07:40

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

Senior Member
Account Moderator

Oracle parses only if the query is unknown, if the environment is different or if you ask it to parse.

So
1/ Do not ask Oracle to parse
2/ Always use the same exact variable data types for your bind variables
3/ Increase session_cached_cursors parameters (this does not reduce hard parses just the soft parses)
(4/ Set cursor_space_for_time to TRUE, only if you are sure to have enough server memory, take care or you will have memory errors)

Report message to a moderator

Re: How to identify the parsing of the query [message #658825 is a reply to message #658797]

Wed, 28 December 2016 00:53

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Hi Michel ,

In order to avoid the hard parsing which kind of cursor sharing is ideal to use.
I have gone through that FORCE,EXACT,SIMILAR options.But unable to take the decision.

Thanks
SaiPradyumn

Report message to a moderator

Re: How to identify the parsing of the query [message #658827 is a reply to message #658825]

Wed, 28 December 2016 01:21

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

Senior Member
Account Moderator

There is only ONE correct value: EXACT.
The other values are there ONLY to workaround bad application code you don't want to fix.

Note that other values than EXACT come with their bugs which can be as bad as wrong results or instance crashes.

Report message to a moderator

Re: How to identify the parsing of the query [message #658840 is a reply to message #658827]

Wed, 28 December 2016 05:41

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Hi Michel ,

Attached file contains SGA and cursor related parameters for my database, DB size is 1500GB.
As you suggested if I set the cursor_space_for_time value as TRUE will it impact any kind of memory errors for attached configuration ?
or
Do I need to increase the SGA target also,if so how much I need to increase?
Are there any other factors that I need to take care if I am increasing the SGA memory?

$X:\REVATHI\michel\db_parameters_info.PNG$

Report message to a moderator

Re: How to identify the parsing of the query [message #658841 is a reply to message #658840]

Wed, 28 December 2016 05:44

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Attachment was missing in the previous thread. Please find the attached file

Attachment: db_parameters_info.PNG
(Size: 26.02KB, Downloaded 1628 times)

Report message to a moderator

Re: How to identify the parsing of the query [message #658842 is a reply to message #658841]

Wed, 28 December 2016 07:41

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

Senior Member
Account Moderator

I gave you 3 (or 4) ways to fix the problem, they are in the order they have to be analyzed/used, why do you pick up the later which is in addition between parentheses which means should be used ONLY when all other ways failed.

First, analyzes the code to be sure they do not ask for parsing.
For instance, be sure the code is like:

prepare
bind
loop
  execute
end loop
close

and not

loop 
  prepare
  bind
  execute
  close
end loop

where "loop" may be procedure calls and not real loop in the code.
Each "prepare" asks for a parse.
Each "close" tells Oracle it can unload the statement from the SGA.

Note that database size is irrelevant.

[Updated on: Wed, 28 December 2016 07:47]

Report message to a moderator

Re: Identifying the parsing of the query (merged by MC) [message #658873 is a reply to message #658789]

Thu, 29 December 2016 05:32

saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad

Senior Member

Thanks for you response.

My Point here is we are less using procedures we have lot of queries which are forming from the application with the help of JDBC.
And all those queries are using bind variable concept.

But we can see parse hard count value so high so coming to a conclusion that most of the time they are going for hard parse that's why we are thinking to go for options 3 and 4.

Even though we are using bind variables why are those queries going for hard parse.

Report message to a moderator

Re: Identifying the parsing of the query (merged by MC) [message #658875 is a reply to message #658873]

Thu, 29 December 2016 06:36

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

Senior Member
Account Moderator

You think wrong, you have to first go to 1 and 2. You have no procedure in JDBC? You have no loop? If you always used the same call with the same variables in the same environment then you'd have only 1 version.
You have not so you have to first know why.

Quote:

Even though we are using bind variables why are those queries going for hard parse.

Different variable types, different environments, different users and usage of synonyms...

Report message to a moderator

Previous Topic:	Which index to choose local or global for attached scenario
Next Topic:	Union all tables with View and using the Index

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 09 00:55:27 CST 2025