Re: GETS vs. PINS

Here's a rewording, to try and make it more readable -

[Context is - analysis of the SQL_AREA of the Library cache.]

Most relevent dynamic performance view - V$LIBRARYCACHE where the relevant row is WHERE namespace='SQL_AREA'.

(From the Oracle Reference (8.0.5 Enterprise Edition) :- "V$SQLAREA Lists statistics on shared SQL area; contains one row per SQL string. Provides statistics on SQL statements that are in memory, parsed, and ready for

execution."

In this context, as far as I understand,

'GET' - a request for the result of a SQL statement.

      Oracle may have parsed the very same statement before.
      (it keeps a copy of the plain text SQL in memory even though
      the parsed components may not be there).
      If Oracle is asked to carry out the same SQL statement again,
      it records a 'GETHIT'.

'GET's and 'GETHITS' deal with plain text / readable SQL.

'PINS' and 'PINHITS' deal with SQL after it has been [broken down /
decoded] into its component parts. You can see components of SQL statements in the view V$SQL_SHARED_MEMORY .

Oracle breaks down / decodes SQL into parts for its own internal use.

Each component part has its own piece of memory in the Shared Pool of the SGA.

Sometimes some or all of these parts are overwritten to make room for other component parts of SQL Statements.

A 'PIN' is a re-use or re-reading of a component part of SQL by Oracle while it is doing its internal working.

A 'PINHIT' is when the component part (and all other directly related meta-data) that Oracle wants to use right now was found to be still in memory.

So a high 'PINHIT' ratio should be the goal, because this shows that Oracle is taking advantage of work done in the past (by IT) to parse pieces of SQL (the results of the work are what are held in memory).

Each and every time it had a 'PIN MISS' ( PINS-PINHITS), Oracle would have to spend more CPU time re-parsing bits of SQL and performing space management in the shared pool. Here, cpu cycles can be saved simply by adding RAM and giving Oracle a larger shared pool.

A 'GETHIT' is when Oracle has been asked again to carry out a (plain text, human readable) SQL statement which it knows it has done before. So if you had 1000 people all using the same application, which sent identical SQL to Oracle (using bind variables) then you would get a very high 'GET HIT' ratio because Oracle knows when its being asked to carry out a piece of SQL which it has done before.

If you had 1000 people all typing in random queries in SQL*Plus sessions, then your 'GETHIT' ratio would be terrible, because Oracle would hardly ever be asked to carry out the same SQL statement twice.

'GETHITS' are 'nice to have' but 'PINHITS' are essential. A 'PIN-MISS'
means extra cpu time only because you don't have a large enough shared pool.

Extra cpu time per statement means less throughput all round and imposing a non-linear scalability problem (gets progressively worse).

RELOADS - as far as I am aware this represents Oracle needing to redo a parse operation because a component part has been overwritten in memory by something else.

Best idea is as always - install Oracle on a test machine and do some detective work.

Have fun,

David N. Pomphrey OCP |DBA| MCP |TCP/IP| B.Tech.

Glasgow, Scotland.

The Internet Engineering Task Force (IETF) : http://www.ietf.org/

'Standards Track' RFCs : ftp://ftp.isi.edu/in-notes/std/std1.txt