Analyze table takes very long time - Page 2
DBAsupport.com Forums - Powered by vBulletin
Page 2 of 3 FirstFirst 123 LastLast
Results 11 to 20 of 22

Thread: Analyze table takes very long time

  1. #11
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by ORA-1578
    hmmm...,if I remember correctly, there used to be a locking issue when selecting from and analyzing the same object!

    what type of analyze? I suspect in your application that you are using RBO, so what are you analyzing for? estimate stats and gathering stats in general are for CBO!
    if you are validating structure, then that is a (no no) if the business users are accessing the table!! since this will lock the object and all its dependencies (if cascade) is used for the duration of the analyze.
    Have you noticed anyone complaining about performance when whatever analyze you are doing is performed?
    but I like the dead locking issue better, however, if memory serves. this was fixed in 7.3.4 and you are on 7.3.4. So I am back to you locking the table. Any error messages, deadlock errors?
    ________________________________________________________________
    ORA-1578
    A nipple is the only intuitive interface, everything else needs a manual
    What has analyze takes long time to do with locking...? Anyone said validating structure? Anyone said dead lock?

  2. #12
    Join Date
    Dec 2002
    Posts
    9
    pando,

    No need to be sarcastic, or testy

    if you know anything about oracle, you'd know that analyze validate structure locks the object. or obviously you didn't know that!!

    In 8i we gather statistics so that the Cost Based Optimizer CBO finds
    the best execution path using the stats.

    The question that was posed was when i analyze it takes a long time.
    7.3.4 uses RBO and CBO was pushed into mainstream in 8 and 8i and 0i, so the question is what is he analyzing for??? if not to gather statistics for CBO, then what is he analyzing for??

    Also there was a problem with "locking" it was a ROW CACHE issue, when analyzing and selecting at the same time from the same object. this was back in 7.3.4 ok, so check your information before getting testy.


    cheers
    ps. post this and don't delete it.

    _________________________________________________________________
    ORA-1578
    A nipple is the only intuitive interface, everything else needs a manual

  3. #13
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Well the problem is your information is irrelevant to the poster, that's what I mean. He asked why it takes longer than months before. Does locking cause that? No. Did he say he was validating structure? No

    I just want to say your information is helpful if it's relevant but causes more confusion if it has nothing to do with the topic. Imagine now he probably thinks "I must hit a bug?" when he was simplying gathering statistics and not doing anything you said

  4. #14
    Join Date
    Dec 2002
    Posts
    9
    Pando,

    most people when 7.3.4 was introduced somewhere 1996/1997 were still using RBO in their applications, the oracle RDBMS user base weren't by then caught on to CBO and generating stats for better performance. If this guy is still on 7.3.4, then that is the assumption I make.why? because it would have been very very expensive to change an application to use CBO when most RBO hints were hard coded, Unless these folks have utilized the stats to run CBO!! then that would have been (and I did) the assumption I made.

    So analyzing when you are on RBO is a waist of time! any other analyze would have to be a validate structure since it is the only analyze available after you remove generate stats from the picture.

    The deadlock issue that I am talking about was within the DD, it was a 7.3.3.x bug and was fixed in 7.3.4 but,i said that already.
    This was bug #511014, where a select on a table can deadlock on
    DC_HISTOGRAM_DEF entries with a concurrently executing analyze against the same table.

    I understand you are the moderator, but, you could be more respectful in how you respond and don't let your moderator ego get in the way!! ok pal, relax and don't think your 4000+ replies intimidates but juniors. Have some more respect for the rest of us unless you think you are Mr. Oracle and we are second class oracle citizens.

    I could have had a lot of fun on here, and have helped a lot of folks as well, but now, your crappy attitude will make it difficult
    to join in. ciao and best of luck, it was fun while it lasted.
    Cheers.
    _________________________________________________________________
    ORA-1578
    A nipple is the only intuitive interface, everything else needs a manual

  5. #15
    Join Date
    Oct 2000
    Posts
    139
    ORA-1578 I agree your information is irrelevant to this topic. We can make a bet htanga is analyzing to get the statistics, who on earth want to validate structure every month!

    Please be more respectful

  6. #16
    Join Date
    Jan 2003
    Location
    London, UK
    Posts
    4
    Lots of optimiser bugs in 7.3.4 I experienced performance problems getting worse and not the analyze itself.

    You may need the 7.3.4.5 patch, where quite a few CBO bugs were fixed.
    (I suspect a bug here!) I haven't seen many successful 7.3.4 databases using statistics.

    m00n

  7. #17
    Join Date
    Mar 2002
    Location
    Manchester, England
    Posts
    202
    Originally posted by Mr.Hanky
    If you are using the "compute statistics" option the indexes on the table will be analyzed implicitly.

    MH
    Sorry to hijack this but as you saying estimate will not do the indexes? Do you have to do the indexes seperatly?

    How about "estimate statistics for all columns"

    I've no experiance in 7.x.x so I can't really comment but going from the original post you got to look at object size and growth.
    chris

  8. #18
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Analyzing a growing table will continue to take longer time if you use COMPUTE. That's just the nature of the beast. ESTIMATE may be a good option, but there again, the more data you have the longer it will take to estimate.

    If your Estimate job taken an exponential jump instead of a incremental one, I would look at what has changed in your TEMP tablespace. Also, monitor your I/O during the analyze process. You may be running into an I/O bottleneck that you can solve with more hardware.

    6 hours to analyze a 100 million row table with 3 indexes is not unreasonable. 6 hours to analyze a table with 100,000 rows with one index is probably a long time (on a Unix box anyway...).

    Originally posted by Mr.Hanky

    Ps. This is the first time I have "inspired" someone. Usually it is words like "repulsed" especially when Jeff is around.
    Jeff where have you been?
    Now, now, now. Don't be like that.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #19
    Join Date
    Mar 2002
    Location
    Manchester, England
    Posts
    202
    Quote:-6 hours to analyze a table with 100,000 rows with one index is probably a long time (on a Unix box anyway...).

    I'll have you know that a windows box would easily do that in 6 hours.....but only just

  10. #20
    Join Date
    Jan 2002
    Location
    Up s**t creek
    Posts
    1,525
    Originally posted by mrchrispy
    Quote:-6 hours to analyze a table with 100,000 rows with one index is probably a long time (on a Unix box anyway...).

    I'll have you know that a windows box would easily do that in 6 hours.....but only just
    Hey thems fighting words

    The only good use for a windowz box is to grow flowers in!
    Jim
    Oracle Certified Professional
    "Build your reputation by helping other people build theirs."

    "Sarcasm may be the lowest form of wit but its still funny"

    Click HERE to vist my website!

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