Re: Help: How should I live with 'literal' application

emdproduction_at_hotmail.com wrote:
> Dear Group,
>
> I have a Oracle 9206 database.
>
> Our application is a third party software. For a recently new added
> module, it uses a lot of hard coded 'literal' value in their SQL
> statement. (It sucks, I know). This new module puts a lot of stress on
> our database, slow the database down on very large scale. Asking the
> vendor to change their code is not an option. My only choice is to
> live the best out of it.
>
> While I am doing some research, this cursor_sharing seems to be able to
> solve our problem. But I also noticed some poster was complaining that
> after setting cursor_sharing = similar, a lot of their execution plan
> changed, and a lot of weird things happened.
>
> What I have is a mission critical database, I do not have the luxury of
> setting "cursor_sharing = similar" and testing.
>
> I would like your comment on this situation and any suggestion on the
> instance tuning to accomdate this memory hungry application will be
> highly appreciated.

A Google search on the keywords:
  cursor_sharing force asktom
http://asktom.oracle.com/pls/ask/f?p=4950:8:243122254267634687::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:5180609822543,

Tom Kyte and Jonathan Lewis describe the impact of the CURSOR_SHARING very well in their books.

Sybrand is correct that in 9i bind variable peeking (to help determine the number of expected rows) can only affect the plan of a SQL statement the first time that it is executed (during the hard parse). 8i does not offer bind variable peeking, while 10g offers bind variable peeking even after the first hard parse.

We have used CURSOR_SHARING=FORCE for roughly five years, mostly to control in-house developed applications that do not make proper use of bind variables. CURSOR_SHARING=FORCE worked very effectively for us in 8.1.7.3, but we ran into problems as we moved to 10.2.0.2.

I write a lot of complex SQL statements, often with nested inline views. One of the SQL statements performed aggregation by month, so it included TO_CHAR(TRANSACTION_DATE,'YYYYMM') as part of the selected items and in the GROUP BY clause. The SQL statement worked without a problem on 8.1.7.3, but would not execute on 10.2.0.2 with CURSOR_SHARING set to FORCE - the SQL statement had to be rewritten. Another SQL statement which executed in a matter of a couple seconds on 8.1.7.3 required roughly 10 minutes to execute on 10.2.0.2, unless a hint was included in the SQL statement to parse it with CURSOR_SHARING set to EXACT.

For nearly the last two weeks I have been fighting patch 9 that was applied to the base release 10.2.0.2. A couple days after the patch was installed, large numbers of sessions were becoming disconnected from Oracle at roughly the same time when attempting to submit simple, single table SELECT statements to the database. Dump files indicated the following problem:
"ORA-07445: exception encountered: core dump [ACCESS_VIOLATION] [opidsa+428] [PC:0x2952594] [ADDR:0x0] [UNABLE_TO_READ]." After trying unsuccessfully to change various parameters to affect the code path used by Oracle, the ORA-07445 errors were returning like clockwork after roughly 40 hours of production use - the database has remained in service (up) 24x7 since applying the patch, but roughly every 40 hours after fixing the problem, the problem returned. As best that I can tell at the moment, the ORA-07445 errors are caused by having CURSOR_SHARING set to anything except EXACT. Metalink's SR process still has not offered any suggestions for the cause of the ORA-07445, or the proposed work around.

My suggestion is to try CURSOR_SHARING=FORCE for a week. If it causes no problems, and seems to help, monitor the system closely.

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Sat Dec 09 2006 - 06:55:46 CST