EFECTO DE ESTADISTICAS NO ACTUALIZADAS UTILIZANDO ORACLE CBO

A partir de la versión 9i  de Oracle se introduce el optimizador basado en costos o 'Cost Based Optimizer' o CBO que sustituye al optimizador basado en reglas o 'Rule Based Optimizer' o RBO que estuvo vigente durante casi una decada.  El CBO  busca el plan de ejecución que requiera el menor número de recursos (cpu, memoria y disco) de la instancia.  Para lograrlo, requiere de estadísticas confiables.  Cuando estas se actualizan, ya sea de una tabla o de un índice, el manejador conoce entre otros, el número de registros de la tabla, valores nulos, máximos, mínimos, frecuencias, valores distintos, cardinalidad y valores únicos. En este articulo no se incluye el uso de histogramas para aquellas tablas que tengan una distribución de datos no uniforme.

Una forma relativamente sencilla de verificar si las estadísticas se encuentran actualizadas (no necesariamente correctas) es por medio de la tablas del sistema dba_tables y dba_indexes.  Estas contienen entre otros los campos num_rows, sample_size y last_analyzed

El campo num_rows contiene el número de registros que va a considerar el CBO al momento de generar el plan de ejecución y no necesariamente reflejan el número de registros que contiene la tabla, aunque lo ideal es que sea lo más aproximado o igual.  Si realizamos un  'select count(*)' a una tabla y validamos que el resultado es el mismo que contenido en el campo num_rows de la tabla dba_tables, sabremos que el manejador tiene información confiable en cuanto al número de registros de la tabla.  

El campo sample_size nos va a servir también, como punto para validar que tan confiables son las estadísticas. Este nos indica el tamaño muestra utilizando durante la recopilación de estadísticas por medio del package DBMS_STATS.  Lo ideal es que el valor del campo sample_size de las tablas del sistema dba_tables y dba_indexes sea igual o lo más cercano  al total de registros de la tabla, es decir que el manejador conto el total de registros de la tabla.  Este conteo se realiza por medio de la opción ESTIMATE_PERCENT del package DBMS_STATS.  Si colocamos la opción ESTIMATE_PERCENT=>100, tendremos la certeza de que el manejador conto el total de registros de la tabla para actualizar estadísticas. Sin embargo, en ocasiones, es demasiado costoso utilizar esta opción al 100%,  sobre todo en tablas con un número muy elevado de registros. 

Se tiene la alternativa de utilizar la opción 'estimate_percent => dbms_stats.auto_sample_size' del package DBMS_STATS  para que Oracle realice un cálculo basado en una muestra,  sin necesidad de realizar un conteo total de registros.  Para conocer que valor es la más adecuado de la opción ESTIMATE_PERCENT para calcular el tamaño muestra, se puede consultar la nota 390374.1 de la guía 'Oracle Performance Diagnostic Guide (OPDG) del sitio de soporte de Oracle.

El campo last_analyzed de las tablas dba_indexes, dba_tables indica la última vez que las estadísticas fueron actualizadas, ya sea para una tabla o un índice. Si se tienen tablas con operaciones DDL (principalemnte inserts, deletes y updates) de forma continua, pero la fecha last_analyzed no se encuentra actualizada, el CBO no podra generar el mejor plan de ejecución.  Este campo también se encuentra en la tablas del sistema dba_tab_partitions y dba_ind_partitions, para validar la fecha de actualización de estadísticas en tablas e índices particionados.

El siguiente ejercicio tiene como objetivo mostrar el efecto negátivo en el CBO cuando no cuenta con estadísticas actualizadas. Se van a generar dos tablas: ‘TEST1’ y ‘TEST2’ las cuales van a contener únicamente el campo ‘CAMPO1’ de tipo numérico.

Se generan los índices IDX_TEXT1 en la tabla TEST1 e IDX_TEXT2 en la tabla TEST2

Insertamos únicamente un registro en la tabla ‘TEST1’ y actualizamos estadísticas. En la tabla ‘dba_tables’ se muestra que para fines del optimizador, se tiene un registro.

Insertamos en la  tabla ‘TEST2’  20,000,004 registros y actualizamos estadísticas. La tabla ‘dba_tables muestra las estadísticas actualizadas de forma correcta:

Ahora vamos a insertar los 20 millones de registros de la tabla ‘TEST2’ a la tabla ‘TEST1’, sin actualizar las estadísticas de esta última, para que el optimizador ‘crea’ que la tabla tiene únicamente un registro, cuando realmente tiene 20 millones:

Ejecutamos un query, el cual va a realizar el join de las tablas ‘TEST1’ y ‘TEST2’ recuperando todos los registros de ambas tablas. Como el optimizador tiene estadísticas erróneas para la tabla ‘TEST1’, ‘cree’ que únicamente es necesario recuperar un registro de dicha tabla para realizar el join, como lo indica la columna E-Rows, cuando, realmente necesita recuperar 20 millones de registros. Por lo que de forma errónea utiliza el índice IDX_TEXT1,  ya que estima recuperar un número limitado de registros. 

Las estadísticas erróneas sobre la tabla ‘TEST1’ afectan todo el plan de ejecución, ya que en lugar de utilizar un ‘HASH JOIN’ que sería el indicado,  ya que se están recuperando todos los registros de las dos tablas, intenta realizar un acceso indexado, por medio de un ‘NESTED JOIN’,  tanto de la tabla ‘TEST1’ como de la tabla ‘TEST2’. 

Recordando que los índices son efectivos cuando se recupera un número limitado de registros. En este caso se está recuperando el 100% de la información de ambas tablas, por lo que los índices ‘IDX_TEST1’ e ‘IDX_TEST2’ en lugar de mejorar el tiempo de respuesta, lo perjudican.

Ahora, vamos a actualizar las estadísticas de la tabla ‘TEST1’, para que el optimizador tenga información real del número de registros. Una vez actualizadas las estadísticas con el package ‘DBMS_STATS’, reiniciamos la memoria.

Con las estadísticas actualizadas, ejecutamos nuevamente el mismo query.  El tiempo de respuesta disminuyo de 2.46 a 1.06 minutos.  Antes de la actualización de estadísticas sobre la tabla ‘TEST1’ se tenía un ‘NESTED JOIN’, el cual fue sustituido por un ‘HASH JOIN’. El nuevo plan muestra que con las estadísticas actualizadas,  el acceso vía índices ‘IDX_TEXT1’ e ‘IDX_TEXT2’ fue sustituido por ‘TABLE ACCESS FULL’ para ambas tablas, que en este caso en particular es más eficiente.

Conclusiones

Si las tablas e índices de una instancia de Oracle no cuentan con estadísticas actualizadas, el optimizador basado en costos o CBO, no tiene los insumos para generar el plan de ejecución más eficiente. Es importante distinguir las tablas históricas de las transaccionales para determinar que metodo y periocidad en la actualización de estadísticas es el más eficiente. Las últimas versiones de Oracle cuentan con algoritmos que le permiten a partir de una muestra pequeña o 'sample size' de la tabla, determinar de forma muy precisa las estadísticas de tablas e índices.   

José Manuel Vizcaíno Culebra

Contacto servicios profesionales:

jose.vizcainoculebra@gmail.com


5532439143 Ciudad de México