Generación Automatica de Estadisticas

Existen dos maneras maneras de generar estadísticas en Oracle: ANALYZE TABLE y DBMS_STATS.
El siguiente script permite calcular estadísticas en las tablas nuevas y en las que no las tengan actualizadas, además de calcular histogramas en las tablas que nosotros le proporcionemos.

Primero hay que saber a que usuario le vamos a calcular estadísticas.
vowner VARCHAR2 (100) := ‘APPLOWNER‘;

Para poder calcularle a las tablas que tienen las estadísticas des actualizadas primero las tablas se deben monitorear para que Oracle pueda identificar cuales tablas han tenido cambios significativos y por lo tanto sea necesario re calcular estadísticas. Para monitorear las tablas se utiliza el comando ALTER TABLE tblname MONITORING.
Se utiliza un cursor el cual genera los comandos para alterar las tablas que no son monitoreadas, regularmente las tablas nuevas.
Note que aqui se utiliza la variable anteriormente declarada.

CURSOR nomonitoringtables
IS
SELECT 'ALTER TABLE ' || vowner || '.' || table_name || ' MONITORING ' AS command
FROM dba_tables
WHERE MONITORING = 'NO' AND TEMPORARY = 'N' AND owner = vowner;

Como también deseamos calcular histogramas a ciertas tablas entonces declaramos una colección la cual contiene la lista de tablas a las cuales se les calcularan histogramas. también se tiene que saber de que tamaño va a ser el histograma, para esto utilizamos una variable.
En caso que no se desee calcular histogramas a ninguna de las tablas entonces dejar la inicialización de la colección vacía. Algo asi:

histtbl_t ();
TYPE histtbl_t IS TABLE OF VARCHAR2 (500);

   --calculate histograms to the next tables
vhisttables      histtbl_t      := histtbl_t ('tbl1','tbl4','tbl3');
vhistogramsize   NUMBER         := 200;

Como primer paso en la ejecución calculamos las estadísticas a las tablas que no tienen y a las que no están actualizadas.

--CALCULATE STALE STATISTICS
DBMS_STATS.gather_schema_stats (ownname => vowner,
options => 'GATHER AUTO',
estimate_percent => DBMS_STATS.auto_sample_size,
method_opt => 'for all columns size auto',
DEGREE => 2,
gather_temp => FALSE,
block_sample => TRUE,
CASCADE => TRUE,
no_invalidate => FALSE
);

Después si hay tablas a las cuales se desea calcular histogramas entonces se calcula a ese grupo de tablas.

--calculate histograms
IF vhisttables.COUNT () <> 0
THEN
FOR indx IN vhisttables.FIRST .. vhisttables.LAST
LOOP
DBMS_STATS.gather_table_stats (ownname => vowner,
tabname => vhisttables (indx),
estimate_percent => DBMS_STATS.auto_sample_size,
block_sample => TRUE,
method_opt => 'FOR ALL INDEXED COLUMNS SIZE '||vhistogramsize   ,
DEGREE => 2,
CASCADE => TRUE,
no_invalidate => FALSE
);
END LOOP;
END IF;

Por ultimo, utilizando el cursor para alterar las tablas, se alteran las tablas nuevas que no han sido monitoreadas para que a la siguiente ejecución se calculen las estadísticas.

--ALTER NEW TABLES TO MONITORING
FOR vtables IN nomonitoringtables
LOOP
EXECUTE IMMEDIATE vtables.command;
END LOOP;


Script completo:
/*
Calculate Weekly Statistics for APPLOWNER
*/

DECLARE
vowner           VARCHAR2 (100) := 'APPLOWNER';

   CURSOR nomonitoringtables
IS
SELECT 'ALTER TABLE ' || vowner || '.' || table_name || ' MONITORING ' AS command
FROM dba_tables
WHERE MONITORING = 'NO' AND TEMPORARY = 'N' AND owner = vowner;

   TYPE histtbl_t IS TABLE OF VARCHAR2 (500);

   --calculate histograms to the next tables
vhisttables      histtbl_t      := histtbl_t ('SALDOS_IMP');
vhistogramsize   NUMBER         := 200;
BEGIN
--CALCULATE STALE STATISTICS
DBMS_STATS.gather_schema_stats (ownname => vowner,
options => 'GATHER AUTO',
estimate_percent => DBMS_STATS.auto_sample_size,
method_opt => 'for all columns size auto',
DEGREE => 2,
gather_temp => FALSE,
block_sample => TRUE,
CASCADE => TRUE,
no_invalidate => FALSE
);

   --calculate histograms
IF vhisttables.COUNT () <> 0
THEN
FOR indx IN vhisttables.FIRST .. vhisttables.LAST
LOOP
DBMS_STATS.gather_table_stats (ownname => vowner,
tabname => vhisttables (indx),
estimate_percent => DBMS_STATS.auto_sample_size,
block_sample => TRUE,
method_opt => 'FOR ALL INDEXED COLUMNS SIZE '||vhistogramsize,
DEGREE => 2,
CASCADE => TRUE,
no_invalidate => FALSE
);
END LOOP;
END IF;

   --ALTER NEW TABLES TO MONITORING
FOR vtables IN nomonitoringtables
LOOP
EXECUTE IMMEDIATE vtables.command;
END LOOP;
END;

