計算機軟件及應用Buffercache課件

1、Buffer Cache WaitsIn This Sectionlatch: cache buffers chains latch: cache buffers lru chain latch: cache buffer handlesFree Buffer WaitBuffer Busy WaitWrite Complete WaitBuffer ExterminateBuffer CacheRedoLib CacheBuffer CacheIOLocksNetworkREDO Log FilesData FilesDBWRLGWRUser2User1User3Log BufferBuff

2、er CacheLog BufferBuffer CacheSGALibrary CacheOracle Memory StructuresBuffer Cache AccessBuffer Cache ManagementLocating Free blocksFinding data blocksManaging LRU listsCleaning Dirty BlocksBuffer Cache management can cause contentionDifferent from IO ( reading blocks of disk )Query0. Parse statemen

3、tFind object information in data dictionaryCalculate execution planIf full table scanLook at all blocks of tableIf index find root of index and follow to keyData Dictionary will have info about table or index blockFile #Block #Once you know the block DBA (file# + block#) Select ename from emp where

4、empno = 12;Is Block in cache?Now you have a file# and block#How do you know if a block is cached?ShadowProcess?Do you search all the blocks? Could be 1000s of blocks to search.Buffer caches are in the multi GigBuffer CacheFind a block by:1) Hash ofData file #Block# 2) Result = Bucket #3) Search link

5、ed list for that bucket #What is a hash valueWhat are Buckets What is the linked list?ConceptsTo understand contention on the buffer cache, need to understand :Linked ListsHashingBucketsDouble Linked Lists 03C3900003C3947803C38F6003C3900003C3900003C38F6003C39478AddressNextPreviousHashing FunctionSim

6、ple hash could be a Mod function1 mod 4 = 12 mod 4 = 23 mod 4 = 34 mod 4 = 05 mod 4 = 16 mod 4 = 27 mod 4 = 38 mod 4 = 0 Using “mod 4” as a hash funtion creates 4 “buckets” to store thingsHash Bucket FillData Block1203Hash Blocks file# block #sResult in a bucket#Put Block in bucket?Hash Blocks 1 fil

7、e# 437 block #s(1+437) mod 4 = 2After a while the buckets become populated with blocksLatches Protect Bucket ContentsBufferHeadersData BlocksHashbucketlatchesBuffer Headers contents described by X$BHX$bhDescribes Contents of Buffer HeadersSQL desc x$bh Name Type - - ADDR RAW(4) DBARFIL NUMBER DBABLK

8、 NUMBER OBJ NUMBER HLADDR RAW(4) NXT_HASH RAW(4) PRV_HASH RAW(4) much more ADDR DBARFIL DBABLK OBJ HLADDR NXT_HASH PRV_HASHA each buffer header contains Information about the data block It points to and the previous and next Buffer header in a linked listCache 03C3900003C3947803C38F6003C3900003C3900

9、003C38F6003C39478ADDRNXT_HASHPRV_HASHX$BH describes HeadersBufferHeadersData BlocksHashbucketlatchesHLADDRNXT_HASHPRV_HASHADDRADDRDBARFILDBABLKOBJx$bh ADDR DBARFIL DBABLK OBJ HLADDR NXT_HASH PRV_HASHTo Find a BlockHash the block addressGet Bucket latchLook for headerFound, read block in cacheNot Fou

10、nd Read block off diskShadowProcessBufferHeadersData BlocksHashbucket2354latches12345Cache Buffers ChainsHash Bucketss5s4s3s2s1SessionsContention if too many accesses on a bucketlatchesBlockHeadersCache Buffer ChainDataBlocksExamplesS1S2S3S4Look up TableNested LoopsSelect t1.val, t2.valfrom t1, t2wh

11、ere t1.c1 = value and t2.id = t1.id;t1Index_t2t2CBC SolutionsFind SQL ( Why is application hitting the block so hard? )Nested loops, possiblyHash PartitionUses Hash Join Hash clustersLook up tables (“select language from lang_table where .”)Change applicationUse plsql functionSpread data out to redu

12、ce contentionSelect from dualPossibly use x$dualHow do you find the SQL?CBC: Statspack 9iTop 5 Timed Events % TotalEvent Waits Time (s) Ela Time- - - -latch free 21,428 1,914 81.37CPU time 360 15.29PL/SQL lock timer 16 48 2.04SQL*Net message from dblink 4,690 14 .58db file sequential read 1,427 5 .1

