I created this function to be caleld from a jobs, If you have some
improvement tellme please.
CREATE OR REPLACE PROCEDURE SYS.DB_CALCULAESTADISTICAS( cTipo VARCHAR2
DEFAULT 'P' )
IS
BEGIN
-- enables monitoring in tables don't having monitoring enabled
- gather statistics for stale object
- gather statistics for empty object
- gather auto statistics any object Oracle think is necessary to
gather statistics
BEGIN
FOR A IN (SELECT OWNER,TABLE_NAME FROM DBA_TABLES
WHERE NOT OWNER IN ('SYS','SYSTEM','OUTLN','DBSNMP','WMSYS',
'PERFSTAT','ORDSYS','ORDPLUGINS','MDSYS',
'CTXSYS','XDB')
AND TEMPORARY='N' AND MONITORING='NO') LOOP
BEGIN
EXECUTE IMMEDIATE 'alter table
'||A.OWNER||'.'||A.TABLE_NAME||' monitoring ';
EXCEPTION WHEN OTHERS THEN
NULL;
END;
END LOOP;
END;
BEGIN
dbms_system.ksdwrt(3,'Gather Stale Inicio...');
DBMS_STATS.GATHER_database_STATS( ESTIMATE_PERCENT=>100,
METHOD_OPT=>'FOR ALL COLUMNS SIZE
SKEWONLY',
options => 'GATHER STALE');
dbms_system.ksdwrt(3,'Gather Stale Exitoso');
EXCEPTION WHEN OTHERS THEN
dbms_system.ksdwrt(3,'!!!!!!!!!!!!!Error en calculo estadísticas'||SQLERRM);
END;
BEGIN
dbms_system.ksdwrt(3,'Gather Empty Inicio...');
DBMS_STATS.GATHER_database_STATS( ESTIMATE_PERCENT=>100,
METHOD_OPT=>'FOR ALL COLUMNS SIZE
SKEWONLY',
options => 'GATHER EMPTY');
dbms_system.ksdwrt(3,'Gather Empty Exitoso');
EXCEPTION WHEN OTHERS THEN
dbms_system.ksdwrt(3,'!!!!!!!!!!!!!Error en calculo estadísticas'||SQLERRM);
END;
BEGIN
dbms_system.ksdwrt(3,'Gather Auto Inicio...');
DBMS_STATS.GATHER_database_STATS( options => 'GATHER AUTO');
dbms_system.ksdwrt(3 ,'Gather Auto Exitoso');
EXCEPTION WHEN OTHERS THEN
dbms_system.ksdwrt(3,'!!!!!!!!!!!!!Error en calculo estadísticas'||SQLERRM);
END;
END; -- Procedure
/
5. Programación
Consultar select * from dba_jobs; para verificar que no exista ninguna
tarea programada.
DECLARE
nJobNumber NUMBER;
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_DATE_LANGUAGE=''AMERICAN''';
DBMS_JOB.SUBMIT
(job => nJobNumber
,what =>'BEGIN DB_CALCULAESTADISTICAS; END;' -- Código/función a ejecutar
,next_date => TRUNC(SYSDATE)+25/24 -- Fecha hora de inicio del job
,interval => 'SYSDATE+1' -- Intervalo de ejecución para el job
);
COMMIT;
END;
/
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Feb 13 2006 - 11:21:39 CST