Home » RDBMS Server » Server Administration » Pause long running query / job (merged 3) (Oracle10g)
Pause long running query / job (merged 3) [message #426240] Wed, 14 October 2009 09:45 Go to next message
firefly
Messages: 53
Registered: March 2009
Location: Europe
Member
Hi,

We have a large load occurring on our test system at the moment, which is impacting a different production system (same SAN) at a set time each night. Does anyone have any ideas on how to pause this load for a given set time each night? We can perform the load as an Oracle job if that makes things easier

Thanks

Firefly.
Re: Pause long running query / job (merged 3) [message #426247 is a reply to message #426240] Wed, 14 October 2009 10:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64132
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use Oracle scheduler and schedule window.

Regards
Michel
Re: Pause long running query / job (merged 3) [message #426259 is a reply to message #426247] Wed, 14 October 2009 10:37 Go to previous messageGo to next message
firefly
Messages: 53
Registered: March 2009
Location: Europe
Member
Thanks Michel, I'll take a look at that. Is there anyway to pause/resume currently running sql via a script e.g. by username, rather than creating a job? Doubt if there is but asking anyway!

Thanks
Re: Pause long running query / job (merged 3) [message #426261 is a reply to message #426240] Wed, 14 October 2009 10:41 Go to previous messageGo to next message
cookiemonster
Messages: 12415
Registered: September 2008
Location: Rainy Manchester
Senior Member
No
Re: Pause long running query / job (merged 3) [message #426262 is a reply to message #426259] Wed, 14 October 2009 10:45 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The only ways of pausing the job that I can think of rely on uit having some form of loop in it.

You could write code to check for a specific value in a table once per loop, and if it doesn't find the value, then it waits 30 seconds and looks again. That way you could pause the job by deleting a value, and restart the job by re-inserting it.

Or, you could have your package use DBMS_LOCK to try to get a named lock once per loop, and then release the lock.
That way, if you get the lock from another session then the job will pause until it can get the lock.
Re: Pause long running query / job (merged 3) [message #426263 is a reply to message #426261] Wed, 14 October 2009 10:45 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
One possible option would be to create a resource consumer group, and then limit the I/O of the user that is running the job on test in the times in question. It wouldn't "pause" the job, just limit it's I/O.

I haven't done that myself yet, but the documentation is here, and more detailed here.
Re: Pause long running query / job (merged 3) [message #426267 is a reply to message #426262] Wed, 14 October 2009 11:29 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>You could write code to check for a specific value in a table once per loop, and if it doesn't find the value, then it waits 30 seconds and looks again.

Oracle's Read Consistency may complicate this approach depending upon actual implementation details.
Re: Pause long running query / job (merged 3) [message #426420 is a reply to message #426267] Thu, 15 October 2009 06:14 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Good point - if whatever you're pausing the job for does a lot of DML, then your chances of getting an ORA-01555 will go up a lot.
Although if the job that's being paused is just a data load, then this shouldn't be a problem.

Really, I think we need more details before we can go much further with this one.
Previous Topic: terminating instance due to error (merged 3)
Next Topic: TEMP_TSXX.dbf files
Goto Forum:
  


Current Time: Wed Dec 07 18:25:26 CST 2016

Total time taken to generate the page: 0.09000 seconds