Shared Pool Waits .


59 views
Uploaded on:
Category: Art / Culture
Description
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
Transcripts
Slide 1

Shared Pool Waits

Slide 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

Slide 3

Library Cache Lib Cache Copyright 2006 Kyle Hailey

Slide 4

Shared Pool Structure Hash Table SQL proclamations are hashed On their content. The subsequent Hash is utilized to locate the suitable pail, which is hunt down the Compiled SQL. On the off chance that it\'s not there, then we parse it. handle Copyright 2006 Kyle Hailey

Slide 5

Shared Pool Latch Contention can emerge when excessively numerous sessions are hard parsing and searching for space in the mutual pool. The mutual pool hook ensures the structure containing memory pieces Protects Space Allocation in the Shared Pool Shared Pool lock ensure two clients don\'t get same lump of memory Copyright 2006 Kyle Hailey

Slide 6

Shared Pool Latch Get library reserve hook Get shared pool lock Search right basin Find best fit If records get long, look gets long _kghdsidx_count number of shared pool hooks Not bolstered to change, however expanding it can build ORA-4031s if shared pool Is not expanded also Copyright 2006 Kyle Hailey

Slide 7

Shared Pool Latch Shared Pool Latch covers changes in the arrangements of free memory pieces Shared Pool Free Space Copyright 2006 Kyle Hailey

Slide 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 greater Shared Pool pre 8.1.6 Memory Chunk Buckets Copyright 2006 Kyle Hailey

Slide 9

Shared Pool Latch Shared Pool Latch Shared Pool Free Space 8.1.6+ Before 8.1.6, oversizing the common pool could be an issue, after 8.1.6 ought to be fine Copyright 2006 Kyle Hailey

Slide 10

Shared Pool Latch Shared Pool Memory Chunk Buckets 8.1.6 and up 0 16 bytes 1 20 bytes … ( 0-198 just have one lump measure in can ) 808 bytes 812 to 872 … . ( 199-248 just have 16 conceivable piece sizes for each container ) 248 3948 - 4008 249 4012 - 4104 250 4108 - 8204 251 8204 - 16392 252 16396 - 32776 253 32780 - 65544 254 bigger Copyright 2006 Kyle Hailey

Slide 11

stick bolt stick bolt handle library store stick and bolts Locks control get to, secures handle Pins ensure coherency, secures stacks To Access to a cursor Lock handle Locking is the method for finding Pin Pinning loads any important piles Guaranteed to remain in memory until stick is discharged Heap 1 Heap 0 Child cursor 1 Heap 6 Copyright 2006 Kyle Hailey

Slide 12

library reserve bolt and sticks Contention when Sessions attempt to stack/incorporate same SQL Compile bundle others are running Locks and Pins are for the most part in share mode unless alterations are being made Copyright 2006 Kyle Hailey

Slide 13

Lib Cache Locks and Pins Object reliance Library reserve secure Null Cursor execution Library secure invalid Pin in Share Cursor assemblage Lock restrictive Pin elite Copyright 2006 Kyle Hailey

Slide 14

stick bolt stick bolt handle library reserve bolt P1 = address of question P2 = address of bolt P3 = mode | namespace See x$kgllk dba_kgllock Copyright 2006 Kyle Hailey

Slide 15

stick bolt stick bolt handle library reserve stick P1 = address of protest P2 = address of bolt P3 = Mode | Namespace See dba_kgllock x$kglpn Copyright 2006 Kyle Hailey

Slide 16

Library store bolt & stick select w.sid, kglob.KGLNAOBJ from x$kglob kglob, v$session_wait w where kglob.KGLHDADR= w.P1RAW and occasion like \'%library%\'; Copyright 2006 Kyle Hailey

Slide 17

dba_kgllock For library reserve sticks and bolt holds up Session_wait.p1raw = x$kglpn.kgllkhdl dba_kgllock.id1 x$kgllk.kgllkhdl Copyright 2006 Kyle Hailey

Slide 18

