Ejemplo práctico con SQL Tuning Advisor y SQL Access Advisor

Parte del perfil de un administrador de base de datos es el afinamiento de sentencias SQL, el afinamiento (o tuning) nos ayuda a mejorar el rendimiento de sentencias SQL dentro de nuestras bases de datos, mejorando: tiempos de respuesta de aplicaciones, tiempo de respuesta de procesos batch, consumo de recursos del sistema, escalabilidad de las aplicaciones.

En Oracle, la ejecución de una sentencia SQL requiere tres fases: parseo, enlace y ejecución (parse, bind and execute). La parte de parseo es la que nos interesa para fines de este artículo. En esta, nuestro servidor interpreta lo que la sentencia significa y cuál es la mejor manera de ejecutarla. El parseo involucra interacción con el segmento de memoria SHARED POOL dentro de la instancia de la base de datos. Las estructuras del SHARED POOL convierten el SQL en algo ejecutable mediante un plan de ejecución.

Plan de ejecución: es el resultado de la fase de parseo, indica la forma en cómo se va a acceder a los datos solicitados.

Estadísticas: cuantifican las características de distribución y almacenamiento de datos de tablas, columnas, índices y particiones. En pocas palabras por ejemplo: nos dicen cuál es la cantidad de filas en una tabla, cual es el valor máximo de una columna, cual es el valor mínimo de una columna, que valores se repiten más en una columna etc. Las estadísticas se almacenan en el diccionario de datos.

El optimizador de Oracle hace uso de las estadísticas para generar un plan de ejecución lo más óptimo posible. Además de las estadísticas evalúa el acceso a la información: la cantidad de índices de una tabla, lo selectivo que puede ser un índice, la cordialidad de los datos, etc.

Después de definir estos conceptos vamos a lo que nos concierne :)

Comenzaremos definiendo estos términos propios de Oracle:

• SQL Profile: provee información adicional al optimizador que ayuda a seleccionar el mejor plan de ejecución, no fuerzan al optimizador a elegir un plan específico sino actúan como estadísticas adicionales auxiliares a las ya obtenidas por tabla, índice o partición.
• SQL Plan Baselines: Es un conjunto de planes de ejecución que el optimizador tiene permitido utilizar para una sentencia SQL. La base de datos acepta un plan dentro del SQL Plan Baseline después de verificar que el plan tiene un buen desempeño.
• SQL Tuning Sets: Es un objeto de base de datos que contiene metadata de un conjunto de sentencias SQL. El objetivo es agrupar sentencias SQL y sus datos asociados para propósitos específicos de afinamiento. 

SQL Tuning Advisor

Permite el análisis de sentencias individuales, el top de sentencias ejecutadas en nuestra base de datos o SQL Tuning sets, entre las recomendaciones de esta herramienta estarán:

• Análisis de estadísticas: comprueba la existencia de estadísticas o estadísticas vencidas y recomiendo refrescarlas o crearlas.
• Perfilado de SQL: recolecta estadísticas auxiliares y las almacena en un SQL Profile que sugiere para la ejecución de la sentencia evaluada.
• Rutas de acceso: analiza la creación de índices, vistas materializadas o particiones de tabla.
• Análisis de estructura: reestructura la sentencia SQL buscando la generación de un mejor plan de ejecución. 

El API  de SQL Tuning Advisor hace uso de los siguientes procedimientos dentro del paquete DBMS_SQLTUNE:

• CREATE_TUNING_TASK: crea la tarea para evaluación de una sentencia SQL individual o un SQL Tuning Set.
• EXECUTE_TUNING_TASK: ejecuta una tarea creada previamente con CREATE_TUNING_TASK.
• REPORT_TUNING_TAS: muestra el resultado y las recomendaciones del SQL Tuning Advisor.

Ejemplo:

Para este ejemplo haremos uso de la tabla TABLE WA_ANTIGUEDAD_SALDOS

SQL> desc SYSDM.WA_ANTIGUEDAD_SALDOS

Name                                      Null?    Type                       

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

 ENTDATE                                            DATE                       

 FECHA_REF                                 NOT NULL DATE                       

 OHXACT                                    NOT NULL NUMBER                     

 OHSTATUS                                           VARCHAR2(2)                

 OHENTDATE                                          DATE                       

 OHREFNUM                                           VARCHAR2(30)               

 CUSTOMER_ID                                        NUMBER                     

 OHREFDATE                                          DATE                       

 OHDUEDATE                                          DATE                       

 OHIPP                                              VARCHAR2(6)                

 OHGLAR                                             VARCHAR2(30)               

 OHINVAMT_DOC                                       FLOAT(126)                 

 OHOPNAMT_DOC                                       FLOAT(126)                 

 UPDATED_DATE                                       DATE                       

 OHCOSTCENT                                         NUMBER

