Re: Shared pool and library cache latch contention

Amir-

You can also check the executions column in v$sqlarea for that statement. If it is being shared, executions will be greater than 1. Even if a statement does not use bind variables, it my be shared simply be force of being identical across executions. Then you have to ask yourself, why so many child versions? A bug perhaps? Or maybe they are distinct statements after all, in which case you probably need to force sharing.

If you need to force sharing - and it sounds like you do - use cursor_sharing=force. Test in a non-production instance first. In 8.1.7.4 I think it should work fine.

Of course, you could get on the tool vendor's case and demand they fix their SQL. Perhaps the best solution of all. :-)

Also, pinning packages may be helpful, but may not be enough. You also need to pin cursors. Unfortunately, this is not so simple. You need to identify cursors with executions > 1, get both the hash value and the address of each cursor and pass the columns concatenated as a parameter to the pin procedure. Then, you need a way to unpin them once the workload shifts and they are no longer in use. A bit tricky and I know of no ready scripts to do this correctly.

Also, session_cached_cursors to some value less than 100 (until 9i, in which case a higher value is not harmful). And, if you do this, make sure cursor_space_for_time = false. Not a good feature for a mixed workload, just eats up more shared pool for all the wrong reasons.

• ricky

Amir Hameed wrote:
>
>
> I then looked at the v$sqlarea and also took library cache dump at
> level 3 (alter session set events 'immediate trace name library_cache
> level 2';) and noticed a lot (thousands) of instances of the following
> statement in the library cache:
> begin PRECISE.precise_apps_log('B',1510,-1,-1,-1); end;
>
> I am thinking that since the above statement is not utilizing bind
> variables and therefore, it is also contributing to the flush rate of
> the instance and thus shared pool and library cache latches
> contention.
>
> Having said the above, I would like to ask:
> -- is my analysis of the above statement being part of the problem is
> correct ?
> -- I need some suggestions on how one can figure out whether a shared
> pool is sized properly or not.
>
> Thank you
> Amir
Received on Fri Mar 14 2003 - 09:09:16 CST