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(plan_hash_value)

from v$sql

group by plan_hash_value,

order by count(plan_hash_value)

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

Frc:=dbms_sql.execute(l_cursor);

FIF DBMS_SQL.FETCH_ROWS (l_cursor) < 0 THEN

FDBMS_SQL.COLUMN_VALUE (l_cursor, 1, cnt);

Fend if;

FEnd 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: shared pool latch

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

check out

http://oracleprof.blogspot.com/2009/04/apppeople-called-that-big-app-is-not.html

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14237/waitevents003.htm"

"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

Parameter	Description
P1	Hash value of cursor
P2	Mutex value (top 2 bytes contains SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0)
P3	Mutex 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

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14237/waitevents003.htm:

"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:

Parameter	Description
P1	Hash value of cursor
P2	Mutex value (top 2 bytes contains SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0)
P3	Mutex 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 )

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14237/waitevents003.htm:

"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."

Parameter	Description
P1	Hash value of cursor
P2	Mutex value (top 2 bytes contain SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0)
P3	Mutex 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

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14237/waitevents003.htm:

"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.

Parameter	Description
P1	Hash value of cursor
P2	Mutex value (top 2 bytes contain SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0)
P3	Mutex 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

check out

http://oracleprof.blogspot.com/2009/04/mutex-reloaded.html

http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14237/waitevents003.htm:

"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

Parameter	Description
P1	Hash value of cursor
P2	Mutex value (top 2 bytes contains SID holding mutex in exclusive mode, and bottom two bytes usually hold the value 0)
P3	Mutex 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

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