Skip to Main Content
  • Questions

  • DIFFERENCE BETWEEN ANALYZE AND DBMS_STATS

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

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 Tom 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

Analyze is still valid for gathering cluster statistics, correct?

http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/stats.htm#PFGRF30102

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1510330500346645922
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

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

Get all the information about database performance in the Database Performance guide.