Llave primaria:

FECHA_RED

OHXACT

Índices:

CUSTOMER_ID

Numero de filas: 77,623,086

Nos ha reportado problemas en la ejecucion de sentencias similares a esta: 

select * from WA_ANTIGUEDAD_SALDOS where OHREFNUM = 'SH0064611'

Paso 1. Definiendo la tarea de evaluación de sentencia:

Declare

tune_task_name varchar2(30);

bad_sql_stmt clob;

begin

    bad_sql_stmt := 'select * from WA_ANTIGUEDAD_SALDOS where OHREFNUM = ''SH0064611''';

    tune_task_name := dbms_sqltune.create_tuning_task

    (sql_text => bad_sql_stmt,

    user_name => 'JAYAPAN',

    scope => 'COMPREHENSIVE',

    time_limit => 60,

    task_name => 'sql_tuning_test',

    description => 'Optimizar sentencia SELECT'

    );

end;

/

Resultado:

PL/SQL procedure successfully completed.

Parámetros:

sql_text: sentencia a analizar

user_name: usuario dueño de la tarea

scope: puede ser LIMITED que no recomendara SQL Profiles o COMPREHENSIVE que realiza un análisis completo (puede tomar más tiempo)

time_limit: tiempo límite de la tarea en ejecución (segundos)

task_name: nombre con el que conoceremos a la tarea

description: breve descripción de la tarea

Paso 2. Ejecutando la tarea creada:

Begin

    dbms_sqltune.execute_tuning_task

    (task_name => 'sql_tuning_test');

end;

/

Resultado:

PL/SQL procedure successfully completed.

Paso 3. Podemos verificar el estado de la tarea mediante:

select task_name, status, sofar, totalwork

from dba_advisor_tasks

join v$advisor_progress using(task_id)

where task_name = 'sql_tuning_test';

/

Resultado:

TASK_NAME                      STATUS           SOFAR  TOTALWORK

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

sql_tuning_test                COMPLETED            1          1

1 row selected.

Paso 4. Cuando el estatus de la tarea sea COMPLETED, podemos visualizar las recomendaciones del SQL Tuning Advisor:

Select dbms_sqltune.report_tuning_task('sql_tuning_test') from dual;

Resultado:

GENERAL INFORMATION SECTION

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

Tuning Task Name                  : sql_tuning_test

Tuning Task Owner                 : JAYAPAN

Scope                             : COMPREHENSIVE

Time Limit(seconds)               : 15

Completion Status                 : COMPLETED

Started at                        : 05/02/2016 15:42:37

Completed at                      : 05/02/2016 15:42:47

Number of Index Findings          : 1

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

Schema Name: JAYAPAN

SQL ID     : 06kh22q0x7nu3

SQL Text   : select * from WA_ANTIGUEDAD_SALDOS where OHREFNUM = 'SH0064611'

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

FINDINGS SECTION (1 finding)

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

1- Index Finding (see explain plans section below)

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

  El plan de ejecución de esta sentencia se puede mejorar mediante la creación

  de uno o más índices.

  Recommendation (estimated benefit: 99,99%)

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

  - Puede ejecutar el Asesor de Acceso para mejorar el diseño del esquema

    físico o crear el índice recomendado.

    create index SYSADM.IDX$$_83060001 on

    SYSADM.WA_ANTIGUEDAD_SALDOS("OHREFNUM");

  Rationale

  ---------

    La creación de índices recomendados mejora significativamente el plan de

    ejecución de esta sentencia. Sin embargo, puede ser preferible ejecutar el

    "Asesor de Acceso" mediante una carga de trabajo SQL representativa en

    contraposición a una única sentencia. Esto permitirá obtener

    recomendaciones de índice globales que tienen en cuenta la sobrecarga de

    mantenimiento de índice y el consumo de espacio adicional.

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

EXPLAIN PLANS SECTION

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

1- Original

-----------

Plan hash value: 2224838835

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

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

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

|   0 | SELECT STATEMENT  |                      |    15 |  1755 |   332K  (1)|01:06:26 |

|*  1 |  TABLE ACCESS FULL| WA_ANTIGUEDAD_SALDOS |    15 |  1755 |   332K  (1)|01:06:26 |

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

Predicate Information (identified by operation id):

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

   1 - filter("OHREFNUM"='SH0064611')

2- Using New Indices

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

Plan hash value: 3782076775

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

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

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

|   0 | SELECT STATEMENT            |                      |    15 |  1755 |   11   (0)| 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| WA_ANTIGUEDAD_SALDOS |    15 |  1755 |   11   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | IDX$$_83060001       |    15 |       |    4   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

   2 - access("OHREFNUM"='SH0064611')

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

Análisis del resultado:

