Skip to Main Content
  • Questions

  • Confusion in the interpretation of V$LIBRARYCACHE columns

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Anwar.

Asked: July 21, 2000 - 8:51 am UTC

Last updated: February 07, 2008 - 4:25 pm UTC

Version: 7.3.2

Viewed 1000+ times

You Asked

Hi Tom,

I'm a bit confused why two different columns are included in V$LIBRARYCACHE to serve a single purpose i.e. PINS & GETS? If the two are different then please explain the difference.

For your reference table structure is given below:

SQL> desc v$librarycache
Name Null? Type
------------------------------- -------- ----
NAMESPACE VARCHAR2(15)
GETS NUMBER
GETHITS NUMBER
GETHITRATIO NUMBER
PINS NUMBER
PINHITS NUMBER
PINHITRATIO NUMBER
RELOADS NUMBER
INVALIDATIONS NUMBER
DLM_LOCK_REQUESTS NUMBER
DLM_PIN_REQUESTS NUMBER
DLM_PIN_RELEASES NUMBER
DLM_INVALIDATION_REQUESTS NUMBER
DLM_INVALIDATIONS NUMBER



and Tom said...

They are very different and in general, have radically different values:

ops$tkyte@8i> select namespace, gets, pins, pins-gets from v$librarycache where gets <> pins;

NAMESPACE GETS PINS PINS-GETS
--------------- ---------- ---------- ----------
SQL AREA 9568694 429224748 419656054
TABLE/PROCEDURE 22172223 64176942 42004719
BODY 31024133 31022250 -1883
TRIGGER 37165 37317 152
INDEX 1236 1240 4
CLUSTER 48827 46019 -2808
PIPE 1582255 1582281 26

7 rows selected.

ops$tkyte@8i> select count(*) from v$librarycache;

COUNT(*)
----------
8

ops$tkyte@8i>


The GETS column indicates the number of times, since database instance startup, that the system requests handles to library objects in this namespace.

The PINS column indicates the number of times, since database instance startup, that the system issues pin requests in order to access objects in the cache. This column shows the number of times an item in the library cache was executed. Whenever an entity (cursor or db object) is accessed in cache it needs to be pinned (PINS) to prevent it from being flushed out by the heap manager.



Rating

  (34 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

PLS Explain more detail

Frank, October 31, 2001 - 3:59 pm UTC

Tom, Quote ur answer here:
"The GETS column indicates ... that the system requests handles ....
The PINS column indicates ... issues pin requests "

1)Could u give us the scenario when system requests handles, but does not issue pin? or vise versa.
2)I think before the system issues pin requests, it has to request handle first, so PINS should always greater than GETS. But that's not true according to the data in the view, why?

I'm still confused

Erik Cook, March 24, 2002 - 4:48 pm UTC

In tom's answer "The GETS column indicates the number of times, since database instance startup, that the system requests handles to library objects in this namespace." was confusing.

Franks question of: "I think before the system issues pin requests, it has to request handle first, so PINS should always greater than GETS. But that's not true according to the data in the view, why? " would help clarify things.

I guess we'll be confused for ever.


pins and gets

A reader, October 01, 2002 - 11:01 am UTC

hi

if I run a query several times i can see gets and pins increasing so in that case I dont understand how can gets and pins differ from each other? I mean under what situation would gets value differ from pins value

if I run

select * from emp

I will be requesting this query´s execution plan, parse tree etc (information) so this would be a get, if then it´s executed (it is always executed, I dont see under what circumstance it wont be executed) I get a pin so I will always have one increment of gets and pins. I mean there wont be execution without previous request no? So relationship between pins and gets wouldnt it be 1 to 1?

Tom Kyte
October 02, 2002 - 9:27 am UTC

You'll "get" more to parse.

You'll "pin" to run.

run over and over and "gets" will be less then the first time, so will pins as you get and pin less objects for subsequent operations.

You might get 1000 things in order to parse something but pin them 2000 times (eg: get me this definition, pin it -- unpin it -- pin it -- unpin it -- etc etc etc)




the term "pin"

A reader, October 02, 2002 - 1:40 pm UTC

hello

I read this term "pin" quite a few times. What's the difference of pinning stuffs in the shared pool with dbms_shared_pool and pin in v$librarycache?


Tom Kyte
October 02, 2002 - 7:30 pm UTC

pinning something in the pool makes it "stick there"

when we pin something in the librarycache, we are making it "stick there" as well but we pretty much go back and unpin it shortly after. When you keep something in the shared pool (some people call that pinning in the shared pool) it stays there till you unkeep or physically change it

Pins vs Gets

Cindy, October 03, 2002 - 7:51 am UTC

I found this in a Metalink forum. Is it accurate?

GETS - the total number of SQL requests sent to the database
PINS - the number of times requested SQL was found in memory (effectively the number hits?)
RELOADS - total number of times requested SQL was not found in memory requiring hard parse (effectively the number of misses?)

Thanks.


Tom Kyte
October 03, 2002 - 9:30 am UTC

No, what note is that? we need to remove that. look at v$librarycache -- most of the entries don't have anything to do with just "sql". It is too myoptic of a view of this information.



Gets vs Pins

Cindy, October 03, 2002 - 9:55 am UTC

Those definitions were from the Oracle Server-Enterprise and Standard Edition Technical Forum. Someone asked (23-Oct-00) if those were correct interpretations and Ken Robinson from Oracle replied "Yes, you have it".

