Oracle Live

12/04/2016

RAT – Database Replay

Filed under: Database — mogukiller @ 12:05 am
Tags: ,

Dentro de las funcionabilidades de RAT (Real Application Testing) tenemos Database Replay.
Database Replay simula el tráfico sobre la base de datos, tanto en el número de conexiones como en la concurrencia de las transacciones. Se recomienda la utilizacion de esta herramienta en los siguientes casos:
– Actualizaciones e la base de datos o del sistema.
– Cambios de configuracion.
– Cambios en la arquitectura.

Version 11.2.0.4

/* TEORIA:
	Origen: Oracle.Database.11g.New.Features.for.ide.Exam.1Z0-050.pdf
	Consideraciones: Es necesario que la base origen sean de la misma version.
	Puntos a tener en cuenta despues de sacar el report:
	-- Pay special attention to the divergence of the replay from the captured workload performance. 
		Data divergences can be any one of the following:
			Smaller or larger results sets
			Updates to a database state
			A return code or an error code
	-- Errors generated during the workload replay.
	-- Performance deviations between workload capture and workload replay. You can see how long the replay took to perform the same amount of work
	-- Performance statistics captured by AWR reports. You can also use ADDM to measure the performance difference.
	
*/
-- Para saber la descripcion del paquete y explicacion de los parametros !!!

SET LONG 100000
SELECT DBMS_METADATA.get_ddl('PACKAGE','DBMS_WORKLOAD_REPLAY') FROM DUAL;


13:01:01 PRUEBA SQL>startup restrict;
 
 ALTER SYSTEM DISABLE RESTRICTED SESSION; -- no seriea necesario esto ya que cuando se inicia la captura se pasa a unrestrict
 
-- FILTROS:
-- Views:	DBA_WORKLOAD_FILTERS
		
-- Añadimos un filtro

begin
DBMS_WORKLOAD_CAPTURE.ADD_FILTER (
	FNAME => 'USER_TPCC',
	FATTRIBUTE => 'USER',
	FVALUE => 'TPCC'
	);
end;
/

/* PARAMETROS
	FNAME:			Nombre del filtro
	FATTRIBUTE:		program | module | action | service | instance_number | user
	FVALUE:			El valor del atributo especificado en FATTRIBUTE.	
Nota: En el ejemplo vamos a capturar el workload de TPCC. Para que unicamente capture el trafico de un usuario hay que configurar el parametro
default_action	=> 'EXCLUDE' de la funcion START_CAPTURE.
*/

-- Quitamos un filtro

begin
	DBMS_WORKLOAD_CAPTURE.DELETE_FILTER (
		FNAME	=>'USER_TPCC'
	);
end;
/


-- Directorio para dejar los capture files:

DIR_EXPORT:  /recovery_area/dir_exports

-- CAPTURA
-- View:	DBA_WORKLOAD_CAPTURES
        


begin
dbms_workload_capture.start_capture (
		name => 'capture_tpcc',
		dir => 'DIR_EXPORT',
		default_action	=> 'EXCLUDE',
		duration => 1200);
end;
/

/* PARAMETROS
	NAME:		Nombre de la captura
	DIR:		Objeto directorio donde dejar las capturas (mandatory)
	DURATION:	Tiempo en segundos. Si no ponemos nada sigue de forma indefinida hasta utilizar la funcion FINISH_CAPTURE.
*/

-- PARAMOS LA CAPTURA
-- nota: si se expecifica duration no es necesario esta funcion
begin
dbms_workload_capture.finish_capture ();
end;
/

-- Movemos los capture files al BE de test y los preprocesamos:

 COL NAME FOR A15
 COL DIRECTORY FOR A20
 COL STATUS FOR A15 
 SELECT ID,NAME, DIRECTORY, STATUS, START_TIME, DURATION_SECS, CAPTURE_SIZE,USER_CALLS,TRANSACTIONS,CONNECTS FROM DBA_WORKLOAD_CAPTURES;
 /*SALIDA
 
        ID NAME            DIRECTORY            STATUS          START_TI DURATION_SECS CAPTURE_SIZE USER_CALLS TRANSACTIONS   CONNECTS
---------- --------------- -------------------- --------------- -------- ------------- ------------ ---------- ------------ ----------
         2 capture_tpcc    DIR_EXPORT           COMPLETED       24/06/14          1193     17099790      18450        23650         26
*/

-- Enviamos las capturas al servidor de test

 scp trazas.tar mogubedb11n2:/recovery_area/dir_export
		
-- PREPROCESAR:

begin
dbms_workload_replay.process_capture (capture_dir => 'DIR_EXPORT');
end;
/

/*
	ROCESS_CAPTURE procedure creates a new subdirectory called pp11.2.0.3.0
*/

begin
  DBMS_WORKLOAD_REPLAY.initialize_replay (replay_name => 'test_capture_1',
                                          replay_dir  => 'DIR_EXPORT');
end;
/

