Analyze table

d27m11y
d27m11y used Ask the Experts™
on
When do you analyze table /collect stats. Is it when you delete/insert large chunks of data from tables. And why do we do that?

Can someone post me some example to understand the concept.

Quick response is appreciated!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Distinguished Expert 2018
Commented:
Yes.  You should do them when you do large DML processing.

Stats are used by the Cost Based Optimizer to figure out the most efficient execution plan.

For more information about them, I would start in the online docs.  It has everything you need to know about the statistics and optimizer:

http://download.oracle.com/docs/cd/E11882_01/server.112/e16638/stats.htm#PFGRF94712

There is also a 2Day DBA on Performance Tuning:
http://download.oracle.com/docs/cd/E11882_01/server.112/e10822/toc.htm
Most Valuable Expert 2011
Top Expert 2012
Commented:
typically statistics are gathered off-hours

however, if you have a particularly large set of data changes, it's not unreasonable to gather statistics when you are done.

dbms_stats.gather_table_stats   -- this is probably what you want

dbms_stats.gather_schema_stats  -- use this if you want to do all of the tables

dbms_stats.gather_index_stats  -- shouldn't need this if you are gathering either of the other two

for more info...

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e16760/d_stats.htm#CIHBIEII

Author

Commented:

How does this work for partitions?
Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Same as non-partitions.  Why are you thinking is is different?
Most Valuable Expert 2011
Top Expert 2012

Commented:
check the documentation links above

the parameters in the gather procedures determine how to handle partitions

Author

Commented:

Also, please let me know how the Oracle Hint /*Append*/ is used after/before gathering statistics on tables?
Most Valuable Expert 2011
Top Expert 2012

Commented:
specifically the "granularity" parameter

from the link above...

Granularity of statistics to collect (only pertinent if the table is partitioned).

'ALL' - Gathers all (subpartition, partition, and global) statistics

'AUTO'- Determines the granularity based on the partitioning type. This is the default value.

'DEFAULT' - Gathers global and partition-level statistics. This option is obsolete, and while currently supported, it is included in the documentation for legacy reasons only. You should use the 'GLOBAL AND PARTITION' for this functionality. Note that the default value is now 'AUTO'.

'GLOBAL' - Gathers global statistics

'GLOBAL AND PARTITION' - Gathers the global and partition level statistics. No subpartition level statistics are gathered even if it is a composite partitioned object.

'PARTITION '- Gathers partition-level statistics

'SUBPARTITION' - gathers subpartition-level statistics
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Append hint is for loading data.  It has nothing to do with statistics.
Most Valuable Expert 2011
Top Expert 2012

Commented:
append hint has nothing to do with statistics

exploring its use would be a new question

Author

Commented:


I was looking at Conventional insert and direct load insert. Does the direct insert loads the data faster than the Conventional insert and does either of these has anythng to do with the "Analyze table"?
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Different questions.

Look up High Water Mark in the docs.

Neither have to do with gathering statistics.

Author

Commented:

I understand that I am asking too many qns. Actually, I am confused with all of these concepts together.
Most Valuable Expert 2011
Top Expert 2012

Commented:
that's another reason to split them into different questions.  You'll get focused answers
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>I am confused with all of these concepts together.

Maybe the confusion is coming from trying to relate concepts that are not related.

We have answered this question and have provided the documentation links necessary to help you gather the answers.  I have also provided you the necessary terms to research to let you figure out the secondary hint question asked here.

Read through the links we provided and if you have any specific answers please ask.  Try not to ask really generic questions like "what are statistics" or you will likely get the same links again since that type of question is so vague it is really impossible to answer on a Q&A site like this.

People have written books on tuning and how Oracle does what it does.  I will say that every one of those books have a least one entire chapter on statistics and the optimizer (I bet more than one chapter in most).


Author

Commented:
Documentation is quite helpful!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial