Recent site activity

Wait Event Docs‎ > ‎

Oracle: library cache








asdf

Library cache latches


Protects changes in Library Cache
Library Locks are not atomic
Thus need library cache latch
Broken out into
  • library cache pin allocation       
  • library cache lock allocation       
  • library cache lock              
  • library cache                   
  • library cache pin               
  • library cache load lock       
causes 
  • Excessive Hard Parsing
    • Not Sharing SQL – use of Literal Values
    • Shared Pool too small
  • Too many invalidations
  • Excessive Soft Parsing

Hard Parsing
Not Sharing SQL 
Bind Variables
Select * from dual where dummy = :var;
Cursor_Sharing
Cursor_sharing = Force
Oracle replaces variables with bind variables
Defaults to Exact


select 
     plan_hash_value,
     count(*)
from 
     v$sqlstats
where plan_hash_value > 0
group by plan_hash_value
having count(*) > 5
order by count(*)
/


SQL> @dups

PLAN_HASH_VALUE        CNT
--------------- ----------
      272002086        520

select sql_text 
  from v$sql 
where 
      plan_hash_value =  272002086  
      and rownum < 10;

SQL_TEXT
-----------------------------------------------
SELECT * FROM dual WHERE dummy=-634891633
SELECT * FROM dual WHERE dummy=1987751014
SELECT * FROM dual WHERE dummy=25965276
SELECT * FROM dual WHERE dummy=32449789
SELECT * FROM dual WHERE dummy=-364632215
SELECT * FROM dual WHERE dummy=-34273351
SELECT * FROM dual WHERE dummy=-699712683
SELECT * FROM dual WHERE dummy=1752437199
SELECT * FROM dual WHERE dummy=-1081512404
Shard pool too small

select namespace, reloads from v$librarycache;

NAMESPACE          RELOADS 
--------------- ----------
SQL AREA               367
TABLE/PROCEDURE        592

Reloads means Cursor heaps were kicked out implying shared_pool too small


Invalidations

select namespace,
invalidations 
from v$librarycache;


NAMESPACE       INVALIDATIONS
--------------- -------------
SQL AREA                 6065


Changes in dependent objects invalidate cursor
      FOR i IN 1..3000 LOOP
            l_cursor:=dbms_sql.open_cursor;
            dbms_sql.parse(l_cursor,
             'SELECT * FROM toto',dbms_sql.native);
            execute immediate 'analyze table toto compute statistics';
            dbms_sql.close_cursor(l_cursor);
      END LOOP;  
  
Soft Parsing
    
