As Published In March/April 2009

TAGS sql, All

Utilice la gestión de planes SQL de Oracle Database 11g para optimizar los planes de ejecución.

¿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.

Figura 1: Historial del plan SQL

Si usted hace click en los nombres del plan SQL, como por ejemplo SYS_SQL_PLAN_27a47aa154bc8843, podrá ver el detalle del plan almacenado en el historial de planes. Las columnas importantes en la pantalla desplegarán lo siguiente:

• Enabled (activado) indica si el plan se encuentra activo.
• Accepted (aceptado) indica si el plan debería ser considerado por el optimizador. Si se acepta más de un plan, el optimizador seleccionará el mejor de ellos.
• Fixed (determinado) indica si el plan se utilizará permanentemente para esa sentencia SQL. Si se establece más de un plan, el optimizador seleccionará el mejor de ellos.
• Auto Purge (eliminación automática) indica que el plan –cuando no sea utilizado- se eliminará automáticamente del historial de planes luego de un tiempo específico, a menos que se deshabilite la función de eliminación automática. El plazo de tiempo después del cual se elimina un plan no utilizado se muestra en la Figura 1, cerca de la etiqueta Plan Retention(Weeks) (Retención del Plan –Semanas). En este caso, se fijó en 53 semanas, pero usted puede cambiarlo al hacer click en el botón Configuración.

También puede activar la captura y el uso de las líneas de base para planes SQL en la pantalla de Oracle Enterprise Manager al hacer click en los enlaces adecuados en la sección Settings (Configuración).

Tenga en cuenta que también puede cargar planes a una línea de base para planes SQL desde el caché del cursor o desde un conjunto de ajuste SQL. Cuando usted carga manualmente los planes en una línea de base para el plan SQL, estos planes cargados se incorporan como planes aceptados. Para obtener más información, vea el Capítulo 15, “Utilizando la Gestión de Planes SQL” en la Guía de Ajuste de Desempeño de la Base de Datos de Oracle.

Utilización de las Líneas de Base

Con las líneas de base para planes SQL capturadas, ahora podemos activar al optimizador para utilizarlas:

alter session set 
optimizer_use_sql_plan_baselines = true;
  

Con el uso de líneas de base activado, cuando el optimizador reevalúa una sentencia SQL, éste examina los planes almacenados en la línea de base para esa sentencia SQL y selecciona la mejor opción. Y de ahí proviene el beneficio más importante de las líneas de base. El optimizador también reevalúa las sentencias SQL—la presencia de una línea de base no evita eso—y si el plan recientemente generado no se encuentra en el historial de planes SQL, éste se agrega, pero no como “aceptado”. Así, si el plan recientemente generado es peor, el desempeño de SQL no se verá afectado, ya que el plan no se utiliza. No obstante, en algunos casos, usted puede decidir que el nuevo plan es mejor, sobre la base de su conocimiento de distribución de datos o de la lógica de aplicaciones. Por ejemplo, supongamos que el plan fue capturado cuando la tabla estaba prácticamente vacía, haciendo que el optimizador seleccione adecuadamente un escaneo de índices. Pero usted sabe que la aplicación completa la tabla antes de invocar la sentencia SQL y que el escaneo de toda la tabla será realmente mejor para el plan a largo plazo. En tal caso, usted puede examinar el nuevo plan, y si es mejor, puede aceptarlo—y después de esto, será considerado por el optimizador. Es por ese motivo que usted obtiene todas las ventajas: siempre se utiliza un buen plan, pero si el optimizador genera uno mejor, éste estará disponible para su comparación.

Si no desea utilizar planes en la línea de base para una sentencia SQL, usted puede utilizar la siguiente sentencia en la sesión antes de invocar la sentencia SQL para desactivar el uso de las líneas de base:

alter session set 
optimizer_use_sql_plan_baselines = false;
  

El Listado 2 ejecuta la misma consulta dos veces—primero con líneas de base activadas y luego con líneas de base desactivadas, y usted puede ver cómo el plan cambia después de desactivar las líneas de base. Inicialmente el optimizador elige BITMAP INDEX FULL SCAN en el índice SALES_TIME_BIX. Después de que la línea de base se desactiva, el plan cambia a TABLE ACCESS FULL en la tabla SALES, porque ése parece ser el mejor plan basado en las estadísticas del optimizador y en otras variables que afectan al optimizador justo en ese momento. Antes, cuando la línea de base se desactivaba, el optimizador seleccionaba el mejor plan del conjunto de planes aceptados almacenados en la línea de base.

