|
|
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   |
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 #421347 is a reply to message #421346] |
Mon, 07 September 2009 10:55   |
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 #421349 is a reply to message #421346] |
Mon, 07 September 2009 11:02   |
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 #421351 is a reply to message #421349] |
Mon, 07 September 2009 11:23   |
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   |
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 #421410 is a reply to message #421355] |
Tue, 08 September 2009 03:03   |
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   |
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   |
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  |
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.
|
|
|