Avatar of Swaminathan K
Swaminathan K
Flag for India asked on

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.
Oracle Database

Avatar of undefined
Last Comment
Swaminathan K

8/22/2022 - Mon
johnsone

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.
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:
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
Sean Stuber

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Devinder Singh Virdi

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?
Swaminathan K

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
Sean Stuber

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Swaminathan K

ASKER
Thanks a lot , I ran the queries got some idea about the statistics gathered. Sdstuber u always rock.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.