Si se desea que este script se ejecute de manera automática entonces hay que calendarizarlo en un job como se muestra a continuación.

DECLARE
X NUMBER;
BEGIN
SYS.DBMS_JOB.SUBMIT
( job       => X
,what      => '/*
Calculate Weekly Statistics for APPLOWNER
*/

DECLARE
vowner           VARCHAR2 (100) := ''APPLOWNER'';

   CURSOR nomonitoringtables
IS
SELECT ''ALTER TABLE '' || vowner || ''.'' || table_name || '' MONITORING '' AS command
FROM dba_tables
WHERE MONITORING = ''NO'' AND TEMPORARY = ''N'' AND owner = vowner;

   TYPE histtbl_t IS TABLE OF VARCHAR2 (500);

   --calculate histograms to the next tables
vhisttables      histtbl_t      := histtbl_t (''tbl1'',''tbl2'',''tbl3'');
vhistogramsize   NUMBER         := 200;
BEGIN
--CALCULATE STALE STATISTICS
DBMS_STATS.gather_schema_stats (ownname => vowner,
options => ''GATHER AUTO'',
estimate_percent => DBMS_STATS.auto_sample_size,
method_opt => ''for all columns size auto'',
DEGREE => 2,
gather_temp => FALSE,
block_sample => TRUE,
CASCADE => TRUE,
no_invalidate => FALSE
);

   --calculate histograms
IF vhisttables.COUNT () <> 0
THEN
FOR indx IN vhisttables.FIRST .. vhisttables.LAST
LOOP
DBMS_STATS.gather_table_stats (ownname => vowner,
tabname => vhisttables (indx),
estimate_percent => DBMS_STATS.auto_sample_size,
block_sample => TRUE,
method_opt => ''FOR ALL INDEXED COLUMNS SIZE ''||vhistogramsize,
DEGREE => 2,
CASCADE => TRUE,
no_invalidate => FALSE
);
END LOOP;
END IF;

   --ALTER NEW TABLES TO MONITORING
FOR vtables IN nomonitoringtables
LOOP
EXECUTE IMMEDIATE vtables.command;
END LOOP;
END;'
,next_date => to_date('17/12/2006 00:00:00','dd/mm/yyyy hh24:mi:ss')
,interval  => 'TRUNC(SYSDATE+7)'
,no_parse  => TRUE
);
SYS.DBMS_OUTPUT.PUT_LINE('Job Number is: ' || to_char(x));
END;
/

commit;

powered by performancing firefox

  1. #1 by Paula on 27 May, 2009 - 18:02

    hola!
    Espero que estés bien.
    Me han gustado mucho tus post, por eso me atrevo a preguntar si puedes compartir información, por favor, referente a “migración” de base de datos. Tengo una bd oracle 10g y la quiero “mover” a otro servidor.
    Gracias anticipadas

  2. #2 by delfinonunez on 27 May, 2009 - 19:53

    Hola Paula,
    Pensaba responderte inmediatamente aqui pero mejor creo un post rápido al respecto, revisa en los siguientes días.

  3. #3 by Paula on 27 May, 2009 - 21:22

    ok, gracias por la rapidez en contestar así como en tomarte la molestia de hacerlo.
    abusando de tus conocimientos …. si tengo una bd de producción oracle 9i (y otras 8i) a las que les quiero generar estadísticas …. es necesario obtener a toda la bd? … solo a los esquemas que no son propios del sistema/oracle? …. qué debo tomar en cuenta para definir el “tipo” de estadísticas? … GRACIAS!

  4. #4 by delfinonunez on 27 May, 2009 - 21:50

    La respuesta rápida seria únicamente a los schemas/usuarios de aplicación y excluir a los de sistema como sys, system, perfstats, etc en un principio.

    Podríamos decir que existen dos tipos de estadísticas, las de aplicación que son de las que hablo en los posts y las de sistema (system statistics). Las primeras se calculan en los objetos(tablas,indices,etc) de nuestra aplicación; en cuanto a las segundas pues son estadísticas que le dicen a Oracle como esta el sistema (CPU,discos,memoria,etc) además Oracle tiene el diccionario de datos que a la vez son tablas,indices,etc y que si aplicamos la teoría pues deberíamos calcularle también estadísticas a los schemas de sys y system y demás usuarios pero en “general” eso no se hace para no tener problemas con el RDBMS. En la version 10G las system statistics se calculan automáticamente con un job que se crea automáticamente en la instalación, en la 9i creo que tu debes habilitarlas y en la 8i no estoy seguro si existen.

    En cuanto a que debes tomar en cuenta para definir el tipo de estadísticas depende mucho de ti (experiencia) y las necesidades de la aplicación, en cierto casos solo necesitas calcular estadísticas una vez por semana en otros casos diariamente, hay otros casos en que es necesario borrar las estadísticas, todo depende de como se hizo la aplicación.

    Como regla general deberíamos calcular estadísticas a los schemas de aplicación al menos y de ahi partir, honestamente yo nunca he calculado estadísticas a toda la base de datos (sys,system,etc) solo a los usuarios de aplicación.

    Claro, primero intenta hacer el calculo en la base de desarrollo o pruebas antes de producción si no te quieres ver con un problema de performance que tengas que resolver inmediatamente en producción.

    Saludos

  5. #5 by Paula on 3 June, 2009 - 22:10

    Que buena onda, mil gracias!

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: