7 Replies Latest reply: Jan 16, 2008 9:52 PM by 615199 RSS

    Scheduler not running the jobs

    85286
      Hi,

      From a PL/SQL procedure I am creating a dbms job as follows:

      Trial 1:

      BEGIN
      DBMS_SCHEDULER.create_job(job_name => 'EMAIL',
      job_type => 'PLSQL_BLOCK',
      job_action => 'BEGIN SEND_MAIL; END;',
      enabled => TRUE);
      END;

      As expected this is creating the named job and running it immediately. Scheduler is running this job immediately till yesterday.

      For some reason the job is not getting kicked off immediately since today morning. I have no idea where the fault is. So, I tried the below:

      Trial 2:

      BEGIN
      DBMS_SCHEDULER.create_job(job_name => 'EMAIL',
      job_type => 'PLSQL_BLOCK',
      job_action => 'BEGIN SEND_MAIL; END;');

      DBMS_SCHEDULER.run_job(job_name => 'EMAIL');
      END;

      This is running the job immediately. Then I tried the below & it is not running the job immediately.

      Trial 3:

      BEGIN
      DBMS_SCHEDULER.create_job(job_name => 'EMAIL',
      job_type => 'PLSQL_BLOCK',
      job_action => 'BEGIN SEND_MAIL; END;');

      -- explicitly specify use new session
      DBMS_SCHEDULER.run_job('EMAIL', FALSE);
      END;

      I want to understand why Trial 1 did not run immediately. Seems like Oracle scheduler is not schedulign the jobs as expected. Can any one think of potential reasons for this.

      Thanks in advance.
      S.
        • 1. Re: Scheduler not running the jobs
          85286
          Essentially, asynchronous mode is not working. run_job is not working if we opt to create a new session.

          My requirement is to create a new session because I am doing some parallel processing.

          Regards,
          S.
          • 2. Re: Scheduler not running the jobs
            214628
            I would look at DBA_SCHEDULER_JOB_RUN_DETAILS and see if there are errors.
            • 3. Re: Scheduler not running the jobs
              RnR
              Hi,

              There are a few things to check. It could be that you are running into some limit.

              Make sure that you have not exceeded the maximum number of sessions or the maximum number of processes or the maximum number of jobs


              select * from dba_scheduler_global_attribute;
              and
              select name,value from v$parameter where name like %process%';
              select name,value from v$parameter where name like '%session%';

              Also check how many jobs are currently running

              select count(*) from dba_scheduler_running_jobs;
              select count(*) from dba_jobs_running ;

              One of these limits may need to be increased.

              Hope this helps,
              Ravi.
              • 4. Re: Scheduler not running the jobs
                615199
                <p>Hi</p>
                <p>I am having the same problem.</p>
                <p>I am able to run a new job with use_current_session = TRUE but not with
                use_current_session = FALSE.</p>
                <p>I need to run the job asynchronously.</p>
                <p>This is the first time we try to use DBMS_SCHEDULER.</p>
                <p>Here are the results of running the queries above.  </p>
                <p>
                <strong>select * from dba_scheduler_global_attribute;</strong>



                <u>ATTRIBUTE_NAME - VALUE</u>


                MAX_JOB_SLAVE_PROCESSES

                LOG_HISTORY 30

                DEFAULT_TIMEZONE

                LAST_OBSERVED_EVENT

                EVENT_EXPIRY_TIME

                CURRENT_OPEN_WINDOW



                6 rows selected.




                <strong>select name,value from v$parameter where name like '%process%';</strong>



                <u>NAME - VALUE</u>


                processes - 300

                gcs_server_processes 0

                db_writer_processes 1

                log_archive_max_processes 1

                job_queue_processes 1

                aq_tm_processes 0



                6 rows selected.




                <strong>select name,value from v$parameter where name like '%session%';</strong>



                <u>NAME - VALUE</u>


                sessions 335

                license_max_sessions 0

                license_sessions_warning 0

                java_soft_sessionspace_limit 0

                java_max_sessionspace_size 0

                logmnr_max_persistent_sessions 1

                shared_server_sessions

                session_cached_cursors 20

                session_max_open_files 10



                9 rows selected.




                <strong>select count(*) from dba_scheduler_running_jobs;</strong>


                  COUNT(*)

                ----------

                         0



                1 row selected.




                <strong>select count(*) from dba_jobs_running;</strong>


                  COUNT(*)

                ----------

                         1



                1 row selected.
                </p>
                <p> </p>
                <p>Could you please let me know which particular limits need to be increased.</p>
                <p> </p>
                <p>Thanks</p>
                <p>H.</p>
                • 5. Re: Scheduler not running the jobs
                  RnR
                  Hi,

                  The value of job_queue_processes (which is set to 1) if non-zero limits the total number of jobs running (both count(*) queries given).

                  Since you already have a job running, and job_queue_processes is 1, no more jobs will run. You should increase this parameter if you want more than 1 job running at a time. I think you can use an "alter session set" statement to do this.

                  Hope this helps,
                  Ravi.
                  • 6. Re: Scheduler not running the jobs
                    85286
                    I our case we restarted the database instance and it started working as expected.

                    But checking the limits and reasoning the cause for the problem sounds like a good idea.

                    S.
                    • 7. Re: Scheduler not running the jobs
                      615199
                      Hi Ravi

                      You are right!

                      We increased job_queue_processes to 20 (I think the DBA did it using ALTER SYSTEM command - no instance restart was required).

                      It now works o.k.

                      Thanks
                      H.