Guru's ! need your expert recommendations !!
Topic: db_writer_processes.
Server OS: SunOS 5.11 11.3 sun4v sparc sun4v.
CPU: 24
Core :3
DB: 12.1.0.2.0 - 64bit with PSUJul2016 applied.
This is the only Local Zone (host) that hosts this particular DB.
I did 2 tests by manipulating db_writer_processes.
All I can see when the db_writer_processes is set to 8, the below DBWR statistics looks good to me.
QUESTION: depending on my server resources, Is there any concern setting the value to a higher number than the default of 1?
I did the tests in 10 minutes while no app users were logged in..
First Test: 1. set the value of db_writer_processes to none. So by default the value is 1.
2. checked DBWR Statistics. Below is the result:
dbwr statistic statistic value
------------------------------------------------------------ ---------------
Batched IO buffer defrag count 2
DBWR checkpoint buffers written 0
DBWR fusion writes 0
DBWR object drop buffers written 0
DBWR parallel query checkpoint buffers written 0
DBWR revisited being-written buffer 0
DBWR tablespace checkpoint buffers written 0
DBWR thread checkpoint buffers written 0
DBWR transaction table writes 19
DBWR undo block writes 25
DEFAULT 8k buffer busy wait 1
DEFAULT 8k free buffer wait 0
DEFAULT 8k hit ratio 95
DEFAULT 8k write complete 0
wait
IM populate (faststart) CUs accumulated write time (ms) 0
IM populate (faststart) CUs wall clock write time (ms) 0
IM populate (faststart) CUs write requests 0
IM populate (faststart) CUs writes 0
SecureFiles DBFS Link Overwrites 0
buffer is not pinned count 202,042
buffer is pinned count 79,791
cell logical write IO requests 0
cell logical write IO requests eligible for offload 0
cell num smartio automem buffer allocation attempts 0
cell num smartio automem buffer allocation failures 0
cell overwrites in flash cache 0
cell partial writes in flash cache 0
cell physical write IO bytes eligible for offload 0
cell physical write IO host network bytes written during off 0
loa
cell physical write bytes saved by smart file initialization 0
cell writes to flash cache 0
change write time 34
commit cleanout failures: buffer being written 0
dbwr statistic statistic value
------------------------------------------------------------ ---------------
commit cleanout failures: write disabled 0
data block 1
dirty buffers inspected 0
flash cache eviction: buffer pinned 0
flashback log write bytes 0
flashback log writes 0
free buffer inspected 4,292
free buffer requested 22,042
hot buffers moved to head of LRU 0
lob writes 5
lob writes unaligned 5
no buffer to keep pinned count 0
physical write IO requests 112
physical write bytes 1,507,328
physical write requests optimized 0
physical write snap IO requests new allocations 0
physical write total IO requests 1,010
physical write total bytes 7,017,022
physical write total bytes optimized 0
physical write total multi block requests 2
physical writes 184
physical writes direct 81
physical writes direct (lob) 72
physical writes direct temporary tablespace 0
physical writes from cache 103
physical writes non checkpoint 184
pinned buffers inspected 0
recieve buffer unavailable 0
recovery blocks read for lost write detection 0
recovery blocks skipped lost write checks 0
securefile direct write bytes 589,824
securefile direct write ops 12
securefile inode write time 0
shared io pool buffer get failure 0
shared io pool buffer get success 4
summed dirty queue length 0
switch current to new buffer 33
dbwr statistic statistic value
------------------------------------------------------------ ---------------
write clones created for recovery 0
write clones created in background 0
write clones created in foreground 0
Second Test: 1. set the value of db_writer_processes to 8.
2. checked DBWR Statistics. Below is the result:
dbwr statistic statistic value
------------------------------------------------------------ ---------------
Batched IO buffer defrag count 1
DBWR checkpoint buffers written 0
DBWR fusion writes 0
DBWR object drop buffers written 0
DBWR parallel query checkpoint buffers written 0
DBWR revisited being-written buffer 0
DBWR tablespace checkpoint buffers written 0
DBWR thread checkpoint buffers written 0
DBWR transaction table writes 11
DBWR undo block writes 12
DEFAULT 8k buffer busy wait 0
DEFAULT 8k free buffer wait 0
DEFAULT 8k hit ratio 95
DEFAULT 8k write complete 0
wait
IM populate (faststart) CUs accumulated write time (ms) 0
IM populate (faststart) CUs wall clock write time (ms) 0
IM populate (faststart) CUs write requests 0
IM populate (faststart) CUs writes 0
SecureFiles DBFS Link Overwrites 0
buffer is not pinned count 74,576
buffer is pinned count 29,330
cell logical write IO requests 0
cell logical write IO requests eligible for offload 0
cell num smartio automem buffer allocation attempts 0
cell num smartio automem buffer allocation failures 0
cell overwrites in flash cache 0
cell partial writes in flash cache 0
cell physical write IO bytes eligible for offload 0
cell physical write IO host network bytes written during off 0
loa
cell physical write bytes saved by smart file initialization 0
cell writes to flash cache 0
change write time 0
commit cleanout failures: buffer being written 0
dbwr statistic statistic value
------------------------------------------------------------ ---------------
commit cleanout failures: write disabled 0
data block 0
dirty buffers inspected 0
flash cache eviction: buffer pinned 0
flashback log write bytes 0
flashback log writes 0
free buffer inspected 0
free buffer requested 3,620
hot buffers moved to head of LRU 0
lob writes 0
lob writes unaligned 0
no buffer to keep pinned count 0
physical write IO requests 49
physical write bytes 409,600
physical write requests optimized 0
physical write snap IO requests new allocations 0
physical write total IO requests 329
physical write total bytes 2,670,142
physical write total bytes optimized 0
physical write total multi block requests 0
physical writes 50
physical writes direct 9
physical writes direct (lob) 0
physical writes direct temporary tablespace 0
physical writes from cache 41
physical writes non checkpoint 50
pinned buffers inspected 0
recieve buffer unavailable 0
recovery blocks read for lost write detection 0
recovery blocks skipped lost write checks 0
securefile direct write bytes 0
securefile direct write ops 0
securefile inode write time 0
shared io pool buffer get failure 0
shared io pool buffer get success 0
summed dirty queue length 0
switch current to new buffer 19
dbwr statistic statistic value
------------------------------------------------------------ ---------------
write clones created for recovery 0
write clones created in background 0
write clones created in foreground 0
Regards,
Anjan