Skip to Main Content
  • Questions
  • DIFFERENCE BETWEEN ANALYZE AND DBMS_STATS

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, VIVEK KUMAR.

Asked: April 15, 2009 - 1:20 am UTC

Last updated: January 14, 2021 - 5:22 am UTC

Version: 9.2.0.8.0

Viewed 10K+ times! This question is

You Asked

DIFFERENCE BETWEEN ANALYZE AND DBMS_STATS

and we said...

The difference is you use dbms_stats to gather statistics (as per the documentation) - the optimizer is developed expecting the statistics dbms_stats collects

And you use analyze to list chained rows, to validate the structure.

The use of analyze to gather statistics is a deprecated feature.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

difference between ANALYZE TABLE command ane DBMS_STATS.GATHER_STATISTICS

VIVEK KUMAR GUPTA, April 16, 2009 - 1:18 am UTC

hi.
i want to know difference between ANALYZE TABLE command ane DBMS_STATS.GATHER_STATISTICS, except that analyze table is sql command and dbms_stats package. i want to know in depth difference between this two commands.
thank you
Tom Kyte
April 16, 2009 - 9:29 am UTC

The difference is, as I stated:

a) you use dbms_stats to gather statistics.

b) you use analyze to list chained rows, to validate structure


you do not use analyze to gather statistics since it is documented that "that is the wrong way, analyze is deprecated for statistics gathering"

That is the difference.

what about clusters?

Chris, April 16, 2009 - 6:35 pm UTC

Tom Kyte
April 17, 2009 - 9:32 am UTC

if you need them, yes.

What About dbms_stats.gather_schema_stats ?

Saddam Meshaal, January 13, 2021 - 2:15 pm UTC

Dear Tom, What about dbms_stats.gather_schema_stats ?.
Is there a difference if I used dbms_stats.gather_schema_stats
OR
loop all schema tables and used dbms_stats.gather_table_stats ?

Thank you in advance.


Connor McDonald
January 14, 2021 - 5:22 am UTC

Is there a difference if I used dbms_stats.gather_schema_stats
OR
loop all schema tables and used dbms_stats.gather_table_stats ?


Not really. If you get into the concurrency options, then potentially yes because we'll use the job scheduler to get concurrent executions going.

More to Explore

DBMS_STATS

More on PL/SQL routine DBMS_STATS here