Las Estadísticas: El superpoder oculto del Cost-Based Optimizer

Level200

Cada vez que ejecutamos una consulta en Oracle, el optimizador debe tomar la decisión del mejor plan en fracciones de segundo, ¿que indice usar? ¿que tabla leer primero? ¿conviene un nested o un join?. Para responder estas preguntas Oracle depende de una fuente fundamental: Las estadísticas, sin ellas el optimizador no ve la realidad y solo podría hacer suposiciones y en el mundo de las base de datos las suposiciones pueden ser costosas.

El modelo de costos del optimizador de Oracle se basa en estadísticas recopiladas sobre los objetos involucrados en una consulta, así como sobre la base de datos y el servidor donde se ejecuta la consulta.

La gráfica resume como funciona las estadísticas como el optimizador basado en costos.

Ahora vayamos a la práctica.

EXPLAIN PLAN FOR

SELECT * FROM ventas WHERE cliente_id = 1;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Esta tabla tiene indices por la columna que se consulta y aun así esta haciendo un full scan, veamos si la tabla tiene estadisticas.

SELECT table_name, num_rows, last_analyzed FROM user_tables WHERE table_name='VENTAS';

**Parece que encontramos el problema y es que la tabla no tiene estadísticas, procedemos a recolectar estadísticas para la tabla:

EXEC DBMS_STATS.GATHER_TABLE_STATS( ownname => 'ACE', tabname => 'VENTAS' );

Validamos si ya están las estadísticas;

Ahora con las estadísticas recolectadas procederemos a ejecutar nuevamente el query:

Y ahora si vemos que el optimizador usa el índice para el plan de ejecución.

**Se han dado cuenta que el where consulta a otro cliente y no al id 1 como el query que hacia full Scan, veamos que pasa con el cliente 1.

Sigue haciendo full y eso puede pasar porque no hemos generado las estadísticas de histograma.

aún con el histograma sigue haciendo full, porque?

Si vemos el estimado de las filas a retornar representa el 99% del total de la tabla, es por eso que el plan de ejecución se ajusta a un full y no usa el indice, por el costo que implica el ir por indice para la cantidad de filas que devuelve.

Y solo podemos recolectar estadísticas de tablas?, no se puede recolectar estadísticas a estos niveles.

GATHER_INDEX_STATS

GATHER_TABLE_STATS

GATHER_SCHEMA_STATS

GATHER_DICTIONARY_STATS

GATHER_DATABASE_STATS

**Las estadísticas puedes ser obsoletas y confundir al optimizador? pues si, a ese concepto de la llama "stale" la buena noticia es que podemos saber si las estadisticas estas actualizadas o no con el siguiente query:

En este caso podemos ver que las estadísticas no estan en "stale".

Conclusion:

Las estadísticas son uno de los componentes más importantes de Oracle y al mismo tiempo uno de los más subestimados. Cada vez que ejecutamos una consulta el Cost-Based Optimizer (CBO) depende de ellas para estimar cardinalidades, calcular costos y seleccionar el plan de ejecución más eficiente.