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.

I would reconsider this sentence
 Asking the
> vendor to change their code is not an option.

Whether you spend your money on more memory or more attorneys doesn't matter.
Basically the problem can not truly be resolved in any fashion, by any setting, and it is quite clear the vendor is liable (at least he would be in my country).
Both cursor_sharing = false and cursor_sharing = similar are not going to work, as bind variable peeking only applies to the first call of the statement.
You need to throw more memory at the server, or sue the vendor.

-- 
Sybrand Bakker
Senior Oracle DBA