Forum Stats

  • 3,770,500 Users
  • 2,253,127 Discussions
  • 7,875,486 Comments

Discussions

Delayed execution of dbms_job.submit in Oracle 12c

810582
810582 Member Posts: 63
edited Apr 4, 2016 3:07AM in General Database Discussions

Dear All,

Database Version: 12.1.0.2.0

Recently the database was migrated from 9i to 12c.

We have the below problem with respect to dbms_job.

I know if we use dbms_scheduler we will overcome these problems, but unfortunately there are more than 500 forms which uses dbms_job to submit the jobs. so we can't change the working code.

DECLARE
  l_job NUMBER;
BEGIN  
DBMS_JOB.SUBMIT(l_job,'BEGIN arunjobtest; END;',sysdate);
dbms_output.put_line('l_job '||l_job);
commit;
END;

When we submit the job using above script, job runs only after 20 seconds or so.(Sometimes 20 seconds, sometimes 15 seconds).

Similarly I can see the job in dba_running_jobs only after 20 seconds. In 9i database we didn't had this problem. Whenever we submit a job it runs immediately without any delay and dba_running_jobs used to populate immediately after submission. We use dba_running_jobs to monitor jobs via Oracle forms.

Please find the below values at the time of running jobs.

select value from v$parameter where name='sessions';--480

select count(*) from v$session ;--106

job_queue_processes                  integer     1000

Also i'm wondering whether any hidden parameter to be set for running the jobs immediately.

When i use below code, i'm able to see the jobs immediately in the dba_running_jobs and jobs are running immediately. As dbms_job.run is not asynchronous, I don't want to use the below code.

DECLARE
  l_job NUMBER;
BEGIN
  
DBMS_JOB.SUBMIT(l_job,'BEGIN arunjobtest; END;',sysdate);
dbms_output.put_line('l_job '||l_job);
commit;
dbms_job.run(l_job);
commit;
END;

Kindly request you to help in this regards.

Arun

Tagged:

Answers

  • Richard Harrison .
    Richard Harrison . Member Posts: 2,065 Gold Trophy
    edited Apr 3, 2016 4:03PM

    Hi,

    It used to be job_queue_interval which became _job_queue_interval some time ago - i think the default used to be 60 seconds.....

    Cheers,

    Rich

    810582
  • Unknown
    edited Apr 3, 2016 5:05PM
    Recently the database was migrated from 9i to 12c.
    We have the below problem with respect to dbms_job.
    I know if we use dbms_scheduler we will overcome these problems, but unfortunately there are more than 500 forms which uses dbms_job to submit the jobs. so we can't change the working code
    

    Sure you can change the 'working code'. If you migrated THREE VERSIONS of Oracle you had to have made changes to you data model and applications.

    So the 'problem with respect to dbms_job' should have been easily discovered when you did the analysis and testing PRIOR to the migration. If the problem wasn't detected then someone jumped the gun by approving the migration before they knew what all of the issues were going to be and what it would take to fix them.

    I suggest you start making the necessary changes now to fix your problem. DBMS_JOB was deprecated long ago.

    DECLARE  
    
      l_job NUMBER;  
    BEGIN    
    DBMS_JOB.SUBMIT(l_job,'BEGIN arunjobtest; END;',sysdate);  
    dbms_output.put_line('l_job '||l_job);  
    commit;  
    END;  
    
    
    When we submit the job using above script, job runs only after 20 seconds or so.(Sometimes 20 seconds, sometimes 15 seconds).
    

    Submitting the job puts in in a queue for Oracle to execute when it gets to it. Your system is likely busy doing other things.

    Similarly I can see the job in dba_running_jobs only after 20 seconds. In 9i database we didn't had this problem. Whenever we submit a job it runs immediately without any delay and dba_running_jobs used to populate 
    immediately after submission. We use dba_running_jobs to monitor jobs via Oracle forms.

    So other than a small delay what is the PROBLEM? Using that view to monitor jobs is fine but that doesn't make sense for jobs that only take a few seconds to execute - you won't ever see them active.

    Also i'm wondering whether any hidden parameter to be set for running the jobs immediately.
    

    Again - what PROBLEM are you trying to solve? If you just did a migration there have to be other things that are far more important than this.

    You should NEVER rely on 'hidden' parameters except at Oracle' explicit direction. If this is that critical an issue open an SR and ask Oracle for direction.

    But frankly I still don't understand why you are even spending any time on this particular issue.

    810582
  • AndrewSayer
    AndrewSayer Member Posts: 12,998 Gold Crown
    edited Apr 3, 2016 7:27PM

    If this is really a problem then why wasn't it found when you were testing the upgrade? Even looking at the 9i manual I can't see anything that suggests jobs submitted using dbms_job will run immediately, Richard has pointed out a parameter that was defaulted to 60 seconds which would suggest they weren't as immediate as you thought they were.

    Does it really matter than an asynchronous process takes some amount of time to start?

  • GregV
    GregV Member Posts: 3,069 Gold Crown
    edited Apr 4, 2016 3:07AM

    Hi,

    I agree with rp0428. The migration scheme should have made it clear that DBMS_JOB would require an upgrade to DBMS_SCHEDULER. The former's been deprecated since 10g, that is, for more than 10 years. It's like people who still use the old exp/imp utilities on releases where data  pump is present...

This discussion has been closed.