Inhalt

Problem

Analyse

Lösung

 
-- gentablestats.sql

DEFINE L_USER="SCOTT"
DEFINE L_TABLE_PATT="PFC"
set serveroutput on
set echo on
DECLARE

cursor c_table (p_table in varchar2) is
   select table_name from dba_tables where owner = '&&L_USER' and upper(table_name) like upper(p_table ||'%');

--
 v_sqlstring VARCHAR2(2000);
begin
  for table_rec in c_table('&&L_TABLE_PATT')
  loop
    DBMS_STATS.gather_table_stats(
        ownname => '&&L_USER'
      , tabname => table_rec.table_name
      , estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE
      , cascade => TRUE
      , method_opt => 'for all columns size auto'
    );
    dbms_output.put_line('Table: ' || table_rec.table_name || ' analyzed ');
  end loop;
end;
/

SYSTEM Statistiken

 
begin
  DBMS_STATS.GATHER_DICTIONARY_STATS;
  DBMS_STATS.GATHER_FIXED_OBJECTS_STATS; 
end;
/

 
col PVAL2 form a20
SELECT pname, pval1, pval2 FROM sys.aux_stats$ WHERE sname = 'SYSSTATS_MAIN';

 
SELECT pname, pval1, pval2 FROM sys.aux_stats$ WHERE sname = 'SYSSTATS_INFO';

Fazit

Referenzen