Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Checkpoint not complete and too frequent archiving

user1765133Mar 30 2016 — edited Apr 1 2016

Hello,

I'm facing some very slow response time on some SQL Queries.

I thought first it was a problem of statistics but the Explain plan shows my Indexes are used.

I see 2 things :

1) Although most of my users are only reading data (very few insert), I have a 35Mb archivelog created every 2 minutes ! It seems to be huge in comparison of my Database (25Gb) et the activity on it.

2) In my alert.log, I see :

Wed Mar 30 12:34:45 2016

Thread 1 cannot allocate new log, sequence 29292

Checkpoint not complete

  Current log# 4 seq# 29291 mem# 0: D:\LAUDREN\SYTEL\DATABASE\VERDUN\REDO04.LOG

Thread 1 cannot allocate new log, sequence 29292

Private strand flush not complete

  Current log# 4 seq# 29291 mem# 0: D:\LAUDREN\SYTEL\DATABASE\VERDUN\REDO04.LOG

Thread 1 advanced to log sequence 29292 (LGWR switch)

  Current log# 3 seq# 29292 mem# 0: D:\LAUDREN\SYTEL\DATABASE\VERDUN\REDO03.LOG

Wed Mar 30 12:34:55 2016

Archived Log entry 29289 added for thread 1 sequence 29291 ID 0xc32bf41c dest 1:

Wed Mar 30 12:35:33 2016

Thread 1 cannot allocate new log, sequence 29293

Checkpoint not complete

  Current log# 3 seq# 29292 mem# 0: D:\LAUDREN\SYTEL\DATABASE\VERDUN\REDO03.LOG

Thread 1 advanced to log sequence 29293 (LGWR switch)

  Current log# 1 seq# 29293 mem# 0: D:\LAUDREN\SYTEL\DATABASE\VERDUN\REDO01.LOG

Wed Mar 30 12:35:40 2016

Archived Log entry 29290 added for thread 1 sequence 29292 ID 0xc32bf41c dest 1:

I've added 2 more redologs. I now have 5 :

select l.group#, lf.member, l.bytes/1024/1024 mb, l.status, l.archived

from v$logfile lf, v$log l

where l.group# = lf.group#

order by 1, 2;

GROUP# MEMBER                                                     MB STATUS           ARC

------ -------------------------------------------------- ---------- ---------------- ---

     1 D:\LAUDREN\SYTEL\DATABASE\VERDUN\REDO01.LOG                50 ACTIVE           YES

     2 D:\LAUDREN\SYTEL\DATABASE\VERDUN\REDO02.LOG                50 CURRENT          NO

     3 D:\LAUDREN\SYTEL\DATABASE\VERDUN\REDO03.LOG                50 ACTIVE           YES

     4 D:\LAUDREN\SYTEL\DATABASE\VERDUN\REDO04.LOG                50 ACTIVE           YES

     5 D:\LAUDREN\SYTEL\DATABASE\VERDUN\REDO05.LOG                50 ACTIVE           YES

I also upsized my datafiles system and undo but I still have a message in alert.log, the archive files are still generated every 2 minutes, and some query doesn't respond.

Where does the "Checkpoint not complete" come from ?

What should I do to avoid so much archive logs ?


Any help would be appreciate because this is a Production Database...

This post has been answered by user1765133 on Apr 1 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 29 2016
Added on Mar 30 2016
11 comments
2,860 views