DBA Junior

Use SQL Tuning Advisor

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

2. SQL Tuning Advisor es una característica de Oracle que tiene dos aproximaciones. Podemos utilizarlo de forma reactiva cuando tenemos un problema en la BD. Supongamos que hemos detectado una o varias sentencias problemáticas y lanzamos el Advisor manualmente para que nos genere un SQL Profile (no confundir con Stored Outlines o Hints) que aporta información adicional al optimizador para escoger mejores planes. Cuando tenemos el SQL Profile, lo aceptamos como válido y a partir de ahí las siguientes ejecuciones de esa o esas consultas pueden verse muy beneficiadas, sobretodo en tiempo de ejecución o DB Time.

La segunda forma de sacar el máximo partido a SQL Tuning Advisor es más proactiva. Existe una subtarea dentro de la tareas automáticas de mantenimiento (AUTOTASKS) que lanza el SQL Tuning Advisor para aquellas sentencias que provoquen un alto consumo de recursos. El origen de está sentencias es el repositorio AWR. A esta tarea se le conoce como Automatic SQL Tuning, y corre dentro del subplan ORA$AUTOTASK_SUB_PLAN dentro del plan DEFAULT_MAINTENANCE_PLAN.

3. El siguiente ejercicio es un ejemplo muy interesante de optimización de una consulta con SQL Tuning Avisor con Enterprise Manager. Vamos a configurar AWR para sacar informes cada 2 minutos, y luego vamos a ejecutar una sentencia con muy mal rendimiento para generar un SQL Profile y que las próximas ejecuciones sean mucho más rápidas.

-- Generamos un SNAPSHOT de AWR
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

-- Modificamos la configuración de AWR para generar informes AWR cada 2 minutos
-- El evento 13508 nos permite utilizar utilizar valores para RETENTION e INTERVAL muy bajos
ALTER SESSION SET EVENTS '13508 TRACE NAME CONTEXT FOREVER, LEVEL 1';
EXECUTE DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(INTERVAL => 2,RETENTION => 480);

-- Lanzamos el siguiente script para generar carga en la BD
-- Para obtener recomendacion de ADDM Advisor, lanzamos el msimo código en varias sesiones al mismo tiempo (2-4 sesiones)
DECLARE
 N NUMBER;
BEGIN
FOR I IN 1..100000 LOOP
 SELECT /*+ ORDERED USE_NL(C) FULL(C) FULL(S)*/ COUNT(*) INTO N
 FROM SH.SALES S, SH.CUSTOMERS C
 WHERE C.CUST_ID = S.CUST_ID AND CUST_FIRST_NAME='Sarah'
 ORDER BY TIME_ID;
 DBMS_LOCK.SLEEP(1);
END LOOP;
END;
/

Tenemos varias formas de identificar la sesión o SQL problemática desde Enterprise Manager.

Una vez en la instancia OCM ->
-> Click «Performance» ->
-> Veremos como la carga de trabajo sube en la gráfica «Average Active Sessions» ->
-> Al cabo de unos minutos nos aparece un icono de un portapeles por debajo de la gráfica con el informe ADDM ->
-> Aunque accedemos por otra vía ->
-> Click «Advisor Central» ->
-> Click en el último informe, por Ej. ADDM:2196200734_1_1348 ->
-> Click «Top SQL Statements» ->
-> Vemos la sentencia con SQL_ID = «80j9sn3ajqgc9» ->
-> Click «Run Advisor Now» ->
-> Después de unos minutos aparece la página con las recomendaciones ->
-> Click icono de gafas de sol debajo de «Compare Explain Plans» ->
-> Observamos las diferencias ->
-> Click «Recommendations for SQL ID:80j9sn3ajqgc9» ->
-> Click «Implement» ->
-> Click «Yes»

Cancelamos las ejecuciones que teniamos abiertas (CTRL-C) y volvemos a lanzarlas. Veremos una caída drástica de la gráfica de «Average Active Sessions».

-- Después de cancelar la ejecución, lanzamos el mismo número de sesiones en paralelo como hemos hecho antes
-- Una vez pasados unos minutos y hayamos comprobado que la gráfica ha bajado, podemos cancelar las sesiones
DECLARE
 N NUMBER;
BEGIN
FOR I IN 1..100000 LOOP
 SELECT /*+ ORDERED USE_NL(C) FULL(C) FULL(S)*/ COUNT(*) INTO N
 FROM SH.SALES S, SH.CUSTOMERS C
 WHERE C.CUST_ID = S.CUST_ID AND CUST_FIRST_NAME='Sarah'
 ORDER BY TIME_ID;
 DBMS_LOCK.SLEEP(1);
