checkpoint happening after a long time

From: Purav Chovatia <puravc_at_gmail.com>
Date: Sat, 18 Aug 2018 17:11:13 +0530
Message-ID: <CADrzpjEqY5eJXM-wZqv8nbEmUsm7eS3aaS8+Cf5gBeFVU1PBxA_at_mail.gmail.com>



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

--
http://www.freelists.org/webpage/oracle-l
Received on Sat Aug 18 2018 - 13:41:13 CEST

Original text of this message