Tom Kyte
October 03, 2002 - 10:28 am UTC

look at the namespace column, its not just about sql.

run some queryies in isolation and try to rectify the counts -- you won't be able to.

there is no one to one correlation between "I submit a query and gets/pins will be affected as such"



Please give a specific, basic example

Robert, October 03, 2002 - 10:05 am UTC

Tom,

I think many of us are sympathetic to Frank and Eric's plight. Can you please give us a specific, basic example that will illustrate gets, pins (and maybe reloads) in the library cache.

Thanks,

Robert

Tom Kyte
October 03, 2002 - 10:31 am UTC

The definitions are here:

.....
The GETS column indicates the number of times, since database instance startup,
that the system requests handles to library objects in this namespace.

The PINS column indicates the number of times, since database instance startup,
that the system issues pin requests in order to access objects in the cache.
This column shows the number of times an item in the library cache was executed.
Whenever an entity (cursor or db object) is accessed in cache it needs to be
pinned (PINS) to prevent it from being flushed out by the heap manager.

....

when we execute a query -- we need a handle to some library cache object(s). If we already GOT the handle on some previous call (eg: we have an open cursor we are just re-executing) we won't have to GET it again. We will have to PIN it for a second, to check out the handle, make sure all is OK, nothings changed (think of a PIN like a LOCK -- we need to lock it to execute/use it).


A reload is when something we had in the library cache went away (flushed, shared pool too small perhaps) and we reloaded it -- OR the definition changed.

is library cache lock latch used for pin?

A reader, October 03, 2002 - 2:55 pm UTC

Hi

Which latch is used when a sql cursor is get or pinned?

Also, if when we query for some library objects (get) and we found it then we dont need to get, it's then pinned however after get one objects is invalidated so the pin become useless, isnt this a reload?

Tom Kyte
October 03, 2002 - 6:43 pm UTC

library cache

your last paragraph is unparsable by my brain, no idea what you might have been trying to say.

reload and latches

A reader, October 04, 2002 - 11:07 am UTC

Hi

What I am saying is if the definition of reload is:

when Oracle look i shared pool for a SQL stmt, if it finds it then it's a get, after that it send an execute call, if the SQL is invalidaded before the shared cursor is pinned then there will be a reload, i.e the SQL has to be reparsed

Also regarding library cache latch, you said when we pin a shared cursor we would need library cache latch to "lock" the cursor temporary, from other threads I read that bind variables minimizes library cache latch contention. I dont understand how bind variable can avoid latch contention because we always have to pin (or lock) shared cursor if we want to execute it no? No matter bind variables are used or not. So at the end of the day we end up with same number of executions


Tom Kyte
October 04, 2002 - 6:44 pm UTC

You might have to pin 1,000 times to do a hard parse -- cause you need to pin hundreds of objects in the libary cache in order to do the parse (the definition of the table, the columns, the indexes, etc etc etc)

When you use bind variable and we do a soft parse -- we do less work -- resulting in less pins.



how can I demostrate this

A reader, October 13, 2002 - 9:20 am UTC

Hi

When executing a query, how can I demostrate If I already GOT the handle on some previous call (eg: I have an open cursor I am just re-executing) I won't have to GET it again

I opened two SQL*PLUS sessions and tried this

first session:
declare
a number;
b number;
begin
a := 7934;
select empno
into b
from emp where empno = a;
end;
/

second session:
here just to see if GETS and GETHITS are increased whenever I run the PL/SQL in another session

I always see GETS increasing even I suppose with my PL/SQL I have a open cursor. When I query v$open_cursor in second session I see

SADDR SID USER_NAME ADDRESS HASH_VALUE SQL_TEXT
-------- ---------- ------------------------------ -------- ---------- ------------------------------------------------------------
02200BC0 7 LSC 0328FD78 3373673168 declare a number; b number; begin a := 7934; select empno i
02200BC0 7 LSC 0325CAB8 4151506772 SELECT EMPNO FROM EMP WHERE EMPNO = :b1
02201C98 9 LSC 03273858 4065929129 select * from v$open_cursor

so I do have the cursor opened but then why GET still increases?

Tom Kyte
October 14, 2002 - 7:36 am UTC

You gotta GET it before you can execute it.

Why WOULDN'T the gets increase? I'd be more worried if it didn't personally. I mean "where did the query plan come from, if we didn't 'get' it".


The soft parses on that query will increase by one every time you parse it -- thats the only thing you can look at. Don't over analyze this here.

but you said if we have open cursor

A reader, October 14, 2002 - 9:14 am UTC

Hi