13、9Top 5 Timed Events % TotalEvent Waits Time (s) Ela Time- - - -latch free 21,428 1,914 81.37CPU time 360 15.29PL/SQL lock timer 16 48 2.04SQL*Net message from dblink 4,690 14 .58db file sequential read 1,427 5 .19Latch Sleep breakdown for DB: CDB Instance: cdb Snaps: 1 -2- ordered by misses descLatc

14、h Name Requests Misses Sleeps Sleeps 1-4- - - - -cache buffers chains 12,123,500 608,415 15,759 0/0/0/0/0library cache pin 12,027,599 173,446 2,862 172694/743/8/1/0library cache 12,072,503 98,065 2,373 97739/279/47/0/0simulator lru latch 606 436 434 6/426/4/0/0Fails to find SQLCBC: Statspack 10gTop

15、5 Timed Events Avg %Total wait CallEvent Waits Time (s) (ms) Time- - - -CPU time 35 54.3latch: cache buffers chains 46 11 243 17.6latch: library cache pin 35 8 229 12.6latch: library cache 27 6 231 9.8log file sequential read 15 1 60 1.4Top 5 Timed Events Avg %Total wait CallEvent Waits Time (s) (ms

16、) Time- - - -CPU time 35 54.3latch: cache buffers chains 46 11 243 17.6latch: library cache pin 35 8 229 12.6latch: library cache 27 6 231 9.8log file sequential read 15 1 60 1.4Fails to find SQLCBC: ASH select count(*), sql_id, nvl(o.object_name,ash.current_obj#) objn, substr(o.object_type,0,10) ot

17、ype, CURRENT_FILE# fn, CURRENT_BLOCK# blocknfrom v$active_session_history ash , all_objects owhere event like latch: cache buffers chains and o.object_id (+)= ash.CURRENT_OBJ#group by sql_id, current_obj#, current_file#, current_block#, o.object_name,o.object_typeorder by count(*)/ CNT SQL_ID OBJN O

18、TYPE FN BLOCKN- - - - - - 84 a09r4dwjpv01q MYDUAL TABLE 1 93170SQL Statement: SuccessExtra: Hot blockCBC: OEMCBC: ADDMProblemSQL StatementSolution?CBC Further Investigationselect * from v$event_namewhere name = latch: cache buffers chains EVENT# NAME - - 58 latch: cache buffers chains PARAMETER1 PAR

19、AMETER2 PARAMETER3 - - - address number tries NOTE: _db_block_hash_buckets = # of hash buckets _db_blocks_per_hash_latch = # of hash latchesCBC: whats the hot blockCan get it from ASH Current_file# Current_block# Where event=latch: cache buffers chains”Sometimes file and block = 0Seems to happen for

20、 Nested LoopsGet the hot block real timeUse Hash Latch Address Ash.p2 = x$bh.hladdrHot Block: X$BH.TCHUpdated when block readUpdated by no more than 1 every 3 secondsCan be used to find “hot” blocksNote: set back to zero when block cycles through the buffer cacheCBC Real Timeselect count(*), lpad(re

21、place(to_char(p1,XXXXXXXXX), ,0),16,0) laddrfrom v$active_session_historywhere event=latch: cache buffers chainsgroup by p1;select , bh.dbarfil, bh.dbablk, bh.tchfrom x$bh bh, obj$ owhere tch 100 and hladdr=00000004D8108330 and o.obj#=bh.objorder by tch COUNT(*) LADDR- - 4933 00000004D8108330N

22、AME DBARFIL DBABLK TCH- - - -EMP_CLUSTER 4 394 120Putting into one Queryselect name, file#, dbablk, obj, tch, hladdr from x$bh bh , obj$ o where o.obj#(+)=bh.obj and hladdr in ( select ltrim(to_char(p1,XXXXXXXXXX) ) from v$active_session_history where event like latch: cache% group by p1 having coun

23、t(*) 5) and tch 5order by tch NAME FILE# DBABLK OBJ TCH HLADDR- - - - - -BBW_INDEX 1 110997 66051 17 6BD91180IDL_UB1$ 1 54837 73 18 6BDB8A80VIEW$ 1 6885 63 20 6BD91180VIEW$ 1 6886 63 24 6BDB8A80DUAL 1 2082 258 32 6BDB8A80DUAL 1 2081 258 32 6BD91180MGMT_EMD_PING 3 26479 50312 272 6BDB8A80This can be

