Shared Pool Waits 2 Shared Pool Waits Library

Shared Pool Waits

Shared Pool Structure Hash Table handle handle handle handle Copyright 2006 Kyle Hailey #.

#. 4 Shared Pool Latch Ø Protects Space Allocation in the Shared Pool Ø

#. 5 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

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

#. 7 Shared Pool Latch • Get library cache latch • Get shared pool

#. 8 Shared Pool Latch Shared Pool Free Space 8. 1. 6+ Copyright 2006

#. 9 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

#. 10 library cache pin and locks Ø Locks control access, protects handle Ø Pins guarentees coherency, protects heaps Ø To Access to a cursor Ø Lock handle Ø Ø Pin Ø Ø pin lock handle Locking is the way of locating Pinning loads any necessary heaps Guarenteed to stay in memory until pin is released handle Heap 1 Child cursor 1 Heap 0 Copyright 2006 Kyle Hailey. Heap 6

#. 11 library cache lock and pins Ø Contention when Sessions try to load/compile same SQL at same time Ø Locks and Pins are usually in share mode unless modifications are being made Copyright 2006 Kyle Hailey

#. 12 library cache lock Ø #20 Ø P 1 = address of object

#. 13 library cache pin lock handle Ø #13 Ø P 1 = address

#. 14 Solutions Ø Have only one Session compile a the same cursor at

#. 15 SQL: Lib Cache Lock Find the waiters and who blocks them column wevent format a 20 column bevent format a 20 select waiter. sid waiter, waiter. p 1 raw wlockp 1, waiter. event wevent, blocker_event. sid blocker, blocker_event bevent from x$kglpn p, gv$session blocker_session, gv$session_waiter, gv$session_wait blocker_event where p. kglpnuse=blocker_session. saddr and p. kglpnhdl=waiter. p 1 raw and (waiter. event like 'library cache lock' ) and blocker_event. sid=blocker_session. sid order by waiter. p 1 raw, waiter. sid / Copyright 2006 Kyle Hailey

#. 16 library cache load lock Ø #23 Ø Waiting For a Reload by

#. 17 Library Cache Latch Ø 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 Ø library cache pin Ø library cache load lock Ø Copyright 2006 Kyle Hailey

Library Cache Hash Table pin lock handle Find and Lock Pin (and Load) Copyright

Hash Table Library Cache Structures waiters pin lock pin Library Cache Latch lock holders pin lock handle Handle handle Cursor(0) flags Cursor (0) pin lock Heap 1 Heap 0 Child cursor 1 Child cursor 2 Child cursor 3 Copyright 2006 Kyle Hailey Heap 6 #. 19

#. 20 Library Cache Latch Contention Ø Excesive Hard Parsing Ø Not Sharing SQL – use of Literal Values Ø Shared Pool too small Ø Too many invalidations Ø Excessive Soft Parsing Copyright 2006 Kyle Hailey

#. 21 Sharing SQL & Literals select SQL> @dups PLAN_HASH_VALUE CNT --------272002086 520 from plan_hash_value, count(plan_hash_value) v$sql group by plan_hash_value, order by count(plan_hash_value) 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 sql_text from v$sql SELECT * FROM dual WHERE dummy=-699712683 where SELECT * FROM dual WHERE dummy=1752437199 plan_hash_value = 272002086 SELECT * FROM dual WHERE dummy=-1081512404 and rownum < 10; Copyright 2006 Kyle Hailey

#. 22 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

#. 23 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 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 #. 24

#. 25 Soft Parsing Cursor Memory = Latch lock pin Execute 1 Execute 2 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 Execute 1 Execute 2 pin Execute 3 pin Execute 4 pin Execute 5 Session_cached_cursor: If Opening/Closing keeps locked in Memory Copyright 2006 Kyle Hailey #. 26

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 cache pin Gets ---120, 028 180, 074 60, 048 Session_cached_cursors=20 library cache lock cache pin Copyright 2006 Kyle Hailey 4 60, 061 60, 048 #. 27

#. 28 Cursor Space for Time Cursor Memory = Latch Close Cursor lock 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 cursor is kept open) Copyright because 2006 Kyle Hailey

#. 29 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; Cursor_space_for_time=false Latch ----library cache lock cache pin Gets ---35 60, 096 60, 044 Cursor_space_for_time=true library cache lock cache pin Copyright 2006 Kyle Hailey 30 85 42

#. 30 Efficient Lock and Pinning Ø Reduce use of latches Ø Improve throughput

#. 31 row cache lock : args Ø P 1 = cache# Ø P

#. 32 Summary Ø Shared Pool Latch Ø Shard pool too small or too much hard pasing Ø Loading Same Cursor Ø Library Cache Pin Ø Library Cache Lock Ø Library Cache Load Lock Ø Library Cache Latch Ø Too much hard or soft parsing Ø Row Cache Lock Copyright 2006 Kyle Hailey

#. 33 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