Listado de Códigos 2: Utilización de la línea de base para el plan SQL

SQL> explain plan for select * /* ARUP */ from sales
  2    where quantity_sold > 1 order by cust_id;
Explained.

SQL> select * from table(dbms_xplan.display(null, null, 'basic'));
PLAN_TABLE_OUTPUT
---------------------------
Plan hash value: 143117509
--------------------------------------------------------------
| Id  | Operation                           | Name            |
--------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |
|   1 |  SORT ORDER BY                      |                 |
|   2 |   PARTITION RANGE ALL               |                 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES           |
|   4 |     BITMAP CONVERSION TO ROWIDS     |                 |
|   5 |      BITMAP INDEX FULL SCAN         | SALES_TIME_BIX  |
--------------------------------------------------------------

-- Now disable baselines and look at the latest plan

SQL> alter session set optimizer_use_sql_plan_baselines = false;
Session altered.

SQL> explain plan for select * /* ARUP */ from sales
  2    where quantity_sold > 1 order by cust_id;
Explained.

SQL> select * from table(dbms_xplan.display(null, null, 'basic'));

PLAN_TABLE_OUTPUT
----------------------------
Plan hash value: 3803407550

--------------------------------------
| Id  | Operation             | Name  |
--------------------------------------
|   0 | SELECT STATEMENT      |       |
|   1 |  SORT ORDER BY        |       |
|   2 |   PARTITION RANGE ALL |       |
|   3 |    TABLE ACCESS FULL  | SALES |
--------------------------------------
  

Gestión y Evolución

Después de crear las líneas de base para una sentencia SQL específica, usted puede examinarlas al hacer click en sus nombres de planes relacionados en la pantalla Oracle Enterprise Manager como se muestra en la Figura 1 (Oracle Enterprise Manager -> SQL Plan Control page -> SQL Plan Baseline tab) y al controlar los detalles del plan. Si un plan específico nunca va a funcionar, usted puede desactivarlo completamente al hacer click en el botón Disable (desactivar). Puede hacer click en el botón Enable (activar) si cambia de opinión más tarde. El botón Drop selecciona un plan de la base de gestión SQL. Recuerde que si un plan no se utiliza, se eliminará automáticamente después de que se haya cumplido el período de retención.

Si usted sospecha que un plan en la actual línea de base no es óptimo y que un plan diferente en el historial del plan podría ser mejor, usted puede comparar el desempeño de los planes al utilizar la función “evolucionar” (en Oracle Enterprise Manager -> SQL Plan Control page -> SQL Plan Baseline tab o utilizando la función DBMS_SPM.EVOLVE_SQL_PLAN_BASELINE desde la línea de comando). Para utilizar la función “evolucionar”, en la pantalla Oracle Enterprise Manager que se muestra en la Figura 1, seleccione el plan que desea comparar y haga click en el botón Evolve. La comparación se realiza entre el plan que el optimizador selecciona como el mejor y el plan que usted selecciona. La función produce un informe, como se muestra en el Listado 3. Vea esta línea en la parte superior del informe:

Listado de Códigos 3: Informe de la evolución de las líneas de base

-----------------------------------------------------
Evolve SQL Plan Baseline Report
-----------------------------------------------------

Inputs:
----
  PLAN_LIST   = SYS_SQL_PLAN_27a47aa15003759b
  TIME_LIMIT = DBMS_SPM.AUTO_LIMIT
  VERIFY        = YES
  COMMIT       = YES


Plan: SYS_SQL_PLAN_27a47aa15003759b
----------------------
  Plan was verified: Time used 41.06 seconds.
  Failed performance criterion: Compound improvement ratio < .36


                       Baseline Plan       Test Plan   Improv. Ratio
                       --------------      ---------   ------------- 
  Execution Status:          COMPLETE       COMPLETE
  Rows Processed:                   0              0
  Elapsed Time(ms):              5036           1033         4.88
  CPU Time(ms):                   254            700          .36
  Buffer Gets:                   1728          43945          .04
  Disk Reads:                     254             22        11.55
  Direct Writes:                    0              0
  Fetches:                         49             22         2.23
  Executions:                       1              1


--------------------------------------------------------------------
Report Summary
--------------------------------------------------------------------
Number of SQL plan baselines verified: 1.
Number of SQL plan baselines evolved: 0.
  

Failed performance criterion: 
Compound improvement ratio < .36. 
  

