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