SQL Tuning advisor recomienda la creación de un índice sobre la columna OHREFNUM utilizada en la sentencia. Ofrece una vista del plan de ejecución usado actualmente (1- Original) y el plan de ejecución que se utilizaría si se crea el índice que recomienda (2- Using New Indices).

Además, proporciona un beneficio estimado 99,99% al aplicar las recomendaciones sugeridas.

SQL Access Advisor

Permite el análisis de desempeño de cargas de trabajo específicas. Estas cargas pueden ser: una sentencia SQL, un SQL Tuning Set, el contenido SQL actual en memoria, estadísticas, o las sentencias generadas en un esquema único. Entre las recomendaciones de esta herramienta estarán:

• Creación de nuevos índices.
• Creación de vistas materializadas.
• Creación de particiones en tablas.
• Actualización de estadísticas.

Para utilizar SQL Access Advisor haremos uso del paquete DBMS_ADVISOR.

Ejemplo:

Para este ejemplo utilizaremos las tablas OPERATORS_MONEY_TRANSACTIONS y POINTS_OF_SALES_BATCHES:

SQL> desc VVS_WAP.OPERATORS_MONEY_TRANSACTIONS

Name                                      Null?    Type

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

ID                                        NOT NULL NUMBER(10)

POS_BATCH_ID                              NOT NULL NUMBER(10)

OPERATOR_ID                               NOT NULL NUMBER(10)

TYPE                                      NOT NULL NUMBER(4)

STATUS                                    NOT NULL NUMBER(4)

TARGETMSISDN                              NOT NULL VARCHAR2(20)

AMOUNT                                    NOT NULL NUMBER(15,2)

TRANSACTIONDATE                           NOT NULL DATE

OPERATIONID                                        VARCHAR2(50)

REGION_CODE_ID                                     NUMBER(10)

AMOUNT_ID                                          NUMBER(10)

RETURNCODE                                         NUMBER(3)

EXTERNALRETURNCODE                                 VARCHAR2(20)

RESPONSETIME                              NOT NULL NUMBER(10)

VOUCHERCODE                                        VARCHAR2(30)

EXTERNAL_TRANSACTION_ID                            VARCHAR2(24)

RELOADEDAMOUNT                            NOT NULL NUMBER(15,2)

LASTUPDATE                                         DATE

REFERENCE_TRANSACTION_ID                           NUMBER(10)

DEALER_PAYMENT_CYCLE_ID                            NUMBER(10)

DISTRIBUTOR_PAYMENT_CYCLE_ID                       NUMBER(10)

Llave primaria:

ID

Índices:

Las columnas más utilizadas tiene un índice asociado

Numero de filas: 73,007,761

SQL> desc VVS_WAP.POINTS_OF_SALE_BATCHES

 Name                                      Null?    Type

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

ID                                        NOT NULL NUMBER(10)

SEQUENCE                                  NOT NULL NUMBER(11)

POS_ID                                    NOT NULL NUMBER(10)

STATUS                                    NOT NULL NUMBER(4)

BATCHOPENDATE                             NOT NULL DATE

BATCHOPENER                                        NUMBER(10)

BATCHCLOSEDATE                                     DATE

BATCHCLOSER                                        NUMBER(10)

PARTITION_ID                              NOT NULL NUMBER(2)

Llave primaria:

ID

Índices:

POS_ID,BATCHOPENER,BATCHCLOSER,TRUNC(“BATCHCLOSEDATE”)

Numero de filas: 60,680,237

Nos han reportado que el tiempo de la siguiente consulta es demasiado elevado por lo que vamos a analizarla utilizando SQL Access Advisor:

SELECT O.operator_id, 
       POSB.pos_id, 
       Trunc (O.transactiondate) TRANSACTIONDATE, 
       Sum (O.amount)            AS AMOUNT_SUM, 
       Count (*)                 AS QNT_TRANS1, 
       Count (O.amount)          AS QNT_TRANS2 
FROM   vvs_wap.operators_money_transactions O 
       INNER JOIN vvs_wap.points_of_sale_batches POSB 
               ON POSB.id = O.pos_batch_id 
WHERE  O.status IN ( 1, 4 ) 
GROUP  BY O.operator_id, 
          POSB.pos_id, 
          Trunc (O.transactiondate); 

Paso 1. Evaluando la sentencia:

Declare

sql_stmt varchar(500);

begin

sql_stmt := q'~SELECT O.OPERATOR_ID,

POSB.POS_ID,

TRUNC (O.TRANSACTIONDATE) TRANSACTIONDATE,

SUM (O.AMOUNT) AS AMOUNT_SUM,

COUNT (*) AS QNT_TRANS1,

COUNT (O.AMOUNT) AS QNT_TRANS2

