Use SQL Plan Management Feature

1. Documentación en Tahiti -> Oracle Database, 11g Release 2 (11.2) -> Masters Book List -> Performance Tuning Guide -> 15 Using SQL Plan Management

2. El objetivo de SQL Plan Management es preservar el rendimiento de las setencias SQL aunque se produzcan cambios en la BD. Se basa en un mecanismo preventivo que almacena y evalua los planes de ejecución de las sentencias a lo largo del tiempo. Con cada sentencia repetitiva se construye lo que se llama un SQL Plan Baseline, que no es más que un conjunto de planes de ejecución de una sentencia.

Un SQL Plan Baseline puede ayudar en situaciones como el cambio de la versión del optimizador, nuevas estadísticas, cambio en el sistema, etc… Hay situaciones en las que un SQL Plan Baseline no puede ayudar, como por ejemplo, cuando se borra un índice, por mucho que quiera el optimizador ya no va a poder usarlo.

Esta característica es útil en varios escenarios, para evitar cambios no deseados en el comportamiento de la BD:

· Una actualización de la BD que conlleva una nueva versión del optimizador
· Cambios a nivel de sistema o cambios en los datos
· Despliegue de una nueva aplicación con la introducción de nuevas sentencias SQL

3. La mejor forma de entender bien SQL Plan Management es a través de un ejemplo.

-- Limpiamos la Shared Pool antes de comenzar el ejercicio
ALTER SYSTEM FLUSH SHARED_POOL;

-- Revisamos los parámetros específicos de SQL Plan Managemente (a partir de ahora SPA)
-- Por defecto:
--   · optimizer_capture_sql_plan_baselines = FALSE
--   · optimizer_use_sql_plan_baselines = TRUE
SHOW PARAMETER BASELINE

-- Modificamos el reconocimiento automático de sentencias SQL repetitivas para nuestra sesión
ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=TRUE;

-- Lanzamos una sentencia de ejemplo
SELECT /* SPA_TEST */ * FROM SH.SALES WHERE QUANTITY_SOLD > 50 ORDER BY PROD_ID;

-- Revisamos si se ha producido un Baseline Plan para nuestra sentencia
-- Como sólo se ha ejecutado una vez la siguiente sentencia no devuelve ningún resultado
SELECT * FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE 'SELECT /* SPA_TEST%';

-- Ejecutamos la consulta sobre SH.SALES de nuevo
SELECT /* SPA_TEST */ * FROM SH.SALES WHERE QUANTITY_SOLD > 50 ORDER BY PROD_ID;

-- Ahora sí debe aparecer una entrada en la vista DBA_SQL_PLAN_BASELINES
--   · ORIGIN nos dice de donde proviene el plan (AUTO-CAPTURE, MANUAL-LOAD, MANUAL-SQLTUNE o AUTO_SQLTUNE)
--   · ENABLED indica que el plan está habilitado para ser utilizado por el optimizador
--   · ACCEPTED significa que el plan ha sido validado como un buen plan
--   · FIXED se utiliza para marcar sólo ciertos planes como válidos aunque haya otros con mejor coste
SELECT SIGNATURE, SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, AUTOPURGE
FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE 'SELECT /* SPA_TEST%';

-- Modificamos un parámetro para ver como afecta al plan y ejecutamos la consulta de nuevo
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;
SELECT /* SPA_TEST */ * FROM SH.SALES WHERE QUANTITY_SOLD > 50 ORDER BY PROD_ID;

-- Si consultamos los planes de la consulta veremos que ahora tenemos dos
-- El nuevo plan no ha sido aceptado, primero debe ser validado antes de que el optimizador lo valore
SELECT SIGNATURE, SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, AUTOPURGE
FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE 'SELECT /* SPA_TEST%';

-- Comprobamos que se ha ejecutado el primer plan y no el segundo
-- Lanzamos la consulta de nuevo
SELECT /* SPA_TEST */ * FROM SH.SALES WHERE QUANTITY_SOLD > 50 ORDER BY PROD_ID;

