Transcript Document

Shared Pool Waits
#.2
Shared Pool Waits
1.
2.
3.
4.
5.
6.
Latch: Library Cache
Latch: Shared Pool Latch
Library Cache Pin
Library Cache Lock
Library Cache Load Lock
Row Cache Lock
Copyright 2006 Kyle Hailey
#.3
Library Cache
Lib
Cache
Copyright 2006 Kyle Hailey
Shared Pool Structure
Hash
Table
SQL statements are hashed
On their text. The resulting
Hash is used to find the appropriate
bucket, which is searched for the
Compiled SQL. If it’s not there,
then we parse it.
handle
handle
handle
handle
handle
handle
handle
handle
handle
handle
handle
handle
handle
Copyright 2006 Kyle Hailey
#.4
#.5
Shared Pool Latch
 Contention can arise when too many
sessions are hard parsing and looking for
space in the shared pool.
 The shared pool latch protects the structure
containing memory chunks
 Protects Space Allocation in the Shared Pool
 Shared Pool latch make sure two users don’t
get same chunk of memory
Copyright 2006 Kyle Hailey
#.6
Shared Pool Latch
Get library cache latch
Get shared pool latch
Search right bucket
Find best fit
If lists get long, search gets long
_kghdsidx_count number of shared pool latches
Not supported to change, but increasing it can increase
ORA-4031s if shared pool Is not increased as well
Copyright 2006 Kyle Hailey
Shared Pool Latch
Shared Pool Latch covers changes in the lists
of free memory chunks
Shared Pool Free Space
Copyright 2006 Kyle Hailey
#.7
#.8
Shared Pool Latch 8.1.6
Bucket
0
1
2
3
4
5
6
7
8
9
10
sizes
< 80 bytes
< 144
< 272
< 528
< 1040
< 2064
< 4112
< 8208
< 16400
< 32784
bigger
Shared Pool pre 8.1.6
Memory Chunk Buckets
Copyright 2006 Kyle Hailey
#.9
Shared Pool Latch
Shared Pool Latch
Shared Pool Free Space 8.1.6+
Before 8.1.6, oversizing the shared pool could be
a problem, after 8.1.6 should be fine
Copyright 2006 Kyle Hailey
#.10
Shared Pool
Shared Pool Latch
Memory Chunk Buckets 8.1.6 and up
0
16 bytes
1
20 bytes
…
(0-198 only have one chunk size in bucket)
198
808 bytes
199
812 to 872
….
(199-248 only have 16 possible chunk sizes per bucket)
248
3948 - 4008
249
4012 - 4104
250
4108 - 8204
251
8204 - 16392
252
16396 - 32776
253
32780 - 65544
254
bigger Copyright 2006 Kyle Hailey
#.11
library cache pin and locks
 Locks control access, protects handle
 Pins guarantee coherency, protects heaps
 To Access to a cursor

Lock handle


Pin


pin
lock
pin
lock
handle
Locking is the way of locating
Pinning loads any necessary heaps
Guaranteed to stay in memory until pin is released
handle
handle
Heap 1
Child cursor 1
Heap 0
Copyright 2006 Kyle HaileyHeap 6
#.12
library cache lock 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
Copyright 2006 Kyle Hailey
#.13
Lib Cache Locks and Pins
 Object dependency
 Library
cache lock in Null
 Cursor execution
 Library
lock in null
 Pin in Share
 Cursor compilation
 Lock
exclusive
 Pin exclusive
Copyright 2006 Kyle Hailey
#.14
library cache lock
 P1 = address of object
 P2 = address of lock
 P3 = mode | namespace
 See
x$kgllk
 dba_kgllock

pin
lock
pin
lock
handle
handle
handle
Copyright 2006 Kyle Hailey
#.15
library cache pin




P1 = address of object
P2 = address of lock
P3 = Mode | Namespace
See
dba_kgllock
 x$kglpn

pin
lock
pin
lock
handle
handle
handle
Copyright 2006 Kyle Hailey
#.16
Library cache lock & pin
select
w.sid,
kglob.KGLNAOBJ
from
x$kglob kglob,
v$session_wait w
where
kglob.KGLHDADR= w.P1RAW and
event like '%library%';
Copyright 2006 Kyle Hailey
#.17
dba_kgllock
For library cache pins and lock waits
 Session_wait.p1raw = x$kglpn.kgllkhdl
 dba_kgllock.id1
 x$kgllk.kgllkhdl
