Re: Subject: 100 percent miss in library cache
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 2 Mar 2006 08:18:42 -0000
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Re: Subject: 100 percent miss in library cache