-- Obtenemos el plan de ejecución utilizado
-- En la sección "Note" no indica que el SQL Plan Baseline utilizado
--   · Ej. "SQL plan baseline SQL_PLAN_89q0m2tmq847654bc8843 used for this statement"
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

-- Obtenemos la información de dicho SQL Plan Baseline
SELECT SIGNATURE, SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, AUTOPURGE
FROM DBA_SQL_PLAN_BASELINES WHERE PLAN_NAME='SQL_PLAN_89q0m2tmq847654bc8843';

-- Revertimos los cambios
-- Desactivamos la captura automática de SQL Plan Baselines
ALTER SESSION SET OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES=FALSE;

-- Dejamos el parámetro OPTIMIZER_MODE al valor original
ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS;

-- Borramos el SQL Plan Baseline
VARIABLE cmd NUMBER;
EXEC :cmd := DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_84d81316676410e6');

-- Comprobamos que ya no existe ningún SQL Plan Baseline
SELECT SIGNATURE, SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, AUTOPURGE
FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE 'SELECT /* SPA_TEST%';

4. Hemos visto como se generan SQL Plan Baselines automáticamente cuando hemos habilitado el parámetro OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES. Pero podemos cargarlos manualmente a partir de un STS o escogiendo las sentecias que queramos. En el siguiente ejercicio vamos a utilizar OEM para cargar dos planes.

-- Limpiamos la Shared Pool antes de comenzar el ejercicio
ALTER SYSTEM FLUSH SHARED_POOL;

-- Revisamos que el parámetro OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES está dehabilitado (FALSE)
SHOW PARAMETER OPTIMIZER_CAPTURE_SQL_PLAN_BASELINES

-- Ejecutamos la consulta del ejercicio anterior modificando el parámetro OPTIMIZER_MODE para tener dos planes
-- Cada vez que ejecutamos la consulta nos fijamos en el plan de ejecución
-- La primera ejecución (con OPTIMIZER_MODE = FIRST_ROWS) realiza un acceso por BITMAP
ALTER SESSION SET OPTIMIZER_MODE = FIRST_ROWS;
SELECT /* SPA_TEST */ * FROM SH.SALES WHERE QUANTITY_SOLD > 50 ORDER BY PROD_ID;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

-- La segunda ejecución (con OPTIMIZER_MODE = ALL_ROWS) realiza un "TABLE ACCESS FULL"
ALTER SESSION SET OPTIMIZER_MODE = ALL_ROWS;
SELECT /* SPA_TEST */ * FROM SH.SALES WHERE QUANTITY_SOLD > 50 ORDER BY PROD_ID;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

En la página principal de la instancia OCM ->
-> Click “Server” ->
-> Click “SQL Plan Control” ->
-> Click “SQL Plan Baseline” ->
-> Click “Load” ->
-> Seleccionamos “Load plans from cursor cache” ->
-> Hacemos click en el icono de la linterna ->
-> SQL Text = “SELECT /* SPA_TEST */” ->
-> Seleccionamos la consulta con SQL_ID = ‘2g4a4jpz99p8f’ ->
-> Click “Select” ->
-> Click “OK” ->
-> Click “Refresh” varias veces hasta que salgan los planes

-- Consultamos la vista DBA_SQL_PLAN_BASELINES para fijarnos en el método de carga (MANUAL-LOAD)
SELECT SIGNATURE, SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, AUTOPURGE
FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE 'SELECT /* SPA_TEST%';

-- Podemos consultar los planes de ejecución a través de OEM o PL/SQL
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(sql_handle=>'SQL_84d81316676410e6',format=>'basic'));

5.Hemos visto que el plan actual de ejecución de la consulta contiene un “TABLE ACCESS FULL”. Vamos a deshabilitar un plan para ver que sucede.

-- Deshabilitamos el plan que contiene la operación "TABLE ACCESS FULL" => SQL_PLAN_89q0m2tmq847654bc8843
EXEC :cmd := DBMS_SPM.ALTER_SQL_PLAN_BASELINE( -
  SQL_HANDLE => 'SQL_84d81316676410e6', -
  PLAN_NAME  => 'SQL_PLAN_89q0m2tmq847654bc8843', -
  ATTRIBUTE_NAME => 'ENABLED', -
  ATTRIBUTE_VALUE => 'NO');

