We are able to tune CKPT process with these paramaters and they should be configured together.
e.g.
Check current parameter:
SQL> show parameter log
NAME TYPE VALUE
------------------------------------ ----------- -------------------
log_buffer integer 5361664
log_checkpoint_interval integer 0
log_checkpoint_timeout integer 1200
Check log file size of one group
SQL> select group#, bytes, blocksize, members from v$log;
GROUP# BYTES BLOCKSIZE MEMBERS
---------- ---------- ---------- ----------
1 52428800 512 2
2 52428800 512 2
Log file size -> 52428800 bytes -> 50 MB
Log buffer should be -> 50 MB * 3 = 150 MB -> 157286400
Log checkpoint timeout could be -> 600 -> 10 minutes
Log checkpoint interval could be -> 0 or 102400 (102400 * 512(OS Block size) -> 52428800 bytes -> 50 MB is equal to log file size)
Apply the setting:
init.ora file:
*.log_buffer=157286400
*.log_checkpoint_interval=102400
*.log_checkpoint_timeout=600
Then restart the instance to perform.
Check lastly:
SQL> show parameter log
NAME TYPE VALUE
------------------------------------ ----------- -------------------
log_buffer integer 157286400
log_checkpoint_interval integer 102400
log_checkpoint_timeout integer 600
Deep notes
Log group members should be at least 2, same size, same amount and located in different paths to take backup. (log members are exactly same, 1-> 1)
Log checkpoint timeout parameter means that at most 600 seconds later, dirty blocks are written to database files from SGA.
Log checkpoint interval parameter means that after active log file size exceeds to 50 MB, CKPT is triggered for DBWR. If this parameter is "0", Oracle decides CKPT running time automatically.
