Inicio > Database > Uso de SQL Performance Advisor durante los upgrades de BBDD Oracle

Uso de SQL Performance Advisor durante los upgrades de BBDD Oracle

Entre las tareas a realizar cuando planificamos un upgrade de BBDD deberíamos incluir pruebas de rendimiento en el nuevo entorno. Estas pruebas nos pueden decir si el nuevo hardware, configuración y recursos asignados, así como la nueva versión del gestor, funcionan como esperamos.

db4

En lo que a rendimiento de las sentencias SQL se refiere, el comportamiento general al cambiar de una versión a otra superior es el mantenimiento o mejora; no obstante, en algunos casos puntuales se pueden producir regresiones (sentencias con un rendimiento peor que en origen).

En caso de que dispongamos de los packs de Diagnostics y Tuning podremos usar las funcionalidades de SQL Performance Analyzer (SPA) para revisar el comportamiento de las sentencias clave de nuestras aplicaciones en el nuevo entorno antes de realizar la migración real.

Podemos usar las funcionalidades de SPA tanto mediante una interface gráfica (con Enterprise Manager Cloud Control) o mediante la API PL/SQL del propio producto.

En este ejemplo usaremos la API de SPA para comparar los planes de ejecución de sentencias SQL entre un entorno origen 11g y un entorno destino 12c.

A grandes trazos vamos a capturar sentencias SQL en la BBDD de versión 11g, trasladar las sentencias y sus datos de rendimiento a la 12c y ejecutarlas nuevamente en la 12c para finalmente poder comparar ambas ejecuciones.

Las sentencias a analizar las almacenaremos en lo que se denomina SQL Tuning Set (STS), una estructura que almacena la sentencia, las bind variables, el plan de ejecución y los datos de rendimiento.

sts

En primer lugar deberemos escoger el método de captura de estas sentencias SQL en origen. Podemos capturar sentencias “al vuelo” desde la cache de cursores, del repositorio AWR, de otro SQL Tuning Set ya existente, de ficheros de traza (para versiones antiguas de la BBDD en las que no existían los STS) o indicar manualmente que sentencias deseamos que se incluyan en la captura.

En este caso capturaremos al vuelo las sentencias SQL, crearemos una tarea que cada cierto tiempo revisará las sentencias que encuentre en la cache de cursores y las ira recopilando.

Empezaremos creando un STS vacío que llamaremos “STS_ORIG”.

BEGIN
dbms_sqltune.create_sqlset(sqlset_name =>; 'STS_ORIG', description =>;'Demo STS', sqlset_owner =>;'WORK');
END;

 

Y creamos la tarea de captura. Lo hacemos dentro de un job a efecto de que la sesión no quede colgada hasta que termine (es una especie de lanzamiento de la tarea en “background”).

Aplicamos a la captura un filtro indicando que solo capture sentencias del usuario “WORK” y que contengan la cadena “T1”. La captura durará 3 minutos y revisaremos la cache de cursores cada 30 segundos durante este intervalo.

begin
DBMS_SCHEDULER.CREATE_JOB( 
job_name => 'CAPTURE_STS_JOB',  
job_type => 'PLSQL_BLOCK', 
job_action => 	'DECLARE  
				bf VARCHAR2(104);  
				BEGIN  
				bf := q''#UPPER(PARSING_SCHEMA_NAME) = ''WORK'' AND UPPER(SQL_TEXT) LIKE ''%T1%'' #''; 
				dbms_sqltune.capture_cursor_cache_sqlset( 
														sqlset_name=>''STS_ORIG'',
														basic_filter=>bf,  
														time_limit=> 180, 
														repeat_interval=>30, 
														capture_option=>''MERGE'',
														sqlset_owner=>''WORK''
														); 
				END;',  
start_date => sysdate, 
auto_drop     =>  TRUE,
enabled => TRUE 
);
end;
/

 

Una vez lanzado ejecutamos sentencias que cumplan las condiciones del filtro y por tanto puedan ser capturadas.

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 PARES                                              CHAR(1)
 MULTIPLO_MIL                                       NUMBER
 TEXTO                                              VARCHAR2(50)

SQL> select count(*) from t1;

  COUNT(*)
----------
     10000
SQL> update t1 set ID=ID+1 where ID>9000;

1000 rows updated.

SQL> select max(ID) from t1;

   MAX(ID)
----------
     10001

SQL> commit;

Commit complete.

SQL> select ID from t1 where MULTIPLO_MIL=0;

        ID
----------
      1000
      2000
      3000
      4000
      5000
      6000
      7000
      8000
      9000
     10001

10 rows selected.

SQL> update t1 set ID=ID+1 where ID>9000;

1000 rows updated.

