SQL Performance Analyzer Oracle Database

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 🙂

Deja un comentario