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.
). La aplicacion esta hecha por J2EE sobre Oracle 9iR2 y como servidor de aplicaciones Bea Weblogic. Se veian queries muy simples contra tablas correctamente indexados realizando Full Table Scan, no era trivial porque lanzaba las mismas queries desde SQLPLUS y me iba de fabula asi que como casi siempre me acorde del bind peeking (los malditos!) pero despues de revisar unas 6 docenas de queries donde algunas no usaban bind variables y tenian el mismo problema de ir rapido en SQLPLUS y lento desde la aplicacion.
