Re: Shared pool and library cache latch contention

Amir,

Are you pinning the most frequently used objects also? If not then that would certainly help your case. Also, there is a note on metalink which lists various checks you can do to tune the shared pool note 62143.1
I'm not sure what the Precise software does, but if its not binding variables then you have a problem right there.

Anurag

"Amir Hameed" <Amir.Hameed_at_usa.xerox.com> wrote in message news:334de71f.0303121921.2c884857_at_posting.google.com...
> Hi Folks,
> We recently upgraded an 11i (11.5.6) environment from 8.1.7.2 (32-bit)
> to 8.1.7.4 (64-bit) on Solaris 2.8 running on Sun E15K. The server has
> 48 CPUs and 96GB RAM. The shared pool size was 500MB and I carried
> over the same size after the 64-bit upgrade. Soon after we upgraded,
> we started seeing "latch free" waits among the top two wait events,
> sometimes even staying at the very top. I looked at v$latch and
> observed that "shared pool" and "library cache" latches had highest
> number of sleeps. At one point when the system slowed down
> considerably, I looked at the x$ksmsp and noticed that one shared pool
> bucket had over 2400 free chunks. At one point we also encountered
> ORA-4031 error and I figured that the shared pool was 'under sized'.
> This particular instance had been running in 32-bit for almost three
> years and we had never seen this issue before. Oracle suggested that
> 64-bit requires more shared pool size based upon the internal
> structure sizes that also increase with 64-bit (pointer, array sizes,
> etc., Metalink Doc 209766.1). So, I increased the shared pool from
> 500MB to 800 MB. I did not want to just double it as I was afraid that
> an oversized shared pool may experience fragmentation at some point.
> Now, even though the "latch free" wait has gone down but during some
> periods in the day it still crawls up between 15-25%. Also, I still
> observe large free chunks in x$ksmsp on some buckets.
>
> We also have Precise software running and it too had to be upgraded to
> 64-bit version. Recently, I have been monitoring x$ksmlru and whenever
> I query it,I almost always see the following with different values in
> the 2nd and 3rd columnns:
>
> KSMLRCOM KSMLRSIZ KSMLRNUM KSMLRHON KSMLROHV KSMLRSES
> BAMIMA: Bam Buffer 4168 408 begin
> PRECISE.precise_apps_l... 41309352 00000003CB9EA0B0
>
> 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 Wed Mar 12 2003 - 23:02:21 CST