you might want to check if cursor_sharing=force is something for you ...
"Oracle User" <spamspamspam_at_ntlworld.com> wrote in message
news:BsrL8.1585$NV6.77522_at_news8-gui.server.ntli.net...
> Scenario...
>
> We have a powerful server (8 CPU / 4GB RAM).
> We have an application who's code cannot be modified. (3rd party etc)
> The shared pool is 700MB, overutilised by 180%
> Most statements do not use bind variables. Most statements are different
and
> fill the pool etc.
>
> So, If I increase the size of the pool , at the expense of free available
> RAM, will this be less damaging than reducing the size of the shared pool
so
> that more cpu cycles are used in servicing the shared pool , i.e. will the
> time spent on the latch be longer in adding new statements to free memory
in
> a massive shared pool or will the time spent on the latch be less than if
I
> had to use a small shared pool and constantly age out rubbish SQL.
>
> This is a non MTS environment and cursor handling is default for 8.1.7.
> We have bags of spare RAM, and bags of spare CPU cycles available.
>
> I acknowledge that I am painting over rust, but I need a more pragmatic
> approach rather than rule of thumb.
> I am at loggerheads with several other DBA's, my approach of increasing
the
> shared pool to avoid shared pool overhead does not go down too well, the
> crux of my case stands on the fact that I think and cannot prove that
adding
> new parsed SQL to the SP is less expensive in CPU terms than ageing out an
> old statement and inserting the new statement. Whilst I know my approach
is
> an horrific waste of memory, we have spare memory in a wildly overspecced
> server and so this should be removed as a consideration.
>
> If only N*ku would write reusable SQL...If only I could determine exact
> times spent on the latch adding a new statement to a pool with free chunks
> available or times spent on a latch ageing out a statement etc.
>
> Thanks in advance...
>
>
>
>
Received on Wed Jun 05 2002 - 12:11:53 CDT
