relo log switch and x_$kccle

[raztaboule]

hi,
i'm on AIX 4.3.3 with oracle 8.1.7
I've redo logs of 50M , log_checkpoint_interval is set to 1000000 , log_buffer is 1M .
Log switch happend each 2or3 minutes and i'm pretty sure there is not 50M written in 2minutes.
I'was expecting a log switch each 15 or 16 minutes because of the checkpoint interval so i'm confused.
I found some scripts on ixora site using a view call x_$kccle and x_$kcccp that sould gives me how many data was on the log when switch happend but those tables doesn't exist on my system and i'm not able to found the scripts that buid them.

any help/suggestion welcomed.
thanks.

 

[SantaMufasa]

Razta,

When we are doing a "deliver" of our proprietary applications databases, it generates 1GB every 20 minutes. That works out to 50MB every minute. So, depending upon what is occurring on your system at any given time, 50MB in two minutes is certainly within the realm of feasibility.

Is your database in ARCHIVE LOG mode? (To confirm the mode, issue the following command while connected "/ as sysdba":

archivelog list

If you are in ARCHIVE LOG mode, then use the "Archive destination" path to assess the sizes of your archived log files. The sizes along with the time intervals between your archived log files will confirm how much data resides in each archived redo log file.

If you are in NOARCHIVE log mode, then we must presume that barring a manual log switch (i.e., someone issuing the command "ALTER SYSTEM SWITCH LOGFILE;") your log files really are 50MB in size.

Now, to the issue of your setting "log_checkpoint_interval = 1000000". First, every time a log switch occurs, Oracle performs a CHECKPOINT. A checkpoint, however, can occur without a log switch. It can occur manually by issuing the command:

ALTER SYSTEM CHECKPOINT;

It also occurs for other events, including SHUTDOWN IMMEDIATE. But it can also occur in your case if the system processes 1000000 (1 million) system blocks of redo log entries without a CHECKPOINT occurring. Presuming that your operating system block size is 2048 bytes, this means if you process 2GB of redo log entries without a CHECKPOINT, then be sure to do a CHECKPOINT. As you can imagine, since your are checkpointing every 50MB, your log_checkpoint_interval has no effect on your system. In fact, log_checkpoint_interval has no effect unless the "<value of log_checkpoint_interval> / <os block size>" < "size of on-line redo log file". So, this parameter has no effect on how much redo resides in a log file, it simply determines whether a CHECKPOINT occurs before a log switch occurs.

I hope this helps. Please let us know your findings for your system and if you have additional follow-on questions.

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:17 (16Sep04) UTC (aka "GMT" and "Zulu"), 09:17 (16Sep04) Mountain Time)

 

[raztaboule]

thanks a lot.

In fact I'm pretty sure that 50M are not written because it's a critical but small application , within 2 month the space used have just increase for 200M.

i'm not in archived mode. I will switch in this mode within 1 month.

if i understand well log_checkpoint_interval have nothing to do with redo logs switching, and there is only two ways of switching log : manually or reaching the size of redo logs file. is that what you explain to me ?

I was on the idea that the 1000000 was in milliseconds , that's the fun , looking for something I learn something else !!!!

 

[raztaboule]

what about those tables or wiew : x_$kccle x_kcccp ?

are they standard tables/views ? how to build them ?


thanks !