Actualizar las estadísticas

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.

Las estadisticas pueden actualizarse de dos maneras
1. Usando el comando sql ANALYZE
2. usando el paquete DBMS_UTILITY

Usando el comando sql ANALYZE:

Para actualizar las estadísticas de una tabla y todos sus índices, se debe ejecutar la siguiente sentencia:

ANALYZE TABLE nombre_de_la_tabla COMPUTE STATISTICS;

Para actualizar las estadísticas unicamente de la tabla y no de los índices, ejecutar:

ANALYZE TABLE nombre_de_la_tabla COMPUTE STATISTICS FOR TABLE;

Para actualizar las estadísticas de los índices:

ANALYZE TABLE nombre_de_la_tabla COMPUTE STATISTICS FOR ALL INDEXES;

El recálculo de las estadísticas puede requerir gran cantidad de espacio temporal (hasta 4 veces el tamaño de la tabla). Podría ser necesario incrementar el valor para SORT_AREA_SIZE. Existe una segunda opción ESTIMATE que evita la utilización de tal cantidad de espacio temporal y que básicamente consiste en actualizar las estadísticas haciendo un muestreo de filas.

El siguiente comando aproxima (estima) estadísticas para una tabla y todos sus índices:

ANALYZE TABLE nombre_de_la_tabla ESTIMATE STATISTICS;

Por defecto, se usan las primeras 1604 filas. Sin embargo, se puede definir el porcentaje con el cual se hace la estimación.

ANALYZE TABLE nombre_de_la_tabla ESTIMATE STATISTICS SAMPLE 15 PERCENT;

ANALYZE TABLE nombre_de_la_tabla ESTIMATE STATISTICS SAMPLE 2500 ROWS;

Si se especifica más de la mitad de los datos, oracle lee todas las filas y hace COMPUTE.

Para borrar las estadíticas de una tabla, el siguiente comando:

ANALYZE TABLE nombre_de_la_tabla DELETE STATISTICS;

Usando el paquete DBMS_UTILITY

Con este paquete se puede analizar un esquema completo o la bd entera, en una sola instrucción.

DBMS_UTILITY.Analyze_Database

SQL> desc dbms_utility.analyze_database
PROCEDURE dbms_utility.analyze_database
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
METHOD VARCHAR2 IN
ESTIMATE_ROWS NUMBER IN DEFAULT
ESTIMATE_PERCENT NUMBER IN DEFAULT
METHOD_OPT VARCHAR2 IN DEFAULT

DBMS_UTILITY.Analyze_Schema
SQL> desc dbms_utility.analyze_schema
PROCEDURE dbms_utility.analyze_schema
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SCHEMA VARCHAR2 IN
METHOD VARCHAR2 IN
ESTIMATE_ROWS NUMBER IN DEFAULT
ESTIMATE_PERCENT NUMBER IN DEFAULT
METHOD_OPT VARCHAR2 IN DEFAULT

donde,

METHOD = COMPUTE / ESTIMATE / DELETE;

METHOD_OPT = FOR TABLE / FOR ALL INDEXES;

No todos los parámetros deben ser especificados; únicamente los necesarios.

ejemplo

SQL> exec dbms_utility.analyze_schema( 'esquemaX', 'COMPUTE', NULL, NULL, NULL)

Este paquete se utiliza para modificar, ver, exportar, importar y borrar estadisticas de la base de datos.

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.