Calcul des statistiques

Afficher les valeurs des paramètres utlisés par défaut par dbms_stats

col sname for a30
col SPARE4 for a50

select SNAME, spare4 from SYS.OPTSTAT_HIST_CONTROL$ ;

SNAME                          SPARE4
------------------------------ --------------------------------------------------
SKIP_TIME
STATS_RETENTION
TRACE                          0
DEBUG                          0
SYS_FLAGS                      1
CASCADE                        DBMS_STATS.AUTO_CASCADE
ESTIMATE_PERCENT               DBMS_STATS.AUTO_SAMPLE_SIZE
DEGREE                         NULL
METHOD_OPT                     FOR ALL COLUMNS SIZE AUTO
NO_INVALIDATE                  TRUE
GRANULARITY                    AUTO
AUTOSTATS_TARGET               AUTO

12 rows selected.

Les valeurs ici ne sont déjà pas les valeurs utilisée par oracle par défaut, NO_INVALIDATE a été modifiée

Ecrire une procédure de calcul des statistiques

Pour un client, j’ai écrit très rapidement cette procédure de calcul de statistiques. Très (trop) imparfaite

create or replace procedure sys.calc_stats is
    /*
     * Procédure de calcul des statistiques            ****************************
     *
     * Les statistiques son calculees sur les tables dont le cumul des modifications
     * (insert / delete / updates) dépasse 5% du volume de la table. Elle invalide
     * les plans en memoire et ignore ertains utilisateurs. Son parallelisme est de 4
     * et les statistiques ne sont pas calculees sur les index de la table
     *
     * ******************************************************************************
     * Version 1.0 pour ************ sur ORA 102040
     * ******************************************************************************
     */
begin
  for c in (
   select tm.table_name tbn
        , t.owner tbo
   from ( select table_name
               , table_owner
               , sum(inserts) inserts
               , sum(deletes) deletes
               , sum(updates) updates
          from dba_tab_modifications
          where table_owner not in ( 'SYS', 'SYSTEM', 'SYSMAN', 'TOOLS'
                                 , 'DBSNMP', 'ANONYMOUS', 'WMSYS', 'GOLDENGATE'
                                 , 'XDB', 'TOAD', 'OUTLN', 'TSMSYS', 'ORDSYS')
          group by table_name, table_owner ) tm
        inner join dba_tables t
                   on ( t.owner=tm.table_owner
                    and t.table_name=tm.table_name )
   where 1=1
     and t.owner not in ( 'SYS', 'SYSTEM', 'SYSMAN', 'TOOLS'
                        , 'DBSNMP', 'ANONYMOUS', 'WMSYS', 'GOLDENGATE'
                        , 'XDB', 'TOAD', 'OUTLN', 'TSMSYS', 'ORDSYS')
     and (tm.inserts+tm.updates+tm.deletes)/(nvl(t.num_rows,0)+1) > 0.05
      order by nvl(t.num_rows,0) asc  )
  loop
      dbms_stats.gather_table_stats(c.tbo, c.tbn, null, null, null, 'for all indexed columns size repeat', 4, 'ALL', FALSE, null, null, null, FALSE );
  end loop;
end;
/

Manque du paramétrage et de l’automatisme

Paramétrage

• seuil de calcul
• no_invalidate

Automatismes

• Echantillonnage
• Cascade

A faire évoluer donc …