Re: library cache latch contention on Oracle Portal

Yong Huang wrote:
>
> 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

I may be mistaken but wasnt there some major library cache issues with 8.1.7.0 - fixed in 8.1.7.1 or 8.1.7.2?

(Don't take this as gospel - I may be starting you on a wild goose chase - but maybe a play with the patchsets could be somewhere to look)

Cheers
Connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."