Oracle DBA ANALYZE COMMAND
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.
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.
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:
http://docs.oracle.com/cd/E11882_01/server.112/e40540/toc.htm
The Documentation home page:
http://docs.oracle.com/cd/E11882_01/nav/portal_4.htm
The Concepts guide has a fairly decent overview of the statistics:
http://docs.oracle.com/cd/E11882_01/server.112/e40540/toc.htm
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:
http://docs.oracle.com/cd/E11882_01/nav/portal_4.htm
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.
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?
ASKER
I need to know what are the types of statistics oracle gathers when we run either ANALYZE or DBMS_GATHER_TABLE_STATS ?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks a lot , I ran the queries got some idea about the statistics gathered. Sdstuber u always rock.
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.