| A scheduler job getting hung causing critical issues [message #611133] |
Fri, 28 March 2014 04:30  |
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
|
|
|
|
| Re: A scheduler job getting hung causing critical issues [message #611136 is a reply to message #611133] |
Fri, 28 March 2014 05:37  |
John Watson
Messages: 8962
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
