Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2010
    Posts
    72

    Unanswered: Oracle job stuck

    Hi,

    I have two box jobs say Job1 and Job2. Both jobs have sub job commands a,b,c,d. These days I facing the following issue.

    Most of the times Job1 stuck at b, I am looking at the query plan to find out where the job got stuck and what are the tables involved in the query, killing that job and then running " dbms_stats.gather_table_stats" for those tables involved in that query. After finishing running the stats, then again I am starting the job b and it is running successfully wih out any issue.

    Between I am keeping job2 on ice because job 1 is not finishing before job2 scheduled time because of job b stuck in job1.

    This scenario is happening in my production environment.

    Can some body suggest me how to overcome this (job b stuck and running stats manually). Is there any other things I have to consider when the job b got stuck? Any permanant fix for this?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    do as below so we can know complete Oracle version & OS name.

    Post via COPY & PASTE complete results of
    SELECT * from v$version;
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Jan 2010
    Posts
    72
    Here is the requested details :

    SQL> select * from v$version;

    BANNER
    ----------------------------------------------------------------
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
    PL/SQL Release 10.2.0.4.0 - Production
    CORE 10.2.0.4.0 Production
    TNS for Solaris: Version 10.2.0.4.0 - Production
    NLSRTL Version 10.2.0.4.0 - Production

    and the operating system is
    ---------------------
    SunOS amulti01 5.10 Generic_142900-03 sun4u sparc SUNW,Sun-Fire-V490
    ----------------------------------

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    If running "dbms_stats.gather_table_stats" fixes the issue. it means your job is stuck due to missing/stale statistics and therefore the optimizer may be generating an execution plan with "Full Table Scans".

    Try gathering stats before you run the job or just after loading data into the tables.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  5. #5
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    SQL does NOT "get stuck".
    SQL is either process or waiting & either state can be determineded via SQL queries
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

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