Re: PINS Vs. GETS

A gethit in the sql area of the library cache part of the shared pool is a successful reuse of a piece of SQL / PL/SQL, which was 'discovered as being reusable' during a parse call for something else (after all you only need to parse a piece of SQL once per execution!).

A pin in the sql area of the library cache of the shared pool is a count of the number of re-executions of pre-parsed SQL statements (because Oracle will end up knowing during each and every execution whether or not the complete version of the parsed form of the piece of SQL is still there or not! (This is self-evident of course).

Logically enough then, a pinhit is when the parsed form is still in the sql area of the library cache, in its entirety.

So gethitratio should be 90% plus or your developers were not structured enough or you've got a lot of ad-hoc query writing going on (and the server is being a bit busier than it might otherwise be because it has to do more parsing)

 and

Pinhitratio should be very high (maybe 98, 99%). - Mainly because a pin'miss' is a clear demonstration of sub-optimal performance (which is eminently DBA-fixable) but a get'miss' is more a comment on the elegance of the application(s) and the conformity and similarity of the SQL that it(they) are submitting to the server.

Putting this another way, a 'get' in v$librarycache is a completely different metric compared to a 'get' as used in metrics 'db block gets' and 'consistent gets' (from v$sysstat).

David P.

Oracle Certified DBA.