Ejecución de las Estadísticas

Saludos,

Algo importante en considerar en una base de datos es mantener actualizado las estadísticas de tablas e índices. Y como me diría un amigo ¿Y esto pa que nos sirve? Sirve de mucho al CBO (Optimizador Basado en Costes) al momento de seleccionar el plan de ejecución con menor costo para ejecutar las consultas SQL.

CBO (Cost Based Optimizer)
Es un método interno del motor de base de datos que selecciona de varios planes de ejecución el menos costoso. Antes de ejecutar una sentencia SQL esta pasa por un análisis que genera varios planes de ejecución cuya selección dependerá de que costo. Este método fue introducido desde la versión 7i. A diferencia del método antecesor RBO (Optimizador Basado Reglas) el CBO busca un plan de ejecución de le represente menor costo a la base de datos. Para poder evaluar el costo de una ejecución este método se ayuda de la información de las tablas alimentadas por las estadísticas.

RBO (Rule Based Optimizer)
Este método se basa en realizar un plan de ejecución basado en las reglas de accesos y no estima costo de ejecución por lo que no necesita de las estadísticas. RBO es un método que Oracle va a desaparecer y no recomienda su uso.

¿Cómo ejecutamos las estadísticas?
 Bueno, existen varias formas de ejecutar las estadisticas y a continuación se las indicaré:

ANALISIS POR OBJETO

--Estadísticas por tabla y los índices relacionados a la tabla ANALYZE TABLE tabla COMPUTE|ESTIMATE|DELETE STATISTICS --Estadísticas solo por tabla ANALYZE TABLE tabla COMPUTE|ESTIMATE|DELETE STATISTICS FOR TABLE --Estadísticas solo de los índices relacionadas a la tabla ANALYZE TABLE tabla COMPUTE|ESTIMATE|DELETE STATISTICS FOR INDEX --Estadísticas por índice ANALYZE INDEX indice COMPUTE|ESTIMATE|DELETE STATISTICS --Estadísticas por cluster ANALYZE CLUSTER cluster COMPUTE|ESTIMATE|DELETE STATISTICS

Parámetro	Descripción	Ejemplo
COMPUTE	Calcula las estadísticas con todos los registros de la tabla	ANALYZE TABLE tabla COMPUTE STATISTICS;
ESTIMATE	Calcula las estadísticas con una muestra de filas basadas en cantidad o porcentaje. Es recomendable usarlo cuando la tabla es muy grande y la estadística demora mucha en realizarse.	--Estimación por porcentaje ANALYZE TABLE tabla ESTIMATE STATISTICS SAMPLE porcentaje_filas PERCENT; --Estimación por filas ANALYZE TABLE tabla ESTIMATE STATISTICS SAMPLE cantidad_filas ROWS;
DELETE	Permite el borrado de las estadísticas	ANALYZE TABLE tabla DELETE STATISTICS;

ANALISIS POR ESQUEMA

--Estadistica a todas las tablas de un esquema por medio de la función ----UTILITY DBMS_UTILITY.ANALYZE_SCHEMA( USUARIO_PROPIETARIO, METODO_ANALISIS, FILAS_A_ESTIMAR, PORCENTAJE_A_ESTIMAR);

Parámetro	Descripción
USUARIO_PROPIETARIO	Es el usuario propietario de las tablas a realizar el análisis de estadísticas
METODO_ANALISIS	Tiene dos opciones: COMPUTE y ESTIMATE. Si el parámetro es COMPUTE el análisis es por todos los registros de la tabla y los siguientes dos parámetros no se usan. Si el parámetro es ESTIMATE se debe de indicar uno de los dos parámetros siguientes, si el análisis se lo realiza tomando una determinada muestra de registros.
FILAS_A_ESTIMAR	Se usa si el método a usar es ESTIMATE se define la cantidad de registros para tomar como muestra para realizar el análisis.
PORCENTAJE_A_ESTIMAR	Se usa si el método a usar es ESTIMATE se define el porcentaje de registros para tomar como muestra para realizar el análisis.

ANALISIS A TODA LA BASE

--Estadistica a todas las tablas de la base DBMS_UTILITY.ANALYZE_DATABSE( METODO_ANALISIS, FILAS_A_ESTIMAR, PORCENTAJE_A_ESTIMAR);

Parámetro	Descripción
METODO_ANALISIS	Tiene dos opciones: COMPUTE y ESTIMATE. Si el parámetro es COMPUTE el análisis es por todos los registros de la tabla y los siguientes dos parámetros no se usan. Si el parámetro es ESTIMATE se debe de indicar uno de los dos parámetros siguientes, si el análisis se lo realiza tomando una determinada muestra de registros.
FILAS_A_ESTIMAR	Se usa si el método a usar es ESTIMATE se define la cantidad de registros para tomar como muestra para realizar el análisis.
PORCENTAJE_A_ESTIMAR	Se usa si el método a usar es ESTIMATE se define el porcentaje de registros para tomar como muestra para realizar el análisis.

Ejemplo
Este ejemplo va ilustrar la forma en que las estadísticas ayudan a mejorar el método CBO para la selección del plan con menor costo.
Creamos la tabla clientes con índices por identificación y tipo_identificacion

CREATE TABLE clientes ( id_cliente NUMBER, identificacion VARCHAR2(20), tipo_identificacion VARCHAR(20), id_provincia VARCHAR2(5), nombres VARCHAR2(60), apellidos VARCHAR2(60), PRIMARY KEY (id_cliente) ); --Creamos dos indices uno por identificacion y otra por el id_provincia CREATE INDEX ind_iden_cliente ON clientes(identificacion); CREATE INDEX ind_id_pro_cliente ON clientes(id_provincia);

Insertamos 300 registros

Insert Into Clientes (Id_Cliente, Identificacion, Tipo_Identificacion, Id_Provincia, Nombres, Apellidos ) Select Rownum Id_Cliente, Trim(To_Char(Rownum,'000000000')) Identificacion, Mod(Rownum,2)+1Tipo_Identificacion, Mod(Rownum,4) Id_Provincia, 'Nombre '||Rownum Nombres, 'Apellido '||Rownum Apellidos From Dual Connect By Level <= 300; Commit;

Revisamos el plan de ejecución se la siguiente consulta sin la estadística ejecutada:

Select * From Clientes Where Id_Provincia='1';

Plan de ejecución con el SQLPLUS

Plan de ejecución con el TOAD

Ahora ejecutemos el plan de ejecución pero antes ejecutemos la estadística a la tabla Clientes.

ANALYZE TABLE clientes COMPUTE STATISTICS;

Plan de ejecución con el SQLPLUS

Plan de ejecución con el TOAD

Ahora vemos que el plan de ejecución se va por el indice y no por acceso full a la tabla

¿Cuando ejecutar las Estadisticas?No existe un periodo estándar la realizar la ejecución de las estadísticas, esto dependerá de la transaccionalidad y cambios en los datos que se realicen en la base, puede ser cada mes, quincena, semana o diario, en mi caso tengo programado la ejecución de las estadísticas cada quincena y en horario nocturno para que no afecte a los usuarios.

Es calro que si las estadisticas no son reales o aproximadas, el metodo CBO no estará seleccionando el plan de ejecución correcto.

Conclusión
Hemos encontrado que el CBO (Optimizador Basado en Costes) funciona de manera óptima siempre y cuando tengamos actualizados las estadísticas de tablas e índices para que al momento de elegir el plan de ejecución menos costoso este se base en estadísticas reales.