En ocasiones después de hacer un cambio sobre la configuración de una BD, ya sea memoria asignada, versión del optimizador, etc… es necesario obtener una evidencia que demuestre que lo que se ha hecho mejora o no el rendimiento.

SQL Performance Analyzer ( SPA ) un paquete que aparece por primera vez en la versión 11g. Básicamente lo que hace es utilizar un SQL Tuning Set y realizar un test de todas las operaciones incluidas en el mismo calculando:

• Planes de ejecución
• Tiempos de CPU

La documentación oficial está aqui.

Partimos de un SQL Tuning Set (STS) creado en la entrada SQL Tuning Set Oracle Database llamado sh_workload

1. Creamos un analysis task SH_SPA.
   

   Source

   DECLARE
   my_task_name VARCHAR2(30);
   BEGIN
   my_task_name := DBMS_SQLPA.create_analysis_task(sqlset_name => 'sh_workload', task_name => 'SH_SPA');
   DBMS_OUTPUT.PUT_LINE(my_task_name);
   END;
   /

2. Ejecutamos el analysis task, execution_name=sh_before_change
   

   Source

   exec DBMS_SQLPA.execute_analysis_task( task_name => 'SH_SPA', execution_type  => 'test execute', execution_name  => 'sh_before_change');

3. Podemos sacar un report del task, en este caso en formato texto aunque podría ser en html.
   

   Source

   SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(task_name => 'SH_SPA', section => 'ALL', execution_name => 'sh_before_change') FROM dual;

   
   Ver fichero report_SH_SPA_sh_before_change.txt
   
4. Creamos un índice para hacer un cambio (podemos cambiar cualquier otra cosa, parámetros, etc…)
   

   Source

   CREATE INDEX SH.IDX$$_00A90001 ON SH.TIMES("CALENDAR_QUARTER_DESC","TIME_ID");--Generamos estadísdicas
   exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SH', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

5. Ejecutamos otro analysis task, execution_name=sh_after_change
   

   Source

   exec DBMS_SQLPA.execute_analysis_task( task_name => 'SH_SPA', execution_type  => 'test execute', execution_name  => 'sh_after_change');

6. Podemos sacar un report del task, una vez más en formato texto
   

   Source

   SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(task_name => 'SH_SPA', section => 'ALL', execution_name => 'sh_after_change') FROM dual;

   
   Ver fichero report_SH_SPA_sh_after_change.txt
   
7. Calculamos un report comparativo de la ejecución sh_before_change vs sh_after_change
   

   Source

   begin
   DBMS_SQLPA.execute_analysis_task(task_name  => 'SH_SPA',  execution_type   => 'compare performance', execution_name  => 'sh_compartivo',
   execution_params => dbms_advisor.arglist(
   'execution_name1',
   'sh_before_change',
   'execution_name2',
   'sh_after_change')
   );
   end;
   /

8. Sacamos el report esta vez en formato html
   

   Source

   SELECT DBMS_SQLPA.report_analysis_task(task_name => 'SH_SPA', type => 'HTML', section => 'ALL', execution_name => 'sh_compartivo') FROM   dual;

   
   Ver fichero report_SH_SPA_sh_comparativo.html

El report indica que la creación del índice ha empeorado el rendimiento, un trabajo estupendo :(. El índice creado lo había propuesto el SQL Tuning Advisor (aunque solo era una de las propuestas) y afecta a todos los planes de ejecución.

Hasta aquí tenemos un ejemplo bastante claro, ahora vamos a continuar pero realizando un cambio del optimizador, algo que si representará una mejora aunque tampoco demasiado. Estábamos trabajando con el optimizador 10.2.0.1 y vamos a utilizar el 12.1.0.1.

1. Cambio del optimizador de 10.2.0.1 a 12.1.0.1
   

   Source

   ALTER system SET optimizer_features_enable='12.1.0.1';
   --Volvemos a calcular estadísticas:
   exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'SH', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

2. Ejecutamos otro analysis task, execution_name=sh_change_optimizer_12.1.0.1
   

   Source

   exec DBMS_SQLPA.execute_analysis_task( task_name => 'SH_SPA', execution_type  => 'test execute', execution_name  => 'sh_change_optimizer_12.1.0.1');

3. Podemos sacar un report del task, en texto de nuevo.
   

   Source

   SELECT DBMS_SQLPA.REPORT_ANALYSIS_TASK(task_name => 'SH_SPA', section => 'ALL', execution_name => 'sh_change_optimizer_12.1.0.1') FROM dual;

   Ver fichero report_SH_SPA_sh_change_optimizer_12.1.0.1.txt
   
4. Calculamos un report comparativo de la ejecución sh_after_change vs sh_change_optimizer_12.1.0.1
   

   Source

   begin
   DBMS_SQLPA.execute_analysis_task(task_name  => 'SH_SPA',  execution_type   => 'compare performance', execution_name  => 'sh_compartivo_cambio_optimizer',
   execution_params => dbms_advisor.arglist(
   'execution_name1',
   'sh_after_change',
   'execution_name2',
   'sh_change_optimizer_12.1.0.1')
   );
   end;
   /

5. Obtenemos el report en html
   

   Source

   SELECT DBMS_SQLPA.report_analysis_task(task_name => 'SH_SPA', type => 'HTML', section => 'ALL', execution_name => 'sh_compartivo_cambio_optimizer') FROM   dual;

   Ver fichero report_SH_SPA_sh_compartivo_cambio_optimizer.html

En este caso obtenemos una mejora, que tengáis unos felices análisis 🙂