DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: dbms_job question

  1. #1
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204

    dbms_job question

    From within procedure, is there a way to tell what job_id started the proc?

    I have a bunch of jobs that run a procedure every hour (after completion, so it's not a predictable minute in the hour). Sometimes the jobs next_date "line up" and we end up with 10 running at the same time. I want to spread them out so no more than 3 are running at any given time.

    So I was thinking about writing something in the header of the procedure to first go see if 3 jobs are running (simple query into dba_jobs), then reschedule the current one for 1 minute later if the system is already running 3.

    A side benefit should be that it automatically rebalances the jobs to run more evenly across the hour.

    The jobs run from 1-5 minutes and right now there's about 30 of them. There could be more or less depending on workload.

    We've got a 4 proc box.

    Ken
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    why dont you control the minute each one runs?

  3. #3
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    I was afraid you would ask. It's a valid question though.

    Bottom line, there's no way to predict how many could be running simultaneously.

    The jobs are initiated from an application and Job duration (in days) and runtime (in minutes) varies.

    -Ken
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    set your job_queue_processes = 3 and you'll never have more than 3 running at once.
    Jeff Hunter

  5. #5
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Doooh! Thinking too hard...
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  6. #6
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Question: If 5 jobs could run, but only 3 will run, will the other 2 run as soon as a process opens up?
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  7. #7
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    yes

  8. #8
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Thanks guys, I'll take my hard head and get back to work .. well, after I go home for the day.

    Live's tough, then you die, so enjoy today.

    Ken
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width