But you said if we have the cursor open then I dont need to GET again or I missread... :-(

Tom Kyte
October 14, 2002 - 9:18 am UTC

you are running alot more then just a query there -- you are submitting an anonymous block that just HAPPENS to have a query inside of it.

#1 The anonymous block is getting soft parsed

#2 The anonymous block is NOT a procedure, hence the cursor cannot be cached (you NEVER really run a anonymous block twice see -- it is NOT a named object)

#3 Your other session is executing TONS of sql itself (to log in, to verify security checks to query the v$ tables itself)!!!





pseudo code for accessing the shared pool

Alberto Dell'Era, August 28, 2003 - 3:29 pm UTC

So, if the kernel were written in Java instead of C, would the kernel developers code something like the following in order to use the shared pool ?

Handle handle = libraryCache.get (....);

handle.pin();

handle.getAttribute (...);
handle.setAttribute (...);

handle.unPin();

Tom Kyte
August 29, 2003 - 8:36 am UTC


"if the kernel were written in Java"... oh that sends chills down my spine!


yes, you set a mutex, do your work, release the mutex.

pinning through the handle

Alberto Dell'Era, August 29, 2003 - 9:14 am UTC

> "if the kernel were written in Java"... oh that sends chills down my spine!

ORA-0666 Garbage Collection in Progress - please retry later - thanks for your cooperation

> yes, you set a mutex, do your work, release the mutex.

And all through the handle I suppose - you can't pin an object without an handle (that was the main part of the question). Is this correct ?





Tom Kyte
August 29, 2003 - 10:10 am UTC

the handle "is" the object in a manner of speaking, so yes..

Instead of ora-666 with a simple message like that -- it would be the standard java error stack of 5,000 or so lines...

Q on INVALIDATIONS

Anil Pant, December 29, 2003 - 9:20 am UTC

Hi,
If value of Invalidations is 0 it means its not yet valid. For example if an Select on Table T1. Now If I alter the table the value becomes 1. If I alter multiple times does it mean that the counter increases. Means if I alter N times the value will be N.
Am I correct ?


Tom Kyte
December 29, 2003 - 10:44 am UTC

no, not correct.

say you

a) parse a query against t
b) alter the table (that'll invalidate (a))
c) alter the table (that'll do nothing to (a), its already invalid)
d) alter the table (see (c))
e) parse that query again


invalidations of 1.
3 alters..

Reg Invalidations

Anil Pant, December 29, 2003 - 11:52 pm UTC

Hi,
Thks for the reply. Yes I got what u said, but for some of the rows the values were greater than 1 in my DB. What does a value 3 indicate ? Or mean to you say any thing other than 0 is invalid. That's it does not matter whether its 1,2,3 or 100. Am I correct ?

Tom Kyte
December 30, 2003 - 10:02 am UTC

if you (assuming empty shared pool at the beginning of this exercise)

select * from t;
alter table t; <<< invalidations = invalidations + 1;
select * from t;
alter table t; <<< invalidations = invalidations + 1;
alter table t;
select * from t;
select * from t;


so, 3 alters, invalidations increase by 2 in that case. invalidations is a COUNTER -- how many times did we invalide stuff. this just shows it is not a function of how many alters have been done, but rather how many statements have been invalidated over time.

GETS, GETHIT, PINS, PINHIT

Henry Yick, March 02, 2004 - 10:34 am UTC

Really useful for me.

So, could I say:
1. If I can share the SQL, the GETS should be comparatively slow.
2. For every object handles in a SQL request, GET is optional (if I get it before), but PINS is unavoidable, and is normal.

Also, what is the meaning of a low GETHIT and a low PINHIT? Both of them indicate that the sharepool size is too small? Any different between them?

Tom Kyte
March 02, 2004 - 6:40 pm UTC

1) if you parse little, gets should be low. not just share
2) true

could indicate, could -- not do.

GETS or RELOADS

Yves Debizet, March 08, 2004 - 10:24 am UTC

Tom, you said:

"A reload is when something we had in the library cache went away (flushed,
shared pool too small perhaps) and we reloaded it -- OR the definition changed."

In case an object has been flushed, how can Oracle make the difference between "the object is no more in the cache: RELOAD will be issued" and "the object is not in the cache: GET will be issued" ?

Where is the information which says: the object had been loaded but it has been flushed and should be reloaded when needed ?

Thank you.

Tom Kyte
March 08, 2004 - 1:57 pm UTC

a get happens with a reload. a get is a get.

this is the view that provides the information about reloads.. if this view has a slot (named slot) for an object that isn't in the cache -- it is a reload.

This cleared so much

hxl, March 23, 2004 - 10:29 am UTC

Tom,

Besides invalidation, what will be other cases when a sql stmt stored in the library cache will be flushed? I am a little puzzled by the stats below which shows I have free memory in shared pool, but I have lots of reload/invalidations, what could that mean? Or is it because there is pre-assigned portions in library cache for each category (sqlarea, data buffer, etc) and each won't go over its quota?

-----------------------------------------------------------
NAME MBYTES MBYTES_TOT PCT SHARED_POOL_SIZE
-------------------------- --------- ---------- --------- ----------------
free memory 87.99 175 50 161
miscellaneous 1.95 175 1 161
transactions .52 175 0 161
db_block_hash_buckets .89 175 1 161
DML locks .28 175 0 161
fixed allocation callback 0 175 0 161
PL/SQL DIANA 3.45 175 2 161
KGFF heap .04 175 0 161
trigger source 0 175 0 161
table definiti .01 175 0 161
trigger inform 0 175 0 161
sessions 1.14 175 1 161
enqueue_resources .24 175 0 161
State objects .62 175 0 161
joxs heap init 0 175 0 161
pl/sql source 0 175 0 161
PL/SQL SOURCE 0 175 0 161
errors 0 175 0 161
PLS non-lib hp 0 175 0 161
ktlbk state objects .25 175 0 161
KGK heap .02 175 0 161
trigger defini 0 175 0 161
dictionary cache 2.13 175 1 161
db_files .35 175 0 161
table columns .03 175 0 161
KQLS heap 1.44 175 1 161
temporary tabl 0 175 0 161
library cache 26.08 175 15 161
db_block_buffers 6.48 175 4 161
sql area 35.25 175 20 161
joxlod: in phe 0 175 0 161
processes .39 175 0 161
PL/SQL MPCODE 3.3 175 2 161
joxlod: in ehe .06 175 0 161
event statistics per sess 1.82 175 1 161

