ÎÒÍ.: ÎÒÍ.: ÎÒÍ.: How to track logfile switch

From: Yavor Ivanov <Yavor_Ivanov_at_stemo.bg>
Date: Fri, 3 Oct 2008 20:01:40 +0300
Message-ID: <BD17E2E69E17C64A9684C940EB580E03010D7D5B541D@stemodc1.stemo.local>

Maybe I have to write this again.
The redo log files are 512 MB each. The archivelog files are usually 1-3 MB. So increasing redo log size will solve nothing. Maybe you are advising larger redo log buffer, but why? Making more redo log groups will help. But I still wonder why the switches are so frequent.

I know DML generates redo. But the redo log files are too far from filling. I was trying to catch "alter system " using AFTER DDL trigger, but maybe it is not DDL too. I may try using database auditing, but it will not be easy (political reasons). So I was just wondering if there is some other way to find what may cause so strange frequent log switches

Regards,
Yavor Ivanov

---

Îò: Bort, Guillermo [guillermo.bort_at_eds.com] Èçïðàòåíè: 03 Îêòîìâðè 2008 ã. 19:43
Äî: Yavor Ivanov; Bobak, Mark; oracle-l
Òåìà: RE: ÎÒÍ.: ÎÒÍ.: How to track logfile switch

Alter system are not DML. DML are regular old fashioned SQL such as insert, update and delete. As someone else said in the list, every DML generates redo information and when the redo log file becomes full a log switch automatically occurs. This is basic transaction processing in Oracle. I would go with increasing the size of redo log buffer and maybe redo log files to have less than four log switches per hour during normal operation. And take it from there. I reckon the frequency of the problem will decrease greatly or the problem may even dissapear.

HTH Guillermo Alan Bort
EDS - ITO DBA Main Group

From: Yavor Ivanov [mailto:Yavor_Ivanov_at_stemo.bg] Sent: Friday, October 03, 2008 1:00 PM
To: Bort, Guillermo; Bobak, Mark; oracle-l Subject: ÎÒÍ.: ÎÒÍ.: How to track logfile switch

This sound promising. What DML could cause log switch (other than "alter system switch logfile" and "alter system archive log ...")?

The issue happens during normal daily load.

Regards,
Yavor Ivanov

---

Îò: Bort, Guillermo [guillermo.bort_at_eds.com] Èçïðàòåíè: 03 Îêòîìâðè 2008 ã. 18:45
Äî: Yavor Ivanov; Bobak, Mark; oracle-l
Òåìà: RE: ÎÒÍ.: How to track logfile switch It may be silly to mention this, however log switches can be caused by valid dml processing not just alter system switch logfile. I’ve found that increasing redo log size and redo log buffer often helps lower the number of Checkpoint not Complete. Do you notice this issue during periods of high transaction load or batch load? Also, do you have fast_start_mttr_target set to a static value?

HTH Guillermo Alan Bort
EDS - ITO DBA Main Group

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Yavor Ivanov Sent: Friday, October 03, 2008 12:36 PM
To: Bobak, Mark; oracle-l
Subject: ÎÒÍ.: How to track logfile switch

 The value of ARCHIVE_LAG_TARGET is 0

Unfortunately the message "Thread X cannot allocate new log, sequence XXXX" is sometimes followed by "Checkpoint not complete". So I cannot ignore it easily. Yesterday there was a short but noticeable database hang at the same time when this happened.

Regards,
Yavor Ivanov
[SNIP]

--
http://www.freelists.org/webpage/oracle-l

Received on Fri Oct 03 2008 - 12:01:40 CDT