begin
	DBMS_WORKLOAD_REPLAY.prepare_replay (synchronization => TRUE);
end;
/

-- nota: esto se lanza en un NUEVO TERMINAL

wrc mode=calibrate replaydir=/recovery_area/dir_exports

/*
Workload Characteristics:
- max concurrency: 3 sessions
- total number of sessions: 21
*/

wrc system/manager@PRUEBA_N2 mode=replay replaydir=/recovery_area/dir_exports
wrc system/manager mode=replay replaydir=/recovery_area/dir_exports
/*SALIDA:
Workload Replay Client: Release 11.2.0.3.0 - Production on Mar Feb 11 17:50:57 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Wait for the replay to start (17:50:57)
Replay started (17:52:02)
Replay finished (18:00:24)
*/

BEGIN
  DBMS_WORKLOAD_REPLAY.start_replay;
END;
/

/* Salida final wrc
.....
Wait for the replay to start (17:50:57)
Replay started (17:52:02)
Replay finished (18:00:24)
*/

-- Por si queremos parar la replica

begin
DBMS_WORKLOAD_REPLAY.cancel_replay();
end;
/

-- Obtenemos informacion del proceso de replay

SQL>COLUMN name FORMAT A30
SQL>SELECT id, name FROM dba_workload_replays;
/* SALIDA
        ID NAME
---------- ------------------------------
         1 test_capture_1
*/


SELECT DBMS_WORKLOAD_REPLAY.report(replay_id => 1, format => 'TEXT') FROM DUAL;

/*SALIDA REPORT:
Replay Information
------------------------------------------------------------------------------
|   Information    | Replay                     | Capture                    |
------------------------------------------------------------------------------
| Name             | test_capture_1             | capture_tpcc               |
------------------------------------------------------------------------------
| Status           | COMPLETED                  | COMPLETED                  |
------------------------------------------------------------------------------
| Database Name    | PRUEBA                     | PRUEBA                     |
------------------------------------------------------------------------------
| Database Version | 11.2.0.3.0                 | 11.2.0.3.0                 |
------------------------------------------------------------------------------
| Start Time       | 24-06-14 10:48:33          | 24-06-14 09:16:23          |
------------------------------------------------------------------------------
| End Time         | 24-06-14 10:59:15          | 24-06-14 09:36:16          |
------------------------------------------------------------------------------
| Duration         | 10 minutes 42 seconds      | 19 minutes 53 seconds      |
------------------------------------------------------------------------------
| Directory Object | DIR_EXPORT                 | DIR_EXPORT                 |
------------------------------------------------------------------------------
| Directory Path   | /recovery_area/dir_exports | /recovery_area/dir_exports |
------------------------------------------------------------------------------

Replay Options
---------------------------------------------------------
|       Option Name       | Value                       |
---------------------------------------------------------
| Synchronization         | SCN                         |
---------------------------------------------------------
| Connect Time            | 100%                        |
---------------------------------------------------------
| Think Time              | 100%                        |
---------------------------------------------------------
| Think Time Auto Correct | TRUE                        |
---------------------------------------------------------
| Number of WRC Clients   | 1 (1 Completed, 0 Running ) |
---------------------------------------------------------

Replay Statistics
---------------------------------------------------------------
|        Statistic        | Replay          | Capture         |
---------------------------------------------------------------
| DB Time                 | 424.529 seconds | 710.813 seconds |
---------------------------------------------------------------
| Average Active Sessions |             .66 |              .6 |
---------------------------------------------------------------
| User calls              |           18450 |           18450 |
---------------------------------------------------------------
| Network Time            | 131.667 seconds |             N/A |
---------------------------------------------------------------
| Think Time              |  52.949 seconds |             N/A |
---------------------------------------------------------------

Replay Divergence Summary
-------------------------------------------------------------------
|                Divergence Type                | Count | % Total |
-------------------------------------------------------------------
| Session Failures During Replay                |     0 |    0.00 |
-------------------------------------------------------------------
| Errors No Longer Seen During Replay           |     0 |    0.00 |
-------------------------------------------------------------------
| New Errors Seen During Replay                 |     0 |    0.00 |
-------------------------------------------------------------------
| Errors Mutated During Replay                  |     0 |    0.00 |
-------------------------------------------------------------------
| DMLs with Different Number of Rows Modified   |     0 |    0.00 |
-------------------------------------------------------------------
| SELECTs with Different Number of Rows Fetched |     0 |    0.00 |
-------------------------------------------------------------------
*/

1 comentario »

  1. […] Performance Analyzer es parte de la funcionabilidad de RAT, que junto con Database Replay permite minimizar los problemas de perdida de performance ante cualquier cambio en el entorno. […]

    Pingback por RAT – SQL Performance Analyzer | Oracle Live — 25/08/2016 @ 12:14 pm


RSS feed for comments on this post. TrackBack URI

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

Blog de WordPress.com.

A %d blogueros les gusta esto: