@@header


/*
*
*  Author  : Vishal Gupta
*  Purpose : Display statistics gathering preferences
*
*  Revision History:
*  ===================
*  Date       Author        Description
*  ---------  ------------  -----------------------------------------
*  11-May-12  Vishal Gupta  First Draft
*
*/


COLUMN sname            HEADING "Preference Name"        FORMAT a30
COLUMN value            HEADING "Value"                  FORMAT a30
COLUMN modified         HEADING "Modified"               FORMAT a8
COLUMN timestamp        HEADING "TimeStamp"              FORMAT a36
COLUMN sval1            HEADING "sval1"                  
COLUMN spare2           HEADING "spare2"                  FORMAT a20
COLUMN spare3           HEADING "spare3"                  FORMAT a20
COLUMN spare5           HEADING "spare5"                  FORMAT a10
COLUMN spare6           HEADING "spare6"                  FORMAT a10

SELECT sname
     , spare4                      value
     , DECODE(spare1,1,'N','YES') modified
     , sval2                       timestamp
     , sval1
     , spare2
     , spare3
     , spare5
     , spare6
FROM  sys.OPTSTAT_HIST_CONTROL$
ORDER BY sname     
;

/*
SELECT 'AUTOSTATS_TARGET     : ' || dbms_stats.get_prefs('AUTOSTATS_TARGET')  ||  chr(10)
    || 'CASCADE              : ' || dbms_stats.get_prefs('CASCADE')           ||  chr(10)
    || 'DEGREE               : ' || dbms_stats.get_prefs('DEGREE')            ||  chr(10)
    || 'ESTIMATE_PERCENT     : ' || dbms_stats.get_prefs('ESTIMATE_PERCENT')  ||  chr(10)
    || 'METHOD_OPT           : ' || dbms_stats.get_prefs('METHOD_OPT')        ||  chr(10)
    || 'NO_INVALIDATE        : ' || dbms_stats.get_prefs('NO_INVALIDATE')     ||  chr(10)
    || 'GRANULARITY          : ' || dbms_stats.get_prefs('GRANULARITY')       ||  chr(10)
    || 'PUBLISH              : ' || dbms_stats.get_prefs('PUBLISH')           ||  chr(10)
    || 'INCREMENTAL          : ' || dbms_stats.get_prefs('INCREMENTAL')       ||  chr(10)
    || 'INCREMENTAL_INTERNAL_CONTROL        : ' || dbms_stats.get_prefs('INCREMENTAL_INTERNAL_CONTROL')     ||  chr(10)
    || 'STALE_PERCENT        : ' || dbms_stats.get_prefs('STALE_PERCENT')     ||  chr(10)
    || 'APPROXIMATE_NDV        : ' || dbms_stats.get_prefs('APPROXIMATE_NDV')     ||  chr(10)
    || 'APPROXIMATE_NDV        : ' || dbms_stats.get_prefs('APPROXIMATE_NDV')     ||  chr(10)
    || 'APPROXIMATE_NDV        : ' || dbms_stats.get_prefs('APPROXIMATE_NDV')     ||  chr(10)
    || 'APPROXIMATE_NDV        : ' || dbms_stats.get_prefs('APPROXIMATE_NDV')     ||  chr(10)
    || 'APPROXIMATE_NDV        : ' || dbms_stats.get_prefs('APPROXIMATE_NDV')     ||  chr(10)
--    || 'AUTO_CASCADE         : ' || dbms_stats.auto_cascade                   ||  chr(10)
--    || 'AUTO_DEGREE          : ' || dbms_stats.auto_degree                    ||  chr(10)
--    || 'AUTO_INVALIDATE      : ' || dbms_stats.auto_invalidate                ||  chr(10)
--    || 'AUTO_SAMPLE_SIZE     : ' || dbms_stats.auto_sample_size               ||  chr(10)
    preferences
  FROM dual
;
*/
  
@@footer