Lib Cache Lock : blockers and servers select waiter.sid server, 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 server, gv$session_wait blocker_event where p.kglpnuse =blocker_session.saddr and p.kglpnhdl=waiter.p1raw and (waiter.event in ( \'library reserve stick\' , \'library store bolt\' , \'library reserve stack bolt\') and blocker_event.sid=blocker_session.sid and waiter.sid != blocker_event.sid arrange by waiter.p1raw,waiter.sid; WAITER WLOCKP1 WEVENT BLOCKER BEVENT - - - - - 129 00000003B76AB620 library store stick 135,15534 PL/SQL bolt clock Copyright 2006 Kyle Hailey

Slide 19

Solutions Have just a single Session gather a similar cursor at once Avoid aggregating while executing Waits – find "contending" Sessions Copyright 2006 Kyle Hailey

Slide 20

library reserve stack bolt Waiting For a Reload by another Session P1 = question address P2 = bolt address P3 = 100*mask+namespace Copyright 2006 Kyle Hailey

Slide 21

Library Cache Latches Protects changes in Library Cache Library Locks are not nuclear Thus require library store hook Broken out into library reserve stick assignment library store bolt portion library reserve bolt library reserve library store stick library store stack bolt Copyright 2006 Kyle Hailey

Slide 22

Library Cache Hash Table stick bolt stick bolt handle Find and Lock Pin (and Load) Copyright 2006 Kyle Hailey

Slide 23

Library Cache Structures Hash Table servers stick bolt Library Cache Latch stick bolt stick bolt holders stick bolt Handle Cursor(0) banners handle Cursor (0) Heap 1 stick bolt Heap 0 Heap 6 stick bolt Child cursor 1 Child cursor 2 Child cursor 3 Copyright 2006 Kyle Hailey

Slide 24

Library Cache Latch Contention Excessive Hard Parsing Not Sharing SQL – utilization of Literal Values Shared Pool too little Too numerous negations Excessive Soft Parsing Copyright 2006 Kyle Hailey

Slide 25

Sharing SQL & Literals select plan_hash_value, count(plan_hash_value) from v$sql amass by plan_hash_value, arrange by count(plan_hash_value) SQL> @dups PLAN_HASH_VALUE CNT - - 272002086 520 Copyright 2006 Kyle Hailey

Slide 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 double WHERE dummy=-634891633 SELECT * FROM double WHERE dummy=1987751014 SELECT * FROM double WHERE dummy=25965276 SELECT * FROM double WHERE dummy=32449789 SELECT * FROM double WHERE dummy=-364632215 SELECT * FROM double WHERE dummy=-34273351 SELECT * FROM double WHERE dummy=-699712683 SELECT * FROM double WHERE dummy=1752437199 SELECT * FROM double WHERE dummy=-1081512404 Copyright 2006 Kyle Hailey

Slide 27

Cursor Sharing Bind Variables Select * from double where sham = :var; Cursor_sharing = Force Oracle replaces factors with tie factors Defaults to Exact Copyright 2006 Kyle Hailey

Slide 28

Shared Pool too Small SQL> select namespace, reloads from v$librarycache; NAMESPACE RELOADS - - SQL AREA 367 TABLE/PROCEDURE 592 Reloads implies Cursor piles were kicked out inferring shared_pool too little Copyright 2006 Kyle Hailey

Slide 29

Invalidations SQL> select namespace, nullifications from v$librarycache; NAMESPACE INVALIDATIONS - - SQL AREA 6065 Changes in ward objects discredit 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 quick \'investigate table toto register insights\'; 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 prompt \'break down table toto process measurements\'; dbms_sql.close_cursor(l_cursor); END LOOP; Copyright 2006 Kyle Hailey

Slide 30

= Latch Soft Parsing Cursor Memory bolt stick Execute 1 Execute 2 Execute 3 Execute 4 Execute 5 Re-Executing a Cursor Libray Cache hook Locks Pins Copyright 2006 Kyle Hailey

Slide 31

Session Cached Cursors = Latch Cursor Memory bolt stick Execute 1 Execute 2 Execute 3 Execute 4 Execute 5 Session_cached_cursor: If Opening/Closing keeps secured Memory Copyright 2006 Kyle Hailey

Slide 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 reserve bolt 120,028 library reserve 180,074 library reserve stick 60,048 Session_cached_cursors=20 library reserve bolt 4 library store 60,061 library store stick 60,048 Copyright 2006 Kyle Hailey

Slide 33

Cursor Space for Time = Latch Cursor Memory bolt Close Cursor Open Cursor stick Execute 1 Execute 2 Execute 3 Execute 4 Execute 5 Cursor_space_for_time=true : if open and re-executing – keeps cursor stuck (Cursor as of now l

Recommended
View more...