FROM VVS_WAP.OPERATORS_MONEY_TRANSACTIONS O

INNER JOIN VVS_WAP.POINTS_OF_SALE_BATCHES POSB ON POSB.ID = O.POS_BATCH_ID

WHERE O.STATUS IN (1, 4) GROUP BY O.OPERATOR_ID, POSB.POS_ID, TRUNC (O.TRANSACTIONDATE)~';

dbms_advisor.quick_tune

(dbms_advisor.sqlaccess_advisor,

'prueba_access_advisor',

sql_stmt

);

end;

/

Resultado:

PL/SQL procedure successfully completed.

La función solo necesita un nombre para la tarea y la sentencia a analizar. Contrario al SQL Tuning Advisor, la tarea se ejecuta inmediatamente.

Paso 2. Visualizando el resultado del análisis:

begin  

dbms_advisor.create_file

(dbms_advisor.get_task_script('prueba_access_advisor'),

'TUNE_SCRIPTS',

'RESUL_ACCESS_ADV.sql'

);

end;

/

Resultado:

PL/SQL procedure successfully completed.

En este utilizamos dos funciones, get_task_script con el cual obtenemos el resultado de prueba_access_advisor y créate_file con la que escribimos a un archivo sql el resultado de la tarea.

Paso 3. El archivo RESUL_ACCES_ADV.sql:

Rem SQL Access Advisor: Version 11.1.0.6.0 – Production

Rem

Rem Username: RJB

Rem Task: second_rjb_tuning_task

Rem Execution date:

Rem

CREATE MATERIALIZED VIEW LOG ON

"VVS_WAP"."OPERATORS_MONEY_TRANSACTIONS"

WITH ROWID, SEQUENCE (AMOUNT,OPERATOR_ID,POS_BATCH_ID,STATUS,TRANSACTIONDATE)

INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON

"VVS_WAP"."POINTS_OF_SALE_BATCHES"

WITH ROWID, PRIMARY KEY, SEQUENCE (POS_ID)

INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW "VVS_WAP"."MV$$_0BDC0000"

REFRESH FAST WITH ROWID

ENABLE QUERY REWRITE

AS SELECT O.OPERATOR_ID,

         POSB.POS_ID,

         TRUNC (O.TRANSACTIONDATE) TRANSACTIONDATE,

         SUM (O.AMOUNT) AS AMOUNT_SUM,

         COUNT (*) AS QNT_TRANS1,

         COUNT (O.AMOUNT) AS QNT_TRANS2

    FROM OPERATORS_MONEY_TRANSACTIONS O

         INNER JOIN POINTS_OF_SALE_BATCHES POSB ON POSB.ID = O.POS_BATCH_ID

   WHERE O.STATUS IN (1, 4)

GROUP BY O.OPERATOR_ID, POSB.POS_ID, TRUNC (O.TRANSACTIONDATE);

begin

dbms_stats.gather_table_stats

('"RJB"','"MV$$_0BDC0000"',NULL,dbms_stats.auto_sample_size);

end;

/

 Análisis del resultado:

SQL Access Advisor retornara sus recomendaciones en forma de instrucciones SQL que deben ser ejecutadas en la base de datos, es por esta razón que es preferible enviar el resultado de la tarea de tuning a un archivo .SQL, en nuestro ejemplo la recomendación es crear una vista materializada.

Una vista materializada, contrario a una vista normal, almacena datos en la base de datos y lo hace a través de los objetos VIEW LOGS que se crear sobre las tablas que se usan en la consulta.

En resumen SQL Access Advisor sugiere: crear VIEW LOGS en las tablas involucradas, crear una visa materializada y finalmente actualizar estadísticas sobre esa vista materializada.

Conclusiones

Como pudimos observar, el funcionamiento de ambas herramientas es similar y las recomendaciones pueden incluir los mismos objetos. La diferencia principal es que, cuando utilizamos SQL Sets o el top de sentencias SQL en memoria, SQL Tuning advisor analiza cada sentencia por separado mientras SQL Access Advisor analiza todas las sentencias en simultaneo. 

Otro punto importante de mencionar es que el resultado de estas herramientas son sugerencias al final y que aunque demuestran con números y porcentajes la mejora en la ejecución siempre quedara a criterio del DBA si aplica o no el resultado.

La optimización de sentencias no debe ser una tarea específica del DBA, desarrolladores o encargados de pruebas de estrés pueden ejecutar estos procedimientos. Bastara con otorgarles privilegios a los paquetes DBMS_SQLTUNE y DBMS_ADVISOR.

Finalmente, las pruebas vistas aquí fueron ejecutadas en ambientes reales de producción en donde las recomendaciones fueron ejecutadas y actualmente puedo decir que realmente mejoraron el rendimiento de los sistemas a los que pertenecen.