6f429244e498457b8400e72e4af683b0 Alberto Blog: Oracle Statistics.....

venerdì 8 aprile 2011

Oracle Statistics.....

Oggi tratterò un argomento di particolare interesse in ambito DBA:raccolta delle statistiche di un DB.
La raccolta delle statistiche è un'operazione di vitale importantanza per tutto il database e serve per poter dare al CBO (Cost Based Optimizer)
tutte le informazioni per poter eseguire al meglio, ossia al costo più basso tutte le operazioni del DB.
Avere le statistiche sempre aggiornate è molto importante, tuttavia la frequenza di questa operazione è molto soggettiva, può essere una
volta alla settimana, una al mese oppure anche quotidianamente in relazione anche alla dimensione del database.

La procedura corretta imporrebbe di esportare le vecchie statistiche prima di prenderne delle nuove questo è molto
importante per prevenire possibili variazioni dei piani di esecuzioni degli statement SQL, in quanto il costo calcolato potrebbe subire delle modifiche e
quindi indurre al CBO per esempio di utilizzare un indice al posto di un altro e non solo, aumentando così il costo e la durata dello statement.

Caso tipico il giorno prima un batch, una query o un altro statement SQL, impiega magari 1 min e il giorno
successivo (dopo aver aggiornato le statistiche) 5 min, avendo le vecchie statistiche esportate è possibile ripristinarle

Tuttavia questa operazione va seriamente valutata.

Nel tempo ho cercato di rendere più facile la consultazione di questa operazione ossia ho cercato di rispondere principalmente a queste domande:
1) Quanto impiega a raccogliere le statistiche (magari anche per SCHEMA) ?
2) Come faccio a verificare il buon esito dell'operazione?
3) Posso creare un file di log oppure altro per tenere uno storico dell'operazione?

Ho creato così nel db un Package,una Procedure e una sequence che vanno a scrivere in un tabella del DB una serie di informazioni molto utili in modo tale che
anche un utente, non DBA con accessi ridotti, può andare a consultare i risultati, oltre a centralizzare per chi gestisce più di un DB questo genere
di informazioni



Cominciamo dal package con relativo body:

create or replace
PACKAGE          "STATISTICS_LOG" as
procedure log( p_APP IN varchar2,
p_MSG IN varchar2,
p_CODE IN varchar2 := '',
p_MOD IN varchar2 := '',
p_SECTION IN varchar2 := '');
end;

create or replace
package body STATISTICS_LOG as
procedure log( p_APP IN varchar2,
p_MSG IN varchar2,
p_CODE IN varchar2 := '',
         p_MOD IN varchar2 := '',
p_SECTION IN varchar2 := '')    
            is
PRAGMA AUTONOMOUS_TRANSACTION;

begin

insert into HSTATISTICS_TAB (
ID,
APP_KEY,
MOD_KEY,
SECTION_KEY,
MSG_CODE,
MSG_TEXT,
TIME_STAMP)
select
seq_stat_id.nextval,
p_APP,
p_MOD,
p_SECTION,
p_CODE,
p_MSG,
sysdate
from
dual;

  commit;

exception
when others then
commit;
raise;
end log;

end;

e la sequence relativa:

CREATE SEQUENCE "SEQ_STAT_ID" MINVALUE 1 MAXVALUE 999999999999999999999999999 INCREMENT BY 1 START WITH 15859 CACHE 20 NOORDER NOCYCLE ;


Con questo package imposto delle variabili che richiamerò durante la procedura di raccolta statistiche per andare ad inserirle in un tabella HSTATISTICS_TAB che
creo così:

CREATE TABLE HSTATISTICS_TAB
  (
    "ID"          NUMBER,
    "APP_KEY"     VARCHAR2(255 BYTE) NOT NULL ENABLE,
    "MOD_KEY"  VARCHAR2(255 BYTE),
    "SECTION_KEY" VARCHAR2(255 BYTE),
    "MSG_CODE"    NUMBER,
    "MSG_TEXT"    VARCHAR2(4000 BYTE) NOT NULL ENABLE,
    "TIME_STAMP"  DATE NOT NULL ENABLE
  )


La sequence la utilizzo per generare un ID progressivo.

A questo punto non ci resta di scrivere  la procedure che esegue la raccolta delle statistiche:


create or replace
procedure analyze_job is
v_owner            varchar2(10);

begin

       select  DISTINCT owner into v_owner from all_all_tables
       where owner='123'
       GROUP BY owner;

             dbms_stats.gather_schema_stats (ownname => '123',
                                estimate_percent=>dbms_stats.auto_sample_size,
                                method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1',
                               granularity => 'ALL',
                               cascade => TRUE,
                               degree => 7);

        STATISTICS_LOG.log(p_APP       =>      'Your app name',
                  p_MOD      =>      'DBA',
                  p_SECTION     =>      'ANALYZE',
                  p_MSG         =>      'Analyzing tables/indexes '|| to_char(v_owner)||' ...');

        STATISTICS_LOG.log(p_APP       =>      'Your app name',
                  p_MOD      =>      'DBA',
                  p_SECTION     =>      'ANALYZE',
                  p_MSG         =>      'End Daily Analyze: Successfully executed '|| to_char(v_owner)||'!');

exception
when others then

   STATISTICS_LOG.log( p_APP           =>      'Your app name',
                p_MOD        =>      'DBA',
                p_SECTION       =>      'ANALYZE',
                p_CODE          =>      SQLCODE,
                p_MSG           =>      'Abort Daily Analyze: Error "' || SQLERRM || '" ocured '|| to_char(v_owner)||'!');
                                 
end ANALYZE_JOB;


Una volta eseguita, scriverà nella tabella HSTATISTICS_TAB delle informazioni molti utili vediamo adesso se sono riuscito a
rispondere alle domande precedenti:

1)Attraverso la colonna time_stamp è possibile verificare la durata dell'operazione anche per singolo SCHEMA.
2)Attraverso la colonna MSG_TEXT e MSG_CODE posso verificarne l'esito, in caso di errore inserirà nella colonna MSG_CODE un ORA:.
3)Creando la tabella HSTATISTICS_TAB posso far consultare anche un utente non DBA, con appositi permessi e soprattutto tenere
uno storico per la raccolta delle statistiche.

Nessun commento:

Posta un commento