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

Sybrandb,

Thanks for your reply. I do not think more memory will help us. We have already have 4G on the box, SGA is setting at 800M. What we are having is mostly "latch free" wait, more memory on shared_pool_size will make "latch free" wait worse, that is my understanding.

Let me ask you this, will RAC (real application cluster) help us here? As most of the pressure is on the memory, not I/O, we can let this 'bad designed' module application go to one instance, and rest of them goes to another instance, how do you like the idea?

Thanks

sybrandb wrote:
> 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
Received on Sat Dec 09 2006 - 06:32:42 CST