Re: Library Hit % in statspack

On Sep 14, 10:43 am, vitalis..._at_gmail.com wrote:
> While reading a 10.2.0.3 statspack report I was wondering why the
> authors chose to compute the "Library Hit %" from the PINHITS rather
> than from the GETHITS of v$librarycache.
> In the report I've been reading, the lack of bind variables can be
> guessed from the 100% Miss for the "Get Requests" of the SQL AREA
> namespace in the section "Library Cache Activity". Reading the
> "Library Hit %" at the beginning of the report, it is less obvious:
> 90%.
>
> Of course ratios are never relevant without further investigation. But
> could anyone tell me why PINHITS was apparently deemed more important
> than the GETHITS by statspack authors when computing the hit ratio?

Here's how I understand the mechanism; let's start by examining the definition of the terms:

GETHITS Number of times an object's handle was found in memory

Finding the 'handle' (statement hash) doesn't necessarily imply the parsed statement and data is present. It does indicate a successful lookup of the source SQL in the cache, though, so Oracle can make use of a 'soft parse' rather than a 'hard parse'.

PINHITS Number of times all of the metadata pieces of the library object were

               found in memory

Oracle found everything it needed (the parsed statement and the data have not been aged out of the cache) so it can make use of it without further action.

So PINHITS reports on the number of times Oracle found parsed and initialized SQL statements in memory. GETHITS reports on the number of times Oracle found the source SQL in memory. 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. David Fitzjarrell Received on Fri Sep 14 2007 - 13:17:26 CDT