Copyright 2006 Kyle Hailey
Lib Cache Lock :
blockers and waiters
#.18
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 WLOCKP1
WEVENT
BLOCKER BEVENT
waiter.p1raw,waiter.sid;
------- ---------------- ----------------- --------- ----------------129 00000003B76AB620 library cache pin 135,15534 PL/SQL lock timer
Copyright 2006 Kyle Hailey
#.19
Solutions
 Have only one Session compile the same
cursor at a time
 Avoid compiling while executing
 Waits – find “competing” Sessions
Copyright 2006 Kyle Hailey
#.20
library cache load lock
Waiting For a Reload by another Session
 P1 = object address
 P2 = lock address
 P3 = 100*mask+namespace
Copyright 2006 Kyle Hailey
#.21
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

Copyright 2006 Kyle Hailey
Library Cache
Hash Table
pin
lock
pin
lock
handle
handle
Find and Lock
Pin (and Load)
Copyright 2006 Kyle Hailey
#.22
Library Cache Structures
Hash Table
waiters
pin
lock
pin
lock
pin
Library Cache Latch
lock
holders
pin
lock
handle
Handle
handle
handle
Cursor(0)
flags
Cursor (0)
pin
lock
pin
lock
Heap 1
Heap 0
Child cursor 1
Child cursor
2
Child cursor
3
Copyright
2006 Kyle Hailey
Heap 6
#.23
#.24
Library Cache Latch Contention
 Excessive Hard Parsing
Sharing SQL – use of Literal Values
 Shared Pool too small
 Too many invalidations
 Not
 Excessive Soft Parsing
Copyright 2006 Kyle Hailey
#.25
Sharing SQL & Literals
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
Copyright 2006 Kyle Hailey
#.26
Sharing SQL & Literals
select sql_text
from v$sql
where
plan_hash_value = 272002086
and rownum < 10;
SQL> @dups
PLAN_HASH_VALUE CNT
--------------- ---272002086 520
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
Copyright 2006 Kyle Hailey
#.27
Cursor Sharing
Bind Variables
 Select
* from dual where dummy = :var;
Cursor_Sharing
 Cursor_sharing
= Force
Oracle replaces variables with bind variables
 Defaults
to Exact
Copyright 2006 Kyle Hailey
#.28
Shared Pool too Small
SQL> 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
Copyright 2006 Kyle Hailey
Invalidations
SQL>
select namespace,
invalidations
from v$librarycache;
NAMESPACE
INVALIDATIONS
--------------- ------------SQL AREA
6065
 Changes in dependent objects invalidate
