Que es SQL Tuning Health-Check (SQLHC)?

SQL Tuning Health-Check, también conocido como SQLHC, es una herramienta de tuning SQL basada en scripts y desarrollada por Oracle Server Technologies Center of Expertise. Es un subconjunto de los SQL  utilizados por SQLTXPLAIN (SQLT), otra herramienta desarrollada por Carlos Sierra y que Oracle utiliza para diagnosticar sentencias SQL con bajo rendimiento.

SQLHC se utiliza fundamentalmente  para analizar y verificar el entorno en el cual se ejecuta una sentencia SQL en particular, verificando distintos factores como  ser, estadísticas de optimizador(CBO), metadatos de objetos, parámetros de configuración y demás elementos que pueden influir en la performance  de la sentencia SQL que está siendo analizada.

El objetivo principal es permitir que los usuarios puedan detectar y evitar los problemas previsibles,  que puedan afectar el rendimiento en la ejecución de un SQL,  garantizando de esta manera un entorno de ejecución lo más óptimo posible para un determinado query SQL.

Este utilitario es totalmente gratuito (FREE), pero debemos tener en cuenta que de acuerdo a las opciones que tenemos licenciadas en nuestra base de datos target, vamos a poder obtener menor o mayor información como resultado de la ejecución del script. Al ejecutar la herramienta tendremos que especificar alguna de las diferentes opciones de licenciamiento:

Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)

·         Tuning Pack (T)

·         Diagnostic Pack (D)

·         None (N) ninguna de las opciones disponibles.

En el caso de no pasar este parámetro (licenciamiento) durante la ejecución, nos lo será requerido de forma mandatoria:

SQL>@sqlhc 0j7vkwyr0a2st

Parameter 1:

Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)

 En el caso de no tener licenciado los “Packs” de “Diagnostic” o “Tuning”, que es lo que nos va a faltar en el reporte obtenido?

Bien, como SQLHC utiliza packages para acceder a la información basada en AWR (Automatic Workload Repository) (la información de AWR está accesible vía Diagnostic Pack; y Tuning Pack es un superconjunto de Diagnostic Pack, por lo cual va a proporcionar acceso a esta información) y

la infraestructura de AWR se instala por defecto (ya que la base de datos utiliza información AWR para fines internos) el acceso va a ser posible. La verdadera pregunta aquí es si se tiene o no licencia para usarlo ya que a partir de allí comenzaran los problemas en caso de una auditoría..

Las secciones del informe que usan información de AWR están marcadas con la frase  'captured by AWR’.

Estas secciones actualmente incluyen: "Resumen de índices", "Estadísticas SQL históricas (DBA_HIST_SQLSTAT)" y "Planes de ejecución históricos",

Si la información AWR no está disponible debido a no tener licenciado  alguno de los paquetes que anteriormente mencionamos, entonces NO habrá información relacionada a AWR en estas secciones.

Algo que resulta interesante de esta herramienta, es que el análisis lo hace sin dejar huellas (foot prints) en la base de datos. Esto asegura poder ejecutarlo en cualquier sistema sin problemas, incluso en ambientes productivos.

Al finalizar su ejecución, el script realiza un rollback, por lo cual ningún cambio realizado es almacenado en la base. Incluso la propia utilización de la plan_table.

Cuando se ejecuta indicando un determinado SQL_ID, el script genera un informe HTML con los resultados de una serie de comprobaciones de estado en torno a la sentencia SQL proporcionada.

Para obtener el SQL_ID de una sentencia SQL, podemos utilizar un informe AWR/ASH o bien utilizando la vista V$SQL (aqui explico cómo).

Como comentaba, puede ser tranquilamente ejecutado en ambientes productivos y es una excelente manera de chequear SQL complejos independientemente del tamaño de la consulta o aplicación  que se esté utilizando.

Para tener en cuenta, es posible utilizar la herramienta en cualquier versión y edición de Oracle Database, pero solo a partir de 10g. De 9i para atrás no funciona.. Para todos los casos el script a ejecutar va a ser el mismo.

Los chequeos que son realizados por el script son a grandes rasgos los siguientes:

CBO Statistics for schema objects accessed by the one SQL statement being analyzed

CBO Parameters

CBO System Statistics

CBO Data Dictionary Statistics

CBO Fixed-objects Statistics

En cuanto a requerimientos:

Nada especial, el script debe ser ejecutado desde SQL*Plus conectado como SYS, DBA o algún usuario con acceso a las vistas del diccionario de datos.

No es necesaria ninguna configuración adicional.

El procedimiento de ejecución es bastante sencillo:

1-     Iniciar sesión en el servidor de la base de datos y setear el ambiente necesario para la instancia de la base de datos en cuestión.

2-     Descargar el archivo "sqlhc.zip"  y descomprimirlo en el directorio que consideremos conveniente.

3-     Conectarnos vía  SQL*Plus como SYS, alguna cuenta DBA, o un usuario con acceso a las vistas del Data Dictionary  y simplemente  ejecutar el script  "sqlhc.sql".

Requiere que indiquemos dos parámetros:

i-              Oracle Pack License (Tuning, Diagnostics or None) [T|D|N] (required)
Si el sitio tiene licenciados ambos (Tuning and Diagnostics), especificamos T  (Oracle Tuning pack incluye Oracle Diagnostics)

ii-             SQL_ID valido correspondiente al SQL que deseamos analizar.

Ejemplo:

[oracle@server01 sqlhc]$ sqlplus / as sysdba
SQL> @sqlhc.sql N 0j7vkwyr0a2st 

Una vez ejecutado el script, se habrá generado en el directorio desde lo corrimos un archivo .zip:

-rw-r--r--. 1 oracle oinstall 145733 Oct 12 15:12 sqlhc_20171012_151125_0j7vkwyr0a2st.zip

Este archive zip contiene adentro, archivos .html con los reportes correspondientes y otros .zip con logs y csv (contenido de distintas vistas del diccionario de datos).

[oracle@server01 sqlhc]$ unzip sqlhc_20171012_151125_0j7vkwyr0a2st.zip

Archive:  sqlhc_20171012_151125_0j7vkwyr0a2st.zip

  inflating: sqlhc_20171012_151125_0j7vkwyr0a2st_1_health_check.html

  inflating: sqlhc_20171012_151125_0j7vkwyr0a2st_2_diagnostics.html

  inflating: sqlhc_20171012_151125_0j7vkwyr0a2st_3_execution_plans.html

  inflating: sqlhc_20171012_151125_0j7vkwyr0a2st_4_sql_detail.html

 extracting: sqlhc_20171012_151125_0j7vkwyr0a2st_9_log.zip

 extracting: sqlhc_20171012_151125_0j7vkwyr0a2st_8_sqldx.zip

 [oracle@server01 sqlhc]$ ls -lrt

-rw-r--r--. 1 oracle oinstall   7965 Oct 12 15:11 sqlhc_20171012_151125_0j7vkwyr0a2st_1_health_check.html

-rw-r--r--. 1 oracle oinstall   4659 Oct 12 15:11 sqlhc_20171012_151125_0j7vkwyr0a2st_3_execution_plans.html

-rw-r--r--. 1 oracle oinstall 120281 Oct 12 15:11 sqlhc_20171012_151125_0j7vkwyr0a2st_2_diagnostics.html

-rw-r--r--. 1 oracle oinstall  23538 Oct 12 15:11 sqlhc_20171012_151125_0j7vkwyr0a2st_9_log.zip

-rw-r--r--. 1 oracle oinstall  41441 Oct 12 15:11 sqlhc_20171012_151125_0j7vkwyr0a2st_4_sql_detail.html

-rw-r--r--. 1 oracle oinstall  95899 Oct 12 15:12 sqlhc_20171012_151125_0j7vkwyr0a2st_8_sqldx.zip

Finalmente, quienes quieran profundizar más en esta herramienta pueden leer las siguientes notas de MOS:

NOTE: 1417774.1 -SQL Health Check (SQLHC) Frequently Asked Questions

NOTE:1366133.1 - SQL Tuning Health-Check Script (SQLHC)

NOTE:1455583.1 - SQL Tuning Health-Check Script (SQLHC) Video

Para descargar el script:

Download the SQLHC Script Here (.zip archive)