Gather statistics log and default values in 10g

Invoking of dbms_stats package to gather statistics log information to database dictionary, the information is avaiabile using below method:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL> col operation for a30
SQL> col start_time for a38
SQL> col duration for a12
SQL> set lin 80

SQL> select operation, start_time,
  2  (end_time-start_time) day(1) to second(0) as duration
  3  from dba_optstat_operations
  4  order by start_time desc
  5  /

OPERATION                      START_TIME                             DURATION
------------------------------ -------------------------------------- ------------
gather_system_stats            10-SEP-09 10.58.38.567359 PM +08:00    +0 00:00:05
gather_database_stats(auto)    10-SEP-09 10.56.39.972009 PM +08:00    +0 00:00:13
gather_database_stats(auto)    10-SEP-09 10.12.02.228579 PM +08:00    +0 00:00:33
gather_database_stats(auto)    31-AUG-09 10.00.02.685188 PM +08:00    +0 00:00:45
set_system_stats               30-AUG-09 11.04.04.842575 PM +08:00    +0 00:00:00
set_system_stats               30-AUG-09 11.04.04.835971 PM +08:00    +0 00:00:00
set_system_stats               30-AUG-09 11.04.04.720261 PM +08:00    +0 00:00:00
gather_dictionary_stats        30-AUG-09 11.03.17.423471 PM +08:00    +0 00:00:24
gather_dictionary_stats        30-AUG-09 11.01.34.178535 PM +08:00    +0 00:00:15
gather_dictionary_stats        30-AUG-09 11.00.45.056407 PM +08:00    +0 00:00:07
gather_dictionary_stats        30-AUG-09 11.00.21.235183 PM +08:00    +0 00:00:02
gather_dictionary_stats        30-AUG-09 11.00.03.201262 PM +08:00    +0 00:00:04
gather_dictionary_stats        30-AUG-09 10.59.38.403871 PM +08:00    +0 00:00:15
gather_dictionary_stats        30-AUG-09 10.58.28.456440 PM +08:00    +0 00:00:08
gather_dictionary_stats        30-AUG-09 10.58.04.499535 PM +08:00    +0 00:00:04
gather_dictionary_stats        30-AUG-09 10.57.41.872271 PM +08:00    +0 00:00:04
gather_dictionary_stats        30-AUG-09 10.57.25.151534 PM +08:00    +0 00:00:03
gather_dictionary_stats        30-AUG-09 10.56.44.901781 PM +08:00    +0 00:00:05
gather_dictionary_stats        30-AUG-09 10.56.02.422642 PM +08:00    +0 00:00:09
gather_dictionary_stats        30-AUG-09 10.54.54.390954 PM +08:00    +0 00:00:06
gather_dictionary_stats        30-AUG-09 10.53.25.199028 PM +08:00    +0 00:00:50

These information will be purged automatically, as statistics history table. current retention period can be found using below query:

SQL> select dbms_stats.get_stats_history_retention() AS retention from dual;

 RETENTION
----------
        31

From 10g onwards, dbms_stats package does not hardcode parameter values, these parameter default values (for example estimate_percent, can be changed using dbms_stats package).

Here is how we can find out the current default values:

SQL> select dbms_stats.get_param('cascade') from dual;

DBMS_STATS.GET_PARAM('CASCADE')
------------------------------------------------------------------------------------------
DBMS_STATS.AUTO_CASCADE

SQL> select dbms_stats.get_param('degree') from dual;

DBMS_STATS.GET_PARAM('DEGREE')
------------------------------------------------------------------------------------------
NULL

SQL> select dbms_stats.get_param('estimate_percent') from dual;

DBMS_STATS.GET_PARAM('ESTIMATE_PERCENT')
------------------------------------------------------------------------------------------
DBMS_STATS.AUTO_SAMPLE_SIZE

SQL> select dbms_stats.get_param('method_opt') from dual;

DBMS_STATS.GET_PARAM('METHOD_OPT')
------------------------------------------------------------------------------------------
FOR ALL COLUMNS SIZE AUTO

SQL> select dbms_stats.get_param('no_invalidate') from dual;

DBMS_STATS.GET_PARAM('NO_INVALIDATE')
------------------------------------------------------------------------------------------
DBMS_STATS.AUTO_INVALIDATE

SQL> select dbms_stats.get_param('granularity') from dual;

DBMS_STATS.GET_PARAM('GRANULARITY')
------------------------------------------------------------------------------------------
AUTO