[计算机软件及应用]Buffercache.ppt

Buffer Cache Waits,In This Section,latch: cache buffers chains latch: cache buffers lru chain latch: cache buffer handles Free Buffer Wait Buffer Busy Wait Write Complete Wait Buffer Exterminate,Buffer Cache,Redo,Lib Cache,Buffer Cache,IO,Locks,Network,REDO Log Files,Data Files,DBWR,LGWR,User2,User1,User3,Oracle Memory Structures,Buffer Cache Access,Buffer Cache Management Locating Free blocks Finding data blocks Managing LRU lists Cleaning Dirty Blocks Buffer Cache management can cause contention Different from IO ( reading blocks of disk ),Query,0. Parse statement Find object information in data dictionary Calculate execution plan If full table scan Look at all blocks of table If index find root of index and follow to key Data Dictionary will have info about table or index block File # Block # Once you know the block DBA (file# + block#) ,Select ename from emp where empno = 12;,Is Block in cache?,Now you have a file# and block# How do you know if a block is cached?,Shadow Process,?,Do you search all the blocks? Could be 1000s of blocks to search. Buffer caches are in the multi Gig,Buffer Cache,Find a block by: 1) Hash of Data file # Block# 2) Result = Bucket # 3) Search linked list for that bucket #,What is a hash value What are Buckets What is the linked list?,Concepts,To understand contention on the buffer cache, need to understand : Linked Lists Hashing Buckets,Double Linked Lists,03C39000,03C39478,03C38F60,03C39000,03C39000,03C38F60,03C39478,Address,Next,Previous,Hashing Function,Simple hash could be a Mod function 1 mod 4 = 1 2 mod 4 = 2 3 mod 4 = 3 4 mod 4 = 0 5 mod 4 = 1 6 mod 4 = 2 7 mod 4 = 3 8 mod 4 = 0 Using “mod 4” as a hash funtion creates 4 “buckets” to store things,Hash Bucket Fill,Data Block,1,2,0,3,Hash Blocks file# block #s Result in a bucket# Put Block in bucket,?,?,?,?,Hash Blocks 1 file# 437 block #s (1+437) mod 4 = 2,After a while the buckets become populated with blocks,Latches Protect Bucket Contents,Buffer Headers,Data Blocks,Hash bucket,latches,Buffer Headers contents described by X$BH,X$bh,Describes Contents of Buffer Headers,SQL 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) much more,ADDR DBARFIL DBABLK OBJ HLADDR NXT_HASH PRV_HASH ,A each buffer header contains Information about the data block It points to and the previous and next Buffer header in a linked list,Cache,03C39000,03C39478,03C38F60,03C39000,03C39000,03C38F60,03C39478,ADDR,NXT_HASH,PRV_HASH,X$BH describes Headers,Buffer Headers,Data Blocks,Hash bucket,latches,HLADDR,NXT_HASH,PRV_HASH,ADDR,ADDR,DBARFIL DBABLK OBJ,x$bh ADDR DBARFIL DBABLK OBJ HLADDR NXT_HASH PRV_HASH,To Find a Block,Hash the block address Get Bucket latch Look for header Found, read block in cache Not Found Read block off disk,Shadow Process,Buffer Headers,Data Blocks,Hash bucket,2,3,5,4,latches,1,2,3,4,5,Cache Buffers Chains,Hash Buckets,s5,s4,s3,s2,s1,Sessions,Contention if too many accesses on a bucket,latches,Block Headers,Cache Buffer Chain,Data Blocks,Examples,Look up Table Nested Loops,Select t1.val, t2.val from t1, t2 where t1.c1 = value and t2.id = t1.id;,t1,Index_t2,t2,CBC Solutions,Find SQL ( Why is application hitting the block so hard? ) Nested loops, possibly Hash Partition Uses Hash Join Hash clusters Look up tables (“select language from lang_table where .”) Change application Use plsql function Spread data out to reduce contention Select from dual Possibly use x$dual How do you find the SQL?,CBC: Statspack 9i,Top 5 Timed Events % Total Event Waits Time (s) Ela Time - - - - latch free 21,428 1,914 81.37 CPU time 360 15.29 PL/SQL lock timer 16 48 2.04 SQL*Net message from dblink 4,690 14 .58 db file sequential read 1,427 5 .19,Top 5 Timed Events % Total Event Waits Time (s) Ela Time - - - - latch free 21,428 1,914 81.37 CPU time 360 15.29 PL/SQL lock timer 16 48 2.04 SQL*Net message from dblink 4,690 14 .58 db file sequential read 1,427 5 .19,Latch Sleep breakdown for DB: CDB Instance: cdb Snaps: 1 -2 - ordered by misses desc Latch Name Requests Misses Sleeps Sleeps 1-4 - - - - - cache buffers chains 12,123,500 608,415 15,759 0/0/0/0/0 library cache pin 12,027,599 173,446 2,862 172694/743/8/1/0 library cache 12,072,503 98,065 2,373 97739/279/47/0/0 simulator lru latch 606 436 434 6/426/4/0/0,Fails to find SQL,CBC: Statspack 10g,Top 5 Timed Events Avg %Total wait Call Event Waits Time (s) (ms) Time - - - - CPU time 35 54.3 latch: cache buffers chains 46 11 243 17.6 latch: library cache pin 35 8 229 12.6 latch: library cache 27 6 231 9.8 log file sequential read 15 1 60 1.4,Top 5 Timed Events Avg %Total wait Call Event Waits Time (s) (ms) Time - - - - CPU time 35 54.3 latch: cache buffers chains 46 11 243 17.6 latch: library cache pin 35 8 229 12.6 latch: library cache 27 6 231 9.8 log file sequential read 15 1 60 1.4,Fails to find SQL,CBC: ASH,select count(*), sql_id, nvl(o.object_name,ash.current_obj#) objn, substr(o.object_type,0,10) otype, CURRENT_FILE# fn, CURRENT_BLOCK# blockn from v$active_session_history ash , all_objects o where 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_type order by count(*) /,CNT SQL_ID OBJN OTYPE FN BLOCKN - - - - - - 84 a09r4dwjpv01q MYDUAL TABLE 1 93170,SQL Statement: Success,Extra: Hot block,CBC: OEM,CBC: ADDM,Problem,SQL Statement,Solution?,CBC Further Investigation,select * from v$event_name where name = latch: cache buffers chains,EVENT# NAME - - 58 latch: cache buffers chains PARAMETER1 PARAMETER2 PARAMETER3 - - - address number tries,NOTE: _db_block_hash_buckets = # of hash buckets _db_blocks_per_hash_latch = # of hash latches,CBC: whats the hot block,Can get it from ASH Current_file# Current_block# Where event=latch: cache buffers chains” Sometimes file and block = 0 Seems to happen for Nested Loops Get the hot block real time Use Hash Latch Address Ash.p2 = x$bh.hladdr,Hot Block: X$BH.TCH,Updated when block read Updated by no more than 1 every 3 seconds Can be used to find “hot” blocks Note: set back to zero when block cycles through the buffer cache,CBC Real Time,select count(*), lpad(replace(to_char(p1,XXXXXXXXX), ,0),16,0) laddr from v$active_session_history where event=latch: cache buffers chains group by p1;,select , bh.dbarfil, bh.dbablk, bh.tch from x$bh bh, obj$ o where tch 100 and hladdr=00000004D8108330 and o.obj#=bh.obj order by tch,COUNT(*) LADDR - - 4933 00000004D8108330,NAME DBARFIL DBABLK TCH - - - - EMP_CLUSTER 4 394 120,Putting into one Query,select 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 count(*) 5 ) and tch 5 order by tch,NAME FILE# DBABLK OBJ TCH HLADDR - - - - - - BBW_INDEX 1 110997 66051 17 6BD91180 IDL_UB1$ 1 54837 73 18 6BDB8A80 VIEW$ 1 6885 63 20 6BD91180 VIEW$ 1 6886 63 24 6BDB8A80 DUAL 1 2082 258 32 6BDB8A80 DUAL 1 2081 258 32 6BD91180 MGMT_EMD_PING 3 26479 50312 272 6BDB8A80,This can be 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 Blocks,Current Block (XCUR),s1,s2,Update,Select,Consistent Read (CR),Clone & Undo,Both have same file# and block# and hash to same bucket,latches,CBC: Consistent Read Blocks,Cache Buffer Chain,Contention: Too Many Buffers in Bucket,s5,s4,s3,s2,s1,Hash Buckets,Block Headers,Max length : _db_block_max_cr_dba 10g = 6,Consistent Read Copies,select count(*) , name , file# , dbablk , 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 ) group by name,file#, dbablk, hladdr having count(*) 1 order by count(*);,CNT NAME FILE# DBABLK HLADDR - - - - - 14 MYDUAL 1 93170 2C9F4B20,CBC : Solution,Fine the SQL causing the problem Change Application Logic Eliminate hot spots Look up tables Uses pl/sql functions Minimize data per block Possibly using x$dual instead of dual Index Nested loops Hash join Hash partition index Hah Cluster Updates, inserts , select for update on blocks while reading those blocks Cause multiple copies,select ash.sql_id, count(*), sql_text from v$active_session_history ash, v$sqlstats sql where event=latch: cache buffers chains and sql.sql_id(+)=ash.sql_id group by ash.sql_id, sql_text;,Latch: cache buffer handles,Buffers can be pinned Possibly increase _db_handles_cached 5 Unsupported Used when pinning block headers for expected reuse,Free Buffer Wait,Data Block Cache lack free buffers Tune by Increase data blocks Try to tune DBWR Improving Inefficient SQL requesting large # of blocks,Free Buffer Wait,Finding a Free Block If the data block isnt in cache Get a free block and header in the buffer cache Read it off disk Update the free header Read the block into the buffer cache Need Free Block to Read in New Data Block,Finding a Free Block,When a session reads a block Into the bufffer cache how does it find a FREE spot?,Finding a Free Block,Buffer Headers,Data Blocks,Hash bucket,latches,Arrange the Buffer Headers into an LRU List Scan LRU for a free block,Cache Buffers LRU,= entry in x$bh,X$bh,Describes Buffer Headers,SQL 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) NXT_HASH RAW(4) PRV_HASH RAW(4),Cache buffer chains,LRU,LRU Chain,03C39000,03C38F60,03C38F60,03C39000,03C39000,03C38F60,03C39478,ADDR,NXT_HASH,PRV_HASH,03C39478,03C38514,03C38638,03C38620,03C385F4,03C38554,NXT_REPL PRV_REPL,Cache Buffers LRU list,Cache Buffers LRU list,LRU Chain of Buffer Headers,Buffer Cache,Cache Buffers LRU Latch,MRU,LRU,Buffer Headers,“Cold”,LRU = Least Recently Used,MRU = Most Recently Used,One LRU Latch protects the linked list during changes to the list,“Hot”,LRU latch,Session Searching for Free Blocks,MRU,LRU,Buffer Headers,Session Shadow,Go to the LRU end of data blocks Look for first non-dirty block If search too many post DBWR to make free Free Buffer wait,Free Buffer Wait Solutions,Tune by Increase data blocks Try to tune DBWR ASYNC If no ASYNC use I/O Slaves (dbwr_io_slaves) Multiple DBWR (db_writer_processes) Direct I/O Tune Inefficient SQL requesting large # of blocks,Session Finding a Free Block,MRU,LRU,Hot End,Mid-Point Insertion,Get LRU Latch,Find Free Block,Insert Header Release LRU Latch,session,LRU Latch,DBWR taking Dirty Blocks off,MRU,LRU,Buffer Headers LRU,DBWR,Dirty List of Buffer Headers LRUW,latch,LRU latch also covers DBWR list of dirty blocs,Cache Buffers LRU Latch,MRU,LRU,Mid-Point Insertion,Oracle Tracks the 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 end,Solution: Multiple Sets,_db_block_lru_latches = 8 10gR2 with cpu_count = 2 X$KCBWDS set descriptor,Set 1,Set 2,LRU Latch 1,LRU Latch 2,Working Sets,select ds.set_id, ds.blk_size , bp.BUFFERS, nvl(.unused) from x$kcbwds ds, v$buffer_pool bp where ds.set_id = bp.lo_setid (+) and ds.set_id = bp.hi_setid (+) /,SET_ID BLK_SIZE BUFFERS NAME - - - - 16 32768 15 32768 14 16384 13 16384 12 8192 11 8192 10 4096 9 4096 8 2048 7 2048 6 8192 4972 DEFAULT 5 8192 4972 DEFAULT 4 8192 3 8192 2 8192 1 8192,Test Case,8 Sessions reading separate tables Tables were too big to hold in cache cache option set on each table Result : lots of buffer cache churn Expected to get “latch: cache buffer chains LRU”,simulator lru latch,CBC Further Investigation,select p2, count(*) from v$active_session_history where event=latch free group by p2,select * from v$latchname where latch#=127,P2 COUNT(*) - - 127 3556,LATCH# NAME - - 127 simulator lru latch,select * from v$event_name where name = latch free,PARAMETER1 PARAMETER2 PARAMETER3 - - - address number tries,db_cache_advice,Alter system set db_cache_advice=off;,Group “other” is very small compared to I/O wait time not a problem,Cache Buffers LRU Latch : Solution Other,Increase Size of Buffer Cache Using multiple cache buffers Keep, recycle Possibly increase _db_block_lru_latches Not supported,Buffer Busy Waits,User 1 tries to change a buffer header User 2 has buffer header “locked” (pinned),1,2,0,3,User1,User2,BBW Solution Paths,Find Block type Resolve if possible Tune SQL Find SQL How often is it called By how many Users Eliminate Hot Block Find Object Find Block Type,Block Types: Undo Header use AUM (or add more RBS) Undo Block hot spot in UNDO Data index hot spot, partition table free lists, ASSM, partition Segment header free lists table datablock - freelists Freelist blocks free lists groups File Header Block look at extent allocation,There is a hot block, eliminate the hot block,BBW: Statspack,Top 5 Timed Events Avg %Total wait Call Event Waits Time(s) (ms) Time - - - - - buffer busy waits 5,832 263 45 28.2 log file parallel write 248 125 505 13.4 read by other session 902 103 114 11.1 db file parallel write 2,166 94 43 10.1 db file sequential read 653 81 125 8.7,Class Waits Wait Time (s) Avg Time (ms) - - - - file header block 264 203 769 data block 6,070 162 27 undo header 355 0 1 segment header 44 0 1,fails to find Object,BBW: ASH,Finds Object Block Type SQL Statement,CNT 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 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 SYSTEM,BBW: OEM,Solutions,BBW Block Types,select rownum n,ws.class from v$waitstat;,NAME P1 P2 P3 - - - - buffer busy waits file# block# class#,select * from v$event_name where 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 unused 15 system undo header 16 system undo block 17 undo header 18 undo block,Note: Before 10g, P3 was BBW type If P3 in 100,110,120,130 then read Now “read by other session” Else Write, P3 in 200,210,220,230, 231,Joining ASH with v$waitstat,select o.object_name obj, o.object_type otype, ash.SQL_ID, w.class from v$active_session_history ash, ( select rownum class#, class from v$waitstat ) w, all_objects o where 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 block TOTO1 TABLE 8gz51m9hg5yuf data block TOTO1 TABLE 8gz51m9hg5yuf segment header TOTO1 TABLE 8gz51m9hg5yuf data block,Alternative to ASH: AWR,select to_char(BEGIN_INTERVAL_TIME,DD-MON HH:MI), , s.BUFFER_BUSY_WAITS_DELTA from dba_hist_seg_stat s, dba_hist_snapshot sn, obj$ o where BUFFER_BUSY_WAITS_DELTA 100 and sn.snap_id = s.snap_id and o.obj# = s.obj#;,TO_CHAR(BEGI NAME BUFFER_BUSY_WAITS_DELTA - - - 11-JAN 10:21 TOTO1 58447,Example: BBW with Insert,Concurrent inserts will insert into the same block Each session has to wait for the previous session to finish its write Usually pretty fast Contention builds on highly concurrent applications Lack of Free Lists Not Using ASSM (Automatic Segment Space Management),Example: Lack of Free List,S1,S2,S3,S4,4 Sess