Thanks as always.

Tom Kyte
March 23, 2004 - 10:40 am UTC

besides invalidation what causes invalidations...... (you say you have lots of invalidations...)

sounds like you have invalidations -- why are you looking for "other things"?

Just want to know all possible causes

hxl, March 23, 2004 - 12:33 pm UTC

More importantly can you confirm on this: I have free memory in shared pool(about 50%), but there are reloads/invalidations, is it because sql area only got assigned a certain % of the shared pool so once its quota is reached, some sql statements will be flushed?

Otherwise I have no idea why the old stmts have to be flushed out - not couting being invalidated.

Thanks.

Tom Kyte
March 23, 2004 - 5:27 pm UTC

how do you know they are getting flushed in the first place?

the sql area can get larger according to your info above.

Cursor managment.......

Mark J. Bobak, March 23, 2004 - 1:25 pm UTC

This got me thinking. I saw the Cursor Management talk at HotSos. Afterwards, I was chatting w/ Steve Adams, and he pointed out that when you set session_cached_cursors, what you're really doing is caching the library cache lock structure (which is what you create when you do a GET.) He also explained to me that when you set cursor_space_for_time=TRUE, you're additionally caching the library cache pin structure, (which is what you create when you do a PIN.)

So, I did some testing, 10.1.0.2, I was the only one on the instance. First, I did:
session_cached_cursors=0
cursor_space_for_time=FALSE
and I executed the following script:
select namespace, gets, gethits, pins, pinhits from v$librarycache where namespace = 'SQL AREA';
For each execution, GETS and GETHITS increased by 1, while PINS and PINHITS increased by 2. Then I set session_cached_cursors=100 Now, GETS and GETHITS stopped increasing, and PINS and PINHITS increased by 1 per execution. So, I set cursor_space_for_time=TRUE and session_cached_cursors=100.
I expected the PINS and PINGETS to go to zero/execution. This was not the case, I saw the same as I had before, with session_cached_cursors=100 and cursor_space_for_time=false.

Any thoughts, Tom?


Tom Kyte
March 24, 2004 - 7:54 am UTC 

what did you use to test with -- different environments will return radically different results -- especially if you use PLSQL for this sort of stuff with its magical cursor caching and such.

But, here was my example:

ops$tkyte@ORA10G> create or replace procedure p
  2  authid current_user
  3  as
  4      l_ns      varchar2(4000);
  5      l_gets    number;
  6      l_gethits number;
  7      l_pins    number;
  8      l_pinhits number;
  9      l_sgets    number;
 10      l_sgethits number;
 11      l_spins    number;
 12      l_spinhits number;
 13  begin
 14      for i in 1 .. 1000
 15      loop
 16          execute immediate
 17          'select namespace, gets, gethits, pins, pinhits
 18             from v$librarycache
 19            where namespace = ''SQL AREA'' '
 20          into l_ns, l_gets, l_gethits, l_pins, l_pinhits;
 21
 22          if ( i in (1,1000) )
 23          then
 24              if ( i = 1 )
 25              then
 26                  l_sgets := l_gets; l_sgethits := l_gethits;
 27                  l_spins := l_pins; l_spinhits := l_pinhits;
 28              end if;
 29              dbms_output.put_line
 30              ( l_ns || to_char(l_gets,'999,999') ||
 31                to_char(l_gethits,'999,999') ||
 32                to_char(l_pins,'999,999') ||
 33                to_char(l_pinhits,'999,999')  );
 34              if ( i = 1000 )
 35              then
 36                  dbms_output.put_line
 37                  ( l_ns || to_char(l_gets-l_sgets,'999,999') ||
 38                    to_char(l_gethits-l_sgethits,'999,999') ||
 39                    to_char(l_pins-l_spins,'999,999') ||
 40                    to_char(l_pinhits-l_spinhits,'999,999')  );
 41              end if;
 42          end if;
 43      end loop;
 44  end;
 45  /
                                                                                                                                                               
Procedure created.


ops$tkyte@ORA10G> @connect "/ as sysdba"
ops$tkyte@ORA10G> set termout off
sys@ORA10G> @login
sys@ORA10G> set termout off
sys@ORA10G> REM GET afiedt.buf NOLIST
sys@ORA10G> set termout on
sys@ORA10G> alter system set session_cached_cursors=0 scope=spfile;
 
System altered.
 
sys@ORA10G> alter system set cursor_space_for_time=FALSE scope=spfile;
 
System altered.
 
sys@ORA10G> startup force
ORACLE instance started.
 