-- Revisamos que se ha realizado (ENABLED = NO para el plan SQL_PLAN_89q0m2tmq847654bc8843)
SELECT SIGNATURE, SQL_HANDLE, SQL_TEXT, PLAN_NAME, ORIGIN, ENABLED, ACCEPTED, FIXED, AUTOPURGE
FROM DBA_SQL_PLAN_BASELINES WHERE SQL_TEXT LIKE 'SELECT /* SPA_TEST%';

-- Lanzamos la consulta
SELECT /* SPA_TEST */ * FROM SH.SALES WHERE QUANTITY_SOLD > 50 ORDER BY PROD_ID;
-- Obtenemos el plan de ejecución y nos fijamos que ha utilizado el otro Baseline => SQL_PLAN_89q0m2tmq847611df68d0

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

-- Deshabilitamos el otro Baseline
EXEC :cmd := DBMS_SPM.ALTER_SQL_PLAN_BASELINE( -
  SQL_HANDLE => 'SQL_84d81316676410e6', -
  PLAN_NAME  => 'SQL_PLAN_89q0m2tmq847611df68d0', -
  ATTRIBUTE_NAME => 'ENABLED', -
  ATTRIBUTE_VALUE => 'NO');

-- Lanzamos la consulta de nuevo
SELECT /* SPA_TEST */ * FROM SH.SALES WHERE QUANTITY_SOLD > 50 ORDER BY PROD_ID;

-- Obtenemos el plan de ejecución y nos fijamos que no se ha utilizado ningún Baseline
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR());

-- Borramos los Baseline Plan
EXEC :cmd := DBMS_SPM.DROP_SQL_PLAN_BASELINE('SQL_84d81316676410e6');

6. Una vez que tenemos un SQL Plan Baseline de una sentencia, si se lanza una tarea de SQL Tuning Advisor sobre dicha sentencia (ya sea manualmente o por AUTOTASK), si aceptamos el SQL Profile se añadirá un Baseline Plan. Hagamos un ejemplo con OEM.

-- Ejecutamos la siguiente sentencia
SELECT /*+ ORDERED USE_NL(C) FULL(C) FULL(S)*/ C.CUST_ID, SUM(S.QUANTITY_SOLD)
 FROM SH.SALES S, SH.CUSTOMERS C
 WHERE C.CUST_ID = S.CUST_ID AND C.CUST_ID < 2
 GROUP BY C.CUST_ID;

En la página principal de la instancia OCM ->
-> Click “Server” ->
-> Click “SQL Plan Control” ->
-> Click “SQL Plan Baseline” ->
-> Click “Load” ->
-> Seleccionamos “Load plans from cursor cache” ->
-> Hacemos click en el icono de la linterna ->
-> SQL Text = “SELECT /*+ ORDERED USE_NL(C) FULL(C) FULL(S)” ->
-> Seleccionamos la consulta con SQL_ID = ‘a1afam8gyqg47’ ->
-> Click “Select” ->
-> Click “OK” ->
-> Click “Refresh” varias veces hasta que salgan los planes

Ahora lanzamos el SQL Tuning Advisor para dicha sentencia ->
-> Click “Database Instance: OCM” ->
-> Click “Performance” ->
-> Click “Search SQL” ->
-> SQL Text LIKE “SELECT /*+ ORDERED USE_NL(C) FULL(C) FULL(S)” ->
-> Click “Search” ->
-> Click “a1afam8gyqg47” ->
-> Click “Schedule SQL Tuning Advisor” ->
-> Click “Submit” ->
-> Cuando finalice la tarea Click “Implement” ->
-> Click “Yes”

Veamos como se ha cargado el nuevo Baseline Plan ->
-> Click “Database Instance: OCM” ->
-> Click “Server” ->
-> Click “SQL Plan Control” ->
-> Click “SQL Plan Baseline” ->
-> Click “Refresh” ->
-> Comprobamos que tenemos dos Baseline Plan, uno de ellos con Origin = “MANUAL-SQLTUNE”