Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2002
    Posts
    104

    Unanswered: jobs taking longer

    Hi,

    I a newbie to oracle.

    My problem is ...some buch of jobs (mainly operating on oracle 9i database) have suddenly started taking double of their time. Can you please provide some lead to look for the reason of this behaviour and/or to solve this ..

    Thanx a ton in advance,

    --Pooja

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    Usually this is due to changes in your data i.e. a big data load OR your stats have changed which has caused your execution plans to change. You can check your stats by looking at dba_tables/dba_indexes and looking at the last analyzed data, also check the sample size.

    Alan

  3. #3
    Join Date
    Dec 2002
    Posts
    104
    we have asked DBA's to rebuilt the indexes and analyze it ...after rebuilting time seems to increase further ...

    Pls help,
    thanx
    Pooja

  4. #4
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    No you didnt get what I said, the newly analyzed tables/indexes are the problem in that they have caused your execution plans to go awry (providing your data volumes/distribution havent changed much).

    This can be down to 2 basic reasons
    1) you stats arent accurate enough i.e. sample not big enough, maybe histograms are required etc.
    2) your new stats have caused the optimizer to take a wrong turn as the optimizer is not perfect and can make incorrect decisions. You may overcome this by checking metalink for known optimizer bugs and hopefully a patch to fix it. Alternatively you can go down the route of adding hints but this may cause problems in the future. You could also rewrite your sql to help the optimizer make the right decision or maybe add/alter an index to help it.

    Alan

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    trace your jobs as they run every day.
    tkprof the traces and see what is holding things up.

    as mentioned by Alan:
    did a specific table suddenly get loaded with a large amount of data of possibly your customer-base jumped. either way follow the statistics info mentioned by Alan.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Sep 2004
    Posts
    60
    If data volume is same/comparable to previous volume, probably you need to ask DBA to check for 'waits' for resources during batch run.

  7. #7
    Join Date
    Oct 2006
    Location
    CA
    Posts
    210
    Time for tracing and research. If it's suddenly happening, it's likely a single cause and tracing 1 or 2 jobs known to have increased will reveal the problem if it's due to size or structure changes. As mentioned; running Stats can have unintended adverse effects.

    Stay away from Hints unless someone very well versed takes over. It is highly unlikely for you to "Hint" how to do it better than the Optimizer unless your DBA is an Oracle guru.

Posting Permissions

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