Use real application testing

Go back to OCM 12c Preparation Project 

Hands On Lab – Index

[1] SQL Performance Analyzer
[2] Database Capture and  Replay from CLI
[3] Database Capture and Replay from OEM

This is another topic that you should avoid at all costs using any 
approach different from Oracle Enterprise Manager. 
It will save you a lot of time!

Real Application Testing = Database Replay + SQL Performance Analyzer

Performance Analyser is specially good when we are doing an Upgrade in 
our Oracle Version, so you can check the times of the queries before/after 
the change. For 12c OCM, probably we will face some database change scenario, 
like:

Parameter Change
Optimizer Statistics
Practice here creating and moving across instances a SQL Tuning Set and 
checking the performance changes after a very intensive parameter 
modification, as optimizer_mode set to ‘RULE’.

A SQL tuning set (STS) is a database object that you can use as input 
to tuning tools.


[1] SQL Performance Analyzer

SQL> SELECT /* sql_perf_analyzer */ * from tbl_mv_objects;
SQL> 
SQL> connect usr_sqlaccess_test/test
Connected.

SQL> SELECT /* sql_perf_analyzer */ * from tbl_mv_objects
2 where object_name = 'MY_OBJECT';

no rows selected

SQL> SELECT /* sql_perf_analyzer */ * from tbl_mv_objects
2 where upper(owner) = 'MY_USR01';

no rows selected

SQL> COL sql_text format a50
SQL> select sql_id,sql_text from v$sql
2 where sql_text like '%sql_perf_analyzer%';

SQL_ID SQL_TEXT
------------- --------------------------------------------------
3dg4sws80g72z SELECT /* sql_perf_analyzer */ * from tbl_mv_objec
ts where upper(owner) = 'MY_USR01'

142zzgq897f1k select sql_id,sql_text from v$sql where sql_text l
ike '%sql_perf_analyzer%'

a80nj0yy3mu6f SELECT /* sql_perf_analyzer */ * from tbl_mv_objec
ts where object_name = 'MY_OBJECT'


SQL> PROMPT STS and capture these two sql statement base of hint
STS and capture these two sql statement base of hint
SQL> 
SQL> 
SQL> PROMPT Click on SQL Performance Analyzer from Advisor Center
Click on SQL Performance Analyzer from Advisor Center
SQL> PROMPT Click on SQL Performance Analyzer from Advisor Central
Click on SQL Performance Analyzer from Advisor Central
SQL> 
SQL> PROMPT Choose the Guided workflow and click on execute button
Choose the Guided workflow and click on execute button
SQL> 
SQL> Prompt Step-1 GUI
Step-1 GUI

SQL> Prompt Step2 GUI
Step2 GUI
SQL> 
SQL> Prompt Step-3 cli
Step-3 cli
SQL> show user
USER is "USR_SQLACCESS_TEST"
SQL> 
SQL> CREATE INDEX IDX_MV_OBJECTS_OBJNAME ON TBL_MV_OBJECTS(OBJECT_NAME);

Index created.

SQL> CREATE INDEX IDX_MV_OBJECTS_OWNER ON TBL_MV_OBJECTS(UPPER(OWNER));

Index created.

SQL>

Check Comparison Report
[2] Database Capture and Replay from CLI
Source Database : CDB1
Test Database : CDB2

#Capture using the DBMS_WORKLOAD_CAPTURE Package
mkdir -p /u03/app/oracle/db_replay_capture

sqlplus sys/oracle_4U@cdb1 AS SYSDBA

CREATE OR REPLACE DIRECTORY db_replay_capture_dir 
AS '/u03/app/oracle/db_replay_capture/';

-- Make sure existing processes are complete.
SHUTDOWN IMMEDIATE
STARTUP

####Capture the workload using the DBMS_WORKLOAD_CAPTURE Package

BEGIN
  DBMS_WORKLOAD_CAPTURE.start_capture (name     => 'test_capture_2', 
                                       dir      => 'DB_REPLAY_CAPTURE_DIR',
                                       duration => NULL);
END;
/

