Estadísticas en Oracle10g

Como todos sabemos el optimizador por defecto en las bases de datos Oracle10g es el CBO (Cost Based Optimizer), el cual es un optimizador de avanzada que utiliza estadísticas de las tablas e indices para poder obtener buenos planes de ejecución. Pero a veces caemos en la cuenta de que esto no es siempre así.

Uno de los principales problemas que podemos encontrarnos es que las estadísticas sean incorrectas, lo cual le estaría dando información erronea al optimizador.

Ejemplo

Creamos un tabla para pruebas:

 
SQL> CREATE TABLE TEMP_TABLE
2  (    data     VARCHAR2(30),
3    created  DATE DEFAULT SYSDATE
4 );
TABLE created.

Y también creamos un índice:

 
SQL> CREATE INDEX SIEBEL.TEMP_TABLE_I1 ON SIEBEL.TEMP_TABLE (DATA);
INDEX created.

Verificamos si tiene estadísticas:

 
SQL> SELECT   table_name, last_analyzed, user_stats,
2           global_stats, MONITORING
3      FROM user_tables
4     WHERE table_name IN ('TEMP_TABLE')
5  ORDER BY 1, 2;
 
TABLE_NAME                     LAST_ANAL USE GLO MON
------------------------------ --------- --- --- ---
TEMP_TABLE                           NO  NO  YES

Como podemos ver no tiene todavia estadísticas. Vamos a insertar datos:

 
SQL> INSERT INTO TEMP_TABLE (data) SELECT SUBSTR(table_name,1,30) FROM user_tables;
4488 rows created.

Nota: Repetir la operación de insert varias veces así podemos contar con un mayor numero de datos.

Ejecutamos un select donde podemos ver que el optimizer esta utilizando el indice creado, pero noten el valor que tiene "cost":

 
SQL> SELECT * FROM temp_table WHERE data = 'TEMP_TABLE';
Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=26)
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEMP_TABLE' (TABLE) (Cost=1 Card=1 Bytes=26)
2    1     INDEX (RANGE SCAN) OF 'TEMP_TABLE_I1' (INDEX) (Cost=1 Card=1)

Ahora actualizamos las estadísticas:

 
SQL> BEGIN
2    SYS.DBMS_STATS.GATHER_TABLE_STATS (
3        OwnName        => 'TEST'
4       ,TabName        => 'TEMP_TABLE'
5      ,Estimate_Percent  => 33
6      ,Method_Opt        => 'FOR ALL INDEXED COLUMNS SIZE 1 '
7      ,Degree            => 4
8      ,Cascade           => TRUE
9      ,No_Invalidate     => FALSE);
10  END;
11  /
PL/SQL PROCEDURE successfully completed.

Volvemos a comprobar el plan de ejecución de la consulta anterior:

 
SQL> SELECT * FROM temp_table WHERE data = 'TEMP_TABLE';
Execution Plan
----------------------------------------------------------
0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=5 Card=108000 Bytes=1080000)
1    0   TABLE ACCESS (BY INDEX ROWID) OF 'TEMP_TABLE' (TABLE) (Cost=5 Card=108000 Bytes=1080000)
2    1     INDEX (RANGE SCAN) OF 'TEMP_TABLE_I1' (INDEX) (Cost=3 Card=107712)

Ahi podemos ver claramente que el "cost" considerado anteriormente era erroneo. Si bien en este caso el CBO optimizer no elegió otro plan de ejecución en la "vida real", esto puede provocar que se prefiera un índice sobre otro, o se haga un full access a una tabla que según estadísticas esta vacia.

Soluciones:

Bueno acá creo que hay dos caminos principales para tener estadísticas actualizadas:

1. Ejecutar un job que actualize todas las estadísticas de las tablas o schemas deseados.
2. Setear las tablas e indices deseados con la opción "monitoring".

La primera es sencilla y no voy a comentarla acá, si alguien desea me puede enviar un comentario y le digo como hacerlo.

La segunda es muy interesante porque obliga a Oracle a monitorear la tabla o índice en busca de cambios, y si los hay actualiza de manera automática las estadísticas.

Supongamos que seguimos insertando datos en nuestra tabla temporal:

 
SQL> INSERT INTO temp_table (data) SELECT data FROM temp_table;
107712 rows created.

Veamos entonces las estadísticas:

 
SQL> SELECT   table_name, num_rows, last_analyzed, user_stats, global_stats, MONITORING
2      FROM user_tables
3     WHERE table_name IN ('TEMP_TABLE')
4  ORDER BY 1, 2;
 
TABLE_NAME                       NUM_ROWS LAST_ANAL USE GLO MON
------------------------------ ---------- --------- --- --- ---
TEMP_TABLE                         108000 23-JUL-07 NO  YES YES

Veamos que nos dice un count de la tabla:

 
SQL> SELECT COUNT(*) FROM temp_table;
COUNT(*)
----------
215424

Como podemos ver las estadísticas son erroneas, nos dicen que tenemos 108000 rows, cuando en realidad tenemos casi el doble.

Para evitar esto pongamos a la tabla en monitoring:

 
SQL> ALTER TABLE temp_table monitoring;
TABLE altered.

Si ahora ejecutamos alguna opcion de insert o update, deberíamos ver que las estadísticas se actualizan automáticamente:

 
SQL> SELECT table_name, num_rows, last_analyzed, user_stats,
2           global_stats, MONITORING
3      FROM user_tables
4     WHERE table_name IN ('TEMP_TABLE')
5  ORDER BY 1, 2;
TABLE_NAME          NUM_ROWS LAST_ANAL  USE GLO MON
---------- -------- ---------  --- --- ---
TEMP_TABLE                          215424    23-JUL-07      NO  YES YES

Espero que les haya sido de utilidad!