Home » RDBMS Server » Server Administration » A scheduler job getting hung causing critical issues (Oracle 11.2.0.2 on Linux.)
- A scheduler job getting hung causing critical issues [message #611133] Fri, 28 March 2014 04:30 Go to next message
orausern
Messages: 826
Registered: December 2005
Senior Member
Hi Experts,

We are on Oracle 11.2.0.2 on Linux. We have some jobs that are scheduled to run at night time like 1 am etc. These jobs copy data from some of the main OLTP tables in the archive tables that are in the same schema. We use PARALLEL hint in this copy sqls. Now most of the times the job finishes in somewhere between 1 to 2 minutes. But yesterday the job was hung for more than 5 hours. It caused outage as the tables got locked and we stopped the job manually to fix the outage. This issues has happened twice thus far - once in a less critical environment and once in production. Today the same job finished in 20 seconds. Could this be due to some bug in scheduler?


I will be thankful for your inputs.

OrauserN

Report message to a moderator

- Re: A scheduler job getting hung causing critical issues [message #611136 is a reply to message #611133] Fri, 28 March 2014 05:37 Go to previous messageGo to next message
John Watson
Messages: 8974
Registered: January 2010
Location: Global Village
Senior Member
A bug in the Scheduler? No.
You say that the tables were locked. How do you know this? Almost certainly, it was just a row lock. If there is an uncommitted transaction against a table, your parallel insert will hang until the transaction commits.

If the operation takes only 20 seconds, don't use parallelism.

Report message to a moderator

- Re: A scheduler job getting hung causing critical issues [message #611139 is a reply to message #611136] Fri, 28 March 2014 06:10 Go to previous message
orausern
Messages: 826
Registered: December 2005
Senior Member
Thank you John! Is there any way I can find out what the situation was? I mean is any such past details of past lock stored anywhere? I am thinking it was some scheduler bug.

Report message to a moderator

Previous Topic: SQL Tuning Adviser error in alert log.
Next Topic: ORA-01033 ORACLE initiation or shutdown in progess
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Apr 17 01:44:42 CDT 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.