Total System Global Area  171966464 bytes
Fixed Size                   777956 bytes
Variable Size             145760540 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.
Database opened.
sys@ORA10G> @connect /
sys@ORA10G> set termout off
ops$tkyte@ORA10G> @login
ops$tkyte@ORA10G> set termout off
ops$tkyte@ORA10G> REM GET afiedt.buf NOLIST
ops$tkyte@ORA10G> set termout on
ops$tkyte@ORA10G> exec p
SQL AREA   1,860   1,536   6,740   6,122
SQL AREA   1,864   1,540   7,751   7,133
SQL AREA       4       4   1,011   1,011
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> exec p
SQL AREA   1,866   1,542   7,756   7,138
SQL AREA   1,866   1,542   8,755   8,137
SQL AREA       0       0     999     999
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> exec p
SQL AREA   1,868   1,544   8,760   8,142
SQL AREA   1,868   1,544   9,759   9,141
SQL AREA       0       0     999     999
 
PL/SQL procedure successfully completed.
 


ops$tkyte@ORA10G> @connect "/ as sysdba"
ops$tkyte@ORA10G> set termout off
sys@ORA10G> @login
sys@ORA10G> set termout off
sys@ORA10G> REM GET afiedt.buf NOLIST
sys@ORA10G> set termout on
sys@ORA10G> alter system set session_cached_cursors=100 scope=spfile;
 
System altered.
 
sys@ORA10G> alter system set cursor_space_for_time=FALSE scope=spfile;
 
System altered.
 
sys@ORA10G> startup force
ORACLE instance started.
 
Total System Global Area  171966464 bytes
Fixed Size                   777956 bytes
Variable Size             145760540 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.
Database opened.
sys@ORA10G> @connect /
sys@ORA10G> set termout off
ops$tkyte@ORA10G> @login
ops$tkyte@ORA10G> set termout off
ops$tkyte@ORA10G> REM GET afiedt.buf NOLIST
ops$tkyte@ORA10G> set termout on
ops$tkyte@ORA10G> exec p
SQL AREA     637     313   5,513   4,903
SQL AREA     637     313   6,520   5,910
SQL AREA       0       0   1,007   1,007
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> exec p
SQL AREA     638     314   6,525   5,915
SQL AREA     638     314   7,524   6,914
SQL AREA       0       0     999     999
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> exec p
SQL AREA     639     315   7,529   6,919
SQL AREA     639     315   8,528   7,918
SQL AREA       0       0     999     999
 
PL/SQL procedure successfully completed.
 


ops$tkyte@ORA10G> @connect "/ as sysdba"
ops$tkyte@ORA10G> set termout off
sys@ORA10G> @login
sys@ORA10G> set termout off
sys@ORA10G> REM GET afiedt.buf NOLIST
sys@ORA10G> set termout on
sys@ORA10G> alter system set session_cached_cursors=100 scope=spfile;
 
System altered.
 
sys@ORA10G> alter system set cursor_space_for_time=TRUE scope=spfile;
 
System altered.
 
sys@ORA10G> startup force
ORACLE instance started.
 
Total System Global Area  171966464 bytes
Fixed Size                   777956 bytes
Variable Size             145760540 bytes
Database Buffers           25165824 bytes
Redo Buffers                 262144 bytes
Database mounted.
Database opened.
sys@ORA10G> @connect /
sys@ORA10G> set termout off
ops$tkyte@ORA10G> @login
ops$tkyte@ORA10G> set termout off
ops$tkyte@ORA10G> REM GET afiedt.buf NOLIST
ops$tkyte@ORA10G> set termout on
ops$tkyte@ORA10G> exec p
SQL AREA   1,121     364   4,507   3,871
SQL AREA   1,180     372   4,824   4,167
SQL AREA      59       8     317     296
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> exec p
SQL AREA   1,181     373   4,828   4,171
SQL AREA   1,181     373   4,828   4,171
SQL AREA       0       0       0       0
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> exec p
SQL AREA   1,182     374   4,832   4,175
SQL AREA   1,182     374   4,832   4,175
SQL AREA       0       0       0       0
 
PL/SQL procedure successfully completed.
 

(which actually shows that 10g is doing a bit more than 9i was since...)


sys@ORA9IR2> alter system set session_cached_cursors=0 scope=spfile;
 
System altered.
 
sys@ORA9IR2> alter system set cursor_space_for_time=FALSE scope=spfile;
 
System altered.
 
sys@ORA9IR2> startup force
ORACLE instance started.
 
Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             167772160 bytes
Database Buffers           67108864 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
sys@ORA9IR2> @connect /
sys@ORA9IR2> set termout off
ops$tkyte@ORA9IR2> REM GET afiedt.buf NOLIST
ops$tkyte@ORA9IR2> set termout on
ops$tkyte@ORA9IR2> exec p
SQL AREA   1,145     977   4,884   4,579
SQL AREA   2,172   1,983   7,908   7,603
SQL AREA   1,027   1,006   3,024   3,024
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec p
SQL AREA   2,175   1,985   7,918   7,611
SQL AREA   3,174   2,984  10,915  10,608
SQL AREA     999     999   2,997   2,997
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec p
SQL AREA   3,177   2,987  10,924  10,617
SQL AREA   4,176   3,986  13,921  13,614
SQL AREA     999     999   2,997   2,997
 
PL/SQL procedure successfully completed.




ops$tkyte@ORA9IR2> @connect "/ as sysdba"
ops$tkyte@ORA9IR2> set termout off
sys@ORA9IR2> REM GET afiedt.buf NOLIST
sys@ORA9IR2> set termout on
sys@ORA9IR2> alter system set session_cached_cursors=100 scope=spfile;
 
System altered.
 
sys@ORA9IR2> alter system set cursor_space_for_time=FALSE scope=spfile;
 
