Buffer%20Cache%20Waits

1
Buffer Cache Waits
2
In This Section

1. latch cache buffers chains
   
2. latch cache buffers lru chain
3. latch cache buffer handles
4. Free Buffer Wait
5. Buffer Busy Wait
6. Write Complete Wait
7. Buffer Exterminate

3
Buffer Cache
Locks
Buffer Cache
Lib Cache
Redo
Network
IO
4
Oracle Memory Structures
DBWR
LGWR
User1
User2
User3
REDO Log Files
Data Files
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
Select ename from emp where empno 12

• 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)

7
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
8
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?
9
Concepts

• To understand contention on the buffer cache,
• need to understand
• Linked Lists
• Hashing
• Buckets

10
Double Linked Lists
Address
03C38F60
03C39000
03C39478
03C39000
Next
03C39478
03C38F60
03C39000
Previous
11
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

12
Hash Bucket Fill
Data Block
Hash Blocks file block s Result in a
bucket Put Block in bucket
0
?
Hash Blocks 1 file 437 block
s (1437) mod 4 2
1
?
2
?
3
?
After a while the buckets become populated with
blocks
13
Latches Protect Bucket Contents
Buffer Headers
Hash bucket
Data Blocks
latches
Buffer Headers contents described by XBH
14
Xbh

• Describes Contents of Buffer Headers

SQLgt desc xbh 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
15
Cache
ADDR
03C38F60
03C39000
03C39478
03C39000
03C39478
NXT_HASH
03C38F60
03C39000
PRV_HASH
16
XBH describes Headers
Buffer Headers
Hash bucket
Data Blocks
latches
HLADDR
ADDR
ADDR
NXT_HASH
xbh ADDR DBARFIL DBABLK OBJ HLADDR
NXT_HASH PRV_HASH
PRV_HASH
DBARFIL DBABLK OBJ
17
To Find a Block
Buffer Headers
Hash bucket
Data Blocks
latches
Shadow Process
4
2
3
5
1

1. Hash the block address
2. Get Bucket latch
3. Look for header
4. Found, read block in cache
5. Not Found Read block off disk

2
3
4
5
18
Cache Buffers Chains
Hash Buckets
latches
Block Headers
Sessions
Data Blocks
s5

Cache Buffer Chain
s4
s3

s2
s1

Contention if too many accesses on a bucket
19
Examples

• Look up Table
• Nested Loops

t1
t2
Index_t2
Select t1.val, t2.val from t1, t2 where t1.c1
value and t2.id t1.id
20
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 xdual
• How do you find the SQL?

21
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 SQLNet 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
• SQLNet 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 -gt ordered by misses desc Latch Name
Requests Misses Sleeps Sleeps
1-gt4 -------------------- ---------- -------
------ ------------ 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
22
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
23
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 vactive_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() /
SQL Statement Success
Extra Hot block
CNT SQL_ID OBJN OTYPE FN
BLOCKN ---- ------------- -------- ------ ---
------ 84 a09r4dwjpv01q MYDUAL TABLE 1
93170
24
CBC OEM
25
CBC ADDM
Problem
SQL Statement
Solution?
26
CBC Further Investigation
select from vevent_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
27
CBC whats the hot block

• Can get it from ASH
• Current_file
• Current_block
• Where eventlatch 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 xbh.hladdr

28
Hot Block XBH.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

