Hello,
On a 11.2.0.2 Standard Edition single instance database, we see that
checkpoint happens after long delays. It does NOT happen during log file
switch (although the alertlog does log a line saying checkpoint). The way
we confirm is as follows:
- we ran a loop to insert 750k records in a table, commit after every 100
records
- we were constantly monitoring iostat on the data disk and it was showing
almost zero writes
- the loop completed in a couple of minutes and we kept waiting looking at
iostats but nothing in it
- we executed alter system switch logfile 2-3 times, still nothing in
iostats
- we executed alter system checkpoint and can see huge writes for 2-4
seconds on the data disk
Instead of alter system checkpoint, if we kept on switching the log file,
then we would see similar huge writes for 2-4 seconds when it is about to
rotate and come back to the log group which contains the change vectors.
I checked and found that log_checkpoint_timeout was set to 1800. I changed
it to 30 and did the test again and this time I could see checkpoints
happening
every 30 seconds and writes to data disk also happening every 30 seconds.
So then I set log_checkpoint_timeout to 0 (to disable it, hoping that
oracle would internally do it i.e. every 3 sec) but again it went back to
original behaviour i.e. no writes for a very long time OR it would happen
when a alter system checkpoint is done OR when the log switching brings
comes back to the log group that contained the change vectors.
4 redolog groups sized 2GB.
fast_start_mttr will not work since this is Oracle SE.
I think I am missing something basic. Shouldn't the writes have been spread
out so that data disk not experience spikes? Because in case of continuous
load this behaviour leads to uneven performance i.e. when the dirty buffers
are to be written out, the throughput of the system drops. OR lets say if I
am doing some load testing which lasts only a few minutes and I am
capturing iostat, iostats is misleading because it does not show the
reads/writes. As a result my sizing goes wrong.
Many Thanks,
PC
