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 🙂
