Como aplicar hints en sentencias sin necesidad de modificarlas desde la aplicación usando SQL-BASELINES (por Alejandro Aguirrez)

           Durante mi experiencia como DBA me he encontrado con incontables problemas de performace sobre sentencias que eran ejecutadas desde  aplicaciones donde el código de las sentencias no puede ser  modificado. 

           En versiones anteriores a 11g debias generar outlines y editarlas para que la sentencia elija el PATH optimo para resolver la consulta en el menor tiempo . En 11g disponemos de una nueva feature que nos permite manejar la estabilidad de los planes de ejecucion SQL BASELINES.

            En el siguiente documento se detallará el procedimiento para cargar una baseline generada a partir de la sentencia original (generada por la aplicación) con la adición de un hint/s que produce la obtención del plan optimo para esta sentencia .

Nomenclatura utilizada

ORIGINAL_SQL_ID = SQL_ID de la sentencia original sin HINTS a la cual le queremos fijar un plan optimo aplicando HINTS

1 - (Opcional si no fue realizado antes )

Capturar la sentencia de la sentencia original sin HINTS. El siguiente código solo cargara la baseline si la misma está actualmente en memoria.

BEGIN

 dbms_spm.load_plans_from_cursor_cache(sql_id => '&original_sql_id', plan_hash_value => &original_plan_hash_value );

END;

/

2 - Ejecutar el SQL con el HINT necesario para generar un plan de ejecución optimo

3 - Identificar el sql_id y plan de ejecuion  de la sentencia con HINTS de la vista V$SQL.

select * from table(dbms_xplan.display_cursor('&hinted_SQL_ID'));

4 - Verificar que la baseline generada en el paso 1 exista. Extraer el campo SQL_HANDLE

SELECT * FROM dba_sql_plan_baselines WHERE signature IN (SELECT force_matching_signature FROM dba_hist_sqlstat WHERE sql_id='&original_sql_id')

5 - Asociar el plan de ejecución de la sentencia con hints con la sentencia original

var res number

exec :res := dbms_spm.load_plans_from_cursor_cache( -

sql_id => '&hinted_SQL_ID', -

plan_hash_value => &hinted_plan_hash_value, -

sql_handle => '&sql_handle_for_original');

6 - Verificar que la baseline fue agregada

SELECT * FROM dba_sql_plan_baselines WHERE signature IN (SELECT force_matching_signature FROM dba_hist_sqlstat WHERE sql_id='&original_sql_id')

7 - Dropear o deshabilitar el plan de ejecución original de la sentencia sin hints

exec :res :=DBMS_SPM.DROP_SQL_PLAN_BASELINE ('&original_sql_handle','&original_plan_name');

8 - (OPCIONAL) Sacar la sentencia de la shared pool solo en caso que siga tomando el plan original

DECLARE

  name varchar2(50);

  version varchar2(3);

BEGIN

  select regexp_replace(version,'\..*') into version from v$instance;

  if version = '10' then

    execute immediate

      q'[alter session set events '5614566 trace name context forever']'; -- bug fix for 10.2.0.4 backport

  end if;

  select address||','||hash_value into name

  from v$sqlarea

  where sql_id like '&original_sql_id';

  sys.dbms_shared_pool.purge(name,'C',1);

END;

/

9 - Ejecutar la sentencia desde la aplicación

10 - verificar que tome la baseline

select  SQL_ID,SQL_PROFILE,SQL_PLAN_BASELINE,PLAN_HASH_VALUE,EXECUTIONS,ELAPSED_TIME,ELAPSED_TIME/1000000/EXECUTIONS,DISK_READS,OPTIMIZER_COST  from V$sql where sql_id='&original_sql_id';

11 - (OPCIONAL) Si se considera que el plan con hints es el plan mas optimo modificar el la baseline para que este FIXED (no seguira capturando baselines para esta sentencia particular, en oportunidades cierto tipo de sentencias capturan demasiados planes lo cual no es recomendado. Por esta razon en ocaciones he tenido que deshabilitar la captura de nuevos planes para el sql_handle asociados al sql_id original).

DECLARE

  l_plans_altered  PLS_INTEGER;

BEGIN

  l_plans_altered := DBMS_SPM.alter_sql_plan_baseline(

    sql_handle      => '&sql_handle_for_original'',

    plan_name       => '& hinted_plan_name'',

    attribute_name  => 'fixed',

    attribute_value => 'YES');

  DBMS_OUTPUT.put_line('Plans Altered: ' || l_plans_altered);

END;

/

Referencias:

Loading Hinted Execution Plans into SQL Plan Baseline. (Doc ID 787692.1)