Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Posts
    2

    Unhappy Unanswered: Stored Procedure runs very slow through DBMS_JOBS

    I have a stored procedure in Oracle 8.1.4 that takes about 4 hours to run via SQL Plus.
    When I schedule the same SP via DBMS_JOBS, it takes over 12 hours to run.
    I can't figure out why. It is not a timing issue as I have tried running it both ways at varoius different times with consistent results.
    Any help wopuld REALLY be appreciated.

  2. #2
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    That is confusing to be sure.

    Have you thought of running statspack or something like that to verify the script is actually when DBMS_JOBS is suppose to start it?

    What about giving DBMS_JOBS the heave ho and just use your OS native scheduling software to run it (ie. cron on UNIX).
    NOTE: Please disregard the label "Senior Member".

  3. #3
    Join Date
    Jan 2005
    Posts
    2
    I tried running it in cron on unix as well and it is still taking 12 hours or so. The only "idea" I have is that Oracle is somehow giving it a lower priority when it is run via CRON or DBMS_JOBS versus interactively in SQL Plus. But I have not been able to find any documentation on it.

  4. #4
    Join Date
    Sep 2004
    Location
    London, UK
    Posts
    565
    The simplest way to add some metrics might be to add calls to DBMS_PROFILER.START_PROFILER/STOP_PROFILER, and possibly

    ALTER SESSION SET TIMED_STATISTICS = TRUE;
    ALTER SESSION SET SQL_TRACE = TRUE;

    You should be able to examine Profiler output via your GUI tool (PL/SQL Developer, TOAD etc), and use tkprof for the tracefile.

    I would not just abandon DBMS_JOB in favour of a more complex and less convenient OS alternative.

  5. #5
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    If you are starting from cron then Oracle will have no control on the priority (nice) level. I do not know if DBMS_JOBS plays with nice levels.

    It must have something to do with the environment of your user versus the environment of the user invoked with cron/dbms_job. Are you using the same user? What is confusing to me is that I can not think of any environmental variables that would affect this. Maybe there is some temp path that is different or something weird.

    When you are logged in capture your env output. Schedule your cron job (or dbms_job) and do and capture the output of an env command. You do not need to run script, just run an env.

    Other than that sounds like it is time for profiler. Please update this thread when you figure it out. I am interested.
    Last edited by Todd Barkus; 01-04-05 at 15:06.
    NOTE: Please disregard the label "Senior Member".

Posting Permissions

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