La línea claramente muestra que el plan recientemente considerado se desempeñó de manera menos favorable que el original de modo que fue rechazado como reemplazo para la mejor opción de plan del optimizador. Si el rango de comparación hubiera arrojado una mejora superior a 1, la gestión de planes SQL habría aceptado ese plan como candidato para consideración del optimizador.

¿Qué sucede si usted siente que la decisión realizada por la función “evolucionar” no es la adecuada y usted preferiría obligar al optimizador a utilizar un plan específico? Usted puede lograr esto al determinar el plan en la línea de base. Puede establecer un plan al ejecutar la función alter_sql_plan_baseline en el paquete dbms_spm, como se muestra en el Listado 4.

Listado de Códigos 4: Determinación del plan de línea de base

declare
   l_plans pls_integer;
begin
   l_plans := dbms_spm.alter_sql_plan_baseline (
      sql_handle         => 'SYS_SQL_f6b17b4c27a47aa1',
      plan_name         => 'SYS_SQL_PLAN_27a47aa15003759b',
      attribute_name   => 'fixed',
      attribute_value  => 'YES'
   );
end;

-- Now examine the plan:
SQL> explain plan for select * /* ARUP */ from sales
  2  where quantity_sold > 1 order by cust_id;

Explained.

SQL> select * from table(dbms_xplan.display(null, null, 'basic'));

Plan hash value: 143117509
--------------------------------------------------------------
| Id  | Operation                           | Name            |
--------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |
|   1 |  SORT ORDER BY                      |                 |
|   2 |   PARTITION RANGE ALL               |                 |
|   3 |    TABLE ACCESS BY LOCAL INDEX ROWID| SALES           |
|   4 |     BITMAP CONVERSION TO ROWIDS     |                 |
|   5 |      BITMAP INDEX FULL SCAN         | SALES_PROMO_BIX |
--------------------------------------------------------------
  

Desde el output, usted puede ver que el nuevo plan utilizó el índice SALES_PROMO_BIX en lugar del índice SALES_TIME_BIX utilizado en el plan anterior (como se muestra en el Listado 2). Ahora se determinará el nuevo plan.

¿En dónde puede utilizar planes fijos? Suponga que el plan para una sentencia SQL no es óptimo, como el plan que utiliza el índice SALES_PROMO_BIX, mientras que un plan que utiliza el índice SALES_TIME_BIX sería más efectivo, pero usted no puede cambiar el código para establecer los hints. En ese caso usted puede seguir los siguientes pasos:

1. En una sesión distinta, cambie el parámetro optimizer_mode hasta el valor que produce el plan deseado, como se muestra en el Listado 1.

2. Ejecute la sentencia SQL, capture las líneas de base como en el Listado 1, y desconecte la sesión.

3. Marque el plan, utilizando el índice SALES_TIME_BIX como determinado, tal como se muestra en el Listado

4. Recuerde reemplazar el manejo SQL y el nombre del plan según el caso.

Una vez que se marca el plan como determinado, la sentencia SQL utilizará ese plan únicamente, y no el generado por el optimizador. Si existe más de un plan establecido, el optimizador elegirá el mejor de ellos.

También puede utilizar la misma técnica para garantizar los procesos de ejecución estable para las sentencias SQL durante las actualizaciones de la base de datos. Primero recopile las líneas de base para todas las sentencias SQL en la base de datos configurando el parámetro del sistema optimizer_capture_sql_plan_baselines como verdadero y marque solo un plan como determinado para cada una de las sentencias SQL críticas. Luego debería gradualmente “dejar sin efecto la determinación” de los planes como fijos y utilizar la función Evolve para controlar su existe algún otro plan óptimo. Si un plan generado con posterioridad por parte del optimizador es peor, usted siempre puede volver al plan anteriormente determinado.

Conclusión

Los contornos almacenados hacen a un plan estable, pero también pueden volverlo rígido. El optimizador identifica que existe un contorno para una sentencia SQL y frena la generación de nuevos planes. Las líneas de base, por otro lado, nunca impiden que el optimizador genere un nuevo plan.

La característica de gestión de planes SQL le permite almacenar planes conocidos validados para las sentencias SQL como líneas de base, las cuales pueden ser muy útiles para diagnosticar degradaciones repentinas de desempeño. Debido a que las líneas de base (y los planes correspondientes) se almacenan en un repositorio, usted también puede compararlas y utilizarlas de la mejor manera.

Envíenos sus comentarios