This discussion is archived
10 Replies Latest reply: Jun 21, 2013 6:27 AM by Greg.Spall RSS

Return the procedure if time taking is more than 'N' mins

913578 Newbie
Currently Being Moderated

Hi,

I have a procedure like this.

 

CURRENT FUNCTIONALITY:
If the table GSM_ITEM_INFO table contains 1005000 expired item_id s then this procedure will delete 100000 expired items deviding each 10000 per delete and
taking gap of 10 seconds for each loop.

i need an extra functionality in this procedure that, if the procedure exection is taking more than 30mins. i have to return/stop the procedure.

please do not change existing functionality(you can change the code) and add the requirement.

 

PROCEDURE ITEM_CLEAN(I_LIMIT IN NUMBER DEFAULT 10000,I_WAIT_TIME IN NUMBER DEFAULT 10)
AS

L_COUNT number;

L_START number default dbms_utility.get_time;

BEGIN

  SELECT COUNT(*) INTO L_COUNT FROM GSM_ITEM_INFO WHERE EXPIRE_TIME<SYSDATE-1;
 
  SELECT floor(L_COUNT/I_LIMIT) INTO L_COUNT FROM DUAL;

    FOR I IN 1..L_COUNT

    LOOP 
      DELETE FROM GSM_ITEM_INFO WHERE EXPIRE_TIME<SYSDATE-1 AND ROWNUM<I_LIMIT+1;     
      COMMIT;     
      DBMS_LOCK.SLEEP(I_WAIT_TIME);

    END LOOP;
   
COMMIT;

EXCEPTION
   WHEN OTHERS THEN
      SP_ERROR_LOG(ERR_DATE_IN => SYSDATE,
                   ERROR_CODE_IN         => SQLCODE,
                   ERROR_DESCRIPTION_IN  => SQLERRM,
                   ERR_OBJECT_NAME_IN    => 'ITEM_CLEAN',
                   ERR_OBJECT_TYPE_IN    => 'GSM_JOB_PACKAGEPROCEDURE');
   
END ITEM_CLEAN;

/

 

