Home » RDBMS Server » Performance Tuning » Query hang (oracle 10g r2,windows 2003 server)
Query hang [message #554057] Thu, 10 May 2012 03:39 Go to next message
zeeshan047
Messages: 99
Registered: June 2010
Location: PAKISTAN
Member

hy,

i am executing a query of creating table joining two tables exist in other database , the query take too much time and get hang.
when i check my alert log file it shows the below message. My database is in noarchive log mode and three redo log group contain 1 member each of 50mb size and set fast_start_mttr_target=0 .




Thread 1 cannot allocate new log, sequence 108850
Checkpoint not complete
Current log# 2 seq# 108849 mem# 0: E:\ORACLE\PRODUCT\10.2.0\ORADATA\ITREP\REDO02.LOG
Thread 1 advanced to log sequence 108850
Current log# 3 seq# 108850 mem# 0: E:\ORACLE\PRODUCT\10.2.0\ORADATA\ITREP\REDO03.LOG
Thu May 10 10:53:27 2012
Thread 1 advanced to log sequence 108851
Current log# 1 seq# 108851 mem# 0: E:\ORACLE\PRODUCT\10.2.0\ORADATA\ITREP\REDO01.LOG
Thu May 10 10:53:41 2012
Thread 1 cannot allocate new log, sequence 108852
Checkpoint not complete
Current log# 1 seq# 108851 mem# 0: E:\ORACLE\PRODUCT\10.2.0\ORADATA\ITREP\REDO01.LOG
Thread 1 advanced to log sequence 108852
Re: Query hang [message #554060 is a reply to message #554057] Thu, 10 May 2012 03:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Increase the size and number of redo logs.

Regards
Michel
Re: Query hang [message #554061 is a reply to message #554057] Thu, 10 May 2012 03:45 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I would create 6 new logfile groups of 1G each and drop the 3 tiny groups you have now.
Re: Query hang [message #554118 is a reply to message #554061] Thu, 10 May 2012 08:00 Go to previous messageGo to next message
zeeshan047
Messages: 99
Registered: June 2010
Location: PAKISTAN
Member

hi,

Thanks for your response i have add two log files in each group but the same error in alert log file.

Thanks
Zeeshan
Re: Query hang [message #554120 is a reply to message #554118] Thu, 10 May 2012 08:05 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Zeeshan, you haven't tried my suggestion, have you: you have done something completely different, and (as you have discovered) useless. It is very annoying when you try to help someone, and then get ignored.
Re: Query hang [message #554127 is a reply to message #554120] Thu, 10 May 2012 08:57 Go to previous message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
+1

Regards
Michel
Previous Topic: Help to alternate or rewrite the query (5 threads merged by bb)
Next Topic: filesystemio_options=SETALL is slower than ASYNCH, why?
Goto Forum:
  


Current Time: Fri Mar 29 05:56:11 CDT 2024