SQL> select max(ID) from t1;

   MAX(ID)
----------
     10002

SQL> commit;

Commit complete.

SQL> select count(*) from t1 where PARES<>'N';

  COUNT(*)
----------
      5000

 

Una vez ha terminado de ejecutarse el job, podemos revisar qué sentencias se han incluido.

SQL>  SELECT sql_id,substr(sql_text, 1, 50) sql
   FROM TABLE(DBMS_SQLTUNE.select_sqlset ('STS_ORIG'));

SQL_ID                                  SQL
--------------------------------------- --------------------------------------------------
1bd7nb37nhf67                           DECLARE job BINARY_INTEGER := :job;  next_date TIM


1vrf2a3mjqjvp                           select max(ID) from t1
3dyz5wttw29jp                           select ID from t1 where MULTIPLO_MIL=0
3pyq496bh0jfs                           update t1 set ID=ID+1 where ID>9000
3rmzwq48k6t4p                           begin
                                        DBMS_SCHEDULER.CREATE_JOB(
                                        job_name => 'CAPT

5bc0v4my7dvr5                           select count(*) from t1
731d443amkqdc                           SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHER


79gm9kbdfw21g                           SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHER


8ftt8vdzcb2d2                           SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHER


c5n9hzk0ukp0n                           select count(*) from t1 where PARES<>'N'
c9fq6x7cw0pcg                           SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHER


gkg5haaf8ghtc                           SELECT /*+
                                              BEGIN_OUTLINE_DATA
                                              IGNORE_O


12 rows selected.

 

Ahora vamos a “empaquetar” todas estas sentencias y su información de rendimiento (que también ha capturado) en una tabla, para poder transportarlas a la BBDD destino.

Creamos la llamada tabla de “stagging”.

SQL> BEGIN
  DBMS_SQLTUNE.CREATE_STGTAB_SQLSET (
    table_name  => 'STS_STAGING_TABLE'
,   schema_name => 'WORK'
,   db_version  => DBMS_SQLTUNE.STS_STGTAB_11_2_VERSION
);
END;
/  2    3    4    5    6    7    8
PL/SQL procedure successfully completed.

 

Y copiamos el STS en ella.

SQL> BEGIN
  2    DBMS_SQLTUNE.PACK_STGTAB_SQLSET (
  3      sqlset_name         => 'STS_ORIG'
  4  ,   sqlset_owner        => 'WORK'
  5  ,   staging_table_name  => 'STS_STAGING_TABLE'
  6  ,   staging_schema_owner => 'WORK'
  7  ,   db_version          => DBMS_SQLTUNE.STS_STGTAB_11_2_VERSION
  8  );
END;
  9   10  /

 

Finalmente exportamos la tabla a un fichero de dump mediante expdp y la trasladamos al entorno destino, donde la cargaremos mediante un impdp. La BBDD destino deberá ser una copia del entorno origen, de manera que las sentencias se puedan ejecutar en ella correctamente.

$ expdp work/work directory=temp tables=work.STS_STAGING_TABLE dumpfile=sts_table.dmp logfile=sts_table.log reuse_dumpfiles=true
$ impdp work/work@workdb directory=temp dumpfile=sts_table.dmp logfile=imp_sts_table.log

 

En la BBDD destino desempaquetamos el STS a partir de la tabla de STAGE.

BEGIN
  DBMS_SQLTUNE.UNPACK_STGTAB_SQLSET (
    sqlset_name        => '%'
,   replace            => true
,   staging_table_name => 'STS_STAGING_TABLE');
END;
/	

SQL> R
  1* SELECT * FROM DBA_SQLSET

        ID   CON_DBID NAME                 OWNER      DESCRIPTION                              CREATED            LAST_MODIFIED      STATEMENT_COUNT
---------- ---------- -------------------- ---------- ---------------------------------------- ------------------ ------------------ ---------------
         1 1895909084 STS_ORIG             WORK       Demo STS                                 02-APR-16          02-APR-16                        8

 

Creamos la tarea de tuning. Dentro de esta tarea podremos lanzar múltiples análisis cambiando diferentes parámetros o analizando según diferentes parámetros.

declare
   v_task varchar2(100);
begin
  v_task := dbms_sqlpa.create_analysis_task(
                        sqlset_name => 'STS_ORIG',
                        sqlset_owner => 'WORK',
                        task_name => 'SPA_12C_UP_TASK',
                        description => 'SPA upgrade a 12c');
end;
/

 

En primer lugar capturaremos el rendimiento de las sentencias en origen. Los datos de este rendimiento forman parte del STS que acabamos de cargar. Indicaremos que deseamos usar los datos existentes en lugar de ejecutar las sentencias con el parámetro “CONVERT SQLSET”.

begin
dbms_sqlpa.execute_analysis_task(task_name => 'SPA_12C_UP_TASK',
execution_type => 'CONVERT SQLSET',
execution_name => 'SQL_TRIAL_WORK',
execution_desc => 'Origin plans',
execution_params => dbms_advisor.arglist('LOCAL_TIME_LIMIT', 'UNLIMITED', 'TIME_LIMIT', 'UNLIMITED'));
end;
/

 

A este grupo de datos de rendimiento les llamaremos “SQL_TRIAL_WORK”. Los usaremos como base para comparar con los que generaremos posteriormente. En la vista USER_ADVISOR_LOG podremos monitorizar el avance del análisis.

SQL> SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE
FROM   USER_ADVISOR_LOG;  2

TASK_ID TASK_NAME                 STATUS                            STATUS_MESSAGE
------- ------------------------- --------------------------------- ---------------------------------
     11 SPA_12C_UP_TASK           COMPLETED

 

Una vez completada esta tarea lanzamos una nueva, ejecutando ahora si, las sentencias en la BBDD 12c.  A esta ejecución de análisis la llamaremos SQL_TRIAL_WORK_POST.

begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
       task_name      => 'SPA_12C_UP_TASK',
       execution_type => 'TEST EXECUTE',
       execution_name => 'SQL_TRIAL_WORK_POST');
end;
/

 

Ya tenemos dos grupos de datos de rendimiento. Podemos llamar nuevamente a la tarea de análisis pero esta vez indicando que queremos comparar resultados con el parámetro “COMPARE PERFORMANCE”; comparará las dos que tenemos disponibles.

En caso de tener múltiples grupos de datos (caso de haber lanzado múltiples análisis con diferentes parámetros de BBDD o configuraciones del sistema) podremos indicar cuales queremos que se comparen indicando sus nombres en los parámetros de la llamada a dbms_advisor.arglist.

execution_params => dbms_advisor.arglist('execution_name1', 'SQL_TRIAL_WORK', 'execution_name2', 'SQL_TRIAL_WORK_POST', 'comparison_metric', 'elapsed_time')

 

Finalmente deberemos decidir en función de que vamos a comparar (tiempos de ejecución, uso de cpu, lecturas a disco, etc), mediante el parámetro COMPARISON_METRIC.

En nuestro caso lanzamos el siguiente código.

SQL> begin
DBMS_SQLPA.EXECUTE_ANALYSIS_TASK(
  2    3     task_name        => 'SPA_12C_UP_TASK',
  4     execution_type   => 'COMPARE PERFORMANCE',
  5     execution_name   => 'STS_COMPARE',
  6     execution_params => dbms_advisor.arglist(
  7           'comparison_metric',
  8           'cpu_time'));
  9  end;
 10  /

 

El último paso consiste en realizar el report de las sentencias que se han comparado. Podemos pedir reports generales en formato HTML/TEXTO o analizar los resultados sentencia por sentencia usando las vistas del paquete DBMS_SPA  (DBA_ADVISOR_FINDINGS, DBA_ADVISOR_SQLPLANS, DBA_ADVISOR_SQLSTATS y DBA_ADVISOR_FINDINGS).

variable rep CLOB;
begin
  :rep := DBMS_SQLPA.REPORT_ANALYSIS_TASK(
              task_name=>'SPA_12C_UP_TASK',
              type=>'HTML',
              level=>'ALL',
              section=>'ALL');
end;
/

SET LONG 100000
set LONGCHUNKSIZE 100000
set LINESIZE 200
set head off
set feedback off
set echo off
spool sts_changes.html
PRINT :rep
spool off
set head on

 

El resultado es un report parecido al siguiente, que nos da una visión general del comportamiento de las selects en el nuevo entorno y un detalle para cada una de las que han cambiado (mejorando o empeorando).

part1  part2

part3 part4

 

Ya para finalizar, algunos detalles más de SQL Performance Analyzer:

  • Para las sentencias de corta duración ejecuta la sentencia varias veces y los datos de rendimiento se obtiene como media de todas las ejecuciones.
  • Para las de duración media intenta ejecutar como mínimo las sentencia dos veces, la primera para “precargar” la cache y la segunda es para la obtención de los datos de rendimiento.
  • En caso de sentencias pesadas las ejecuta una sola vez, usando los datos obtenidos en esta única ejecución.
  • No ejecuta sentencias DML (INSERT, UPDATE, DELETE), únicamente la parte de SQL de las mismas; no obstante podemos forzar a que ejecute las sentencias DML realizando rollback al final de éstas.
  • No ejecuta PL/SQL pero sí las sentencias SQL que éste contenga.
  1. Aún no hay comentarios.
  1. No trackbacks yet.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: