【正文】 Buffer Cache IO Locks Network .4 REDO Log Files Data Files DBWR LGWR User2 User1 User3 Log Buffer Buffer Cache Log Buffer Buffer Cache SGA Library Cache Oracle Memory Structures .5 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 ) .6 Query 0. Parse statement 1. Find object information in data dictionary 2. Calculate execution plan 3. If full table scan ? Look at all blocks of table 4. If index find root of index and follow to key 5. Data Dictionary will have info about table or index block ? File ? Block 6. Once you know the block DBA (file + block) … Select ename from emp where empno = 12。 and (+)= group by sql_id, current_obj, current_file, current_block, , order by count(*) / CNT SQL_ID OBJN OTYPE FN BLOCKN 84 a09r4dwjpv01q MYDUAL TABLE 1 93170 SQL Statement: Success Extra: Hot block .24 CBC: OEM .25 CBC: ADDM Problem SQL Statement Solution? .26 CBC – Further Investigation select * from v$event_name where name = 39。),39。),16,0) laddr from v$active_session_history where event=39。00000004D810833039。latch: cache%39。) ) from v$active_session_history where event like 39。latch: cache buffers chains39。 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 = 39。 NAME P1 P2 P3 buffer busy waits file block class select * from v$event_name where name = 39。 and (+)= and (+)= Order by sample_time。 TO_CHAR(BEGI NAME BUFFER_BUSY_WAITS_DELTA 11JAN 10:21 TOTO1 58447 .67 Example: BBW with Insert ?Concurrent inserts will insert into the same block ?Each session has to wait for the previous session to finish it?s write ?Usually pretty fast ?Contention builds on highly concurrent applications ?Lack of Free Lists ?Not Using ASSM (Automatic Segment Space Management) .68 Example: Lack of Free List S1 S2 S3 S4 4 Sessions running Insert into toto values (null, ?a?)。 .70 Solution 2: ASSM ?Multiple Bitmap Blocks Track Free Space ?Unformatted ?Up to 25% Free ?Up to 50% Free ?Up to 75% Free ?Full ?Free block chosen by Process ID ?Possibly instance for RAC .71 Solution 2: ASSM Header Level 2 Level 1 Level 1 Level 1 Data Blocks Bitmap Blocks .72 Tablespace Types : ASSM select tablespace_name, extent_management LOCAL, allocation_type EXTENTS, segment_space_management ASSM, initial_extent from dba_tablespaces TABLESPACE_NAME LOCAL EXTENTS ASSM SYSTEM LOCAL SYSTEM MANUAL UNDOTBS1 LOCAL SYSTEM MANUAL SYSAUX LOCAL SYSTEM AUTO TEMP LOCAL UNIFORM MANUAL USERS LOCAL SYSTEM AUTO EXAMPLE LOCAL SYSTEM AUTO DATA LOCAL SYSTEM MANUAL create tablespace data2 datafile 39。 .77 BBW and RBS Segs OBJN OTYPE FILEN BLOCKN SQL_ID BLOCK_TYPE 54962 TOTO1 TABLE 16 45012 8gz51m9hg5yuf data block 54962 TOTO1 TABLE 16 161 8gz51m9hg5yuf segment header 0 14 9 8gz51m9hg5yuf 87 0 14 9 8gz51m9hg5yuf 87 Select CURRENT_OBJ||39。||to_char() block_type from v$active_session_history ash, (select rownum class, class from v$waitstat ) w, all_objects o where event=39。 SEGMENT_NAME SEGMENT_TYPE R2 ROLLBACK .79 ADDM finds old style RBS .80 BBW: File Header ?Querying ASH, make sure ?P1=current_file ?P2=current_block ?If not, use p1, p2 and not current_object Time P1 P2 OBJN OTYPE FN BLOCKN BLOCK_TYPE 11:44 202 2 1 0 0 file header block 11:44 202 2 TOTO TABLE 1 60218 file header block 11:44 202 2 TOTO TABLE 1 60218 file header block 11:44 202 2 TOTO TABLE 1 60218 file header block 11:44 202 2 TOTO TABLE 1 60218 file header block SELECT FROM ALL_OBJECTS A, ( SELECT * FROM ALL_OBJECTS WHERE ROWNUM 1000) B ORDER BY .81 BBW : File Header Time P1 P2 OBJN OTYPE FN BLOCKN BLOCK_TYPE 11:44 202 2 TOTO TABLE 1 60218 file header block Solution is make initial and next extent larger in Temp Table Space ADDM doesn?t say much .82 write plete waits ? Usually happens in tandem with free buffer ? Tune by ? Increase data block cache ? Happens because shadow wants to access blocks that are currently being written to disk by DBWR ? also seen it happen when there is a lot of write to sort the waits are on block 2 of the temp tablespace file .83 Write Complete Waits LRU DBWR Dirty List of Buffer Headers LRUW Session .84 Buffer Ext