Analysing Tables and Indexes
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Analysing Tables and Indexes

  1. #1
    Join Date
    Sep 2000
    Posts
    362
    Hi,
    How frequentlly should one analyse the tables and indexes of a database in which where all tables are cleared (all rows deleted ) and populated again.

    The following steps are taken

    - Indexes Dropped
    -All records from all tables deleted.
    -Indexes recreated.

    Since the indexes are being recreated do they need to be analysed so that CBO takes the best path.

    Please Help.

    Thanks
    Anurag

  2. #2
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    Yes you can analyze the table after deleting, and then build the indices and then analyze the indices.

    Though it is not necessary for you to analyze the newly created indices, it would give some statistics for the optimizer.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  3. #3
    Join Date
    Feb 2000
    Location
    Washington DC
    Posts
    1,843
    That depends upon How much DML goin on your database. If more the DML, more often you need to analyze for optimizer to choose the best access path. Have a crontab script which runs weekly or monthly... Or at whatever frequency you wanted.
    Reddy,Sam

  4. #4
    Join Date
    Sep 2000
    Posts
    362
    The database is used mainly for querying purpose.

    Is dbms_utility.analyze_database a good option or should I analyse schema by schema.

    Please suggest.

    Thanks
    Anurag

  5. #5
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    make sure that yo do not analyze any of the system tablespace objects, i.e objects that belong to sys/system.

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  6. #6
    Join Date
    Sep 2000
    Posts
    362
    This means that analyze database should not be used because it will analyze the sys/system table spaces also.

    Anyways why should we not analyze the sys/system tablespaces.

    Also how can I see the statistics which have been computed by the analyze command. I know that oracle stores it somewhere.

    Thanks a lot for the help.
    Anurag

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    No, DBMS_UTILITY.ANALYZE_DATABASE does skip the objects owned by SYS, so you should not have problems with it. However there were some bugs reported (8.0.5) that ANALYZING_DATABASES actually analyzes SYS's objects (particualry dangerous to anylyze are fet$ and uet$) - check for example the bug# 969814 on Metalink.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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