Re: Library Hit % in statspack

<vitalisman_at_gmail.com> wrote in message news:1190046163.440683.316170_at_g4g2000hsf.googlegroups.com...
> On Sep 14, 8:17 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> ...
>> So the library hit %
>> is actually referencing the percent of parsed and initialized
>> statements found in the cache, which is why PINHITS was chosen over
>> GETHITS.
>
> Thanks for sharing your thoughts David. The page of the reference
> guide about v$librarycache is not very clear (it mentions locks then
> handles for GETS and GETHITS, and pins then metadata pieces for PINS
> and PINHITS...)
>
> According to your explanation, how would you explain that PINS>GETS
> (often by several orders of magnitude) and that
> PINHITRATIO>GETHITRATIO, for most namespace of any instance (or
> rather: of any instance I have checked - please check it with your
> instances if you have time) ?
>
> Jerome
>
>

You have to do some gets when you parse a statement, but if you leave the cursor open, or the pl/sql cursor cache or session cursor cache hold it open for you, you can re-execute it. In this case, execution requires the pin, but no get. In a reasonably behaved system, you probably execute more than you parse.

(Bear in mind that not all parse calls turn into "real" parses -

    http://jonathanlewis.wordpress.com/2007/07/03/parse-calls/ it makes counting parses quite difficult.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

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

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