Use SQL Performance Analyzer

1. Documentación en Tahiti -> Oracle Database, 11g Release 2 (11.2) -> Masters Book List -> Real Application Testing User’s Guide -> Part I SQL Performance Analyzer

2. Esta característica pertenece al producto Oracle Real Application Testing junto con Database Replay. Ya hemos visto casi todas las herramientas que Oracle pone a nuestra disposición para optimizar la Base de Datos. Merece la pena hacer un resumen de como están agrupados fichos productos y herramientas, ya que algunos de ellos requiere licencia por separado.

· Oracle Database 11g Enterprise Edition

La BD nos ofrece herramientas interesantes para hacer Tuning. Entre ellas las herramientas de traza, las alertas y las vistas de rendimiento V$.

· Oracle Enterprise Manager

En sus dos versiones: Database Console (incluido en Oracle Database 11g) o Grid Control, nos ofrece un interfaz muy ágil para ciertas operaciones. Con OEM podemos utilizar el resto de las herramientas con bastante facilidad. Algunas de ellas son especialmente indicadas para OEM, como la utilizaciónd de los Advisors, el Scheduler y las páginas de Performance.

· Oracle Diagnostics Pack

Este pack adicional incluye el repositorio AWR, el monitor ADDM y el histórico de sesiones activa (ASH).

· Oracle Database Tuning Pack

En este pack encontramos el SQL Tuning Advidor y SQL Access Advisor.

· Oracle Real Application Testing

También conocido como Oracle RAT, incluye Database Replay para capturar carga de trabajo en producción y reproducirla (Replay) en otro entorno distinto al de producción con distintas finalidades (tuning, migraciones, …). La otra herramienta que tiene este paquete es la que vamos a ver en este objetivo, SQL Performance Analyzer.

3. Después de la pequeña introducción vamos al tema que nos ocupa. SQL Performance Analyzer sirve para medir el impacto que puede producir en el sistema uno o varios cambios. Para ello, se ejecuta una carga de trabajo dos veces. La primera, antes de hacer cualquier cambio para tomar estadísticas de referencia. La segunda, tras implementar dicho cambio para poder comparar las estadísticas con la primera ejecución y evaluar el si el cambio es positivo o no.

En el objetivo anterior “Use SQL Access Advisor” generamos un script con las recomendaciones a seguir para optimizar una serie de consultas que introdujimos en un SQL Tuning Set (STS). Con SQL Performance Analyzer podemos medir si el sistema se comporta mejor tras implementar dicho cambio, ya que en ciertas situaciones, aunque el cambio pueda mejorar un subconjunto de instrucciones, el rendimiento del sistema en conjunto puede empeorar. Vamos a comprobarlo.

En la página principal de la instancia OCM ->
-> Click “Software and Support” ->
-> Click “SQL Performance Analyzer” ->
-> Click “Guided Workflow” ->
-> Click en el primer icono de Execute ->
-> Name = “SPA_TEST” ->
-> Click en el icono de linterna ->
-> Seleccionamos STS_TEST ->
-> Click “Select” ->
-> Click “Create” ->
-> Click en el segundo icono de Execute ->
-> SQL Trial Name = “SPA_TEST_BEFORE” ->
-> Seleccionamos “Trial environment established” ->
-> Click “Submit” ->
-> Esperamos hasta que finalice la ejecución (Status OK en el Paso 2) ->
-> IMPORTANTE! Ejecutamos el script “sqladvisor_deploy.sql” en la BD de OCM ->
-> Click en el tercer icono de Execute ->
-> SQL Trial Name = “SPA_TEST_AFTER” ->
-> Seleccionamos “Trial environment established” ->
-> Esperamos hasta que finalice la ejecución (Status OK en el Paso 3) ->
-> Click en el cuarto icono de Execute ->
-> Click “Submit” ->
-> Esperamos hasta que finalice la ejecución (Status OK en el Paso 4) ->
-> Click en el quinto icono de Execute ->
-> Investigamos el informe para comprobar la mejora que tenemos con los cambios aplicados ->
-> Podemos guardar el informe en formato HTML haciendo Click “Save”

SPA Report

4. Vamos hacer una prueba de regresión para ver como afecta un cambio en el optimizador de una versión a otra a una consulta de ejemplo.

-- Ejecutamos la siguiente consulta en la BD
SELECT /* TEST_REGRESION */ customers.cust_first_name, customers.cust_last_name,
  MAX(QUANTITY_SOLD), AVG(QUANTITY_SOLD)
FROM sh.sales, sh.customers
WHERE sales.cust_id=customers.cust_id
GROUP BY customers.cust_first_name, customers.cust_last_name;

En la página principal de la instancia OCM ->
-> Click “Performance” ->
-> Click “SQL Tuning Sets” ->
-> Click “Create” ->
-> SQL Tuning Set Name = “STS_OPT” ->
-> Click “Next” ->
-> Seleccionamos “Load SQL statements one time only” ->
-> Data Source = “Cursor Cache” ->
-> Click “Next” ->
-> SQL Text LIKE “SELECT /* TEST_REGRESION */%” ->
-> Click “Next” ->
-> Click “Next” ->
-> Click “Submit” ->
-> Click “OCM” ->
-> Click “SQL Performance Analyzer” ->
-> Click “Parameter Change” ->
-> Task Name = “SPA_TEST2” ->
-> SQL Tuning Set = “STS_OPT” ->
-> Parameter Name = “optimizer_features_enable” ->
-> Base Value = “9.2.0” ->
-> Changed Value = “11.2.0.3” ->
-> Click “Submit” ->
-> Seleccionamos “SPA_TEST2” ->
-> Click “View Latest Report” y observamos que hay una mejora significativa de rendimiento
-> Click SQL ID “b9nwd02f7s654” y vemos la diferencia entre los planes

La razón de la diferencia entre las dos versiones del optimizador se debe a que en versiones 9.2.0 se realizaba una operación de ordenación por defecto, aunque no lo indicáramos, lo que provoca un coste más alto en el plan de ejecución.