Shared pool and library cache latch contention

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 - 21:21:48 CST