Calculo de estadisticas de forma automatica en Oracle 9i

Uno de los grandes problemas con la tecnología en general es no conocer a cabalidad todo el conjunto de herramientas o funcionalidades disponibles.  En particular muchas de las funcionalidades que tienen los motores 10g y 11g ya existían en 9i, ejemplo de ello el cálculo de estadísticas de forma automática. Ejemplo:

A nivel de tabla

SQL> alter table SCOTT.EMP2 monitoring;

Table altered.

SQL> insert into scott.emp2 select * from scott.emp;

14 rows created.

SQL> commit;

Commit complete.

Cuando el motor determina que los datos de la tabla han cambiado en un 10% considera las estadisticas como STALE

SQL>  select table_name, num_rows, blocks, avg_row_len    from dba_tables where table_name='EMP2';

TABLE_NAME                       NUM_ROWS     BLOCKS AVG_ROW_LEN

------------------------------ ---------- ---------- -----------

EMP2                                   14          1          37

SQL> select * from sys.dba_tab_modifications;

no rows selected

SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO();

PL/SQL procedure successfully completed.

SQL> select * from sys.dba_tab_modifications;

TABLE_OWNER                    TABLE_NAME

------------------------------ ------------------------------

PARTITION_NAME                 SUBPARTITION_NAME                 INSERTS

------------------------------ ------------------------------ ----------

   UPDATES    DELETES TIMESTAMP TRU DROP_SEGMENTS

---------- ---------- --------- --- -------------

SCOTT                          EMP2

                                                                      42

         0          0 01-OCT-11 NO              0

 Esta informacion la usara el motor para realziar el calculo de las estadisticas en caso de que sea necesario.

A nivel de esquema

SQL> EXEC DBMS_STATS.delete_table_stats('SCOTT', 'EMP2');

PL/SQL procedure successfully completed.

SQL>  select table_name, num_rows, blocks, avg_row_len    from dba_tables where table_name='EMP2';

TABLE_NAME                       NUM_ROWS     BLOCKS AVG_ROW_LEN

------------------------------ ---------- ---------- -----------

EMP2

SQL>  exec dbms_stats.ALTER_SCHEMA_TAB_MONITORING('SCOTT',TRUE);

PL/SQL procedure successfully completed.

SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO();

PL/SQL procedure successfully completed.

SQL> select * from sys.dba_tab_modifications;

no rows selected

SQL> insert into scott.emp2 select * from scott.emp2;

84 rows created.

SQL> commit;

Commit complete.

SQL> select * from sys.dba_tab_modifications;

no rows selected

SQL> exec dbms_stats.FLUSH_DATABASE_MONITORING_INFO();

PL/SQL procedure successfully completed.

SQL> select * from sys.dba_tab_modifications;

TABLE_OWNER                    TABLE_NAME

------------------------------ ------------------------------

PARTITION_NAME                 SUBPARTITION_NAME                 INSERTS

------------------------------ ------------------------------ ----------

   UPDATES    DELETES TIMESTAMP TRU DROP_SEGMENTS

---------- ---------- --------- --- -------------

SCOTT                          EMP2

                                                                      84

         0          0 01-OCT-11 NO              0

La idea de todo esto es que el motor calcule selectivamente las estadísticas. Veamos que como interpreta la información anterior y nos permite recolectar automática y selectivamente las estadísticas de las tablas que hayan cambiado más del 10%

Lo primero será crear un Job que use el DBMS_STATS (para nuestro ejemplo) con la funcion gather_schema_stats y la opcion GATHER STALE.

DECLARE jobno number;
BEGIN
      DBMS_JOB.submit(jobno,'BEGIN DBMS_STATS.gather_schema_stats(ownname => ''SCOTT'',degree =>2,estimate_percent => 15,cascade => TRUE , OPTIONS => ''GATHER STALE''); END;',
     TO_DATE('01-10-11 06:35:00', 'DD-MM-YY HH:MI:SS'),
    'SYSDATE+5/(24*60)',
    TRUE);
COMMIT;
END;
/

Después de muchos insert y haber calculado las estadísticas nuestra tabla de ejemplo quedo de la siguiente manera:

SQL> select table_name, num_rows, blocks, avg_row_len,to_char(last_analyzed,'DD-MM-YY HH:MI:SS')    from dba_tables where owner='SCOTT';

TABLE_NAME                       NUM_ROWS     BLOCKS AVG_ROW_LEN TO_CHAR(LAST_ANAL
------------------------------ ---------- ---------- ----------- -----------------
BONUS                                   0          0           0 01-10-11 06:29:17
DEPT                                    4          1          20 01-10-11 06:29:17
EMP                                    14          1          37 01-10-11 06:29:17
EMP1                                   14          1          37 01-10-11 06:29:17
EMP2                                14336         87          37 01-10-11 06:49:35
EMP3                                   14          1          37 01-10-11 06:29:17
SALGRADE                                5          1          10 01-10-11 06:29:17

7 rows selected.

Cuando el job se dispare obtendrá la información de los cambios de la tabla  sys.dba_tab_modifications y calculara las estadísticas de manera automática.

SQL> select table_name, num_rows, blocks, avg_row_len,to_char(last_analyzed,'DD-MM-YY HH:MI:SS')    from dba_tables where owner='SCOTT' --table_name='EMP2';

TABLE_NAME                       NUM_ROWS     BLOCKS AVG_ROW_LEN TO_CHAR(LAST_ANAL
------------------------------ ---------- ---------- ----------- -----------------
BONUS                                   0          0           0 01-10-11 06:29:17
DEPT                                    4          1          20 01-10-11 06:29:17
EMP                                    14          1          37 01-10-11 06:29:17
EMP1                                   14          1          37 01-10-11 06:29:17
EMP2                                56906        340          37 01-10-11 06:54:36
EMP3                                   14          1          37 01-10-11 06:29:17
SALGRADE                                5          1          10 01-10-11 06:29:17