cursor
FOR
i IN
1..3000
LOOP
FOR
i IN
1..3000
LOOP
l_cursor:=dbms_sql.open_cursor;
l_cursor:=dbms_sql.open_cursor;
dbms_sql.parse(l_cursor,
dbms_sql.parse(l_cursor,
'SELECT
* FROM
toto',dbms_sql.native);
'SELECT
* FROM
toto',dbms_sql.native);
execute
immediate
'analyze
table
toto
compute
statistics';
execute
immediate
'analyze
table
toto
compute
statistics';
dbms_sql.close_cursor(l_cursor);
dbms_sql.close_cursor(l_cursor);
END
LOOP;
END
LOOP;
Copyright 2006 Kyle Hailey
#.29
#.30
Soft Parsing
Cursor Memory
= Latch
lock
pin
lock
pin
Execute 1
Execute 2
lock
lock
pin
Execute 3
Re-Executing a Cursor
1.
Libray Cache latch
2.
Locks
3.
Pins Copyright 2006 Kyle Hailey
lock
pin
Execute 4
pin
Execute 5
Session Cached Cursors
Cursor Memory
= Latch
lock
pin
pin
Execute 1
pin
Execute 2
Execute 3
pin
Execute 4
pin
Execute 5
Session_cached_cursor:
If Opening/Closing keeps locked in Memory
Copyright 2006 Kyle Hailey
#.31
Session Cached Cursors
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
----library cache lock
library cache
library cache pin
Gets
---120,028
180,074
60,048
Session_cached_cursors=20
library cache lock
library cache
library cache pin
Copyright 2006 Kyle Hailey
4
60,061
60,048
#.32
Cursor Space for Time
= Latch
Cursor Memory
Close
Cursor
lock
Open
Cursor
pin
Execute 1
#.33
Execute 2
Execute 3
Execute 4
Execute 5
Cursor_space_for_time=true :
if open and re-executing – keeps cursor pinned
(Cursor already locked because cursor is kept open)
Copyright 2006 Kyle Hailey
#.34
Cursor Space For Time
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
----library cache lock
library cache
library cache pin
Gets
---35
60,096
60,044
Cursor_space_for_time=true
library cache lock
library cache
library cache pin
Copyright 2006 Kyle Hailey
30
85
42
#.35
Efficient Lock and Pinning
 Reduce use of latches
 Improve throughput
 Improve Concurrency ***
Copyright 2006 Kyle Hailey
Cursor Sharing
pin
lock
pin
lock
Handle
handle
handle
handle
handle
select * from (
select sql_id, count(*) cnt
from V$SQL_SHARED_CURSOR
group by sql_id )
where cnt > 5
order by cnt;
Cursor(0)
flags
Cursor (0)
Heap 1
Child cursor 2
Heap 0
Heap 6
Heap 1
Child cursor 3
Heap 0
Heap 6
Heap 1
Child cursor 4
#.36
Heap 0
Heap 6
Copyright 2006 Kyle Hailey
#.37
V$SQL_SHARED_CURSOR
 10gR2, 53 reasons why cursors aren’t shared
 If using “cursor_sharing=similar” might not
work – bugs
 Examples


OPTIMIZER_MODE_MISMATCH , see V$SQL_OPTIMIZER_ENV
STATS_ROW_MISMATCH, could be sql trace
AUTH_CHECK_MISMATCH
TRANSLATION_MISMATCH – different object in SQL stmt
 BIND_MISMATCH – bind variable different sizes
 LANGUAGE_MISMATCH – NLS Language

http://www.juliandyke.com/Presentations/Presentations.html#LibraryCacheInternals
Copyright 2006 Kyle Hailey
V$SQL_SHARED_CURSOR
UNBOUND_CURSOR
SQL_TYPE_MISMATCH
OPTIMIZER_MISMATCH
OUTLINE_MISMATCH
STATS_ROW_MISMATCH
LITERAL_MISMATCH
SEC_DEPTH_MISMATCH
EXPLAIN_PLAN_CURSOR
BUFFERED_DML_MISMATCH
PDML_ENV_MISMATCH
INST_DRTLD_MISMATCH
SLAVE_QC_MISMATCH
TYPECHECK_MISMATCH
AUTH_CHECK_MISMATCH
BIND_MISMATCH
DESCRIBE_MISMATCH
LANGUAGE_MISMATCH
TRANSLATION_MISMATCH
ROW_LEVEL_SEC_MISMATCH
INSUFF_PRIVS
INSUFF_PRIVS_REM
REMOTE_TRANS_MISMATCH
LOGMINER_SESSION_MISMATCH
INCOMP_LTRL_MISMATCH
OVERLAP_TIME_MISMATCH
SQL_REDIRECT_MISMATCH
MV_QUERY_GEN_MISMATCH
USER_BIND_PEEK_MISMATCH
TYPCHK_DEP_MISMATCH
NO_TRIGGER_MISMATCH
FLASHBACK_CURSOR
ANYDATA_TRANSFORMATION
INCOMPLETE_CURSOR
TOP_LEVEL_RPI_CURSOR
DIFFERENT_LONG_LENGTH
LOGICAL_STANDBY_APPLY
DIFF_CALL_DURN
BIND_UACS_DIFF
PLSQL_CMP_SWITCHS_DIFF
CURSOR_PARTS_MISMATCH
STB_OBJECT_MISMATCH
ROW_SHIP_MISMATCH
PQ_SLAVE_MISMATCH
TOP_LEVEL_DDL_MISMATCH
MULTI_PX_MISMATCH
BIND_PEEKED_PQ_MISMATCH
MV_REWRITE_MISMATCH
ROLL_INVALID_MISMATCH
OPTIMIZER_MODE_MISMATCH
PX_MISMATCH
MV_STALEOBJ_MISMATCH
FLASHBACK_TABLE_MISMATCH
LITREP_COMP_MISMATCH
Copyright 2006 Kyle Hailey
#.38
#.39
10g : Mutex
 Mutex

Mutual exclusion object
 Similar to a latch, prevents
Deallocation while someone is using it
 Read/write while someone else is modifying

 Different from latch
Every object can have it’s own mutex
 A mutex can cover multiple objects
 Usually dynamically allocated along with structure they
protect
 Can be stored in the structure, thus destroying structure
deletes the mutex

Copyright 2006 Kyle Hailey
Mutexes
 10gR2 new library cache latch mechanism
 Replace latches
 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
Copyright 2006 Kyle Hailey
#.40
#.41
Mutex Views and Stats
 Views
V$mutex_sleep
 V$mutex_sleep_history

 Waits
Cursor:mutex X
 Cursor:mutex S
 Cursor:pin X
 Cursor:pin S
 Cursor:pin S wait on X



Bug on 10.2.0.3 typically with DBMS_STATS
Metalink Note:401435.1, Note:5907779.8, bug 5907779
Copyright 2006 Kyle Hailey
#.42
10.2g “cursor: pin S”
 cursor: pin S
 re-executions
of the same cursors
 _kks_use_mutex_pin=true
 Instead of latching for execute pin we use a
shared mutex
 If can’t get the mutex spin
 Turning off should increase
 Library
cache pin events
Copyright 2006 Kyle Hailey
#.43
row cache lock : args
 P1 = cache#
 P2 = Lock Mode Held
 P3 = Lock Mode Requested
select parameter as “name”
from v$rowcache
where cache# = P1;
Copyright 2006 Kyle Hailey
#.44
Row Cache Lock - Statspack
Top 5 Timed Events
Avg %Total
~~~~~~~~~~~~~~~~~~
wait
Call
Event
Waits
Time (s)
(ms)
Time
---------------------------------- ----------- ------ -----row cache lock
11,925
57
5
53.8
CPU time
26
24.1
log file parallel write
1,828
20
11
18.7
log file sequential read
15
1
66
.9
control file parallel write
31
1
24
.7
Copyright 2006 Kyle Hailey
#.45
Row Cache Lock – Statspack
Dictionary Cache Stats
->"Pct Misses"
DB/Inst: linux3 Snaps: 68-69
should be very low (<2% in most cases)
->"Final Usage" is the number of cache entries being
Get
Pct Scan Pct
Mod Final
Cache
Requests
Miss Reqs Miss
Reqs Usage
------------- -------- ------ ---- ---- ------ ----dc_awr_control
1
0.0
0
0
1
dc_object_ids
10
0.0
0
0
650
dc_objects
28
0.0
0
3
960
dc_profiles
6
0.0
0
0
1
dc_sequences
12,002
0.0
0
12,002
4
dc_tablespaces
31
0.0
0
0
10
dc_usernames
14
0.0
0
0
11
dc_users
262
0.0
0
0
22
Copyright 2006 Kyle Hailey
Row Cache Lock - ASH
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
SID BSID OBJ OTYPE FILEN BLOCKN SQL_ID
where event='row
cache
lock'----- ----- ------- --------------- ------143 131
-1
0
0 41y8w0sfqb61m
and rc.cache#(+)=ash.p1
134 131(+)= ash.CURRENT_OBJ#
-1
0
0
and o.object_id
151
-1
0
0
and ash.session_state='WAITING'
134 151
0
0
and ash.sample_time
>-1
sysdate - &minutes/(60*24)
131
151
-1
0
0
Order by
sample_time
151
-1
0
0
ROW_CACHE
-----------dc_sequences
dc_sequences
dc_sequences
dc_sequences
dc_sequences
dc_sequences
#.46
#.47
Row Cache Lock
 Select seq.next_val
 Sequence cache set to 1
 Default sequence cache is 20
SQL> @sqltext
Enter value for 1: 41y8w0sfqb61m
SQL_FULLTEXT
----------------------------------SELECT TOTO_SEQ.NEXTVAL FROM DUAL
Copyright 2006 Kyle Hailey
#.48
Shared Pool Waits
 Parsing issues


Shared Pool Latch
Library Cache Pin
 Compilation problems


Library Cache Lock
Library Cache Load Lock
 Row Cache Lock

Depends on the cache
Copyright 2006 Kyle Hailey
#.49
Summary
 Shared Pool Latch
 Shard
pool too small or too much hard parsing
 Loading Same Cursor
 Library
Cache Pin
 Library Cache Lock
 Library Cache Load Lock
 Row Cache Lock
 Depends
on the cache
Copyright 2006 Kyle Hailey
#.50
Library Cache Latch Solutions
 Share Cursors
 Use
bind variables
 User cursor_sharing=force
 Avoid invalidations and reloads
 Size
shared_pool large enough
 Avoid changing dependent objects
 Soft Parsing
 Session_cached_cursors
=20 : keep across open/close
 Cursor_space_for_time=true : keep pinned across executes
 hold_cursor=true : used in precompilers
Copyright 2006 Kyle Hailey