| A question about checkpoint and log switch [message #59098] |
Tue, 28 October 2003 07:41  |
Jadie
Messages: 64
Registered: January 2002
|
Member |
|
|
Hi everyone, I have a question, hope anyone can give me some advice.
In my database, the redo log file is 10MB, the parameter log_checkpoint_interval = 10000, log_checkpoint_timeout = 1800. But whatever the database is idle or busy (actually most of the time the sysdem is idle), it keeps creating one archive log every 10 to 15 minutes. So there are a lot of archive logs generated every day!
I don't know why, and can anyone tell me how to investigate it?
Thanks....
Jadie
|
|
|
|
|
|
| Re: A question about checkpoint and log switch [message #59102 is a reply to message #59098] |
Tue, 28 October 2003 09:21  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
You could increase log_checkpoint_interval to a much higher value..I generally make this a very high value like 9999999 or 0 (to disable it) and disable time based checkpoints(ie log_checkpoint_timeout=0) and make the checkpoint occur during the log switches (in your case every 10MB)..
Also you could control checkpointing/recovery time using FAST_START_MTTR_TARGET..
|
|
|
|
|
|
| Re: A question about checkpoint and log switch [message #59107 is a reply to message #59102] |
Tue, 28 October 2003 09:54 |
Jadie
Messages: 64
Registered: January 2002
|
Member |
|
|
Thanks for your reply.
According to the Oracle documentation, log_checkpoint_interval and log_checkpoint_timeout just are used to set the checkpoint flequency, but not log switch.
log_checkpoint_interval=10000 (5MB redo log triggers a checkpoint)
log_checkpoint_timeout=1800 (30 minutes trigger a checkpoint)
This setting is pretty high for a OLTP system.
What I am confused is whatever the database has one log switch every 10-15 minutes even in the early morning there are no transactions processed... :(
Thanks again. I really appreciate it.
Jadie
|
|
|
|
| Re: A question about checkpoint and log switch [message #59110 is a reply to message #59107] |
Tue, 28 October 2003 12:18 |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
Oh,I thought you mentioned there is a checkpoint every 10-15 min...Oops
Right,those parameters control the checkpoints and not the log switch.. what controls frequency of log switches ?
- size of the redolog file ( smaller the file , more the log switches)
- amount of redo generated by transactions ( larger the redo, more the log switches)
- time based log-switching : ARCHIVE_LAG_TARGET parameter
-manual switching
Are you sure you are not doing hotbackup during that time interval when there are more frequent log switches ? maybe bulk loads ? manual switching ...
maybe you could increase your logfile size ?
-Thiru
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
