How to gather statistics of all stale objects in Oracle database

There is a way to gather statistics of all stale objects in a schema or in a whole database by using dbms_stats procedure.

Before running the procedure you should check which stale objects are present in the database. For this you can query dba_tab_statistics and dba_ind_statistics views. This views will show you all the stale objects in a database.

select owner, table_name from dba_tab_statistics where stale_stats='YES';

select owner, index_name from dba_ind_statistics where stale_stats='YES';

***********************************************************************************

However if you want to check stale objects by using dbms_stats procedure before gathering statistics, then please refer metalink below :

How to List the Objects with Stale Statistics Using 

dbms_stats.gather_schema_stats options=>'LIST STALE' (Doc ID 457666.1)

***********************************************************************************

Now we know the details of stale objects. Let's gather statistics of stale objects.

For Schema :

To gather statistics of all stale objects in a schema use dbms_stats.gather_schema_stats with options parameter set to GATHER STALE.

exec dbms_stats.gather_schema_stats(ownname=>'XYZ',options=>'GATHER STALE',cascade=>TRUE,degree=>4);

where ownname parameter accepts schema name as input, set this to schema of which you want to gather statistics. In my case it is XYZ.,

options parameter will either gather statistics or list objects. This is an important parameter. Set this parameter to GATHER STALE to gather statistics of only stale objects,

cascade parameter if set to TRUE will gather statistics of indexes as well,

degree parameter sets degree of parallelism. I have set this to 4.

 

For Database :

To gather statistics of all stale objects in a database use dbms_stats.gather_database_stats with options parameter set to GATHER STALE

exec dbms_stats.gather_database_stats(options=>'GATHER STALE',cascade=>TRUE,degree=>4);

OR if you want to gather stale statistics of a single object then use dbms_stats.gather_table_stats procedure

exec dbms_stats.gather_table_stats(ownname=>'XYZ',tabname=>'EMP',estimate_percent=>dbms_stats.auto_sample_size,cascade=>TRUE,degree=>4);

where estimate_percent parameter can be set between 0 to 100 or to auto_sample_size. It is recommended to set this parameter to auto_sample_size so that system automatically determines the appropriate sampling percentage.

If you have any doubt on this topic, you can ask in comments. Thanks for reading. :)