SQL TO CHECK STATISTICS PREFERENCES ORACLE
To check the optimizer statistics parameter settings for DBMS_STATS, this query will be helpful
select 'AUTOSTATS_TARGET =====> '||DBMS_STATS.GET_PREFS('AUTOSTATS_TARGET')||'' as "STATS_PARAM" from dual
union all
select 'CASCADE =====> '||DBMS_STATS.GET_PREFS('CASCADE')||'' from dual
union all
select 'CONCURRENT =====> '||DBMS_STATS.GET_PREFS('CONCURRENT')||'' as "STATS_PARAM" from dual
union all
select 'DEGREE =====> '||DBMS_STATS.GET_PREFS('DEGREE')||'' from dual
union all
select 'ESTIMATE_PERCENT =====> '||DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT')||'' from dual
union all
select 'GRANULARITY =====> '||DBMS_STATS.GET_PREFS('GRANULARITY')||'' from dual
union all
select 'INCREMENTAL =====> '||DBMS_STATS.GET_PREFS('INCREMENTAL')||'' from dual
union all
select 'INCREMENTAL_LEVEL =====> '||DBMS_STATS.GET_PREFS('INCREMENTAL_LEVEL')||'' from dual
union all
select 'INCREMENTAL_STALENESS =====> '||DBMS_STATS.GET_PREFS('INCREMENTAL_STALENESS')||'' from dual
union all
select 'METHOD_OPT =====> '||DBMS_STATS.GET_PREFS('METHOD_OPT')||'' from dual
union all
select 'OPTIONS =====> '||DBMS_STATS.GET_PREFS('OPTIONS')||'' from dual
union all
select 'STALE_PERCENT =====> '||DBMS_STATS.GET_PREFS('STALE_PERCENT')||'' from dual;
For specific table
select 'AUTOSTATS_TARGET =====> '||DBMS_STATS.GET_PREFS('AUTOSTATS_TARGET','KISH','XSAL1')||'' as "STATS_PARAM" from dual
union all
select 'CASCADE =====> '||DBMS_STATS.GET_PREFS('CASCADE','KISH','XSAL1')||'' from dual
union all
select 'CONCURRENT =====> '||DBMS_STATS.GET_PREFS('CONCURRENT','KISH','XSAL1')||'' as "STATS_PARAM" from dual
union all
select 'DEGREE =====> '||DBMS_STATS.GET_PREFS('DEGREE','KISH','XSAL1')||'' from dual
union all
select 'ESTIMATE_PERCENT =====> '||DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT','KISH','XSAL1')||'' from dual
union all
select 'GRANULARITY =====> '||DBMS_STATS.GET_PREFS('GRANULARITY','KISH','XSAL1')||'' from dual
union all
select 'INCREMENTAL =====> '||DBMS_STATS.GET_PREFS('INCREMENTAL','KISH','XSAL1')||'' from dual
union all
select 'INCREMENTAL_LEVEL =====> '||DBMS_STATS.GET_PREFS('INCREMENTAL_LEVEL','KISH','XSAL1')||'' from dual
union all
select 'INCREMENTAL_STALENESS =====> '||DBMS_STATS.GET_PREFS('INCREMENTAL_STALENESS','KISH','XSAL1')||'' from dual
union all
select 'METHOD_OPT =====> '||DBMS_STATS.GET_PREFS('METHOD_OPT','KISH','XSAL1')||'' from dual
union all
select 'OPTIONS =====> '||DBMS_STATS.GET_PREFS('OPTIONS','KISH','XSAL1')||'' from dual
union all
select 'STALE_PERCENT =====> '||DBMS_STATS.GET_PREFS('STALE_PERCENT','KISH','XSAL1')||'' from dual;