1 / 50

Shared Pool Waits

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

emily
Download Presentation

Shared Pool Waits

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Shared Pool Waits

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

  3. Library Cache Lib Cache Copyright 2006 Kyle Hailey

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

  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

  7. Shared Pool Latch Shared Pool Latch covers changes in the lists of free memory chunks Shared Pool Free Space Copyright 2006 Kyle Hailey

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

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

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

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

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

  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

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

  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

  22. Library Cache Hash Table pin lock pin lock handle handle Find and Lock Pin (and Load) Copyright 2006 Kyle Hailey

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

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

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

  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

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

  30. = 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

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

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

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

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

  35. Efficient Lock and Pinning • Reduce use of latches • Improve throughput • Improve Concurrency *** Copyright 2006 Kyle Hailey

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

  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

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

  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

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

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

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

  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

More Related