Execution Consistency

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 Sat Jun 12 2004 - 23:10:42 CDT