24、misleading, as TCH gets set to 0 ever rap around the LRU and it only gets updated once every 3 seconds, so in this case DUAL was my problem table not MGMT_EMD_PING Consistent Read BlocksCurrentBlock(XCUR)s1s2Update Select ConsistentRead(CR)Clone& Undo Both have same file# and block# and hash to same

25、 bucketlatchesCBC: Consistent Read BlocksCache Buffer ChainContention: Too Many Buffers in Buckets5s4s3s2s1Hash BucketsBlockHeadersMax length :_db_block_max_cr_dba10g = 6Consistent Read Copiesselect count(*) , name , file# , dbablk , hladdr from x$bh bh , obj$ owhere o.obj#(+)=bh.obj and hladdr in (

26、 select ltrim(to_char(p1,XXXXXXXXXX) ) from v$active_session_history where event like latch: cache% group by p1 )group by name,file#, dbablk, hladdrhaving count(*) 1order by count(*);CNT NAME FILE# DBABLK HLADDR- - - - - 14 MYDUAL 1 93170 2C9F4B20CBC : SolutionFine the SQL causing the problemChange

27、Application LogicEliminate hot spotsLook up tablesUses pl/sql functionsMinimize data per blockPossibly using x$dual instead of dualIndex Nested loops Hash joinHash partition indexHah ClusterUpdates, inserts , select for update on blocks while reading those blocksCause multiple copiesselect ash.sql_i

28、d, count(*), sql_textfrom v$active_session_history ash, v$sqlstats sqlwhere event=latch: cache buffers chainsand sql.sql_id(+)=ash.sql_idgroup by ash.sql_id, sql_text;Latch: cache buffer handlesBuffers can be pinned Possibly increase_db_handles_cached 5UnsupportedUsed when pinning block headers for

29、expected reuse Free Buffer WaitData Block Cache lack free buffersTune byIncrease data blocksTry to tune DBWRImproving Inefficient SQL requesting large # of blocksFree Buffer Wait Finding a Free BlockIf the data block isnt in cache Get a free block and header in the buffer cacheRead it off diskUpdate

30、 the free headerRead the block into the buffer cacheNeed Free Block to Read in New Data BlockFinding a Free BlockShadowProcessWhen a session reads a block Into the bufffer cache how does it find a FREE spot?Finding a Free BlockBufferHeadersData BlocksHashbucketlatchesArrange the Buffer Headers into

31、an LRU ListScan LRU for a free blockCache Buffers LRU= entry in x$bhX$bhDescribes Buffer HeadersSQL desc x$bh Name Type - - ADDR RAW(4) DBARFIL NUMBER DBABLK NUMBER OBJ NUMBER HLADDR RAW(4) NXT_HASH RAW(4) PRV_HASH RAW(4) NXT_REPL RAW(4) PRV_REPL RAW(4) NXT_REPL RAW(4) PRV_REPL RAW(4) HLADDR RAW(4)

32、NXT_HASH RAW(4) PRV_HASH RAW(4)Cache buffer chainsLRULRU Chain 03C3900003C38F6003C38F6003C3900003C3900003C38F6003C39478ADDRNXT_HASHPRV_HASH03C3947803C3851403C3863803C3862003C385F403C38554NXT_REPL PRV_REPL Cache Buffers LRU listCache Buffers LRU listLRU Chain of Buffer HeadersBuffer Cache Cache Buffe

33、rs LRU LatchMRULRUBuffer Headers“Cold”LRU = Least Recently UsedMRU = Most Recently UsedOne LRU Latch protects the linked list during changes to the list“Hot”LRU latchSession Searching for Free BlocksMRULRUBuffer HeadersSession ShadowGo to the LRU end of data blocksLook for first non-dirty blockIf se

34、arch too many post DBWR to make freeFree Buffer waitFree Buffer Wait SolutionsTune byIncrease data blocksTry to tune DBWRASYNCIf no ASYNC use I/O Slaves (dbwr_io_slaves)Multiple DBWR (db_writer_processes)Direct I/OTune Inefficient SQL requesting large # of blocksSession Finding a Free BlockMRULRUHot