FOR i IN 1..30000 LOOP
            l_cursor:=dbms_sql.open_cursor;
            dbms_sql.parse(l_cursor,'SELECT * FROM dual’,dbms_sql.native);
            dbms_sql.close_cursor(l_cursor);
      END LOOP;


Session_cached_cursors=0

Latch                              Gets    
-----                              ----    
library cache lock              120,028         
library cache                   180,074         
library cache pin                60,048 
Session_cached_cursors=20

library cache lock                    4         
library cache                    60,061         
library cache pin                60,048 


FOR i IN 1..30000 LOOP
           rc:=dbms_sql.execute(l_cursor);
           IF DBMS_SQL.FETCH_ROWS (l_cursor) < 0 THEN
                 DBMS_SQL.COLUMN_VALUE (l_cursor, 1, cnt);
          end if;
End loop;


Cursor_space_for_time=false

Latch                              Gets    
-----                              ----    
library cache lock                   35
library cache                    60,096
library cache pin                60,044

Cursor_space_for_time=true

library cache lock                   30
library cache                        85
library cache pin                    42




latch: library cache 

library cache latch - usually indicates too much hard parsing

latch: library cache lock          

latch: library cache pin   

latch: shared pool latch


Library Cache Locks and Pins


Contention when Sessions try to
  • Load/compile same SQL
  • Compile package others are running
Locks and Pins are usually in share mode unless modifications are being made

Object dependency
    • lock in Null
Cursor execution
    • lock in null
    • Pin in Share
Cursor compilation
    • Lock exclusive
    • Pin exclusive

library cache load lock 

multiple users waiting for same SQL to be compiled

library cache lock 

- user trying to compile code and can't access it in exclusive access

library cache pin

asdf
Find the blocker
select
       waiter.sid   waiter,
       waiter.event wevent,
       to_char(blocker_event.sid)||','||to_char(blocker_session.serial#) blocker,
       substr(decode(blocker_event.wait_time,
                     0, blocker_event.event,
                    'ON CPU'),1,30) bevent
from
       x$kglpn p,
       gv$session      blocker_session,
       gv$session_wait waiter,
       gv$session_wait blocker_event
where
          p.kglpnuse=blocker_session.saddr
   and p.kglpnhdl=waiter.p1raw
   and waiter.event in ( 'library cache pin' , 
                                      'library cache lock' ,
                                      'library cache load lock')
   and blocker_event.sid=blocker_session.sid
   and waiter.sid != blocker_event.sid
order by
      waiter.p1raw,waiter.sid;

result
WAITER WLOCKP1          WEVENT              BLOCKER BEVENT
------- ---------------- ----------------- --------- -----------------
129 00000003B76AB620 library cache pin 135,15534 PL/SQL lock timer
asdf

Mutexes in Library Cache


10gR2 new library cache latch mechanism that replace latches and takes less memory
From Tanel Pode, On 32bit linux installation a mutex was 28 bytes in size, regular latch structure was 110 bytes. 
Takes less instructions to mutex get is about 30-35 instructions latch get is 150-200 instructions
Less contention than latches, because there can be more mutexes
Mutexes stored in each child cursor
Turn off with _kks_use_mutex_pin=false , unsupported

Instead of "library cache pin" mutexes are used under "cursor: pin X", "cursor: pin S" and "cursor: pin S wait on X"
The field blocking_session in  v$session  is not filled in when there are mutex waits on 10.2 at least.
cursor: pin ( S, S wait on X)  means re-executions of the same cursors
Instead of latching for  a pin on execute we use a shared mutex
If can’t get the mutex spin.
Turning off should increase ‘library cache pin’ events
_kks_use_mutex_pin=false
Contention should be worse for latches because latches cover multiple objects
cursor_space_for_time  not needed if mutexes are used 

cursor: pin S

Pin cursor for execute, and cursor is currently being examined by another session


"A session waits on this event when it wants to update a shared mutex pin and another session is currently in the process of updating a shared mutex pin for the same cursor object.  This wait event should rarely be seen because a shared mutex pin update is very fast.

Wait Time: Microseconds

ParameterDescription
P1Hash value of cursor
P2Mutex value (top 2 bytes contains SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0)
P3Mutex where (an internal code locator) OR'd with Mutex Sleeps

            "

cursor: pin X

When attempting to rebuild a cursor. This event should not be seen typically, because if a cursor is currently being used, and it needs to be rebuilt, another cursor will be create

"A session waits on this event when it is requesting an exclusive mutex pin for a cursor object and it must wait because the resource is busy. The mutex pin for a cursor object can be busy either because a session is already holding it exclusive, or there are one or more sessions which are holding shared mutex pin(s). The exclusive waiter must wait until all holders of the pin for that cursor object have released it, before it can be granted.

Wait Time:

ParameterDescription
P1Hash value of cursor
P2Mutex value (top 2 bytes contains SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0)
P3Mutex where (an internal code locator) OR'd with Mutex Sleeps


cursor: mutex S  

examining a parent (when looking for a cursor to execute)
examining a cursor’s statistics (usually querying v$sqlstats ) 

"A session waits on this event when it is requesting a mutex in shared mode, when another session is currently holding a this mutex in exclusive mode on the same cursor object."
ParameterDescription
P1Hash value of cursor
P2Mutex value (top 2 bytes contain SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0)
P3Mutex where (an internal code locator) OR'd with Mutex Sleeps


cursor: mutex X    

building a new cursor under a parent
building and updating cursor-related statistics
capture SQL bind data

"The session requests the mutex for a cursor object in exclusive mode, and it must wait because the resource is busy. The mutex is busy because either the mutex is being held in exclusive mode by another session or the mutex is being held shared by one or more sessions. The existing mutex holder(s) must release the mutex before the mutex can be granted exclusively.

ParameterDescription
P1Hash value of cursor
P2Mutex value (top 2 bytes contain SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0)
P3Mutex where (an internal code locator) OR'd with Mutex Sleeps

"

cursor: pin S wait on X    


Pinning a cursor for execute 
Bug on 10.2.0.3 typically with DBMS_STATS
Metalink Note:401435.1, Note:5907779.8, bug 5907779


"A session waits for this event when it is requesting a shared mutex pin and another session is holding an exclusive mutex pin on the same cursor object.

Wait Time: Microseconds

ParameterDescription
P1Hash value of cursor
P2Mutex value (top 2 bytes contains SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0)
P3Mutex where (an internal code locator) OR'd with Mutex Sleep
"
From Connie Green (have to check this reference):
"An example of the ‘cursor: pin S wait on X’ event.  
If a session is waiting on the wait event ‘cursor: pin S wait on X’, the session is most likely trying to execute a cursor (pin S), and must wait as another session (who is most likely parsing the cursor) has it pinned X (wait on X) 
v$session.p1 can be used to compare with v$mutex_sleep_history.mutex_identifier
Example v$session data (64 bit platform), from an instance where the mutex holder was hung due to a bug not related to mutexes, causing requestors to back up behind the holder:
select p1, p2raw, count(*)
  from v$session
 where event = ‘cursor: pin S wait on X’
   and wait_time = 0
 group by p1, p2;
                 P1  P2RAW            COUNT(*)
         ----------  ---------------- --------
         2700259466  0000139700000000        9
         <Mutex Id>  <   SId><RefCnt>

As you can see, 9 sessions were waiting for Session Id 0x1397, which was holding exclusive  the Mutex with the Id 2700259466.  The Ref Count is zero, as we would expect.  The 9 sessions are waiting to execute the cursor protected by mutex Id 2700259466.  If latches had been in use instead of mutexes, it is likely this bug would have had a greater impact, as there would have been many more requestors backed up on a latch which protects many objects, than a mutex that in this case protects just one cursor.
To find the blocking session, use the top bytes of v$session.p2raw e.g. the top bytes of p2raw is the blocker 0x00001397 which when converted to decimal, is session Id 5015.

   

Row Cache


latch: row cache objects

row cache lock 

Depends on which cache the lock is happening on
select
       ash.session_id sid,
       ash.blocking_session bsid,
       nvl(o.object_name,to_char(CURRENT_OBJ#)) obj,
       o.object_type otype,
       CURRENT_FILE# filen,
       CURRENT_BLOCK# blockn,
       ash.SQL_ID,
       nvl(rc.name,to_char(ash.p3)) row_cache
from v$active_session_history ash,
     ( select cache#, parameter name from v$rowcache ) rc,
      all_objects o
where event='row cache lock'
   and rc.cache#(+)=ash.p1
   and o.object_id (+)= ash.CURRENT_OBJ#
   and ash.session_state='WAITING'
   and ash.sample_time > sysdate - &minutes/(60*24)
Order by sample_time

SID BSID OBJ  OTYPE FILEN BLOCKN  SQL_ID        ROW_CACHE
--- ---- ---- ----- ----- ------- ------------- ------------
143 131    -1           0       0 41y8w0sfqb61m dc_sequences
134 131    -1           0       0               dc_sequences
151        -1           0       0               dc_sequences
134 151    -1           0       0               dc_sequences
131 151    -1           0       0               dc_sequences
151        -1           0       0               dc_sequences

select cache#, parameter name from v$rowcache order by cache#;

    CACHE# NAME
---------- --------------------------------
         0 dc_tablespaces
         1 dc_free_extents
         2 dc_segments
         3 dc_rollback_segments
         4 dc_used_extents
         5 dc_tablespace_quotas
         6 dc_files
         7 dc_users
         7 dc_users
         7 dc_users
         7 dc_users
         8 dc_objects
         8 dc_object_grants
         9 dc_qmc_cache_entries
        10 dc_usernames
        11 dc_object_ids
        12 dc_constraints
        13 dc_sequences
        14 dc_profiles
        15 dc_database_links
        16 dc_histogram_data
        16 dc_histogram_data
        16 dc_histogram_defs
        17 dc_global_oids
        18 dc_outlines
        19 dc_table_scns
        19 dc_partition_scns
        20 rule_info
        21 rule_or_piece
        21 rule_fast_operators
        22 dc_awr_control
        23 dc_qmc_ldap_cache_entries
        24 outstanding_alerts
        25 dc_hintsets
        26 global database name
        27 qmtmrcin_cache_entries
        28 qmtmrctn_cache_entries
        29 qmtmrcip_cache_entries
        30 qmtmrctp_cache_entries
        31 qmtmrciq_cache_entries
        32 qmtmrctq_cache_entries
        33 kqlsubheap_object

Example 1) dropping an object while another session tries to get information on the object gets dc_object_ids lock waits
Example 2) having the sequence cache too small and a lot of contention on sequences gets dc_sequences  lock waits
if on Sequence cache, then find the sequence and increase number cached

To find any sequences that have less than default cache size see, from Tim Gorman:

select sequence_owner, sequence_name, cache_size, order_flag, last_number
from   dba_sequences
where  (order_flag = 'Y' or cache_size < 20)
and    last_number > 10000
order by last_number desc;

The reason for ordering the query by LAST_NUMBER is to try to get a sense for the most heavily-used sequences; eyeballing the results should make it pretty obvious which sequences need to be cached and which just started with high values to begin with.  If this query returns something of use, then I generally modify the query above to instead generate "ALTER SEQUENCE ... CACHE 100;" commands.

I'm not recommending altering the AUDSES$ sequence (I'm not sure if there are problems with that, but I'd be inclined to leave it alone), but instead relieve the "pressure" on sequence-generation by caching all of the under-cached application sequences.

AUDSES has nothing to do with having database auditing enabled -- it is an identifier populated to uniquely identify non-SYSDBA sessions for use by the database audit trail, should database auditing be enabled. 
Comments