DBMS_STATS. Parte 1

DBMS_STATS

Otra manera de calcular estadisticas además del comando ANALYZE es el paquete DBMS_STATS. Este paquete se utiliza para modificar, ver, exportar, importar y borrar estadisticas de la base de datos.

Como ya habia mencionado Oracle recomienda la utilización de este paquete a partir de la versión 9i para el calculo de estadisticas en lugar de usar ANALYZE debido a que es mas exacto y mas eficiente. Aunque ANALYZE ya no sea la opción adecuada para calcular estadisticas eso no quiere decir que ya no sirva para nada, todavia se puede utilizar para validar la estructura de una tabla o buscar por CHAINED ROWS y algunas otras cosas.

Cuando se generan nuevas estadisticas para una tabla, columna o indice las estadisticas existentes son actualizadas por Oracle, cuando se actualizan las estadisticas Oracle invalida cualquier SQL que se encuentra en memoria (parsed) que accesa el objeto al cual se les estan calculando estadisticas. Esto quiere decir que si alguien ejecuto un query sobre la tabla empleados y se empiezan a calcular estadisticas sobre esa table Oracle invalida el query SQL que esta compartido en la memoria en lugar de re-utilizarlo (espero no haber confundido aqui esto tiene que ver con la manera en que Oracle reutiliza SQL previamente ejecutados, cualquier duda pregunten). Oracle utiliza las nuevas estadisticas cuando el query SQL es ejecutado de nuevo y por lo cual puede utilizar un plan de ejecucion diferente.

Procedimientos para el calculo de estadisticas dentro del paquete DBMS_STATS

Procedimiento	Que calcula?
GATHER_INDEX_STATS	Indices
GATHER_TABLE_STATS	Tablas, columnas e indices
GATHER_SCHEMA_STATS	Para todos los objetos del schema
GATHER_DICTIONARY_STATS	Para todos los objetos del diccionario de datos
GATHER_DATABASE_STATS	Para todos los objetos en la base de datos

Bueno vamos a empezar con el primero antes de que se haga mas aburrido y luego vamos explicando el por que de algunas cosas.

GATHER_INDEX_STATS

Bueno ya habiamos mencionado que este procedimiento calcula estadisticas a los indices.

Sintaxis

DBMS_STATS.GATHER_INDEX_STATS (
ownname          VARCHAR2,
indname          VARCHAR2,
partname         VARCHAR2 DEFAULT NULL,
estimate_percent NUMBER   DEFAULT to_estimate_percent_type
(GET_PARAM('ESTIMATE_PERCENT')),
stattab          VARCHAR2 DEFAULT NULL,
statid           VARCHAR2 DEFAULT NULL,
statown          VARCHAR2 DEFAULT NULL,
degree           NUMBER   DEFAULT to_degree_type(get_param('DEGREE')),
granularity      VARCHAR2 DEFAULT GET_PARAM('GRANULARITY'),
no_invalidate    BOOLEAN  DEFAULT to_no_invalidate_type
(GET_PARAM('NO_INVALIDATE')),
force            BOOLEAN DEFAULT FALSE);

Parametro Descripcion

ownname Schema/usuario/dueño del indice al cual se le van a calcular

indname Nombre del indice

partname Nombre de la particion

estimate_percent Porcentaje de registros a estimar (NULL significa compute[todos]). Rango valido [0.000001,100]. Se recomienda usar la constante DBMS_STATS.AUTO_SAMPLE_SIZE para dejar que Oracle obtenga el valor adecuado para calcular las estadisticas.

stattab Tabla de estadisticas donde se guardan las actuales (mas adelante mencionamos eso)

statid Identificador para asociar las estadisticas actuales

statown Schema que contiene la tabla de estadisticas (si es diferente al usuario)

degree Grado de paralelismo, el default es NULL, lo cual significa que usa el que tenga la tabla cuando se creo.

granularity Detalle del calculo, solo se utiliza cuando la tabla esta particionada.

ALL,AUTO,DEFAULT,GLOBAL,GLOBAL AND PARTITION, PARTITION, SUBPARTITION.

no_invalidate No invalida los cursores (SQL query) que dependen del objeto si se pone TRUE. Por default los invalida inmediatamente.