35、 EndMid-Point InsertionGet LRU LatchFind Free BlockInsert HeaderRelease LRU LatchsessionLRU LatchDBWR taking Dirty Blocks offMRULRUBuffer Headers LRUDBWRDirty List of Buffer Headers LRUWlatchLRU latch also covers DBWR list of dirty blocsCache Buffers LRU LatchMRULRUMid-Point InsertionOracle Tracks t

36、he touch count of blocks. As the block is pushed to the LRU end, if its touch count is 3 or more, its promoted to the MRU endSolution: Multiple Sets_db_block_lru_latches = 810gR2 with cpu_count = 2 X$KCBWDS set descriptorSet 1Set 2LRU Latch 1LRU Latch 2Working Sets select ds.set_id, ds.blk_size , bp

37、.BUFFERS, nvl(.unused) from x$kcbwds ds, v$buffer_pool bp where ds.set_id = bp.lo_setid (+) and ds.set_id freelistsFreelist blocks free lists groupsFile Header Block look at extent allocationThere is a hot block, eliminate the hot blockBBW: StatspackTop 5 Timed Events Avg %Total wait CallEven

38、t Waits Time(s) (ms) Time- - - - -buffer busy waits 5,832 263 45 28.2log file parallel write 248 125 505 13.4read by other session 902 103 114 11.1db file parallel write 2,166 94 43 10.1db file sequential read 653 81 125 8.7Class Waits Wait Time (s) Avg Time (ms)- - - -file header block 264 203 769d

39、ata block 6,070 162 27undo header 355 0 1segment header 44 0 1fails to find ObjectBBW: ASHFindsObjectBlock TypeSQL StatementCNT OBJ OTYPE SQL_ID BLOCK_TYPE TBS- - - - - - 2 BBW_INDEX_VAL_I INDEX 635xhydd6fzgg segment header SYSTEM 2 0 635xhydd6fzgg usn 5 header UNDOTBS1 3 0 1hsb81ypyrfs5 file header

40、 block UNDOTBS1 32 BBW_INDEX_VAL_I INDEX 1hsb81ypyrfs5 data block SYSTEM 33 BBW_INDEX_VAL_I INDEX 6avm49ys4k7t6 data block SYSTEM 34 BBW_INDEX_VAL_I INDEX 5wqps1quuxqr4 data block SYSTEMBBW: OEMSolutionsBBW Block Typesselect rownum n,ws.classfrom v$waitstat;NAME P1 P2 P3- - - -buffer busy waits file

41、# block# class#select * from v$event_namewhere name = buffer busy waits N CLASS - - 1 data block 2 sort block 3 save undo block 4 segment header 5 save undo header 6 free list 7 extent map 8 1st level bmb 9 2nd level bmb 10 3rd level bmb 11 bitmap block 12 bitmap index block 13 file header block 14

42、unused 15 system undo header 16 system undo block 17 undo header 18 undo blockNote: Before 10g, P3 was BBW typeIf P3 in 100,110,120,130 then readNow “read by other session” Else Write, P3 in 200,210,220,230, 231 Joining ASH with v$waitstatselect o.object_name obj, o.object_type otype, ash.SQL_ID, w.

