67 lines
3.1 KiB
SQL
67 lines
3.1 KiB
SQL
@@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,'NO','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 |