Results 1 to 4 of 4

Thread: Analyze Process

  1. #1
    Join Date
    Dec 2003
    Location
    India
    Posts
    12

    Unanswered: Analyze Process

    Can anyone explain me the differences between the following types of analyze processes :-


    1) analyze table abc estimate statistics sample 20 percent;

    2) analyze table abc estimate statistics sample 20 percent for table for all indexes for all indexed columns;

    3)analyze table abc validate structure;

    4) analyze table abc compute statistics for table
    for all indexes for all indexed columns;

    5) analyze table abc compute statistics for table;

    Which process would be the best if i need to analyze a 300GB database everyday .

    Thanks in advance
    Sbdash
    Last edited by sbdash; 12-31-03 at 01:11.

  2. #2
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    Hi Sbdash,

    Only you can truly decide which method is best for your tables. Different tables can be analysed in different ways and some could be skipped altogether to gain the most optimal statistics.

    You should also check out histograms on certain table columns - these can have a dramatic effect on performance, again only you can decide on which columns/tables these would be appropriate.

    You should also consider moving to the DBMS_STATS package for statistics gathering etc.

    Finally, it is unlikely that you would need to analyze the db every day. Unless the content (skew of data) of a table changes significantly each day you will gain no benefit.

    I would suggest you read the Oracle tuning for performance guides, it's far too much to go into here.

    Hth
    Bill
    Please don't email me directly with questions. I've probably just got home from the pub and cannot guarantee the sanity of my answers. In fact, I can't believe I actually made it home.

  3. #3
    Join Date
    Sep 2003
    Location
    Milan, Italy
    Posts
    130

    monitoring

    For daily analyze, I would consider taking a look at the MONITORING option of the tables/indexes, which basically means that Oracle will flag the tables/indexes that were modified "more than 10%" so that you can re-analyze only them daily. Requires use of the dbms_stats package, that you would want to use anyway instead of the ANALYZE command which is somewhat "deprecated".

    You can take a look at this question on Tom Kyte's site

    http://asktom.oracle.com/pls/ask/f?p...:5792247321358

    which is exactly the same as your one.

    HTH
    Al

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Over the last couple of days there has been much discussion on the ORACLE-L email list regarding the frequency when statistics should be collected.

    A decent number of the resident Wizards now conclude that "frequent" collection of statistics; especially when done via regularly scheduled task, is no longer a "Best Practice".

    FWIW - I tend to agree with them.

    HTH & YMMV

    Happy New Year!

    P.S.


    The official ORACLE-L FAQ: http://www.orafaq.net

    You may also send the HELP command for other information (like subscribing) to ListGuru@fatcity.com

Posting Permissions

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