A few days ago, I published a simple query you could run against v$log_history to show the time between log file switches. So if you use this script and decide that your log file switches are happening at inappropriate intervals, what can you do ?
If the intervals are too short (which could cause excessive activity from the database writer(s) as the log file checkpoint occurs) you can add new, larger, log files and drop the old ones.
If the intervals are too long – or if the timing fluctuates wildly and you want to make the switches more regular - then you can use the parameter archive_lag_target (introduced in 9i) to set a timeout interval in seconds for log file switches. If a log file switch has not occurred within the specified amount of time then a switch is forced (it’s actually a little more subtle than that, but it’s a fair approximation) and the portion of the online redo log that has been used is archived (which is why you can see some systems where the archived redo log files vary in size during the course of the day).
Historically people used to use tools like cron or dbms_job to issue a ‘alter system switch logfile’ command. From 9i it’s much tidier to take advantage of the appropriate parameter.
There are many different types of checkpoint, and the list varies with version of Oracle. The range of options for 10.2.0.3 seems to be:
- Instance Recovery Checkpoint
- Media Recovery Checkpoint
- Thread Checkpoint
- Interval Checkpoint
- Tablespace Checkpoint
- PQ Tablespace Checkpoint
- Close Database Checkpoint
- Incremental Checkpoint
- Local Database Checkpoint
- Global Database Checkpoint
- Object Reuse Checkpoint
- Object Checkpoint
Of these, I think the checkpoint we see at a log file switch is probably the one called the Thread Checkpoint.
Addendum 2 (Sept 2010)
I’ve been prompted by a discussion on the OTN database forum to add a couple of updates. First, 126.96.36.199 seems to have two more checkpoint types:
- RBR Checkpoint
- Multiple Object Checkpoint
(RBR checkpoint is probably short for “re-use block range”).
Secondly, it’s worth noting that different checkpoints requests can have different priorities – some have to complete as rapidly as possible, some can be performed fairly casually. For example, a Parallel Query (or serial direct path read) needs to issue a checkpoint, and the query can’t start running until the checkpoint is complete – so it’s high priority checkpoint; on the other hand when a log file switch occurs it’s “nice” to get the checkpoint complete before too many more log file switches have completed, but clearing the checkpoint queue is not urgent.
Finally, the thread raised a quote from the OCA guide for 11g that ‘checkpoints don’t occur on log file switch and haven’t since 8i’. The checkpoint behaviour may have changed at 8i, but this statement is essentially wrong, as can be seen if you set parameter log_checkpoints_to_alert to true and then issue a few calls to switch logfiles. The alert log (11g) will show text like:
Wed Sep 22 23:06:21 2010 ALTER SYSTEM SET log_checkpoints_to_alert=TRUE SCOPE=MEMORY; Wed Sep 22 23:06:26 2010 Thread 1 cannot allocate new log, sequence 1908 Private strand flush not complete Current log# 2 seq# 1907 mem# 0: C:\ORACLE\ORADATA\D11G\D11G\REDO02.LOG Beginning log switch checkpoint up to RBA [0x774.2.10], SCN: 17046722 Thread 1 advanced to log sequence 1908 Current log# 3 seq# 1908 mem# 0: C:\ORACLE\ORADATA\D11G\D11G\REDO03.LOG Wed Sep 22 23:06:53 2010 Beginning log switch checkpoint up to RBA [0x775.2.10], SCN: 17046733 Thread 1 advanced to log sequence 1909 Current log# 1 seq# 1909 mem# 0: C:\ORACLE\ORADATA\D11G\D11G\REDO01.LOG Wed Sep 22 23:07:31 2010 Thread 1 cannot allocate new log, sequence 1910 Checkpoint not complete Current log# 1 seq# 1909 mem# 0: C:\ORACLE\ORADATA\D11G\D11G\REDO01.LOG Wed Sep 22 23:07:34 2010 Completed checkpoint up to RBA [0x774.2.10], SCN: 17046722 Beginning log switch checkpoint up to RBA [0x776.2.10], SCN: 17046748 Thread 1 advanced to log sequence 1910 Current log# 2 seq# 1910 mem# 0: C:\ORACLE\ORADATA\D11G\D11G\REDO02.LOG
The whole “what is a checkpoint” has many more variations than you might expect so any simple statements about “checkpointing” should be treated with a little caution – they may give the right sort of indication of what the purpose is but there’s a strong chance that there are far more details behind the scenes that you might (in special cases) need to know.
Consider, for example the parameter _defer_log_count which has the description: “Number of log boundaries media recovery checkpoint lags behind”. This probably tells us that when parameter _defer_log_boundary_ckpt is set to true Oracle will allow some log file switches to occur without trying to force a “log switch checkpoint”. (But those are 11g parameters, and they’re hidden parameters anyway so you shouldn’t be playing with them).