library cache latch contention on Oracle Portal

Oracle 8.1.7.0.0, Portal 3.0.8.9.1, Sun Solaris 2.6, 8 CPUs, 400 MHz each, 8GB memory. Oracle Support is also working on this (tar 1877831.999).

Ever since our Portal site went live, we have severe latch free waits. Latch# 106, library cache. Whenever the homepage (after logged in) is refreshed, a dozen such latch waits are shown in v$session_wait for 1 or 2 seconds. But 2 or 3 such waits persist for as long as 15 seconds and go away approximately the same time the Web page refresh is finished. SQLs last or currently run by sessions waiting on these latches are always "begin dbms_session.reset_package; end;". Parse_calls in v$sql for this SQL is very high. v$latch_children.gets of these latches are not evenly distributed. 1 or 2 have more than double of the average. Gets/misses is as low as 13 and gets/sleeps 36. Free memory in v$sgastat drops sharply from 200M to 13M within 20 hours of instance startup or shared pool flushing. Increasing child latches (_kgl_latch_count) from default 11 to 23 may have helped a little. Pinning packages such as dbms_session, analyzing schemas and setting _latch_wait_posting have no noticeable effect. A fairly big improvement seems to have come from setting cursor_sharing=force since some Oracle-supplied SQLs use literals heavily (http://www.stormloader.com/yonghuang/portalSQL.txt; SQLs taken about 3 days after instance startup). But due to bug 1783876 (NoteID 149016.1), I unset it.

I want to pinpoint the objects (including cursors, java classes) in library cache most contended for. I ran
http://www.ixora.com.au/scripts/sql/objects_on_hot_latches.sql and also alter session set events 'immediate trace name library_cache level 3' (http://www.ixora.com.au/q+a/library.htm). But the output is too much to digest. Let me know if I need to post them on the Web.

Since Oracle Support can't reproduce the problem, I'm seeking advice from public forums.

Yong Huang
yong321_at_yahoo.com Received on Mon Sep 24 2001 - 16:08:47 CDT