【正文】 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。/d3/kyle/39。 size 200M segment space management auto。 .73 BBW: ASSM ?Consider using Freelists instead of ASSM ?Normally waits on ASSM blocks should be too small to warrant using Freelists ?ASSM is easier, automatically managed 1st level bmb 2nd level bmb 3rd level bmb .74 BBW on Index Index Session 1 Session 2 Session 3 Increasing index key creates a hot spot on the leading index leaf OBJN OTYPE FILEN BLOCKN SQL_ID BLOCK_TYPE BBW_INDEX_INDEX 1 113599 97dgthz60u28d data block 1 ?Use Reverse Key indexes ?Breaks Index scans ?Hash Partition Index ?More IOs per index access .75 BBW on Index : ADDM Recs Also consider “reversing” the key .76 Example: BBW on RBS ?IF BBW happen on old style RBS ?Class 18 ?Switch to UNDO ?Old style RBS, the DBA had to figure out of RBS Segments ?With UNDO, it is automatically managed alter system set undo_management=auto scope=spfile。 .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。 39。|| objn, otype, CURRENT_FILE filen, CURRENT_BLOCK blockn, , ||39。 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。buffer busy waits39。 and (+)= and (+)= Order by sample_time。 .78 Further Investigation RBS ?Old Style RBS if Class 18 P1 P2 P3 SQL_ID COUNT(*) CLASS 14 9 87 72wa5hjpzr0by 1 14 9 87 72wa5hjpzr0by 1 14 9 87 3gkmtvxzu6p2m 1 14 9 87 3gkmtvxzu6p2m 1 6 561325 1 7zx1krfcgn88t 8 data block 14 9 87 8s29zyzr55z2t 1 select segment_name, segment_type from dba_extents where file_id = P1 and P2 between block_id and block_id + blocks – 1。 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 Exterminate ? Buffer cache dynamically resized ? V$SGA_DYNAMIC_COMPONENTS displays information about the dynamic SGA ponents. This view summarizes information based on all pleted SGA resize operations since instance startup. ? V$SGA_CURRENT_RESIZE_OPS displays information about SGA resize operations which are currently in progress. An operation can be a grow or a shrink of a dynamic SGA ponent. ? V$SGA_DYNAMIC_FREE_MEMORY displays information about the amount of SGA memory available for future dynamic SGA resize operations. Alter system set db_cache_size=50M。 .85 Summary Buffer Cache Waits 1. latch: cache buffers chains find SQL ? Eliminate hot spots 2. latch: cache buffers lru chain – increase sets 3. Free Buffer Wait increase cache size 4. Buffer Busy Wait ? Index : alleviate hot spots, partition ? Data DML : add free lists or use ASSM ? File Segment Header : looked at high extent allocations 5. Write Complete Waits increase cache size