System altered.
 
sys@ORA9IR2> startup force
ORACLE instance started.
 
Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             167772160 bytes
Database Buffers           67108864 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
sys@ORA9IR2> @connect /
sys@ORA9IR2> set termout off
ops$tkyte@ORA9IR2> REM GET afiedt.buf NOLIST
ops$tkyte@ORA9IR2> set termout on
ops$tkyte@ORA9IR2> exec p
SQL AREA     461     293   4,195   3,895
SQL AREA     485     296   6,216   5,916
SQL AREA      24       3   2,021   2,021
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec p
SQL AREA     487     297   6,225   5,923
SQL AREA     487     297   8,223   7,921
SQL AREA       0       0   1,998   1,998
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec p
SQL AREA     489     299   8,231   7,929
SQL AREA     489     299  10,229   9,927
SQL AREA       0       0   1,998   1,998
 
PL/SQL procedure successfully completed.


ops$tkyte@ORA9IR2> @connect "/ as sysdba"
ops$tkyte@ORA9IR2> set termout off
sys@ORA9IR2> REM GET afiedt.buf NOLIST
sys@ORA9IR2> set termout on
sys@ORA9IR2> alter system set session_cached_cursors=100 scope=spfile;
 
System altered.
 
sys@ORA9IR2> alter system set cursor_space_for_time=TRUE scope=spfile;
 
System altered.
 
sys@ORA9IR2> startup force
ORACLE instance started.
 
Total System Global Area  236000356 bytes
Fixed Size                   451684 bytes
Variable Size             167772160 bytes
Database Buffers           67108864 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
sys@ORA9IR2> @connect /
sys@ORA9IR2> set termout off
ops$tkyte@ORA9IR2> REM GET afiedt.buf NOLIST
ops$tkyte@ORA9IR2> set termout on
ops$tkyte@ORA9IR2> exec p
SQL AREA     471     303   2,736   2,435
SQL AREA     495     306   3,751   3,450
SQL AREA      24       3   1,015   1,015
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec p
SQL AREA     497     307   3,757   3,454
SQL AREA     497     307   4,756   4,453
SQL AREA       0       0     999     999
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA9IR2> exec p
SQL AREA     499     309   4,761   4,458
SQL AREA     499     309   5,760   5,457
SQL AREA       0       0     999     999
 
PL/SQL procedure successfully completed.

Thanks for the followup, Tom....

Mark J. Bobak, March 24, 2004 - 11:15 am UTC

My test environment was just SQL*Plus. I just ran that
query multiple times from the command line and observed the
results.

Obviously, my test was flawed. I'm going to try my test
again, as well as your script.

Also, you mention that 10g was doing more than 9i, but,
with CURSOR_SPACE_FOR_TIME=TRUE in 10g, you no longer do
any pins. With the same in 9i, you're still doing 1 pin/exec.

The analysis continues...

Thanks Tom!

-Mark

If reload counts grow, can I be sure that at least some stored statements have been flushed?

hxl, March 24, 2004 - 5:05 pm UTC

"A reload is when something we had in the library cache went away (flushed, shared pool too small perhaps) and we reloaded it -- OR the definition changed.
"

I have free space in shared pool, so shared pool too small is out.

Question 1: Say I have 500 reloads, 500 invalidations the 1st day, and 1500 reloads, 1000 invalidations the 2nd day, can I say that there are 1500 - 500 - (1000 - 500) = 500 reloads?

Question 2: Can I say every invalidation increments counts for both reloads and invalidations?

Thanks soooo much!



Tom Kyte
March 24, 2004 - 6:41 pm UTC


you can say that, yes -- seems right. reloads are "invalidated and reloaded" or "just isn't here anymore and reloaded"


every invalidation may or may not reload (what if you don't use it again)



Can you more simplified "gets" please

pjp, July 10, 2004 - 7:16 am UTC

Hi Tom,

I have not understood the meaings of "gets" what oracle documentation says :

Gets : The number of lookups for objects of the namespace.

What it wants to say, pl explain in very simple words so dump person like me can understand ? Sorry for my poor understanding

Secondly :

16:36:08 atlas@ATP1P1> select namespace from v$librarycache
16:36:25 2 /

NAMESPACE
---------------
SQL AREA
TABLE/PROCEDURE
BODY
TRIGGER
INDEX
CLUSTER
OBJECT
PIPE

8 rows selected.

What does mean SQL Area, Objects, Pipe it means here ? ( Is SQL Area means for SQL + PL/SQL ?, is Object means Index, LOB, Procedure, Queue, Sequects ... ). What is PIPE ?

Thanks for your detail answer

best regards
pjp

Tom Kyte
July 10, 2004 - 9:23 am UTC

pipe -- dbms_pipe, an SGA object.

the names are just what the names are -- don't over analyze this. sql area, that would be shared sql, in the shared pool. these are just "things" that people go after in the SGA.

GETS ?

pjp, July 10, 2004 - 9:40 am UTC

Hi Tom,

Thanks for your answer. Will you pl explain "gets" in simple language. After going thru documentaion & your threads still I have not understood very clearly.

Can I say gets means

various oracle components under different categories ( what we are getting by select namespace column from v$librarycache ) reuested by shared pool to keep in the SGA ?

best regards
pjp


Tom Kyte
July 10, 2004 - 8:46 pm UTC

"gets" -- retrievals, accesses, times used, times it went after those things.

not to "keep" in the shared pool but rather a request "get a reference to an object in the shared pool"

review

Sven, October 12, 2004 - 11:26 am UTC

Hi Tom,

I would like to reference on a question posted by:
Reviewer: Mark J. Bobak from Ann Arbor, MI

"I was chatting w/ Steve Adams, and he pointed out that when you set
session_cached_cursors, what you're really doing is caching the library cache
lock structure (which is what you create when you do a GET.) He also explained
to me that when you set cursor_space_for_time=TRUE, you're additionally caching
the library cache pin structure, (which is what you create when you do a PIN.)"

I would like to ask you does make sense to set cursor_space_for_time=TRUE (suppose we fulfill the condition for setting this parameter to true) if session_cached_cursors are set to zero?
My opinion: no.

If yes, could you please elaborate?

Thanks.




Tom Kyte
October 12, 2004 - 11:33 am UTC

if you don't NEED session_cached_cursors (because you have properly coded your application to open a statement ONCE and execute it over and over -- instead of "open, bind, execute, close" over and over) - cursor space for time is still useful.

In fact, it predates session cached cursors by many releases (so why would it have existed before session cached cursors :)


so, why do you think "no"

review

Sven, October 12, 2004 - 12:17 pm UTC

If your answer is yes, than I can think only of that cursor_space_for_time=TRUE cache both library cache
lock structure and library cache pin structure or you can avoid direct answer asking me another question...:-)

