7 Replies Latest reply: Feb 28, 2014 10:44 AM by user9521526 RSS

    DBMS_SCHEDULER.DROP_JOB performance

    user9521526

      Hello

       

      I have noticed from user_scheduler_programs view, over 1 million entries exist for programs . I have written a housekeeping utility that will drop these programs once they have been execuited. I wanted first to clear all entries from user_scheduler_programs as well as user_scheduler_program_args. I used dbms_scheduler.drop_program to delete these program entries but saw that it was taking over 120 seconds just to drop one entry. So for one million+ this will take way too long.

       

      I have seen that the view refers to tables scheduler$_program and obj$. For test purposes I deleted one program and corresponding object from these tables and it ran fine and didn;t take 120 seconds.

       

      My question is, what exactly does dbms_scheduler.drop_program do in addition to removing entries from these tables ? If I can find this out I can try and clear these down manually and from then on use the dbms_scheduler.drop_program  utility as part og  the housekeeping procedures I have writtten.

       

      Thanks

      Shaq