Re: Shared pool and library cache latch contention

Hi,

"Ricky Sanchez" <rsanchez_at_more.net> wrote in message news:3E71F0A6.6461CD3F_at_more.net...
> 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.

I think lots of code with executions = 1 will be thrown out the LRU list and shared pool, thus not showing up in v$sqlarea. And the code which stays in SP nicely, isn't probably the cause for excessive library cache latch contention..

>
> 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.

Setting this might be a bit problematic in Apps environment. Apps has very special requirements to work correctly. I once had an issue: Apps 11i on Oracle 8.1.6 required optimizer_max_permutations to be 80000 (default), but 8.1.7 required 2000. I thought it was a minor change and didn't do it initially. As a result some large views took huge time to select. So, cursor sharing might introduce several problems even when it is supposed to be transparent.

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

Yeah, and next time they should do it correctly from start ;)

>
> 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.

Check http://www.ixora.com.au/scripts/sql/keeper.sql

Tanel. Received on Sat Mar 15 2003 - 06:28:05 CST