Re: Execution Consistency

With cursor_sharing = similiar, you are telling Oracle to force the use of bind variables in certain situations. In this case, Oracle is using bind variables in a way that is detrimental to performance.

If you avoid gathering histograms on the lname of the entity column, that will likely cause Oracle not to choose a different plan for literals and bind variables. Whether this will lead Oracle to choose the "good" plan or the "bad" plan universally, though, can't be predicted with the information provided.

My preference would be to issue an ALTER SESSION command before executing the particular statement(s) you don't want to be forced to use bind variables.

ALTER SESSION SET cursor_sharing=EXACT

will cause your session not to force the use of bind variables. After the statement executes, you can switch back to

ALTER SESSION SET cursor_sharing=SIMILIAR

Justin Cave <jcave_at_ddbcinc.com>
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

dobrien_at_amcad.com (Dan) wrote in message news:<bdc62384.0406122010.1f9db078_at_posting.google.com>...
> I am new to tuning and I am having some trouble with my Oracle
> 9.2.0.1. I have a database that I am trying to gain consistent
> performance out of but I seem to have changing execution plans. Due
> to a number of factors outside my control, I have to handle an
> application that does not send bind variables, I handle them as they
> come in. To do this, I have set the init.ora file to have an entry of
> 'cursor_sharing=similiar'. I know this is a temporary fix, but I have
> to deal with it for now.
>
> When I test a general search query on a stand alone application, I
> get good returns (<10s, good enough anyway). When the application
> runs, it takes forever. I copied the query that was being run from
> the Oracle session manager and tried playing with it. It seems that
> the execution plan of this query changes if there are bind variables
> in it or actual data. I narrowed this down to a single field. It
> does not matter what I put in the other parts of the where clause. As
> soon as I change this particular where clause
> AND (Entity.lname like 'MART%')
>
> to
>
> AND (Entity.lname like :"SYS_B_22")
>
> the execution plan changes and my returns lose about a minute of
> response time, not acceptable. Does anyone have any experience with
> something like this? Any help is greatly appreciated.
>
> -Dan
Received on Sun Jun 20 2004 - 01:19:41 CEST