Home » SQL & PL/SQL » SQL & PL/SQL » How to check procedure is running more than stipulate time frame (Oracle9i Enterprise Edition Release 9.2.0.3.0 - 64bit )
How to check procedure is running more than stipulate time frame [message #421335] Mon, 07 September 2009 09:37 Go to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi,

As Once the Procedure XYZ is called than after 5 Minute(or stipulated time frame) it need to be terminated.

How to terminate the procedure once it reaches the defined stipulated time-frame?

Please suggest.
Re: How to check procedure is running more than stipulate time frame [message #421340 is a reply to message #421335] Mon, 07 September 2009 09:58 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What does the procedure do?
If it loops then check the time at each end of loop and return if time exceeds the limit.

Regards
Michel
Re: How to check procedure is running more than stipulate time frame [message #421345 is a reply to message #421340] Mon, 07 September 2009 10:32 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi Michel,
Procedure XYZ is calling a procedure ABC in the FOR Loop, if
Procedure ABC takes time more than 5 minute terminate and genrate error message and pass the next iteration value of the for loop to procedure ABC.

Pls. Suggest

CREATE OR REPLACE PROCEDURE ABC (v_name VARCHAR2)IS
 BEGIN
   ----------------
    STATEMENTS
   ----------------
 END ABC;


CREATE OR REPLACE PROCEDURE XYZ 
IS
 BEGIN
  FOR i IN (SELECT NAME FROM TANG)  
  LOOP
     BEGIN
       -- If this Procedure ABC takes more than 5 Minute than
       -- terminate the procedure ABC with error message and
       -- Passing the next iterative value for i.name  
       ABC(i.NAME);
     EXCEPTION
       WHEH OTHER THEN
        DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM) ;
     END; 

  END LOOP;
  COMMIT;
 END ABC;


Re: How to check procedure is running more than stipulate time frame [message #421346 is a reply to message #421345] Mon, 07 September 2009 10:42 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
     EXCEPTION
       WHEH OTHER THEN
        DBMS_OUTPUT.PUT_LINE(SQLCODE||' '||SQLERRM) ;

This is silly (and syntaxically wrong).
What do you want to do? You do not say.

Regards
Michel

[Updated on: Mon, 07 September 2009 10:42]

Report message to a moderator

Re: How to check procedure is running more than stipulate time frame [message #421347 is a reply to message #421346] Mon, 07 September 2009 10:55 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi Michel,

Procedure ABC is being called by Procedure XYZ in For Loop.
If Procedure ABC is taking more than 5 mins inside for loop than stop the Procedure ABC and pass new iterative value from for loop to the procedure ABC.

Is there any way to track the current procedure 'IS IN RUNNING'/'COMPLETE' State like this ...

Pls. suggest.

CREATE OR REPLACE PROCEDURE ABC (v_name VARCHAR2)IS
 BEGIN
   ----------------
    STATEMENTS
   ----------------
 END ABC;


CREATE OR REPLACE PROCEDURE XYZ 
IS
 BEGIN
  FOR i IN (SELECT NAME FROM TANG)  
  LOOP
       -- If this Procedure ABC takes more than 5 Minute than
       -- terminate the procedure ABC with error message and
       -- Passing the next iterative value for i.name  
       ABC(i.NAME);
    
  END LOOP;
  COMMIT;
 END ABC;

Re: How to check procedure is running more than stipulate time frame [message #421348 is a reply to message #421335] Mon, 07 September 2009 10:59 Go to previous messageGo to next message
cookiemonster
Messages: 13964
Registered: September 2008
Location: Rainy Manchester
Senior Member
Once XYZ has called ABC it can do nothing until ABC finishes.

So you need to tell us what ABC is doing.
Re: How to check procedure is running more than stipulate time frame [message #421349 is a reply to message #421346] Mon, 07 September 2009 11:02 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Depends on what ABC does. Specifically:


1) Is it save to interrupt, or will it leave behind partially committed data.

2) If the thing that takes much time is writing data, then when you kill it after 5 minutes it still might take another five minutes do to the rollback.


Not enough information to be sure, but my feeling so far is that only ABC knows when it is save to interrupt ABC, so the "break" condition must be placed inside ABC.


From the "outside", you could probabyl use alter system kill session to kill the procedure, but then you would have to start the procedure in another session, perhaps with DBMS_JOB/DBMS_SCHEDULER.
Re: How to check procedure is running more than stipulate time frame [message #421350 is a reply to message #421347] Mon, 07 September 2009 11:08 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
So back to my first answer for ABC: raise an exception in it when it exceeds 5 minutes.

Regards
Michel
Re: How to check procedure is running more than stipulate time frame [message #421351 is a reply to message #421349] Mon, 07 September 2009 11:23 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi,

Once XYZ has called ABC it can DO nothing UNTIL ABC finishes.
So you need TO tell us what ABC IS doing.


Please find the required STATEMENT FOR PROCEDURE ABC.
Pls. Suggest.

CREATE OR REPLACE PROCEDURE ABC (v_name IN VARCHAR2, V_STATUS OUT VARCHAR2)IS
 BEGIN
   
   -- If insert update is done within 5 minute than commit the transaction
   -- else rollback the transaction and run with new value
   ------------------------------------------------------------------------------------
   INSERT INTO TEMP_TANG SELECT * FROM GAB_COUNTRY WHERE GANG_NAME=v_name;
   UPDATE TANG_COUNTRY SET TANG_NAME=v_name||' '||'COMPLETED' WHERE TANG_NAME=v_name;;
   ----------------------------------------------------------------------------------
 
  -- Need to try this option--
  IF complete within 5 minute THEN
   V_STATUS :=0; -- Success
  ELSIF  takes more than 5 minutes THEN
   V_STATUS :=1;-- Failure
  END IF;
   
 END ABC;
 
 CREATE OR REPLACE PROCEDURE XYZ 
IS
 BEGIN
  FOR i IN (SELECT NAME FROM TANG)  
  LOOP
       -- If this Procedure ABC takes more than 5 Minute than
       -- terminate the procedure ABC with error message and
       -- Passing the next iterative value for i.name  
        
	   ABC(i.NAME,V_STATUS);
	   
	   IF V_STATUS = 0 THEN
       COMMIT; -- For Insert/Update 
	   ELSIF V_STATUS = 1 THEN
	   ROLLBACK;  -- For Insert/Update 
	   END;
	    
  END LOOP;
  
 END ABC;

 
 



NOT enough information TO be sure, but my feeling so far IS that ONLY ABC knows 
WHEN it IS save TO interrupt ABC, so the "break" condition must be placed inside ABC.

How TO tackle BREAK Steps within the PROCEDURE ABC?
Pls. Suggest.
Re: How to check procedure is running more than stipulate time frame [message #421352 is a reply to message #421335] Mon, 07 September 2009 11:38 Go to previous messageGo to next message
cookiemonster
Messages: 13964
Registered: September 2008
Location: Rainy Manchester
Senior Member
There's no easy way and, as ThomasG already pointed out, if you stop that part way through you'll need to rollback which'll take even longer.

I really have to question why you're trying to limit this operation to 5 minutes at all. I would suspect you'd be far better off looking for ways to speed it up if time is a problem.
Re: How to check procedure is running more than stipulate time frame [message #421353 is a reply to message #421351] Mon, 07 September 2009 11:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
How TO tackle BREAK Steps within the PROCEDURE ABC?

third time
What does the procedure do?
If it loops then check the time at each end of loop and return if time exceeds the limit.

Regards
Michel

Re: How to check procedure is running more than stipulate time frame [message #421354 is a reply to message #421335] Mon, 07 September 2009 11:54 Go to previous messageGo to next message
cookiemonster
Messages: 13964
Registered: September 2008
Location: Rainy Manchester
Senior Member
@Michel - ABC isn't looping.
Re: How to check procedure is running more than stipulate time frame [message #421355 is a reply to message #421354] Mon, 07 September 2009 12:24 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You need to figure out why the procedure takes so long.

If it's because rows in TANG_COUNTRY are locked, then one way to handle that would be to do the update in a cursor loop with an "for update nowait" / "where current of" construct like explained here.

That way, the procedure would raise an exception when rows are locked, even before waiting for an timeout.
Re: How to check procedure is running more than stipulate time frame [message #421356 is a reply to message #421354] Mon, 07 September 2009 12:52 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
cookiemonster wrote on Mon, 07 September 2009 18:54
@Michel - ABC isn't looping.

Anyway it is ABC to know where it spend time and when it has to return.
If ABC xan't do that you can't achieve what you want.

Regards
Michel

Re: How to check procedure is running more than stipulate time frame [message #421410 is a reply to message #421355] Tue, 08 September 2009 03:03 Go to previous messageGo to next message
tapaskmanna
Messages: 98
Registered: January 2007
Location: Cyprus,Nicosia
Member
Hi Thomas,

The requirement is that time allocation for Procedure ABC is 5 minute only for each run.


From the "outside", you could probabyl use alter system kill session to kill the procedure,
but then you would have to start the procedure in another session, perhaps with DBMS_JOB/DBMS_SCHEDULER.


Can I use PRAGMA AUTONOMOUS_TRANSACTION in Procedure ABC in order to open in other Session,
if yes, pls. suggest me how to track that session in order to kill?
Pls. Suggest.

Hi Michel,

 So back to my first answer for ABC: raise an exception in it when it exceeds 5 minutes.
 

This is what i am trying to do with all's valuable feedback as an suggestion.
Pls. suggest.
Re: How to check procedure is running more than stipulate time frame [message #421411 is a reply to message #421410] Tue, 08 September 2009 03:15 Go to previous messageGo to next message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You can't to that from pure PL/SQL.

The easiest way to do it would be to run the queries from a client that supports a time-out for queries, like the with the JDBC driver from Java.

But all of that would definitely be more complicated than figuring out why the query takes so long and make it run faster.

Re: How to check procedure is running more than stipulate time frame [message #421418 is a reply to message #421410] Tue, 08 September 2009 04:01 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Perversely, this might actually be a case where a good solution is a row-by-row approach:

If the code was rewritten as:
FOR rec IN (SELECT * FROM GAB_COUNTRY WHERE GANG_NAME = v_name
            MINUS
            SELECT * FROM temp_tang) LOOP
  INSERT INTO temp_Tang VALUES rec;

  <Check elapsed time and exit loop if too long
END LOOP;

commit;
FOR rec in (SELECT rowid FROM tang_country
            WHERE  tang_name = v_name) LOOP
  UPDATE TANG_COUNTRY SET TANG_NAME=v_name||' '||'COMPLETED' 
  WHERE rowid = rec.rowid;

  <check elapsed time and exit loop if too long>
END LOOP;

commit;


It will be slower than the original, and you will need to consider if you want it to commit partial sets of data (as I've written it) or if you want to move the commits to the end and roll back if you run out of time.

You need to bear in mind that rolling back Inserts or Updates will take slightly longer than the time spent inserting/updating in the first place (ie time spent excluding time taken to select data)

I agree with everyone else that you'd be better off looking at why somthing as simple as the queries you've posted takes so long.

The fact that you're stepping through a list of values to run this for makes me think that you'd be better off removing the loop from XYZ procedure and just running one big Insert and one big Update

Are you by any chance running this in a loop so that you can get an index based explain plan, under the mistaken belief that this will always be faster than a full table scan?
Re: How to check procedure is running more than stipulate time frame [message #421422 is a reply to message #421418] Tue, 08 September 2009 04:18 Go to previous message
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
The row-by-row approach with intermediate commits definitely has it's place.

In a transaction-system it might definitely be preferable to have the whole update process run 30 minutes, but have single rows only locked for a fraction of a second, than have the whole process finish in 5 minutes but having rows people are trying to work with locked that long.
Previous Topic: select query failing
Next Topic: null and empty string not being the same in object?
Goto Forum:
  


Current Time: Thu Feb 13 10:53:29 CST 2025