END LOOP;
END;
/

Borramos el plan para seguir haciendo otros ejercicios.

En la página principal de la instancia OCM ->
-> Click «Search SQL» ->
-> SQL ID = «80j9sn3ajqgc9» ->
-> Si no aparece ninguna, podemos incluir en la búsqueda «AWR Snapshots» ->
-> Click «80j9sn3ajqgc9»
-> Click «Plan Control» ->
-> Seleccionamos el profile ->
-> Click «Delete» ->
-> Click «Yes»

4. Vamos a ver como realizar el mismo trabajo con el paquete DBMS_SQLTUNE.

-- Preparamos la tarea
DECLARE
 my_task_name VARCHAR2(30);
 my_sqltext   CLOB;
BEGIN
 my_sqltext := 'SELECT /*+ ORDERED USE_NL(C) FULL(C) FULL(S)*/ COUNT(*) '  ||
               'FROM SH.SALES S, SH.CUSTOMERS C  '                         ||
               'WHERE C.CUST_ID = S.CUST_ID AND CUST_FIRST_NAME=''Sarah'' '  ||
               'ORDER BY TIME_ID';
 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
         sql_text    => my_sqltext,
         user_name   => 'SH',
         scope       => 'COMPREHENSIVE',
         time_limit  => 30,
         task_name   => 'TASK_TEST',
         description => 'Exercise for OCM Exam');
END;
/

-- Ejecutamos SQL Tuning Advisor
BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'TASK_TEST');
END;
/

-- Podemos ver el estado de la tarea con esta consulta
SELECT * FROM DBA_ADVISOR_LOG WHERE TASK_NAME='TASK_TEST';

-- También podemos obtener el progreso de la tarea a través de la vista V$ADVISOR_PROGRESS
SELECT SOFAR, TOTALWORK FROM V$ADVISOR_PROGRESS WHERE TASK_ID =
  (SELECT TASK_ID FROM DBA_ADVISOR_TASKS WHERE TASK_NAME='TASK_TEST');

-- Obntemos el informe de la tarea
SET LONG 10000000
SET PAGES 512 LINES 512
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'TASK_TEST') FROM DUAL;

-- Nos recomienda aceptar el SQL Profile
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(TASK_NAME => 'TASK_TEST', TASK_OWNER => 'SYS', REPLACE => TRUE);

-- Revisamos el SQL Profile creado
SELECT * FROM DBA_SQL_PROFILES WHERE TASK_ID =
  (SELECT TASK_ID FROM DBA_ADVISOR_TASKS WHERE TASK_NAME='TASK_TEST');

-- Boramos el SQL Profile
EXEC DBMS_SQLTUNE.DROP_SQL_PROFILE ('SYS_SQLPROF_013f8a26713b0002');

-- Volvemos a dejar la configuración previa de AWR
EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(RETENTION => 43200, INTERVAL => 15, TOPNSQL => 100);

-- Eliminamos el evento que fijamos temporalmente
ALTER SESSION SET EVENTS '13508 TRACE NAME CONTEXT OFF';

5. Ya hemos hablado de la tarea de mantenimiento aumática de SQL Tuning Advisor. Está siempre activada por defecto, pero los SQL Profiles que se encuentran se habilitan a no ser que modificamos el parámetro ACCEPT_SQL_PROFILES.

-- Podemos ver el informe y modificar el parámetro ACCEPT_SQL_PROFILES a través del paquete DBMS_AUTO_SQLTUNE
-- Revisamos el informe
VARIABLE my_rept CLOB;
BEGIN
  :my_rept :=DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK(
    begin_exec   => NULL,
    end_exec     => NULL,
    type         => 'TEXT',
    level        => 'TYPICAL',
    section      => 'ALL',
    object_id    => NULL,
    result_limit => NULL);
END;
/
PRINT :my_rept

-- Habilitamos la aprobación de los SQL Profiles de forma automática
-- Mucho cuidado de activar esto en producción, que puede tener un resultado no deseado
BEGIN
  DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
    parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');
END;
/

-- Para desactivarlo, es prácticamente el mismo comando
BEGIN
  DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
    parameter => 'ACCEPT_SQL_PROFILES', value => 'FALSE');
END;
/
Fortunas y adversidades de un DBA Oracle
Linkedin Github

Raúl Ibáñez Peral © CC BY 4.0