Execution Consistency

[Dan]

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

[Justin Cave]

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 <jc***@ddbcinc.com>
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC

do*****@amcad.com (Dan) wrote in message news:<bd**************************@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