Tom Kyte
October 12, 2004 - 1:34 pm UTC

the answer was clearly "yes, it is useful"

"cursor space for time is still useful."

did not seem ambigous to me? nor did:

"In fact, it predates session cached cursors by many releases (so why would it
have existed before session cached cursors :)"

so, I'm confused by your comments.



cursor_space_for_time PREDATES session cached cursors.

IF setting session cached cursors was a prerequisite for it to be useful, it could not have PREDATED it.


<quote>

CURSOR_SPACE_FOR_TIME lets you use more space for cursors in order to save time. It affects both the shared SQL area and the client's private SQL area.

Values:

* TRUE

Shared SQL areas are kept pinned in the shared pool. As a result, shared SQL areas are not aged out of the pool as long as an open cursor references them. Because each active cursor's SQL area is present in memory, execution is faster. However, the shared SQL areas never leave memory while they are in use. Therefore, you should set this parameter to TRUE only when the shared pool is large enough to hold all open cursors simultaneously.

In addition, a setting of TRUE retains the private SQL area allocated for each cursor between executions instead of discarding it after cursor execution, saving cursor allocation and initialization time

</quote>

*as long as the open cursor references them* -- so if you

if (first_time) then open a statement

bind/execute


instead of

open/bind/execute/close

(eg: you have no use for session cached cursors) this is useful.


Hope that is more clear, but it is just saying the same thing.

review

Sven, April 07, 2005 - 1:47 pm UTC

Hi Tom,
Referencing to your previous answer, if we cache library cache lock structure (which is a library cache handle - a handle can be freed if there are no current references to it and it has not been expressly marked to be kept) using session_caches_cursors, the pin lock structure (which is library cache object - library cache handle point to LCO) and all subheaps allocated to him are still subject of possible aging.
In case where for cached library cache lock handle, pin structure has been aged out, session accessing this object will have to generate the execution plan again - basically do the hard parse? Is this correct? Probably not :-) and I appreciate your answer.

Thanks,

Sven

Tom Kyte
April 07, 2005 - 4:17 pm UTC

if the true cursor has been aged out, we would have a reload (or a totally new "i'm seeing this for the first time" event) causing a hard parse.

as long as the plan is in the pool it would be a soft parse (or a softer soft parse)...

review

sven, April 07, 2005 - 4:29 pm UTC

What do you mean under term "true cursor"?

Tom Kyte
April 07, 2005 - 4:44 pm UTC

the stuff in the shared pool, the "real plan, the reusable stuff that we all point to"

review

sven, April 07, 2005 - 5:02 pm UTC

SESSION_CACHED_CURSORS (from documentation)
"Oracle checks the library cache to determine whether more than three parse
requests have been issued on a given statement. If so, then Oracle assumes that the
session cursor associated with the statement should be cached and moves the cursor
into the session cursor cache. Subsequent requests to parse that SQL statement by
the same session then find the cursor in the session cursor cache."

i.e. the library cache lock handle is cached in local session cache but library cache object is not in local cache but in a shared library pool. So, what we gain in this case with local session caching is only less latches on a library cache hash chains (double linked lists) and if we are lucky that our lib.cache object (which contains execution plans) is not aged out, we get soft parse, otherwise hard parse.
So, with SESSION_CACHED_CURSORS we reduce only load on a lib. cache latches. Is this correct?

Tom Kyte
April 07, 2005 - 5:09 pm UTC

correct, that is why I've dubbed it the "softer soft parse"

Very Nice

Gaurav C. Pandey, June 14, 2005 - 9:26 am UTC

Hi Tom,

After reading this material now i can say i have a clear understanding of gets and pins. But Tom i have 2 questions. Can you explain it please...

1) Differnce between Request_Misses and Request_failures column in V$Shared_Pool_Reserved?
2) Whats the use of Aborted_Request_Threshold procedure in DBMS_Shared_Pool

Thanks in advance.

library

abc, June 24, 2005 - 5:37 pm UTC

