Statistics Preferences - Script

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

This procedure is used to set the global statistics preferences.

Read More

NLS_ENV - DBMS_SCHEDULER ou DBMS_JOB

Olá pessoal,

Hoje um cliente me chamou para relatar uma situação estranha na geração de arquivos via JOB.

Ele relatou que não estava mais gerando datas e valores no formato Brasil e sim no Americano.

Investigando, verifiquei que a coluna NLS_ENV da DBA_JOBS estava diferente.

Cheguei a seguinte nota do Metalink:

The Priority of NLS Parameters Explained (Where To Define NLS Parameters) (Doc ID 241047.1)

E ao "problema":

* DBMS_SCHEDULER or DBMS_JOB store the SESSION values of the SUBMITTING session for each job. This is visible in the NLS_ENV column of DBA_SCHEDULER_JOBS or DBA_JOBS


Abs
Alex Zaballa

Read More