470,604 Members | 2,265 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 470,604 developers. It's quick & easy.

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
Jul 19 '05 #1
1 2693
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

Jul 19 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

11 posts views Thread by Markus Breuer | last post: by
1 post views Thread by Sergio | last post: by
2 posts views Thread by bruce strom | last post: by
6 posts views Thread by | last post: by
19 posts views Thread by Catherine Jo Morgan | last post: by
23 posts views Thread by Chance Ginger | last post: by
1 post views Thread by Dan | last post: by
5 posts views Thread by =?ISO-8859-1?Q?Marcel_M=FCller?= | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.