Times spent on latches...fast responses gratefully appreciated.

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:00:12 CDT