Cache Sizes
~~~~~~~~~~~
db_block_buffers: 35000 log_buffer: 163840
db_block_size: 8192 shared_pool_size: 1073741824

Load Profile
~~~~~~~~~~~~ Per Second Per Transaction
--------------- ---------------
Redo size: 97.84 536.21
Logical reads: 5,749.45 31,508.41
Block changes: 0.70 3.86
Physical reads: 3.78 20.72
Physical writes: 3.85 21.11
User calls: 27.80 152.34
Parses: 4.44 24.34
Hard parses: 0.01 0.05
Sorts: 1.24 6.77
Logons: 0.26 1.44
Executes: 26.97 147.81
Transactions: 0.18

% Blocks changed per Read: 0.01 Recursive Call %: 32.85
Rollback per transaction %: 51.67 Rows per Sort: 56.28

Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 99.93 In-memory Sort %: 99.87
Library Hit %: 99.98 Soft Parse %: 99.80
Execute to Parse %: 83.53 Latch Hit %: 99.90
Parse CPU to Parse Elapsd %: 93.10 % Non-Parse CPU: 99.90

Shared Pool Statistics Begin End
------ ------
Memory Usage %: 45.43 45.41
% SQL with executions>1: 8.41 8.30
% Memory for SQL w/exec>1: 23.04 22.92

Top 5 Wait Events
~~~~~~~~~~~~~~~~~ Wait % Total
Event Waits Time (cs) Wt Time
-------------------------------------------- ------------ ------------ -------
library cache pin 6,548 1,960,093 99.21
PL/SQL lock timer 26 14,341 .73
control file parallel write 588 551 .03
SQL*Net more data to client 13,310 309 .02
direct path write 403 112 .01
-------------------------------------------------------------


Please suggest on above

V$LIBRARY_CACHE_MEMORY

Arindam Mukherjee, August 13, 2006 - 1:54 am UTC 

Respected Mr. Tom,

 I request you to take a look at the chapter 24 – Oracle 9i Database performance tuning Guide and Reference. (Release 2 (9.2) March 2002 Part No. A96533-01)

Query – 1
-----------

Example 24–2 Finding Objects with Large Number of Loads

SELECT owner, name sharable_mem, kept, loads
FROM V$DB_OBJECT_CACHE
WHERE loads > 1
OR invalidations > 0
ORDER BY loads DESC;

The above statement can’t be executed as the column “invalidations” does not exist in the view “V$DB_OBJECT_CACHE”.

SQL> desc v$db_object_cache
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                              VARCHAR2(64)
 NAME                                               VARCHAR2(1000)
 DB_LINK                                            VARCHAR2(64)
 NAMESPACE                                          VARCHAR2(28)
 TYPE                                               VARCHAR2(28)
 SHARABLE_MEM                                       NUMBER
 LOADS                                              NUMBER
 EXECUTIONS                                         NUMBER
 LOCKS                                              NUMBER
 PINS                                               NUMBER
 KEPT                                               VARCHAR2(3)
 CHILD_LATCH                                        NUMBER


Query – 2
-----------

In that document and the same chapter - 24, the column names and its descriptions of the view “V$LIBRARY_CACHE_MEMORY” are quite different from what I am getting at my site.

SQL> desc v$library_cache_memory
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 LC_NAMESPACE                                       VARCHAR2(15)
 LC_INUSE_MEMORY_OBJECTS                            NUMBER
 LC_INUSE_MEMORY_SIZE                               NUMBER
 LC_FREEABLE_MEMORY_OBJECTS                         NUMBER
 LC_FREEABLE_MEMORY_SIZE                            NUMBER
 
Hope it’s a problem due to “copy&paste” from somewhere, although I am not pretty sure. Could you kindly advise me when I would use this view during tuning the application / system?

Tom Kyte
August 13, 2006 - 9:21 am UTC

it looks like they jumped the gun on the db object cache (that column is there in 10g) and messed up the library_cache memory description all together.

I'll file a doc bug.

Namspace in library cache

Sri, February 07, 2008 - 4:10 pm UTC

Tom,

With reference to eralier questions on the different namespaces in v$librarycache, what exactly is index name space doing in v$librarycache? Are not index definitions stored in rowcache ? What has librarycache to do with index definitions.
Tom Kyte
February 07, 2008 - 4:25 pm UTC

this sort of minutiae...

but why pick on index? why not table, why not procedure - they are all stored in the dictionary too :)

...


in general, v$rowcache is our cache of rows from the dictionary describing things.

the librarycache is utilization of that stuff.

Reloads clarification

A reader, October 06, 2008 - 5:46 pm UTC

Tom,
thanks for the helpful information.

Couple questions:

1) just want to close out on my understanding of Reloads. Please confirm the following:

- Does a reload occur in situations where the Heap 0 is still available in cache and the Heap 6(execution plan) had to be loaded again?
If so, following that thought - if the heap 0 is also flushed out of cache(due to age-out), then the next request for the same cursor, would it be considered a "load" (and not reload). The reason I say is that the cache doesn't have any information of its past existence in shared pool, if the Heap0 and heap6 are flushed out.


2) Can you think of any reason as to why child_number=0 would have non-Y values in the v$sql_shared_cursor. Since, its the 1st incarnation of the parent, it has nothing to compare itself against, so am puzzled as to why not all columns are Y ?

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library