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.

  1. #1 by Sir Ragnar on 3 October, 2007 - 15:23

    Gracias por postear esta guía. Es de gran ayuda y queda todo bastante claro.

  2. #2 by delfinonunez on 3 October, 2007 - 17:58

    Muchas gracias, que bueno que quedo claro por que estaba leyendo de nuevoy tengo faltas de ortografia y el formato del codigo, tablas no esta muy bien que digamos a ver si en la semana me doy un tiempo para darle una “chuleada” al post.

    Gracias de nuevo.

  3. #3 by Felipe on 4 October, 2007 - 10:50

    Gracías por la info. Espero que sigas trabajando en el blog y nos sigas documentando en la mayor brevedad posible.

  4. #4 by Delfino Nunez on 4 October, 2007 - 13:49

    Gracias Felipe, voy a intentar(no lo prometo :) escribir algo de jueves a domingo que son los días que tengo más tiempo libre.

  5. #5 by Alberto on 7 May, 2008 - 19:51

    Gracias Delfino por tu tiempo y dedicación a estos temas.

    Una consulta, cuando se recrean las estadísticas existe la posibilidad que se note lentitud en la base de datos ?

    O bien si se nota lentitud en la base de datos es recomendable las estadisticas o creacion de indices ?

    Gracias de antemano.

  6. #6 by delfinonunez on 8 May, 2008 - 21:39

    Hola Alberto, gracias por los comentarios, voy a tratar de responder los mas simple posible…

    >Una consulta, cuando se recrean las estadísticas existe la posibilidad que se note lentitud en la base de datos ?

    Si te refieres al momento en el que estas calculando las estadisticas el proceso puede alentar la base de datos, la respuestas es: puede ser.
    Depende en que momento ejecutas las estadisticas, si lo haces al mismo tiempo que hay usuarios conectados en la BD es seguro que les va a afectar, tambien depende como estas calculando las estadisticas, si es a una sola tabla o a toda la BD. Pero definitivamente el proceso afecta el performance de la BD en el momento de su ejecucion ya que debe recabar informacion sobre las tablas,indices,etc y la unica manera es leyendo esos objetos (I/O).
    Pero si la BD esta lenta despues de calcular estadisticas esto quiere decir que algo puede andar mal en los queries, o que faltan indices,etc; tendrias que ver ya un caso especifico. Acuerdate que una vez que calculas estadisticas el CBO (cost based optimizer) puede ahora tomar una decision diferente respecto al explain plan que va a utilizar, ahora “tiene” mas informacion para tomar la “mejor” decision para ejecutar el query.

    >O bien si se nota lentitud en la base de datos es recomendable las estadisticas o creacion de indices ?

    Creo que son dos cosas distintas pero relacionadas.
    Primero, el calculo de estadisticas es algo que Oracle recomienda fuertemente hacer en una base de datos, como mencione, es la manera que tiene el CBO para tomar la “mejor” decision sobre que tipo de plan de ejecucion tomar, es lo que puede “decirle” si debe usar un indice o no,etc.

    Ahora, la lentitud en la base puede ser causada por diferentes cosas, como tu mencionas una puede ser la falta de indices pero eso no lo vas a saber hasta que obtengas un explain plan del query que esta “lento” y ver como esta accesando la informacion y asi ver si falta o no un indice,etc.

    Lo que te recomiendo primero es asegurarte que las estadisticas de tu schema,base, tablas,etc esten actualizadas y si no es asi tal vez es tiempo de calcular estadisticas.
    Una vez que tengas las estadisticas actualizadas entonces ejecuta tu query y ve si el tiempo de respuesta es aceptable o no, si esta “lento” entonces tienes que obtener un explain plan del query y analizarlo (esta es la parte compleja/dificil) y determinar si se necesita un indice o no.

    Si TODA la BD esta lenta entonces tal vez no sean ni las estadisticas ni los indices el problema, tal vez sea otra cosa. Tal vez deberias preguntar a tu DBA que te ayude a investigar el problema.

  7. #7 by Percy on 29 June, 2008 - 07:07

    Muy bueno tu articulo, esta excelente la informacion,
    sigue escribiendo , muchas gracias

  8. #8 by Marcelo on 30 September, 2009 - 19:06

    Muchas gracias por este blog. Me despejó algunas dudas acerca de las estadísticas. Como referencia les hago llegar un link que encontré en la Web donde aparecen los mismos ejemplos que aquí. Claro está en este blog se escribieron primero.

    Gracias.
    Marcelo.

  1. call center recording software

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: