Statistics Preferences – Script

By Alex Zaballa Tabelas:

Select * from DBA_TAB_STAT_PREFS;

SELECT
owner, table_name,
DBMS_STATS.get_prefs(ownname=>owner,tabname=>table_name,pname=>’INCREMENTAL’) incremental,
DBMS_STATS.get_prefs(ownname=>owner,tabname=>table_name,pname=>’GRANULARITY’) granularity,
DBMS_STATS.get_prefs(ownname=>owner,tabname=>table_name,pname=>’STALE_PERCENT’) stale_percent,
DBMS_STATS.get_prefs(ownname=>owner,tabname=>table_name,pname=>’NO_INVALIDATE’) no_invalidate,
DBMS_STATS.get_prefs(ownname=>owner,tabname=>table_name,pname=>’ESTIMATE_PERCENT’) estimate_percent,
DBMS_STATS.get_prefs(ownname=>owner,tabname=>table_name,pname=>’CASCADE’) cascade,
DBMS_STATS.get_prefs(ownname=>owner,tabname=>table_name,pname=>’METHOD_OPT’) method_opt
FROM dba_tables
WHERE owner like ‘SAN%’
ORDER BY owner, table_name;

Schemas:

SELECT
username,
DBMS_STATS.get_prefs(ownname=>USERNAME,pname=>’INCREMENTAL’) incremental,
DBMS_STATS.get_prefs(ownname=>USERNAME,pname=>’GRANULARITY’) granularity,
DBMS_STATS.get_prefs(ownname=>USERNAME,pname=>’STALE_PERCENT’) stale_percent,
DBMS_STATS.get_prefs(ownname=>USERNAME,pname=>’NO_INVALIDATE’) no_invalidate,
DBMS_STATS.get_prefs(ownname=>USERNAME,pname=>’ESTIMATE_PERCENT’) estimate_percent,
DBMS_STATS.get_prefs(ownname=>USERNAME,pname=>’CASCADE’) cascade,
DBMS_STATS.get_prefs(ownname=>USERNAME,pname=>’METHOD_OPT’) method_opt
FROM dba_users
ORDER BY username;

Database:

SELECT
DBMS_STATS.get_prefs(pname=>’INCREMENTAL’) incremental,
DBMS_STATS.get_prefs(pname=>’GRANULARITY’) granularity,
DBMS_STATS.get_prefs(pname=>’STALE_PERCENT’) publish,
DBMS_STATS.get_prefs(pname=>’NO_INVALIDATE’) no_invalidate,
DBMS_STATS.get_prefs(pname=>’ESTIMATE_PERCENT’) estimate_percent,
DBMS_STATS.get_prefs(pname=>’CASCADE’) cascade,
DBMS_STATS.get_prefs(pname=>’METHOD_OPT’) method_opt
FROM dual;


Diferença entre

SET_GLOBAL_PREFS Procedure

Fonte: Alex Zaballa