Una manera de guardar y comparar queries

Desde hace unas semanas estoy inmerso en un upgrade de 9iR2 a 10gR2, por question de sencillez y rapidez decidí usar el Real Application Testing para comparar los planes de ejecución las queries en las dos versiones, siempre puede haber cambios.

Durante la configuración del RAT se me surgió la idea de probar comparar las queries de una misma versión pero de las diferentes pruebas (de varias iteraciones) como una validación que se ha probado bien y el entorno de prueba es estable.

Es bastante sencillo

1. activar 10046 y capturar las sentencias, bind y planes de ejecución en prueba X.
2. activar 10046 y capturar las sentencias, bind y planes de ejecución en prueba Y.
3. crear una tabla de mapeo de objet_id y objet_name, exportarlo e importarlo en la 11gR2 (para RAT)
4. Cargar las trazas de la prueba X a la BD de 11gR2 (para RAT)
5. Cargar las trazas de la prueba Y a la BD de 11gR2 (para RAT)

Para paso 3:

create table mapping_table as
select object_id id, owner,substr(object_name,1,30) name
from dba_objects
where object_type not in (
‘CONSUMER GROUP’,’EVALUATION CONTEXT’,
‘FUNCTION’,’INDEXTYPE’,’JAVA CLASS’,
‘JAVA DATA’,’JAVA RESOURCE’,’LIBRARY’,
‘LOB’,’OPERATOR’,’PACKAGE’,
‘PACKAGE BODY’,’PROCEDURE’,’QUEUE’,
‘RESOURCE PLAN’,’SYNONYM’,’TRIGGER’,
‘TYPE’,’TYPE BODY’)
union all
select user_id id,username owner, null name from dba_users;

Para los pasos 4 y 5 se utiliza esto:


DECLARE
   mycur   DBMS_SQLTUNE.sqlset_cursor;
BEGIN
   DBMS_SQLTUNE.create_sqlset ('lsc_sieb_x');

   OPEN mycur FOR
      SELECT VALUE (p)
        FROM TABLE
                (DBMS_SQLTUNE.select_sql_trace
                         (DIRECTORY => 'PRUEBA_X_DIR',
                          file_name => 'sieb_%.trc',
                          mapping_table_name  => 'MAPPING_TABLE',
                          select_mode => DBMS_SQLTUNE.single_execution
                         )
                ) p;

   DBMS_SQLTUNE.load_sqlset (sqlset_name => 'lsc_sieb_x',
                             populate_cursor => mycur,
                             commit_rows => 1000
                            );

   CLOSE mycur;
END;
/

DECLARE
   mycur   DBMS_SQLTUNE.sqlset_cursor;
BEGIN
   DBMS_SQLTUNE.create_sqlset ('lsc_sieb_y');

   OPEN mycur FOR
      SELECT VALUE (p)
        FROM TABLE
                (DBMS_SQLTUNE.select_sql_trace
                          (DIRECTORY => 'PRUEBA_Y_DIR',
                           file_name => 'sieb_%.trc',
                            mapping_table_name => 'MAPPING_TABLE',
                           select_mode => DBMS_SQLTUNE.single_execution
                           )
                ) p;

   DBMS_SQLTUNE.load_sqlset (sqlset_name => 'lsc_sieb_y',
                             populate_cursor => mycur,
                             commit_rows => 1000
                            );

   CLOSE mycur;
END;
/


con esta query sacamos las queries que repiten en las dos pruebas, ordeno por buffer_gets y cpu porque el resto de metricas fluctuan bastante:

WITH dist_sql_id AS
     (SELECT   sql_id, COUNT (*)
          FROM dba_sqlset_statements
         WHERE sqlset_name in ('lsc_sieb_x', 'lsc_sieb_y')
      GROUP BY sql_id
        HAVING COUNT (*) > 1)
SELECT   a.sqlset_name, a.sql_id, executions, elapsed_time, cpu_time,
         buffer_gets, disk_reads, rows_processed, fetches,
         parsing_schema_name
    FROM dba_sqlset_statements a, dist_sql_id b
   WHERE a.sql_id = b.sql_id
     AND sqlset_name in ('lsc_sieb_x', 'lsc_sieb_y')
     AND parsing_schema_name = 'XXX'
ORDER BY 2, 1, 6, 5

Para procesar los datos de rendimiento podemos jugar con las funciones LAG(), LEAD(), añadir filtros como elapsed_time mayor que xxx segundos, buffer_gets, disk_reads etc.

Luego para comparar plan de ejecucion podemos utilizar la vista DBA_SQLSET_PLANS.

Basicamemte creo que es lo que hace SQL Performance Analyzer por debajo pero aqui lo hacemos más manual. Este método es sencillo y muy rapido, me acuerdo hace 4 años en un upgrade similar para llevar acabo todas estas tareas tardaba entre 6 a 8 semanas de trabajo y scripting para guardar y comparar 12000 queries.

En este upgrade tambien usare el TTS con Physical Standby ya que el tiempo de upgrade es muy reducido, 45 minutos. Con el método de TTS Y Physical Standby espero reducir el tiempo de upgrade a 9iR2 a 10gR2 a 15 minutos, esto ya sera otro post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s