We make use of an Oracle 10.2..5 database being produced.

Optimizer is within "cost-based" mode.

Do we have to calculate statistics (DBMS_STATS package) after:

  • developing a new index
  • adding a column
  • developing a new table



There is no short answer. It totally is dependent in your data and just how you utilize it. Here are a few points to consider:

As @NullUserException stated, statistics are instantly collected, usually every evening. That's usually adequate in many (OLTP) conditions, should you just added new objects they will not contain lots of data prior to the stats are instantly collected. The plans will not be that bad, and when the objects are new they most likely will not be utilized much immediately.

  • developing a new index - No. "Oracle Database now automatically collects statistics during index creation and rebuild".
  • adding a column - Maybe. When the column is going to be utilized in joins and predicates you most likely want stats onto it. Whether it's used just for storing and exhibiting data it will not really affect any plans. But, when the new column occupies much space it might considerably affect the average row length, quantity of blocks, row chaining, etc., and also the optimizer ought to know about this.
  • developing a new table - Most likely. Oracle has the capacity to make amends for missing statistics through dynamic sampling, even though this frequently is not adequate. Particularly if the new table provides extensive data bad statistics more often than not result in under-calculating the cardinality, that will result in nested loops when you wish hash joins. Also, even when the table data has not transformed, you may want to gather statistics once more make it possible for histograms. Automatically, Oracle produces histograms for skewed data, and can not enable individuals histograms if individuals posts weren't used like a predicate. (Which means this is applicable to adding a brand new column too). Should you drop and re-produce a table, even with similar title, Oracle won't maintain any one of that column use data, and won't know that you'll require histograms on certain posts.

Gathering optimizer statistics is a lot more difficult than many people realize. Inside my current job, the majority of our performance troubles are ultimately due to bad statistics. If you are attempting to develop an agenda for the system you need to browse the Managing Optimizer Statistics chapter.


You shouldn't have to collect statistics for empty objects dynamic sampling works just like rapidly as reading through stats in the data dictionary. (With different quick test hard parsing a lot of queries with and without stats.) Should you disable dynamic sampling then there might be some strange cases when the Oracle default values result in inaccurate plans, and you'd be best with statistics with an empty table.

I believe the main reason Oracle instantly gathers stats for indexes at creation time happens because it does not cost much extra. Whenever you create a catalog you need to read all of the blocks within the table, so Oracle may as well calculate the amount of levels, blocks, secrets, etc., simultaneously.

Table statistics could be more difficult, and could require multiple passes from the data. Creating a catalog is comparatively simple in comparison towards the arbitrary SQL that might be included in a create-table-as-choose. It might not be possible, or efficient, to consider individuals arbitrary SQL claims and transform them right into a query which returns the data required to gather statistics.

Obviously it can't cost anything extra to collect stats to have an empty table. However it does not gain you anything either, also it would certainly be misleading to anybody who compares the USER_TABLES.LAST_Examined - the table seem to be examined, although not with any significant data.