Skip to Main Content
  • Questions
  • dbms_redefinition VS dbms_stats.gather_schema_stats

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Sambhav.

Asked: March 24, 2017 - 9:21 am UTC

Last updated: March 26, 2017 - 10:20 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi,

Can you please advice which scenario we need to use these.

dbms_redefinition VS dbms_stats.gather_schema_stats

1) As per my Knowledge We need to use dbms_stats.gather_table_stats after large number of DML occur on the table .So that table stats are upto date. By using this it release the block as well which are not in use( when we delete records from table).

2) dbms_redefinition - It can be used to release the unoccupied block. So if we are doing urging mechanism , do we need to use this as well.

3) in dbms_stats.gather_schema_stats ,method should always be method_opt => 'for all columns size auto' ; what does it means and what are the other method_opt option.

and Connor said...

You generally dont need to over complicate things unless you need to.

1) Stats will kept up to date in most cases without your needing to worry about it. There is an automatic job that detects those tables that need refreshed stats. If you perform large adhoc maintenance, then you might want to consider a manual call after that operation.

2) You dont need dbms_redefinition for after purging. You *might* not need to do anything (because we will re-use free space as required). You *might* be doing an 'alter table move' or 'alter table shrink space' after the purge, in which case, you might follow that up with a dbms_stats call (see 1)

3) With dbms_stats, I always recommend going with the defaults *unless* there is a reason not to. 'for all columns size auto' basically means the database will actively monitor queries in the database, and try make an intelligent decision based on your queries about which columns will need histograms and what size those histograms should be.

Some people find its a bit "generous" with histograms, in which case, you can set a table level preference to explicitly limit or exclude histograms if its causing your problems.

The METHOD_OPT options are all in the docs

http://docs.oracle.com/database/121/ARPLS/d_stats.htm#ARPLS68582

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

More to Explore

DBMS_REDEFINITION

More on PL/SQL routine DBMS_REDEFINITION here