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
