Comando Analyze ORACLE

Prof. Ing. Erick López Ch. M.R.I.

ANALYZE Consiste en recopilar y actualizar el CATALOGO de

oracle con datos estadísticos. Oracle elige el plan de ejecución más adecuado a

cada sentencia (EXPLAIN PLAN). Estos datos no pueden actualizarse en tiempo real

porque penlizaría mucho el rendimiento general de la base de datos.

Se actualizan datos como el número de registros de una tabla, el tamaño de los objetos, etc.

Actualmente hay dos formas de actualiza las estadísticas de las tablas e indices, con el comando ANALYZE y con el paquete DBMS_UTILITY.

Comando ANALYZE Consiste en recopilar y actualizar

el CATALOGO de oracle con datos estadísticos La cláusula COMPUTE hace un cálculo exacto

de la estadísticas (tarda más en realizarse en ANALYZE)

La cláusula ESTIMATE hace una estimación partiendo del anterior valor calculado y de un posible factor de variación.

La cláusula DELETE borra las anteriores estadísticas.

Ejemplos ANALYZE Para analizar tabla con sus indices:

ANALYZE TABLE T_PEDIDOS COMPUTE STATISTICS; Para analizar solo la tabla:

ANALYZE TABLE T_PEDIDOS COMPUTE STATISTICS FOR TABLE; Para analizar solo sus indices:

ANALYZE TABLE T_PEDIDOS COMPUTE STATISTICS FOR ALL INDEXES;

Para analizar tabla con sus indices por estimación mirando el 20% de las filas: ANALYZE TABLE T_PEDIDOS ESTIMATE STATISTICS SAMPLE 20

PERCENT; Para borrar las estadísticas:

ANALYZE TABLE T_PEDIDOS DELETE STATISTICS;

Sintaxis completa:

Consideraciones: Al validar la estructura se realiza un control de

la integridad y puede bloquear la tabla/indice/cluster mientras se está ejecutando.

El recálculo de las estadísticas necesita gran cantidad de espacio de forma temporal. Podriamos tener que incrementar el valor para SORT_AREA_SIZE.

Si usamos la clausula INTO para almacenar una lista de filas enlazadas, la tabla por defecto es CHAINED_ROWS.

Paquete DBMS_UTILITY El comando ANALYZE esta disponible en todas

las versiones de oracle, pero es más comodo, más rapido y se obtienen mejores resultados si se usa el paquete DBMS_UTILITY, disponible a partir de la version 8.0.

DBMS_UTILITY.ANALYZE_SCHEMA('PROGRAMADOR','ESTIMATE', NULL, 10)

DBMS_UTILITY.ANALYZE_DATABASE('ESTIMATE',NULL,20,NULL)

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

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.

Paquete DBMS_UTILITY 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.

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 ejecutó un query sobre la tabla empleados y se empiezan a calcular estadisticas sobre esa tabla Oracle invalida el query SQL que esta compartido en la memoria en lugar de re-utilizarlo.

Oracle utiliza las nuevas estadisticas cuando el query SQL es ejecutado de nuevo y por lo cual puede utilizar un plan de ejecucion diferente.

Cálculo de estadísticas 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

GATHER_INDEX_STATS

GATHER_INDEX_STATS (parámetros)

http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_stats.htm#sthref8108

Ejemplo   EXEC DBMS_STATS.DELETE_INDEX_STATS('SCOTT','PK_EMP');

EXEC DBMS_STATS.DELETE_INDEX_STATS('SCOTT','EMP_PRIMARY_KEY');

select OWNER,INDEX_NAME,NUM_ROWS, SAMPLE_SIZE,LAST_ANALYZED, BLEVEL,LEAF_BLOCKS,DISTINCT_KEYS from dba_indexes where owner = 'SCOTT' and index_name= 'EMP_PRIMARY_KEY';

AquÍ podemos ver que el Índice no ha sido analizado y por lo tanto no tiene estadisticas aunque la tabla puede llegar a tenerlas

select OWNER,table_NAME,LAST_ANALYZED from dba_tables where owner = 'SCOTT' and table_name = 'EMP';

Ejemplo Se procede a calcular las estadísticas

EXEC DBMS_STATS.GATHER_INDEX_STATS('SCOTT', 'EMP_PRIMARY_KEY');

Cuando una tabla es muy pequeña Oracle usa el 100% del compute, es decir NULL en el parámetro compute. Cuando una tabla contiene miles o millones de registros es mejor utilizar:

DBMS_STATS.AUTO_SAMPLE_SIZE

Ejemplo

Al igual que el comando ANALYZE si le damos un valor más alto a estimate_percent entones obtendremos estadísticas más exactas, pero de igual manera va a tardar más tiempo en calcularlas. El último ejemplo se calcularon usando AUTO_SAMPLE_SIZE y Oracle decidió que lo ideal era calcular para todos los registros.

Ejemplo