Re: v$librarycache -- sorry for crossposting

On Tue, 04 Mar 2003 04:52:20 +0000, aNotherDBA wrote:

> Does anyone have a better explanation of the GETS and PINS columns in this
> view, besides:
>
> GETS: Handle requests for objects of this namespace
> PINS: PIN requests for objects of this namespace
>
> Are the GETS necessarily physical gets??? Can they be physical GETS (from
> the disk)? I'm struggling with this concept somewhat.

Whenever you issue a piece of SQL against the database, it gets hashed into a numeric value, based on the ASCII value of the characters making up the SQL statement. Your server process then waltzes along to the Library Cache, looking to see if anything with the same hash value is already in the cache (because if it is, it must by definition be the exact same SQL statement, and the costs of the hard parse you are about to face can be avoided). That is a 'get' -a lookup for something in the Library Cache, to try and avoid a hard parse.

It has nothing to do with physical gets at all. The contents of the Library Cache don't exist on disk, and if you achieve a 'GETMISS', you simply hard parse.

One way or another, you will eventually execute your sql statement, or rather execute the execution plan that is developed for your sql statement during the parse phase (or, even better, execute someone else's execution plan that they went to the trouble of parsing, but you can pinch because your sql statement is identical to theirs). That's an execution. Otherwise known as a Pin.

Hope that helps.
Regards
HJR Received on Tue Mar 04 2003 - 02:07:27 CST