high parse to execute ratio

[hansah]

Hi,
Is there a general way to find out what causes a high parse to execute ratio ?

Regards,
Hans

[dknight]

I will take a shot at it.

If the SQL running on your system is not in the dictionary cache, it needs to be reparsed when it is executed.

Here are two possible ideas to combat this.

First, try to establish coding standards in your shop. If a SQL statement differs on the order of the columns retrieved or on the case of the statements (i.e. upper or lower), then the statements are different. Therefore, if you can code SQL for one result in just one way, you save dictionary cache resources.

The second way would be to increase the resources allocated to dictionary cache.

Good luck.

[denevge]

use bind variables

Gert

[shawish_sababa]

Hi

Yes, first look at the direction of the way your sql statments are coded. After that , look at you sga tunning.

in order to look at the sql code side of things look at:
select sql_text, parse_calls, executions from v$sqlarea; look at sql_text, if you see your statments like this:
select * from table where x=1
select * from table where x=2
select * from table where x=3

you have a problem with your codeing. first try to chnage your code to bind variebles, then change CURSOR_SHARING=FORCE. This makes oracle recognise that statements are the same, differing just by a value.

Aftre that i think you should look at your memory management, ei, sga. at this point i think you better try to read the oracle doc about the sga so you have a better understanding of what you need to look for.

also look at asktom.oracle.com

[hansah]

Hi,
Thank you all for helping.
As far as I can see. most of the SQL-statements are well programmed by the vendor of our app.
Our problem is probably temporary, due to conversion procedures.

About CURSOR_SHARING : the vendor told us specifically to leave the value of this parameter empty!

Hans

[pando]

well programmed you mean using bind variables?

[hansah]

Yes Pando,
that's what I meant