#Alertlog
Tue Oct 08 10:18:49 2019
DBMS_WORKLOAD_CAPTURE.START_CAPTURE(): Starting database capture at 10/08/2019 10:18:48

#Generate load on source database : cdb1
CREATE USER c##db_replay_test IDENTIFIED BY db_replay_test
  QUOTA UNLIMITED ON users;
  
GRANT CONNECT, CREATE TABLE TO c##db_replay_test;

CONN c##db_replay_test/db_replay_test@cdb1

CREATE TABLE db_replay_test_tab (
  id           NUMBER,
  description  VARCHAR2(50),
  CONSTRAINT db_replay_test_tab_pk PRIMARY KEY (id)
);

BEGIN
  FOR i IN 1 .. 500000 LOOP
    INSERT INTO db_replay_test_tab (id, description)
    VALUES (i, 'Description for ' || i);
  END LOOP;
  COMMIT;
END;
/

#Stop the workload
CONN sys/oracle_4U@cdb1 AS SYSDBA

BEGIN
  DBMS_WORKLOAD_CAPTURE.finish_capture;
END;
/

#Alertlog
Tue Oct 08 10:22:27 2019
DBMS_WORKLOAD_CAPTURE.FINISH_CAPTURE(): Stopped database capture 
(not all sessions  could flush their capture buffers) at 10/08/2019 10:22:26


$ cd /u03/app/oracle/db_replay_capture
$ ls
wcr_4f9rtgw00238y.rec  wcr_cr.html       wcr_scapture.wmd
wcr_4f9rtjw002397.rec  wcr_cr.text
wcr_4f9rtyw00239h.rec  wcr_fcapture.wmd
$

SELECT DBMS_WORKLOAD_CAPTURE.get_capture_info('DB_REPLAY_CAPTURE_DIR')
FROM   dual;

DBMS_WORKLOAD_CAPTURE.GET_CAPTURE_INFO('DB_REPLAY_CAPTURE_DIR')
---------------------------------------------------------------
                                                             65

1 row selected.


COLUMN name FORMAT A30
SELECT id, name FROM dba_workload_captures;

        ID NAME
---------- ------------------------------
        65 test_capture_2

		
DECLARE
  l_report  CLOB;
BEGIN
  l_report := DBMS_WORKLOAD_CAPTURE.report(capture_id => 65,
                         format     => DBMS_WORKLOAD_CAPTURE.TYPE_HTML);
END;
/

BEGIN
  DBMS_WORKLOAD_CAPTURE.export_awr (capture_id => 65);
END;
/

$ cd /u03/app/oracle/db_replay_capture
$ ls
wcr_4f9rtgw00238y.rec  wcr_ca.dmp   wcr_cr.text
wcr_4f9rtjw002397.rec  wcr_ca.log   wcr_fcapture.wmd
wcr_4f9rtyw00239h.rec  wcr_cr.html  wcr_scapture.wmd
$


####Replay using the DBMS_WORKLOAD_REPLAY Package

mkdir /u03/app/oracle/db_replay_capture

sqlplus sys/oracle_4U@cdb2 AS SYSDBA

CREATE OR REPLACE DIRECTORY db_replay_capture_dir AS '/u03/app/oracle/db_replay_capture/';

SET SERVEROUTPUT ON
BEGIN
  DBMS_WORKLOAD_REPLAY.process_capture('DB_REPLAY_CAPTURE_DIR');

  DBMS_WORKLOAD_REPLAY.initialize_replay (replay_name => 'test_capture_2',
                                          replay_dir  => 'DB_REPLAY_CAPTURE_DIR');

  DBMS_WORKLOAD_REPLAY.prepare_replay (synchronization => TRUE);
END;
/


wrc mode=calibrate replaydir=/u03/app/oracle/db_replay_capture


wrc system/oracle_4U@cdb2 as sysdba mode=replay replaydir=/u03/app/oracle/db_replay_capture		

BEGIN
  DBMS_WORKLOAD_REPLAY.start_replay;
END;
/

-bash-4.1$ wrc system/oracle_4U@cdb2 mode=replay replaydir=/u03/app/oracle/db_replay_capture

Workload Replay Client: Release 12.1.0.2.0 - Production on Tue Oct 8 12:03:43 2019

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