force Calcula estadisticas inclusive si el objeto esta bloqueado.

Verficar el manual para obtener mas detalles de los parametros.(http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#sthref8108)\

Bueno, primero vamos a usar un procedimiento que ayuda a borrar las estadisticas actuales.

SQL> EXEC DBMS_STATS.DELETE_INDEX_STATS('SCOTT','PK_EMP');

PL/SQL procedure successfully completed.

Ahora vamos a ver que es lo que esta almacenado como estadisticas:

SQL> select OWNER,INDEX_NAME,NUM_ROWS,
2         SAMPLE_SIZE,LAST_ANALYZED,
3         BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS
4  from dba_indexes
5  where owner = 'SCOTT'
6  and index_name ='PK_EMP';
OWNER      INDEX_NAME   NUM_ROWS SAMPLE_SIZE LAST_ANAL     BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ---------- ---------- ----------- --------- ---------- ----------- -------------
SCOTT      PK_EMP

Aqui podemos ver que el indice no ha sido analizado y por lo tanto no tiene estadisticas aunque la tabla puede tenerlas como se muestra en el siguiente ejemplo:

SQL> select OWNER,table_NAME,LAST_ANALYZED
2  from dba_tables
3  where owner = 'SCOTT'
4  and table_name = 'EMP';
OWNER      TABLE_NAME                     LAST_ANAL
---------- ------------------------------ ---------
SCOTT      EMP                            22-AUG-07

Asi que podemos tener tablas con estadisticas e indices sin estadisticas y viceversa. Aunque no es remendable tener este tipo de estadisticas es posible.

Ahora vamos a calcular las estadisticas del indice:

SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS('SCOTT','PK_EMP');
PL/SQL procedure successfully completed.
SQL> select OWNER,INDEX_NAME,NUM_ROWS,
2         SAMPLE_SIZE,LAST_ANALYZED,
3         BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS
4  from dba_indexes
5  where owner = 'SCOTT'
6  and index_name ='PK_EMP';
OWNER      INDEX_NAME   NUM_ROWS SAMPLE_SIZE LAST_ANAL     BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ---------- ---------- ----------- --------- ---------- ----------- -------------
SCOTT      PK_EMP             14          14 02-OCT-07          0           1            14

Y asi de sencillo se pueden calcular estadisticas para un indice. En este caso utilizamos unicamente las opciones por default.

Vamos a analizar que se hizo, le dijimos que indice queremos calcular, pero podemos ver que el sample_size utilizado fue el mismo numero de registros de la tabla esto quiere decir que Oracle uso NULL que significa COMPUTE que significa 100% o todos los registros. Para este caso en que la tabla es muy pequeña es muy rapido pero hay casos donde las tablas son de millones de registros estoy puede tardar varios minutos asi que se recomienda dejar que oracle lo determine utilizando DBMS_STATS.AUTO_SAMPLE_SIZE.

Vamos a cambiar este parametro para compara resultados con una tabla con algunos registros demas:

SQL> INSERT INTO SCOTT.EMP2(EMPNO,ENAME)
2  SELECT LEVEL,'TEST'
3  FROM DUAL
4  CONNECT BY LEVEL <=1000000;
1000000 rows created.
SQL> EXEC DBMS_STATS.DELETE_INDEX_STATS('SCOTT','PK_EMP2');
PL/SQL procedure successfully completed.
SQL> select OWNER,INDEX_NAME,NUM_ROWS,
2         SAMPLE_SIZE,LAST_ANALYZED,
3         BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS
4  from dba_indexes
5  where owner = 'SCOTT'and index_name ='PK_EMP2';
OWNER      INDEX_NAME   NUM_ROWS SAMPLE_SIZE LAST_ANAL     BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ---------- ---------- ----------- --------- ---------- ----------- -------------
SCOTT      PK_EMP2
SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS('SCOTT','PK_EMP2');
PL/SQL procedure successfully completed.
SQL> select OWNER,INDEX_NAME,NUM_ROWS,
2         SAMPLE_SIZE,LAST_ANALYZED,
3         BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS
4  from dba_indexes
5  where owner = 'SCOTT'and index_name ='PK_EMP2';
OWNER      INDEX_NAME   NUM_ROWS SAMPLE_SIZE LAST_ANAL     BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ---------- ---------- ----------- --------- ---------- ----------- -------------
SCOTT      PK_EMP2       1000000     1000000 02-OCT-07          2        1875       1000000
SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS('SCOTT','PK_EMP2',null,10);
PL/SQL procedure successfully completed.
SQL> select OWNER,INDEX_NAME,NUM_ROWS,
2         SAMPLE_SIZE,LAST_ANALYZED,
3         BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS
4  from dba_indexes
5  where owner = 'SCOTT'and index_name ='PK_EMP2';
OWNER      INDEX_NAME   NUM_ROWS SAMPLE_SIZE LAST_ANAL     BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ---------- ---------- ----------- --------- ---------- ----------- -------------
SCOTT      PK_EMP2        971743      593140 02-OCT-07          2        1822        971743
SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS('SCOTT','PK_EMP2',null,50);
PL/SQL procedure successfully completed.
SQL> select OWNER,INDEX_NAME,NUM_ROWS,
2         SAMPLE_SIZE,LAST_ANALYZED,
3         BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS
4  from dba_indexes
5  where owner = 'SCOTT'and index_name ='PK_EMP2';
OWNER      INDEX_NAME   NUM_ROWS SAMPLE_SIZE LAST_ANALYZED            BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ---------- ---------- ----------- -------------------- ---------- ----------- -----------
SCOTT      PK_EMP2       1008372      615498 02/OCT/2007 11:53:18          2        1891       1008372
SQL> EXEC DBMS_STATS.GATHER_INDEX_STATS('SCOTT','PK_EMP2',null,DBMS_STATS.AUTO_SAMPLE_SIZE);
PL/SQL procedure successfully completed.
SQL> select OWNER,INDEX_NAME,NUM_ROWS,
2         SAMPLE_SIZE,LAST_ANALYZED,
3         BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS
4  from dba_indexes
5  where owner = 'SCOTT'and index_name ='PK_EMP2';
OWNER      INDEX_NAME   NUM_ROWS SAMPLE_SIZE LAST_ANALYZED            BLEVEL LEAF_BLOCKS DISTINCT_KEYS
---------- ---------- ---------- ----------- -------------------- ---------- ----------- -----------
SCOTT      PK_EMP2       1000000     1000000 02/OCT/2007 11:55:34          2        1875       1000000

Aqui podemos ver que al igual que el comando ANALYZE si le damos un valor mas alto a estimate_percent entones obtendremos estadisticas mas exactas, pero de igual manera va a tardar mas tiempo en calcularlas. Puede observarse que en el ultimo ejemplo se calcularon usando AUTO_SAMPLE_SIZE y Oracle decidio que lo ideal era calcular para todos los registros.

Y asi se calculan estadisticas para indices, en lo personal yo casi nunca he utilizado este metodo debido a que siempre las calculo automaticamente con el procedimiento GATHER_TABLES_STATS o GATHER_SCHEMA_STATS, esto lo voy a mostrar en los proximos dias.

Los procedimientos tienen mas parametros sobre los cuales no mostre como funcionaban, la verdad no lo hice por que los parametros explican claramente que hacen cada uno y debido a que se utilizan para casos mas “avanzados” como particiones, paralelismo, etc. Y la verdad me dio flojera hacer una caso de prueba para ese tipo, creo que los mas importantes y que se aplican mas seguido son los mostrados, a menos que yo personalmente considere mencionar otro lo voy a hacer y si alguien desea saber como funciona entonces haganmelo saber.

Cuando calcular estadisticas?

De nuevo, no existe una regla sobre cada cuando se deben de calcular; pero algunos consejos son por ejemplo si se insertan/borran/actualizan un gran numero de registros a una tabla, tal vez millones, entonces inmediatamente despues hay que calcular debido a que si puede afectar los planes de ejecucion ya que se hizo un gran cambio. Si se desea hacer de manera automatica entonces depende de la carga de la base de datos, de la aplicacion, puede haber casos que semanalmente esta bien o hay veces que 1 vez al mes.

Bueno hasta la siguiente…con GATHER_TABLE_STATS.