shared pool waits l.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Shared Pool Waits PowerPoint Presentation
Download Presentation
Shared Pool Waits

Loading in 2 Seconds...

play fullscreen
1 / 50

Shared Pool Waits - PowerPoint PPT Presentation


  • 431 Views
  • Uploaded on

Shared Pool Waits. Shared Pool Waits. Latch: Library Cache Latch: Shared Pool Latch Library Cache Pin Library Cache Lock Library Cache Load Lock Row Cache Lock. Library Cache. Lib Cache. Shared Pool Structure. Hash Table. SQL statements are hashed On their text. The resulting

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Shared Pool Waits' - emily


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
shared pool waits2
Shared Pool Waits
  • Latch: Library Cache
  • Latch: Shared Pool Latch
  • Library Cache Pin
  • Library Cache Lock
  • Library Cache Load Lock
  • Row Cache Lock

Copyright 2006 Kyle Hailey

library cache
Library Cache

Lib Cache

Copyright 2006 Kyle Hailey

shared pool structure
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

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

shared pool latch6
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 latch7
Shared Pool Latch

Shared Pool Latch covers changes in the lists of free memory chunks

Shared Pool Free Space

Copyright 2006 Kyle Hailey

shared pool latch 8 1 6
Shared Pool Latch 8.1.6

Bucket sizes

0 < 80 bytes

1 < 144

2 < 272

3 < 528

4 < 1040

5 < 2064

6 < 4112

7 < 8208

8 < 16400

9 < 32784

10 bigger

Shared Poolpre 8.1.6

Memory Chunk Buckets

Copyright 2006 Kyle Hailey

shared pool latch9
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

shared pool latch10
Shared Pool Latch
  • Shared Pool
  • Memory Chunk Buckets 8.1.6 and up
    • 0 16 bytes
    • 1 20 bytes
    • … (0-198 only have one chunk size in bucket)
    • 808 bytes
    • 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

library cache pin and locks

pin

lock

pin

lock

handle

handle

handle

library cache pin and locks
  • Locks control access, protects handle
  • Pins guarantee coherency, protects heaps
  • To Access to a cursor
    • Lock handle
      • Locking is the way of locating
    • Pin
      • Pinning loads any necessary heaps
      • Guaranteed to stay in memory until pin is released

Heap 1

Heap 0

Child cursor 1

Heap 6

Copyright 2006 Kyle Hailey

library cache lock and pins
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

lib cache locks and pins
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

library cache lock

pin

lock

pin

lock

handle

handle

handle

library cache lock
  • P1 = address of object
  • P2 = address of lock
  • P3 = mode | namespace
  • See
    • x$kgllk
    • dba_kgllock

Copyright 2006 Kyle Hailey

library cache pin

pin

lock

pin

lock

handle

handle

handle

library cache pin
  • P1 = address of object
  • P2 = address of lock
  • P3 = Mode | Namespace
  • See
    • dba_kgllock
    • x$kglpn

Copyright 2006 Kyle Hailey

library cache lock pin
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

dba kgllock
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
Lib Cache Lock : blockers and waiters

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;

WAITER WLOCKP1 WEVENT BLOCKER BEVENT

------- ---------------- ----------------- --------- -----------------

129 00000003B76AB620 library cache pin 135,15534 PL/SQL lock timer

Copyright 2006 Kyle Hailey

solutions
Solutions
  • Have only one Session compile the same cursor at a time
  • Avoid compiling while executing
  • Waits – find “competing” Sessions

Copyright 2006 Kyle Hailey

library cache load lock
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

library cache latches
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 cache22
Library Cache

Hash Table

pin

lock

pin

lock

handle

handle

Find and Lock

Pin (and Load)

Copyright 2006 Kyle Hailey

library cache structures
Library Cache Structures

Hash Table

waiters

pin

lock

Library Cache Latch

pin

lock

pin

lock

holders

pin

lock

Handle

Cursor(0)

flags

handle

handle

handle

Cursor (0)

Heap 1

pin

lock

Heap 0

Heap 6

pin

lock

Child cursor 1

Child cursor 2

Child cursor 3

Copyright 2006 Kyle Hailey

library cache latch contention
Library Cache Latch Contention
  • Excessive Hard Parsing
    • Not Sharing SQL – use of Literal Values
    • Shared Pool too small
    • Too many invalidations
  • Excessive Soft Parsing

Copyright 2006 Kyle Hailey

sharing sql literals
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

sharing sql literals26
Sharing SQL & Literals

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

Copyright 2006 Kyle Hailey

cursor sharing
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

shared pool too small
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
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

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;

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;

Copyright 2006 Kyle Hailey

soft parsing

= Latch

Soft Parsing

Cursor Memory

lock

lock

lock

lock

lock

pin

pin

pin

pin

pin

Execute 1

Execute 2

Execute 3

Execute 4

Execute 5

  • Re-Executing a Cursor
  • Libray Cache latch
  • Locks
  • Pins

Copyright 2006 Kyle Hailey

session cached cursors
Session Cached Cursors

= Latch

Cursor Memory

lock

pin

pin

pin

pin

pin

Execute 1

Execute 2

Execute 3

Execute 4

Execute 5

Session_cached_cursor:

If Opening/Closing keeps locked in Memory

Copyright 2006 Kyle Hailey

session cached cursors32
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 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

Copyright 2006 Kyle Hailey

cursor space for time
Cursor Space for Time

= Latch

Cursor Memory

lock

Close

Cursor

Open

Cursor

pin

Execute 1

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

cursor space for time34
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 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

Copyright 2006 Kyle Hailey

efficient lock and pinning
Efficient Lock and Pinning
  • Reduce use of latches
  • Improve throughput
  • Improve Concurrency ***

Copyright 2006 Kyle Hailey

cursor sharing36

Heap 1

Heap 1

Heap 1

Heap 0

Heap 0

Heap 0

Heap 6

Heap 6

Heap 6

handle

Handle

Cursor(0)

flags

pin

lock

Cursor (0)

pin

lock

handle

handle

handle

Cursor Sharing

select * from (

select sql_id, count(*) cnt

from V$SQL_SHARED_CURSOR

group by sql_id )

where cnt > 5

order by cnt;

Child cursor 2

Child cursor 3

Child cursor 4

Copyright 2006 Kyle Hailey

v sql shared cursor
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 cursor38
V$SQL_SHARED_CURSOR

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

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

Copyright 2006 Kyle Hailey

10g mutex
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
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

mutex views and stats
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

10 2g cursor pin s
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

row cache lock args
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

row cache lock statspack
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

row cache lock statspack45
Row Cache Lock – Statspack

Dictionary Cache Stats DB/Inst: linux3 Snaps: 68-69

->"Pct Misses" 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
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

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

row cache lock
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

shared pool waits48
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

summary
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

library cache latch solutions
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