29
CBC Real Time
select count(), lpad(replace(to_char(p1,'
XXXXXXXXX'),' ','0'),16,0) laddr from
vactive_session_history where event'latch
cache buffers chains' group by p1
COUNT() LADDR ---------- ----------------
4933 00000004D8108330
select o.name, bh.dbarfil, bh.dbablk, bh.tch from
xbh bh, obj o where tch gt 100 and
hladdr'00000004D8108330' and
o.objbh.obj order by tch

NAME DBARFIL DBABLK TCH -----------
------- ------ ---- EMP_CLUSTER 4 394
120
30
Putting into one Query

• select
• name, file, dbablk, obj, tch, hladdr
• from xbh bh
• , obj o
• where
• o.obj()bh.obj and
• hladdr in
• (
• select ltrim(to_char(p1,'XXXXXXXXXX') )
• from vactive_session_history
• where event like 'latch cache'
• group by p1
• having count() gt 5
• )
• and tch gt 5
• order by tch

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
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
31
Consistent Read Blocks
Both have same file and block and hash to same
bucket
Current Block (XCUR)
Consistent Read (CR)
Clone Undo
s2
s1
Select
Update
32
CBC Consistent Read Blocks
Hash Buckets
Block Headers
latches
s5
Cache Buffer Chain
s4
s3
Max length _db_block_max_cr_dba 10g 6
s2
s1
Contention Too Many Buffers in Bucket
33
Consistent Read Copies
select count() , name , file
, dbablk , hladdr from xbh bh
, obj o where o.obj()bh.obj and
hladdr in ( select ltrim(to_char(p1,'XXXXXX
XXXX') ) from vactive_session_history
where event like 'latch cache' group by p1
) group by name,file, dbablk, hladdr having
count() gt 1 order by count()
CNT NAME FILE DBABLK HLADDR ---
---------- ------ ------- -------- 14 MYDUAL
1 93170 2C9F4B20
34
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 xdual 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 vactive_session_history ash,
vsqlstats sql where event'latch cache
buffers chains' and sql.sql_id()ash.sql_id group
by ash.sql_id, sql_text
35
Latch cache buffer handles

• Buffers can be pinned
• Possibly increase
• _db_handles_cached 5
• Unsupported
• Used when pinning block headers for expected
  reuse

36
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

37
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

38
Finding a Free Block
When a session reads a block Into the bufffer
cache how does it find a FREE spot?
39
Finding a Free Block
Buffer Headers
Hash bucket
Data Blocks
latches

1. Arrange the Buffer Headers into an LRU List
2. Scan LRU for a free block

40
Cache Buffers LRU
entry in xbh
41
Xbh

• Describes Buffer Headers

SQLgt desc xbh 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)
Cache buffer chains
HLADDR RAW(4) NXT_HASH
RAW(4) PRV_HASH RAW(4)
NXT_REPL RAW(4) PRV_REPL
RAW(4)
LRU
42
LRU Chain
ADDR
03C38F60
03C39000
03C39478
03C39000
03C38F60
NXT_HASH
03C38F60
03C39000
PRV_HASH
03C385F4
03C39478
03C38638
NXT_REPL PRV_REPL
03C38554
03C38514
03C38620
43
Cache Buffers LRU list
44
Cache Buffers LRU list
LRU Chain of Buffer Headers
Buffer Cache
45
Cache Buffers LRU Latch
Buffer Headers
LRU
MRU
LRU latch
Hot
Cold
LRU Least Recently Used
MRU Most Recently Used
One LRU Latch protects the linked list during
changes to the list
46
Session Searching for Free Blocks
LRU
Buffer Headers
MRU

1. Go to the LRU end of data blocks
2. Look for first non-dirty block
3. If search too many post DBWR to make free
4. Free Buffer wait

Session Shadow
47
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

48
Session Finding a Free Block
LRU Latch
MRU
LRU
Hot End
Mid-Point Insertion
Find Free Block
Get LRU Latch
Insert Header Release LRU Latch
session
49
DBWR taking Dirty Blocks off
Buffer Headers LRU
LRU
MRU
latch
Dirty List of Buffer Headers LRUW
LRU latch also covers DBWR list of dirty blocs
DBWR
50
Cache Buffers LRU Latch
LRU
MRU
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
51
Solution Multiple Sets
Set 1
LRU Latch 1
Set 2
LRU Latch 2
_db_block_lru_latches 8 10gR2 with cpu_count
2 XKCBWDS set descriptor
52
Working Sets
select ds.set_id, ds.blk_size ,
bp.BUFFERS, nvl(bp.name.unused)
from xkcbwds ds, vbuffer_pool
bp where ds.set_id gt bp.lo_setid ()
and ds.set_id lt 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
53
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

54
simulator lru latch
55
CBC Further Investigation
select from vevent_name where name 'latch
free'
PARAMETER1 PARAMETER2 PARAMETER3 ----------
---------- ---------- address number
tries
select p2, count() from vactive_session_history
where event'latch free' group by p2
P2 COUNT() ---------- ----------
127 3556
select from vlatchname where latch127
LATCH NAME ----------
-------------- 127 simulator lru latch
56
db_cache_advice
Alter system set db_cache_adviceoff
Group other is very small compared to I/O wait
time not a problem
57
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

58
Buffer Busy Waits

• User 1 tries to change a buffer header
• User 2 has buffer header locked (pinned)

0
1
2
3
User2
User1
59
BBW Solution Paths
There is a hot block, eliminate the hot block

• 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 -gt freelists
• Freelist blocks free lists groups
• File Header Block look at extent allocation

60
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
61
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
62
BBW OEM
63
Solutions
64
BBW Block Types

• select rownum n,ws.class
• from vwaitstat

select from vevent_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
NAME P1 P2
P3 ----------------- ----- ------ ----- buffer
busy waits file block class
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
65
Joining ASH with vwaitstat
select o.object_name obj,
o.object_type otype, ash.SQL_ID,
w.class from vactive_session_history ash, (
select rownum class, class from vwaitstat ) 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
66
Alternative to ASH AWR
select to_char(BEGIN_INTERVAL_TIME,'DD-MON
HHMI'), o.name, s.BUFFER_BUSY_WAITS_DELTA
from dba_hist_seg_stat s,
dba_hist_snapshot sn, obj o where
BUFFER_BUSY_WAITS_DELTA gt 100 and sn.snap_id
s.snap_id and o.obj s.obj
TO_CHAR(BEGI NAME BUFFER_BUSY_WAITS_DELTA ------
------ ----- ---------------------- 11-JAN 1021
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 its 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
4 Sessions running Insert into toto values
(null, a) Commit
S4
S1
S2
S3
OBJN OTYPE FILEN BLOCKN SQL_ID
BLOCK_TYPE ----------- ------ ------ ------
------------- ------------ 54962 TOTO1 TABLE
16 45012 8gz51m9hg5yuf data block 54962 TOTO1
TABLE 16 161 8gz51m9hg5yuf segment header
69
Solution1 Free Lists
S4
S1
S2
S3
4 Sessions running Insert into toto values (null,
a) Commit
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
Bitmap Blocks
Data 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
'/d3/kyle/data2_01.dbf' 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

• Use Reverse Key indexes
• Breaks Index scans
• Hash Partition Index
• More IOs per index access

OBJN OTYPE FILEN BLOCKN SQL_ID
BLOCK_TYPE --------- ----- ----- -------
------------- ------------ BBW_INDEX_INDEX 1
113599 97dgthz60u28d data block 1
Index
Session 1
Increasing index key creates a hot spot on the
leading index leaf
Session 2
Session 3
75
BBW on Index ADDM Recs
Also consider reversing the key
76
Example BBW on RBS

• IF BBW happen on old style RBS
• Class gt 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_managementauto
scopespfile
77
BBW and RBS Segs
Select 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_type from
vactive_session_history ash, (select
rownum class, class from vwaitstat ) 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
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
78
Further Investigation RBS

• Old Style RBS if Class gt 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
• P1current_file
• P2current_block
• If not, use p1, p2 and not current_object

Time P1 P2 OBJN OTYPE FN BLOCKN
BLOCK_TYPE ----- --- --- ---- ----- -- ------
----------------- 1144 202 2 -1 0
0 file header block 1144 202 2 TOTO TABLE 1
60218 file header block 1144 202 2 TOTO TABLE
1 60218 file header block 1144 202 2 TOTO
TABLE 1 60218 file header block 1144 202 2
TOTO TABLE 1 60218 file header block
SELECT A.OBJECT_ID FROM ALL_OBJECTS A, ( SELECT
FROM ALL_OBJECTS WHERE ROWNUM lt 1000) B ORDER BY
A.OBJECT_NAME
81
BBW File Header
Time P1 P2 OBJN OTYPE FN BLOCKN
BLOCK_TYPE ----- --- --- ---- ----- -- ------
----------------- 1144 202 2 TOTO TABLE 1
60218 file header block
ADDM doesnt say much
Solution is make initial and next extent larger
in Temp Table Space
82
write complete 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
Dirty List of Buffer Headers LRUW
Session
DBWR
84
Buffer Exterminate

• Buffer cache dynamically resized
• VSGA_DYNAMIC_COMPONENTS displays information
  about the dynamic SGA components. This view
  summarizes information based on all completed SGA
  resize operations since instance startup.
• VSGA_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 component.
• VSGA_DYNAMIC_FREE_MEMORY displays information
  about the amount of SGA memory available for
  future dynamic SGA resize operations.

Alter system set db_cache_size50M
85
Summary Buffer Cache Waits

• latch cache buffers chains - find SQL
• Eliminate hot spots
  
• latch cache buffers lru chain increase sets
• Free Buffer Wait - increase cache size
• Buffer Busy Wait
• Index alleviate hot spots, partition
• Data DML add free lists or use ASSM
• File Segment Header looked at high extent
  allocations
• Write Complete Waits - increase cache size