Thanks

  • 1. Re: Return the procedure if time taking is more than 'N' mins
    Purvesh K Guru
    Currently Being Moderated

    913578 wrote:

     

    Hi,

    I have a procedure like this.

     

    CURRENT FUNCTIONALITY:
    If the table GSM_ITEM_INFO table contains 1005000 expired item_id s then this procedure will delete 100000 expired items deviding each 10000 per delete and
    taking gap of 10 seconds for each loop.

    i need an extra functionality in this procedure that, if the procedure exection is taking more than 30mins. i have to return/stop the procedure.

    please do not change existing functionality(you can change the code) and add the requirement.

     

    Well, this isn't a Freelance website to help you for FREE to build a cr@p functionality that you are, unable to think a way of and are being Paid by your Employer.

     

    First, post what your attempt has been on this. And then, expect the volunteers to find a way.

     

    My suggestion, which I am certain that you will not pay any heed, is to scrap the entire code and replace it with a simple Delete Statement which will

    1. perform better

    2. consume lesser resources

    3. will be more scalable, maintainable and easier to Debug.

     

    And, most certainly, it should complete the entire activity within a matter of few seconds/minutes, depending on load on database and the concurrent access to table rows; certainly much faster than your PL/SQL piece of code will ever perform.

  • 2. Re: Return the procedure if time taking is more than 'N' mins
    913578 Newbie
    Currently Being Moderated

    Hi Purvesh,

    Thanks for your reply and Apologies for your misunderstanding.

    I know the direct delete statement will work more better than all this plsql stuff. but i dont have rights to change this code, i just need to add the additional funcationlity.

    I have tried to do some experiments on this, but not sure.

     

    Added a check condition in for loop with dbms_utitlity.get_time

     

    FOR I IN 1..L_COUNT 

        LOOP      

          IF (DBMS_UTILITY.GET_TIME-L_START)/100>1800 THEN

          RETURN;

          END IF;

          DELETE FROM GSM_ITEM_INFO WHERE EXPIRE_TIME<SYSDATE-1 AND ROWNUM<I_LIMIT+1;     
          COMMIT;     
          DBMS_LOCK.SLEEP(I_WAIT_TIME);

        END LOOP;

  • 3. Re: Return the procedure if time taking is more than 'N' mins
    Etbin Guru
    Currently Being Moderated

    Would it be too risky to add the additional functionality like this?

     

    declare

      l_start      number := dbms_utility.get_time;   -- current time in 100th's of a second

      delete_max   number := 100000;                  -- maximum number of rows to delete

      limit_step   number := 10000;                   -- maximum number of rows to delete in a single step

      l_count      number := delete_max / limit_step; -- steps to perform

      time_limit   number := 30 * 60 * 100;           -- 30 minutes in 100th's of a second

      i_wait_time  number := 10;                      -- seconds

      deleted_rows number := 0;                       -- deleted rows count

    begin

      for i in 1 .. l_count

      loop     

        if dbms_utility.get_time - l_start > time_limit then

          exit;

        elsif deleted_rows < delete_max then

          delete from gsm_item_info

           where expire_time < sysdate - 1

             and rownum <= delete_max;

          deleted_rows = deleted_rows + sql%rowcount;

          commit;     

          dbms_lock.sleep(i_wait_time);

          delete_max := deleted_rows;

         else

           exit;

        end if;  

      end loop;

    end;

     

    Regards

     

    Etbin

  • 4. Re: Return the procedure if time taking is more than 'N' mins
    Purvesh K Guru
    Currently Being Moderated


    Right way, but how do you mitigate the risk of a single Delete being stuck and in execution for 30 mins?

  • 5. Re: Return the procedure if time taking is more than 'N' mins
    ascheffer Expert
    Currently Being Moderated

    I would say: too risky :

    Maybe if you change it to this

            and rownum <= limit_step;

    And remove this line

          delete_max := deleted_rows;

  • 6. Re: Return the procedure if time taking is more than 'N' mins
    Etbin Guru
    Currently Being Moderated

    It was meant as a joke (lacking explanation deliberately)

    But being in OP's shoes (forced to use/retain the loop) I would take the risk as anybody rarely checks the code around here, mostly due to bad reactions of "thinking heads" when being exposed.

    I would delete the maximum number of rows with a single delete statement and exit the loop (therefore delete_max := deleted_rows;) hoping:

    • not to get noticed at the first glance - the loop would still be there, accumulating rowcounts - a kind of disguise, ... .
    • deleting 100000 rows in a single delete would never take more than 30 minutes (never saw it in past ten years).
    • the requirement is due to some infrequent occasions when the procedure didn't make it in 30 minutes.
    • the situation would not repeat too soon.


    Regards


    Etbin

  • 7. Re: Return the procedure if time taking is more than 'N' mins
    Etbin Guru
    Currently Being Moderated

    Maybe:

    • just saying: Oops, something went wrong then.
    • Or (even better) asking: How could checking (within the loop) whether or not the procedure is running for 30 minutes already help if maybe the delete statement is just waiting for a resource to be released or something like that. (the OP is required just to enhance the present solution)

    I think it has to be done on system level (something I don't have priviliges to do, so don't ask how a solution would look like) - if the particular/named PL/SQL block is active for more than 30 minutes ...

    The OP most probably won't have the chance either - if they don't let him use a single delete statement ...

    How easy it is when you don't really have to do it !

     

    Regards

     

    Etbin

  • 8. Re: Return the procedure if time taking is more than 'N' mins
    Purvesh K Guru
    Currently Being Moderated

    Etbin wrote:

     

    Maybe:

    • just saying: Oops, something went wrong then.
    • Or (even better) asking: How could checking (within the loop) whether or not the procedure is running for 30 minutes already help if maybe the delete statement is just waiting for a resource to be released or something like that. (the OP is required just to enhance the present solution)

     

    Yes, may be Wait events are the one that can be checked prior to the execution of Delete.

     

    For PL/SQL Block, May be DBA could monitor a particular SQL and its execution time and kill the session if it exceeds the Threshold of 30 mins. I would not worry much on that part if it isn't crucial enough to spend time on it.

     

     

     

    Etbin wrote:

     

    How easy it is when you don't really have to do it !

     

    Extremely!!!

    But last time I was stopped from using a Single Delete, I had a war with the DBA and Managers, who were prejudiced (and Ignorant) that PL/SQL and Batch Delete/DML performs better than plain SQL. Eventually, I did win.

  • 9. Re: Return the procedure if time taking is more than 'N' mins
    Etbin Guru
    Currently Being Moderated

    PurveshK wrote:

    ... Eventually, I did win.

    My best wishes you keep winning ...

    Don't get me started what I have been through until now and I'm not having great hopes things will change substantially in the near future.

    That's where my "underground" reasoning comes from - it's all about my peace of mind, so I can sleep at night.

    So just two citations:

    • Never be afraid to try something new. Remember that a lone amateur built the Ark. A large group of professionals built the Titanic. (source unknown)
    • Standards are a very poor substitution for knowledge and experience. (Billy Verreynne https://forums.oracle.com/message/10858402#10858402)

     

    Regards

     

    Etbin

  • 10. Re: Return the procedure if time taking is more than 'N' mins
    Greg.Spall Expert
    Currently Being Moderated

    913578 wrote:

    taking gap of 10 seconds for each loop.

     

    I think I found where it's slowing down.

     

    913578 wrote:

     

          DBMS_LOCK.SLEEP(I_WAIT_TIME);

     

    Remove this line - it'll run faster

     

    Seriously, though ..

    1. Fighting for changing this to a single DELETE statement would be something totally worth it in my opinion.
    2. It'd run faster, less errors, easier to debug .. all very very easy to prove.
    3. In any case - if you really are determined to mutilate this and build "Frankencode", the only way you can monitor some task like you are asking would be to use some method utilizing several processes. So, for example: submit the delete's into a Queue, or using dbms_scheduler (or dbms_job on older versions), and monitor the progress.

     

    Good luck - you might want to start brushing up your resume 

     

    [edit]

    Oh yeah, check out this thread:

    http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:767025833873

    It's another good idea - if you insist on keeping this row/by/row logic, at the very least use dbms_application_info to update the client_info so other processes can get an idea of how well you are progressing.

    [/edit]

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points