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...