Re: PINS vs GETS

Think of a GET as a parse, and a PIN as an execution.

SQL statements have to be parsed first, then executed. A 'gethit' means that you didn't actually have to parse the SQL fresh, because an existing execution plan for an identical SQL statement was already in the library cache, and you were permitted to re-use it. It was still a "get", though, because you are still 'requesting information about the item' -in other words, you are still asking of Oracle 'do I have an EMP table, does it have an ename column, is there an index on that column that I can use' and so on... all the sort of questions that a 'parse' is designed to answer. A high gethit ratio means therefore that you are submitting beautifully shareable code (lots of bind variables etc), and most of the time you ask those sorts of questions about a table, the answer has already been worked out on behalf of someone else's SQL statement.

If an execution plan gets aged out of the library cache, although you might have been able to share or re-use it, you'll actually need to 'reload' (ie, re-parse) it. So, whilst we expect to see a lot of executions (pins), we don't want to see lots of re-parses (reloads), meaning that the reload to pins ratio ought to be very low -which will be the case when your library cache is sufficiently large (ie, changes to the size of the shared pool will affect the reloads/pins ratio).

Regards
HJR

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================


"Charli" <b.charlinski_at_elka.pw.edu.pl> wrote in message
news:a43hli$joj$1_at_julia.coi.pw.edu.pl...


> Does anybody know diffrence between columns PINS and GETS in

V$LIBRARYCACHE.


> The answer that:

>     GETS shows the total number of request for information on the

> correcponding item

>     PINS shows the number of executions of SQL statements or procedure

> doesn't satisfy my. I don't understand it.

>     When the procedure is executed and when only information on the

> procedure is requested?

>

> Please help me.

>

>