OTN Appreciation Day : SQL Profiles

Los SQL Profiles son objetos de la base de datos persistentes en el diccionario de datos. Estos objetos son diferentes a otros objetos de la base porque no se crean con la sentencia "CREATE" como "CREATE TABLE" o "CREATE INDEX", estos objetos tienen que ser generados.
El objetivo de los SQL Profiles es mejorar la performance de los SQL sin modificar el código, y son implementados usando HINTS. Si bien con la vista dba_sql_profiles podemos ver los SQL Profiles creados no hay ninguna vista para ver los HINTS que continen.
Los SQL Profiles una vez generados tienen que ser aceptados para su implementación.
Un SQL Profile se genera e implementa para una sentencia SQL especifica. Una vez implementado, cada vez que ejecuta esa sentencia SQL el optimizador usa el SQL Profile para dicha sentencia y genera un mejor plan de ejecución.
Inicialmente fueron creados por Oracle en 10g para que sean generados por el SQL Tuning Advisor. Luego Carlos Sierra desarrollo un script coe_xfr_sql_profile dentro de su herramienta SQLT que genera SQL Profiles.

Se usan para dos situaciones diferentes:

1 - Cuando tenemos un problema de un SQL con alto consumo de recursos y el plan de ejecución mejoraría con un ajuste de estadisticas, cuando invocamos al SQL Tuning Advisor va a generar un SQL Profile.

2 - Cuando tenemos una sentecia SQL que cambia el elapsed time de un dia para el otro pasando a aumentarlo dramaticamente, el script coe_xfr_sql_profile encuentra el mejor plan de ejecución anterior en el AWR y genera un SQL Profile.

Hay varias diferencias entre los SQL Profiles generados por el SQL Tuning Advisor y los generados por el script coe_xfr_sql_profile.

SQL Profiles generados por SQL Tuning Advisor 
- Son HINTS que ajustan las estadisticas para que el optimizador elija un mejor plan
- Son generados por el ATO (Automatic Tuning Optimizer) que es el optimizador en modo Tuning
- Tiene total dependencia con las estadisticas, si cambian las estadisticas ya quedan obsoletos estos ajustes y el optimizador puede elegir otro plan

SQL Profiles generados por el script coe_xfr_sql_profile
- Son Hints para reproducir un plan de ejecución específico
- Son generados por el script coe_xfr_sql_profile desde el mejor plan de ejecución para esa sentencia que encuentra en el AWR
- No tiene dependencia con las estadisticas, si cambian las estadisticas igual va a repodrocir el mismo plan

Para saber si un SQL esta usando un SQL Profile podemos verlo en su plan de ejecución en la sesion NOTE
Ejemplo:

SQL> select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------------------------

SQL_ID g3wubsadyrt37, child number 1

-------------------------------------

select count(*) from plan_stability where owner='SYS'

Plan hash value: 363261562

-------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |

-------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT | | | | 2476 (100)| |

| 1 | SORT AGGREGATE | | 1 | 6 | | |

|* 2 | TABLE ACCESS FULL| PLAN_STABILITY | 23092 | 135K| 2476 (1)| 00:00:30 |

-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

2 - filter("OWNER"='SYS')

Note

-----

- SQL profile coe_g3wubsadyrt37_363261562 used for this statement

El SQLT se puede bajar desde MOS
Using Sqltxplain to create a 'SQL Profile' to consistently reproduce a good plan (Doc ID 1487302.1)
o del Blog de Carlos Sierra
https://carlos-sierra.net/2012/04/09/custom-sql-profile-and-plan-stability-on-10g/

La otra forma de generar SQL Profiles manualmente no documentada por Oracle es importando los HINTS con el DBMS_SQLTUNE.IMPORT_SQL_PROFILE.

Este es mi post en agradecimiento a OTN, OTN Appreciation Day es una iniciativa de Tim Hall https://oracle-base.com/blog/2016/09/28/otn-appreciation-day/