Wait for the replay to start (12:03:43)
Replay client 1 started (12:04:04)
Replay client 1 finished (12:07:46)


SQL> CONN sys/oracle_4U@cdb2 AS SYSDBA
Connected.
SQL> SELECT table_name FROM dba_tables WHERE owner = 'C##DB_REPLAY_TEST';

TABLE_NAME
------------------------------
DB_REPLAY_TEST_TAB

SQL> SELECT COUNT(*) FROM C##DB_REPLAY_TEST.db_replay_test_tab;

  COUNT(*)
----------
    500000

SQL>

COLUMN name FORMAT A30
SELECT id, name FROM dba_workload_replays;

        ID NAME
---------- ------------------------------
        51 test_capture_2
		
DECLARE
  l_report  CLOB;
BEGIN
  l_report := DBMS_WORKLOAD_REPLAY.report(replay_id => 51,
                     format     => DBMS_WORKLOAD_REPLAY.TYPE_HTML);
END;
/		

[3] Database Capture and Replay from OEM

Source Database : cdb1 (pdb1 is pluggable database in cdb1
Target Database : pdb1 (pdb1 is pluggable database in cdb2)

OEM Cloud 
==========
Enterprise
=>Quality Management
==>> Database Replay

====
CDB1
====
#Start Capture from OEM

$ sqlplus SH/SH@racnode2:1521/pdb1.example1.com                        


SQL> CREATE TABLE db_replay_test_tab2 (
  id           NUMBER,
  description  VARCHAR2(50),
  CONSTRAINT db_replay_test_tab_pk2 PRIMARY KEY (id)
);

Table created.

SQL> BEGIN
  FOR i IN 1 .. 500000 LOOP
    INSERT INTO db_replay_test_tab2 (id, description)
    VALUES (i, 'Description for ' || i);
  END LOOP;
  COMMIT;
END;
/ 

PL/SQL procedure successfully completed.

SQL> select count(*) from db_replay_test_tab2;

  COUNT(*)
----------
    500000

SQL> quit

#from OEM -> Preprocess Workload	
-> Preprocess the captured workload. Preprocessing prepares the workload 
for replay and only needs to be performed once against a specific database 
version. A workload should be preprocessed using the target test database.


-bash-4.1$ export ORACLE_SID=cdb2                  
SQL> CREATE OR REPLACE DIRECTORY db_replay_capture_dir 
AS '/u03/app/oracle/db_replay_capture/DBReplayWorkload_cdb1_capture2_21';

Directory created.

 Log Report: REPLAY_TEST2_JOB
Log Report

	Done

	Job		REPLAY_TEST2_JOB
	Status		Succeeded
	Execution ID		94751956F2A43D9BE0536538A8C0E513


Select to hide informationStep: Analyze (Succeeded)

	Started		October 9, 2019 12:19:22 PM IST
	Ended		October 9, 2019 12:19:57 PM IST
	Targets		cdb2.example2.com

	
Output Log
Enter database username: 
Enter password: 
Importing AWR data from directory '/u03/app/oracle/db_replay_capture/DBReplayWorkload_cdb1_capture2_21'
	Capture ID: 102
	AWR dbid: 455837261
	Snapshots range: 31 -> 32
AWR import is done!
Analysis done!


Select to hide informationStep: Save Workload Analyzer Report (Failed)

	Started		October 9, 2019 12:20:00 PM IST
	Ended		October 9, 2019 12:20:00 PM IST
	Targets		cdb2.example2.com

	
Output Log
Could not retrieve or save the workload analyzer report for captured workload cdb1_capture2: 
ORA-00001: unique constraint (SYSMAN.EM_DBREPLAY_CAPTURE_RESULT_PK) violated
ORA-06512: at "SYSMAN.EM_DBREPLAY", line 124
ORA-06512: at "SYSMAN.EM_DBREPLAY", line 201
ORA-06512: at line 6


Select to hide informationStep: Preprocess (Succeeded)

	Started		October 9, 2019 12:20:01 PM IST
	Ended		October 9, 2019 12:20:09 PM IST
	Targets		cdb2.example2.com

	
Output Log
capture_name=cdb1_capture2
capture_start_time=20191009063659
capture_end_time=20191009064122
capture_status=COMPLETED
dbid=994682456
dbname=CDB1
dbversion=12.1.0.2.0
dir_path=/u03/app/oracle/db_replay_capture/DBReplayWorkload_cdb1_capture2_21
dir_path_shared=TRUE
directory=DB_REPLAY_CAPTURE_DIR
parallel=NO
start_scn=2076020
preprocessed_version=12.1.0.2.0


Select to hide informationStep: Generate and Save Replay Calibration Estimate (Succeeded)

	Started		October 9, 2019 12:20:09 PM IST
	Ended		October 9, 2019 12:20:11 PM IST
	Targets		cdb2.example2.com

	
Output Log
Replay client estimate: 1
Replay CPU core estimate: 1
Workload name: cdb1_capture2
Workload ID: 21
Database version: 12.1.0.2.0


Select to hide informationStep: Save Preprocess Results (Succeeded)

	Started		October 9, 2019 12:20:09 PM IST
	Ended		October 9, 2019 12:20:09 PM IST
	Targets		


Select to hide informationStep: Collect job issues (Succeeded)

	Started		October 9, 2019 12:20:12 PM IST
	Ended		October 9, 2019 12:20:12 PM IST
	Targets		


#Replay Workload	
-> Replay the preprocessed workload on a test copy of the production database.

-bash-4.1$ wrc system/oracle_4U mode=replay replaydir=/u03/app/oracle/db_replay_capture/DBReplayWorkload_cdb1_capture2_21/

Workload Replay Client: Release 12.1.0.2.0 - Production on Wed Oct 9 12:26:21 2019

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


Wait for the replay to start (12:26:21)
Replay client 1 started (12:27:31)

#Alertlogs
Wed Oct 09 12:27:31 2019
DBMS_WORKLOAD_REPLAY.START_REPLAY(): Starting database replay at 10/09/2019 12:27:30
.........
.........

Wed Oct 09 12:29:15 2019
Thread 1 advanced to log sequence 43 (LGWR switch)
  Current log# 1 seq# 43 mem# 0: /u01/app/oracle/oradata/cdb2/redo01.log
Thread 1 cannot allocate new log, sequence 44
Checkpoint not complete
  Current log# 1 seq# 43 mem# 0: /u01/app/oracle/oradata/cdb2/redo01.log
Wed Oct 09 12:29:19 2019
Thread 1 advanced to log sequence 44 (LGWR switch)
  Current log# 2 seq# 44 mem# 0: /u01/app/oracle/oradata/cdb2/redo02.log
=========================================================================Wed Oct 09 12:31:43 2019
DBMS_WORKLOAD_REPLAY: Database replay ran to completion at 10/09/2019 12:31:42


-bash-4.1$ sqlplus SH/SH@racnode2:1521/pdb1.example2.com

SQL> select count(*) from db_replay_test_tab2;

  COUNT(*)
----------
    500000

SQL> quit
=====================
Path to Documentation:
=====================

Oracle Database SQL Tuning Guide -> 19 Managing SQL Tuning Sets
Database Testing Guide -> 1.1 SQL Performance Analyzer

Database Testing Guide
Part I SQL Performance Analyzer
2 Introduction to SQL Performance Analyzer
3 Creating an Analysis Task
4 Creating a Pre-Change SQL Trial
5 Creating a Post-Change SQL Trial
6 Comparing SQL Trials
7 Using SPA Quick Check
8 Testing a Database Upgrade

Oracle® Database Testing Guide
12c Release 1 (12.1)
14 Using Consolidated Database Replay
14.5 Example: Replaying a Consolidated Workload with APIs

Example: Replaying a Consolidated Workload with APIs

Part II Database Replay
9 Introduction to Database Replay
10 Capturing a Database Workload
11 Preprocessing a Database Workload
12 Replaying a Database Workload
13 Analyzing Captured and Replayed Workloads
14 Using Workload Intelligence
15 Using Consolidated Database Replay
16 Using Workload Scale-Up

Thank you for visiting this blog 🙂