Uso de SQL Performance Advisor durante los upgrades de BBDD Oracle
22/04/2016 -
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. 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.
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”.
|
1
2
3
|
BEGINdbms_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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
|
beginDBMS_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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
|
SQL> desc t1 Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER PARES CHAR(1) MULTIPLO_MIL NUMBER TEXTO VARCHAR2(50)SQL> select count(*) from t1; COUNT(*)---------- 10000SQL> update t1 set ID=ID+1 where ID>9000;1000 rows updated.SQL> select max(ID) from t1; MAX(ID)---------- 10001SQL> commit;Commit complete.SQL> select ID from t1 where MULTIPLO_MIL=0; ID---------- 1000 2000 3000 4000 5000 6000 7000 8000 9000 1000110 rows selected.SQL> update t1 set ID=ID+1 where ID>9000;1000 rows updated.SQL> select max(ID) from t1; MAX(ID)---------- 10002SQL> 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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
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 TIM1vrf2a3mjqjvp select max(ID) from t13dyz5wttw29jp select ID from t1 where MULTIPLO_MIL=03pyq496bh0jfs update t1 set ID=ID+1 where ID>90003rmzwq48k6t4p begin DBMS_SCHEDULER.CREATE_JOB( job_name => 'CAPT5bc0v4my7dvr5 select count(*) from t1731d443amkqdc SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHER79gm9kbdfw21g SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHER8ftt8vdzcb2d2 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERc5n9hzk0ukp0n select count(*) from t1 where PARES<>'N'c9fq6x7cw0pcg SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERgkg5haaf8ghtc SELECT /*+ BEGIN_OUTLINE_DATA IGNORE_O12 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”.
|
1
2
3
4
5
6
7
8
9
|
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 8PL/SQL procedure successfully completed. |
Y copiamos el STS en ella.
|
1
2
3
4
5
6
7
8
9
10
|
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.
|
1
2
|
$ 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.
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
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.
|
1
2
3
4
5
6
7
8
9
10
|
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”.
|
1
2
3
4
5
6
7
8
|
begindbms_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.
|
1
2
3
4
5
6
|
SQL> SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGEFROM USER_ADVISOR_LOG; 2TASK_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.
|
1
2
3
4
5
6
7
|
beginDBMS_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.
|
1
|
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.
|
1
2
3
4
5
6
7
8
9
10
|
SQL> beginDBMS_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).
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
|
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 100000set LONGCHUNKSIZE 100000set LINESIZE 200set head offset feedback offset echo offspool sts_changes.htmlPRINT :repspool offset 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).
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.
