Link to home
Start Free TrialLog in
Avatar of Swaminathan K
Swaminathan KFlag for India

asked on


Hi All,

I have been tuning the tables using the ANALYZE command , without knowing what happens internally . Iam new to Oracle DBA in tuning. Hence request anyone to provide a detailed explanation as to what happens when we ANALYZE the table or use DBMS_GATHER_STATISTICS package.

Thanks in advance.
Avatar of johnsone
Flag of United States of America image

You shouldn't be using ANALYZE.  It doesn't gather all the statistics.  Use the DBMS_STATISTICS package procedures.

These are the statistics that the optimizer uses to determine which plan is the best.  Information gathered includes number of rows, distributions, histograms and other information.
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Since you are new to Oracle, I would strongly encourage you to spend some time in the Concepts documentation and the 2-Day books.

The Concepts guide has a fairly decent overview of the statistics:
Optimizer Statistics

Optimizer statistics are a collection of data that describe details about the database and the objects in the database. The statistics provide a statistically correct picture of data storage and distribution usable by the optimizer when evaluating access paths.

The Documentation home page:
if you really want to see what happens with either or both of the commands, try tracing your own session, then running the commands.  You'll see every query the database runs internally to determine the output.

Note - ANALYZE can still be used, just don't use it for statistics, instead, use it for structure analysis.
There are lots of parameters when creating a table, therefore tuning a table is very generic statement. Is there any specific problem you are trying to solve?
Avatar of Swaminathan K


I need to know what are the types of statistics oracle gathers when we run either ANALYZE or DBMS_GATHER_TABLE_STATS ?
Avatar of Sean Stuber
Sean Stuber

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks a lot , I ran the queries got some idea about the statistics gathered. Sdstuber u always rock.