This is a very busy database on solaris 10, oracle 10.2.0.3 with 80G data buffer. There are too much update sql running and we can't tune it anymore or reduce the frequency the update sql rans. I am looking to reduce the physical write from db side. From a stackpack report, most of the physical write comes from checkpoint, because "physical writes non checkpoint" is 1,066.3 per second but "physical write" is 3,603.2 which is much higher
so I plan to disable all checkpoints (by set all the parameters related to checkpoint to 0 explicitly), and increase the redo log to let it switch every 20 minute. Does anyone have idea whether this could work? I might face "free buffer wait", but since AIO is used, it might not be a problem.
from statspack
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- -------------------
Begin Snap: 7568 12-Aug-09 11:34:02 1,103 2.5
End Snap: 7569 12-Aug-09 11:49:03 1,103 2.4
Elapsed: 15.02 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 79,872M Std Block Size: 8K
Shared Pool Size: 1,920M Log Buffer: 5,948K
Load Profile Per Second Per Transaction
~~~~~~~~~~~~ --------------- ---------------
Redo size: 7,799,655.08 307,279.81
Logical reads: 279,413.70 11,007.95
Block changes: 45,017.20 1,773.52
Physical reads: 1,553.65 61.21
Physical writes: 3,603.19 141.95
User calls: 7,960.96 313.63
Parses: 79.83 3.15
Hard parses: 0.00 0.00
Sorts: 7.54 0.30
Logons: 0.35 0.01
Executes: 7,574.60 298.41
Statistic Total per Second per Trans
--------------------------------- ------------------ -------------- ------------
physical writes 3,246,476 3,603.2 142.0
physical writes non checkpoint 960,700 1,066.3 42.0
physical reads 1,399,839 1,553.7 61.2
check what's in the cache buffer.
prod SQL> select a.STATUS,a.DIRTY,count(*) from v$bh a group by a.STATUS,a.DIRTY;
STATUS D COUNT(*)
------- - ----------
cr N 3336203
read N 3
xcur N 5700199
xcur Y 802310
Edited by: user646745 on Aug 19, 2009 7:01 AM