43、classfrom v$active_session_history ash, ( select rownum class#, class from v$waitstat ) w, all_objects owhere event=buffer busy waits and w.class#(+)=ash.p3 and o.object_id (+)= ash.CURRENT_OBJ#Order by sample_time;OBJ OTYPE SQL_ID CLASS- - - -TOTO1 TABLE 8gz51m9hg5yuf data blockTOTO1 TABLE 8gz51m9h

44、g5yuf data blockTOTO1 TABLE 8gz51m9hg5yuf segment headerTOTO1 TABLE 8gz51m9hg5yuf data blockAlternative to ASH: AWRselect to_char(BEGIN_INTERVAL_TIME,DD-MON HH:MI), , s.BUFFER_BUSY_WAITS_DELTAfrom dba_hist_seg_stat s, dba_hist_snapshot sn, obj$ owhere BUFFER_BUSY_WAITS_DELTA 100 and sn.snap_id

45、 = s.snap_id and o.obj# = s.obj#;TO_CHAR(BEGI NAME BUFFER_BUSY_WAITS_DELTA- - -11-JAN 10:21 TOTO1 58447Example: BBW with InsertConcurrent inserts will insert into the same blockEach session has to wait for the previous session to finish its writeUsually pretty fastContention builds on highly concurr

46、ent applicationsLack of Free ListsNot Using ASSM (Automatic Segment Space Management)Example: Lack of Free List S1S2S3S44 Sessions runningInsert into toto values (null, a);Commit;OBJN OTYPE FILEN BLOCKN SQL_ID BLOCK_TYPE- - - - - -54962 TOTO1 TABLE 16 45012 8gz51m9hg5yuf data block 54962 TOTO1 TABLE

47、 16 161 8gz51m9hg5yuf segment headerSolution1: Free ListsS1S2S3S44 Sessions runningInsert into toto values (null, a);Commit;Solution 2: ASSMMultiple Bitmap Blocks Track Free SpaceUnformattedUp to 25% FreeUp to 50% FreeUp to 75% FreeFullFree block chosen by Process IDPossibly instance # for RACSoluti

48、on 2: ASSMHeaderLevel 2Level 1Level 1Level 1Data BlocksBitmap BlocksTablespace Types : ASSMselect tablespace_name, extent_management LOCAL, allocation_type EXTENTS, segment_space_management ASSM, initial_extentfrom dba_tablespacesTABLESPACE_NAME LOCAL EXTENTS ASSM- - - -SYSTEM LOCAL SYSTEM MANUALUND

49、OTBS1 LOCAL SYSTEM MANUALSYSAUX LOCAL SYSTEM AUTOTEMP LOCAL UNIFORM MANUALUSERS LOCAL SYSTEM AUTOEXAMPLE LOCAL SYSTEM AUTODATA LOCAL SYSTEM MANUALcreate tablespace data2 datafile /d3/kyle/data2_01.dbf size 200Msegment space management auto;BBW: ASSMConsider using Freelists instead of ASSMNormally wa

50、its on ASSM blocks should be too small to warrant using FreelistsASSM is easier, automatically managed1st level bmb2nd level bmb 3rd level bmbBBW on IndexIndexSession 1Session 2Session 3Increasing index key creates a hot spot on the leading index leafOBJN OTYPE FILEN BLOCKN SQL_ID BLOCK_TYPE- - - -

51、- -BBW_INDEX_INDEX 1 113599 97dgthz60u28d data block 1Use Reverse Key indexesBreaks Index scansHash Partition IndexMore IOs per index accessBBW on Index : ADDM RecsAlso consider “reversing” the keyExample: BBW on RBSIF BBW happen on old style RBSClass# 18Switch to UNDOOld style RBS, the DBA had to f

52、igure out # of RBS SegmentsWith UNDO, it is automatically managedalter system set undo_management=auto scope=spfile;BBW and RBS SegsOBJN OTYPE FILEN BLOCKN SQL_ID BLOCK_TYPE- - - - - -54962 TOTO1 TABLE 16 45012 8gz51m9hg5yuf data block 54962 TOTO1 TABLE 16 161 8gz51m9hg5yuf segment header0 14 9 8gz5

53、1m9hg5yuf 870 14 9 8gz51m9hg5yuf 87Select CURRENT_OBJ#| |o.object_name objn, o.object_type otype, CURRENT_FILE# filen, CURRENT_BLOCK# blockn, ash.SQL_ID, w.class | |to_char(ash.p3) block_typefrom v$active_session_history ash, (select rownum class#, class from v$waitstat ) w, all_objects owhere event

54、=buffer busy waits and w.class#(+)=ash.p3 and o.object_id (+)= ash.CURRENT_OBJ#Order by sample_time;Further Investigation RBSOld Style RBS if Class# 18P1 P2 P3 SQL_ID COUNT(*) CLASS- - - - - -14 9 87 72wa5hjpzr0by 114 9 87 72wa5hjpzr0by 114 9 87 3gkmtvxzu6p2m 114 9 87 3gkmtvxzu6p2m 1 6 561325 1 7zx1

55、krfcgn88t 8 data block14 9 87 8s29zyzr55z2t 1select segment_name, segment_typefrom dba_extents where file_id = P1 and P2 between block_id and block_id + blocks 1;SEGMENT_NAME SEGMENT_TYPE- -R2 ROLLBACKADDM finds old style RBSBBW: File HeaderQuerying ASH, make sureP1=current_file#P2=current_block#If not, use p1, p2 and not current_object#Time P1 P2 OBJN OTYPE FN BLOCKN BLOCK_TYPE- - - - -