Líneas de Base y Planes Más Favorables

Líneas de Base y Planes Más Favorables

¿Alguna vez ha experimentado una situación en la cuál las consultas de base de datos que solían ejecutarse de manera adecuada, de pronto comenzaban a funcionar mal? Es muy probable que usted lo haya atribuido a un cambio en el plan de ejecución. Pero un análisis más minucioso podría revelar que el cambio de desempeño fue producido por las estadísticas del Optimizador recientemente recogidas en torno a las tablas e índices a los que se hicieron referencia en esas consultas.

Y debido a esta situación, ¿ha alguna vez tomado la brusca decisión de frenar la recopilación de estadísticas? Este curso de acción mantiene prácticamente los mismos planes de ejecución para aquellas consultas, no obstante empeora algunas cosas. El desempeño de otras consultas, o incluso de las mismas consultas con distintos predicativos (cláusulas WHERE), se ve deteriorado debido a los planes de ejecución por debajo de los niveles óptimos generados a partir de estadísticas pasadas.

Cualquier sea la próxima medida que adopte, se generarán ciertos riesgos, por lo tanto ¿cómo puede mitigar ese riesgo y garantizar que los planes de ejecución para las sentencias SQL generadas son óptimos mientras se mantiene un entorno saludable en el cual las estadísticas del optimizador son rutinariamente recogidas y todas las sentencias SQL tienen un buen desempeño sin producirse cambios significativos (como por ejemplo incorporar pistas)? Usted puede recurrir al uso de contornos almacenados (store outlines) para detener el plan, pero eso también implicaría impedir que el optimizador genere planes de ejecución potencialmente beneficiosos.

En Oracle Database 11g, al utilizar la nueva característica de gestión de planes SQL, usted puede examinar cómo cambian los planes de ejecución con el transcurso del tiempo, hacer que las bases de datos verifiquen nuevos planes al ejecutarlos antes de utilizarlos, y desarrollar gradualmente planes más adecuados de manera controlada. 

Gestión de Planes SQL

Cuando se activa la gestión de planes SQL, el optimizador almacena los planes de ejecución generados en un repositorio especial, la base de gestión SQL. Todos los planes almacenados para una sentencia SQL específica son parte de un historial de planes para esa sentencia SQL.

Algunos de los planes en el historial pueden marcarse como “aceptados”. Cuando se vuelven a analizar la sentencia SQL, el optimizador considera solo los planes aceptados en el historial. Este grupo de planes aceptados para esa sentencia SQL se denomina línea de base (baseline) para el plan SQL, o simplemente línea de base.

No obstante, el optimizador aún intenta generar un mejor plan. Si el optimizador no genera un nuevo plan, lo agrega al historial de planes pero no lo considera mientras se reevalúa el SQL, a menos que el nuevo plan sea mejor que todos los planes aceptados en la línea de base. Por consiguiente, con la capacidad de gestión de planes SQL incorporada, las sentencias SQL nunca tendrán un plan menos eficiente que resulte en un peor desempeño.

Con la gestión de planes SQL, usted puede examinar todos los planes disponibles en el historial de planes para una sentencia SQL, compararlos para ver su eficiencia relativa, promover un plan específico y asignarle el estado aceptado, e incluso establecer un plan como permanente (determinado).

Este artículo le mostrará cómo administrar las líneas de base para el plan SQL—con inclusión de la captura, selección y evolución de las líneas de base—utilizando Oracle Enterprise Manager y SQL desde la línea de comando para garantizar el desempeño óptimo de las sentencias SQL.

Captura

La función de captura ofrecida a través de la gestión de planes SQL captura los distintos planes del optimizador utilizados por las sentencias SQL. Por defecto, la función de captura se encuentra desactivada—es decir, la gestión de planes SQL no captura el historial para las sentencias SQL que son evaluadas o reevaluadas.

Ahora observemos la captura de las líneas de base para algunos ejemplos de sentencias SQL provenientes de una sola sesión. Utilizaremos el esquema de muestra proporcionado por Oracle Database 11g—SH—y la tabla SALES (ventas) en particular.

Primero, activamos la captura de línea de base en la sesión:

 

alter session 
set optimizer_capture_sql_plan_baselines = true;
 

 

Ahora, todas las sentencias SQL ejecutadas en esta sesión serán capturadas junto con sus planes de optimización, en la base de gestión SQL. Cada vez que el plan cambia para una sentencia SQL, se almacena en el historial de planes. Para ver esto, ejecute el script que se muestra en el Listado 1, el cual ejecuta exactamente el mismo SQL pero bajo distintas circunstancias. Primero, SQL se ejecuta con todos los valores por defecto (con inclusión del valor implícito optimizer_mode = todas las filas). En la próxima ejecución, el valor de parámetro optimizer_mode se establece en first_rows. Antes de la tercera ejecución del SQL, recopilamos estadísticas actualizadas sobre la tabla y los índices.

Listado de Códigos 1: Captura de líneas de base para planes SQL

 

alter session set optimizer_capture_sql_plan_baselines = true;
-- First execution. Default Environment
select * /* ARUP */ from sales
where quantity_sold > 1 order by cust_id;
-- Change the optimizer mode
alter session set optimizer_mode = first_rows;
-- Second execution. Opt Mode changed
select * /* ARUP */ from sales
where quantity_sold > 1 order by cust_id;
-- Gather stats now
begin
    dbms_stats.gather_table_stats (
        ownname            => 'SH',
        tabname             => 'SALES',
        cascade              => TRUE,
        no_invalidate      => FALSE,
        method_opt        => 'FOR ALL INDEXED COLUMNS SIZE AUTO',
        granularity          => 'GLOBAL AND PARTITION',
        estimate_percent => 10,
        degree                => 4
    );
end;
/
-- Third execution. After stats
select * /* ARUP */ from sales
where quantity_sold > 1 order by cust_id;
 

 

Si el plan cambia en cada una de las ejecuciones SQL en el Listado 1, los distintos planes serán capturados en el historial de planes para esa sentencia SQL. (El comentario /* ARUP */ fácilmente identifica las sentencias SQL específicas en el grupo compartido.)

La manera más fácil de visualizar el historial de planes es a través de Oracle Enterprise Manager. Desde la página principal de la Base de Datos, seleccione la etiqueta Servidor y luego haga click en SQL Plan Control (Control de Plan SQL). En esa página, seleccione la etiqueta SQL Plan Baseline (Línea de Base para el Plan SQL); y busque las sentencias SQL que contienen el nombre ARUP, como se muestra en la Figura 1, que despliega el historial de planes para las sentencias SQL en la parte inferior de la pantalla.