Why do I need to Analyze Tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Why do I need to Analyze Tables

  1. #1
    Join Date
    Feb 2001
    Posts
    44

    Question

    Why do I need to analyze tables?...what's the benifits by anaylzing it?..what is it for?

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    3,997
    You only need to analyze tables if you use the cost based optimizer. Based on the statistics that are estimated/computed from the objects Oracle will find the lowest cost alternative for performing your query. The alternative is rule based optimization, which Oracle seems to be moving away from. If you have a large database you may want to analyze your objects, tables, indexes etc, and set the optimizer mode to choose so that Oracle could use the stats if it wants to.

    I created a dbms job that runs the following sys package Dbms_Utility.Analyze_Schema(r_myschemas.owner, analyzemethod, analyzerows, analyzepercent)

    You may want to encaspulate the procedure call in another procedure so that you can have it analyze more than one procedure and do common error handeling.

  3. #3
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    you can also spot things like row chaining/migration through analyse.
    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

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