Re: Subject: 100 percent miss in library cache

There is a big clue in the fact that your PINS, rather than the GETS on v$library cache is the high figure. You are 140,000 gets means not very much new activity in the library cache in 40 minutes - the problem is not hard parsing. The PINS tell you you've got a lot of existing objects being used.

                         Get  Pct        Pin        Pct               Invali-
Namespace           Requests  Miss     Requests     Miss     Reloads  dations


--------------- ------------ ------ -------------- ------ ---------- -------- 

BODY                   1,188    0.4          1,396    0.4          0        0
CLUSTER                   16    6.3             30    3.3          0        0
INDEX                     37   48.6            128   15.6           2        0
SQL AREA             142,617  100.0      3,975,409    6.2        140        9
TABLE/PROCEDURE        1,192   14.7      2,018,879   -0.3        158        0
TRIGGER                   72    0.0          68,984   -0.0          0        0

What do your stats (v$sysstat) for parse-related values and "session cursor cache" related values look like.

In 9.2.0.4 the pl/sql cursor cache was dictated by open_cursors, in the upgrade to 9.2.0.5 it changed to follow session_cached_cursors. I don't recall if there was a similar change across versions of 10g, or whether 10g has always followed session_cached_cursors - but it may be relevant.

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

--

http://www.freelists.org/webpage/oracle-l Received on Thu Mar 02 2006 - 02:18:42 CST