Guest Author – Prashant Dixit, Oracle ACE, Database Architect, CBTS – ONX
As an Oracle Ace Associate from Canada and a long-time Oracle Database advocate, I love to share what I’ve learned and experienced while working on complex database performance issues, optimization, tuning, and troubleshooting. I am a seasoned Database Architect/DBA with passion and a creative mind for Database. As a senior member of the Cloud/Database Engineering Team, I have a wide range of Development, Implementation, and administration experience with large organizations. I’m able to combine my love for Oracle Databases and OS/Application Management. (www.fatdba.com)
I have used RAT many times in the past. RAT is the best tool to simulate Oracle database workloads, it empowers us, the DBAs, to make proactive changes while knowing exactly what to expect when a change is implemented and minimizes the likelihood of any negative repercussions. RAT is a great option when performing hardware replacements i.e., CPU, RAM, etc., DB or OS Upgrades, Storage level changes, or in case of big or critical configuration level changes.
Real Application Testing allows for the testing of system changes in a simulation of production-level workload and use. Some of the really cool options available with RAT are SPA Quick Check, SQL Performance Analyzer, Concurrent Database Replay, Migration Planner, and DB Replay.
Recently, I had the opportunity to use RAT during a migration project where we moved to an Oracle Database multitenant 19.15 environment. With Oracle Release 19c, you can capture and replay the workload from within an individual pluggable database (PDB). This enhancement enables you to capture and replay workloads at the PDB level. This leads to better testing, less downtime, and more effective and efficient change control. This was not possible in previous versions and was limited to capturing and replaying multitenant databases at the root multitenant container database (CDB) level.
Capture and replay workloads with Database Release 19c
Today’s post is to show how to test system changes with RAT using a short demo on one of my test builds.
-- For testing purpose, writing a shell script to capture workload.
[oracle@oracleontario ~]$cat pdbtest_fatdba.sh
while sleep 1; do \
sqlplus <USERNAME1>/<PASSWORD1>@<PDB1_CONNECTION_STRING> &>/dev/null <<EOF
set head off
set pages 0
select count(*) from dixit.bigtab;
exit
EOF
done
-- Create a capture directory on the source database server. This is where all your capture specific files will reside.
[oracle@oracleontario ~]$sqlplus <USERNAME1>/<PASSWORD1>@<PDB1_CONNECTION_STRING>
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 19 09:05:12 2022
Version 19.15.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Sat Nov 19 2022 09:03:41 +00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SQL> CREATE or replace DIRECTORY mycapture_dir AS '/ontadomain/home/capture_pdb_fatdbatest';
Directory created.
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
-- Lets start the test, call the shell script which we prepared in the beginning.
[oracle@oracleontario ~]$sh ./pdbtest_fatdba.sh&
[1] 2189
And now capture the workload, use DBMS_WORKLOAD_CAPTURE.start_capture to collect workload data. Once completed, copy all the files from the source to the target server where they will be replayed.
[oracle@oracleontario ~]$sqlplus <USERNAME1>/<PASSWORD1>@<PDB1_CONNECTION_STRING>
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Nov 19 09:05:41 2022
Version 19.15.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Last Successful login time: Sat Nov 19 2022 09:05:39 +00:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
SQL> BEGIN
DBMS_WORKLOAD_CAPTURE.start_capture (name => 'PDB_666', dir => 'mycapture_dir', duration => 900);
END;
/
PL/SQL procedure successfully completed.
SQL> SQL> col name for a10
col status for a22
SELECT id, name, STATUS FROM dba_workload_captures WHERE name='PDB_666';
ID NAME STATUS
---------- ---------- --------------------
31 PDB_666 IN PROGRESS
SQL> BEGIN
DBMS_WORKLOAD_CAPTURE.finish_capture;
END;
/
PL/SQL procedure successfully completed.
SQL> quit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.15.0.0.0
Next, we must create the replay directory structure, process captured files and initialize the replay through the workload clients. – WRC (Workload Clients) are multi-threaded JAVA clients (located in the $ORACLE_HOME/bin directory).
Though it can be started on the same machine/host, for Production level tests, it’s highly recommended to start them on a separate host as each thread submits a captured workload and consumes system resources (CPU/Memory & IO) which may provide incorrect results.
[oracle@oracleontario ~]$sqlplus <USERNAME1>/<PASSWORD1>@<PDB1_CONNECTION_STRING> SQL*Plus: Release 19.0.0.0.0 - Production on Sun Mar 1 01:20:18 2022 Version 19.15.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. Last Successful login time: Sat Nov 19 2022 09:06:37 +00:00 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.15.0.0.0 SQL> CREATE or REPLACE DIRECTORY replaydir_fatdbatest1 AS '/ontadomain/home/replay_pdb_fatdbatest'; Directory created. SQL>
Once the directory structure is created, next we must start the preprocessing phase of the workload and the first step in preparation is to process the CAPTURE in the replay directory. During this step we use the DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE procedure which creates a workload replay set of metadata files used for the replay.
A few important points about workload processing are:
- This will be a one-time activity for each database version or RU and only done if you are doing it for the first time. If the load is already processed and you have replayed it earlier, then there is not any use to follow this step.
- Preprocessing is a time taking activity and can be done on a separate seed database while this database can be used for other operations i.e., creating capture subsets etc.
-- Process all files that we have captured during the capture step.
SQL> BEGIN
DBMS_WORKLOAD_REPLAY.process_capture('replaydir_fatdbatest1');
END;
/
PL/SQL procedure successfully completed.
SQL>
-- Initialize the replay once above step of capture is successfully completed.
SQL> BEGIN
DBMS_WORKLOAD_REPLAY.initialize_replay (replay_name => 'REPLAY_FATDBA1', replaydir_fatdbatest1 => 'replaydir_fatdbatest1');
END;
/
PL/SQL procedure successfully completed.
SQL>
Next, we must remap connections. This is the step where we remap the connect strings of the production system and point them to the test database.
For this step check DBA_WORKLOAD_CONNECTION_MAP view for connection mappings. This view displays the connection mapping details for the workload replay. Each row defines one connection mapping for a particular workload replay.
SQL> select conn_id,schedule_cap_id,capture_conn,replay_conn from dba_workload_connection_map;
CONN_ID SCHEDULE_CAP_ID CAPTURE_CONN REPLAY_CONN
---------- --------------- -------------------------------------------------- --------------------------------------------------
1 (DESCRIPTION=(CONNECT_DATA=(SERVER=DEDICATED)(SERV
ICE_NAME=<NAME_OFTHE_PDB_SERVICE>)(CID=(PROGRAM=sqlplus)
(HOST=<HOSTNAME>)(USER=oracle)))(A
DDRESS=(PROTOCOL=TCP)(HOST=<IP_ADDRESS>)(PORT=<port>
)(HOSTNAME=<HOSTNAME>)))
We will use the full connect descriptor method to remap our connections. This is where we will put full TNS details of the service we want. We will use DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION procedure to remap the captured connection to a new one so that the user sessions can connect to the database in a desired way during workload replay.
SQL> BEGIN DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION (connection_id => 1, replay_connection => 'DESCRIPTION=(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=<NAME_OFTHE_PDB_SERVICE>)(CID=(PROGRAM=sqlplus)(HOST=<HOSTNAME>)(USER=oracle)))(ADDRESS=(PROTOCOL=TCP) (HOST=<IP_ADDRESS>)(PORT=<port>)(HOSTNAME=<HOSTNAME>)))'); END; / PL/SQL procedure successfully completed.
Verify new connection details that will be used during the replay. Query the dba_workload_connection_map again:
SQL> select conn_id,schedule_cap_id,capture_conn,replay_conn from dba_workload_connection_map;
CONN_ID SCHEDULE_CAP_ID CAPTURE_CONN REPLAY_CONN
---------- --------------- -------------------------------------------------- --------------------------------------------------
1 (DESCRIPTION=(CONNECT_DATA=(SERVER=DEDICATED)(SERV DESCRIPTION=(CONNECT_DATA=(SERVER=DEDICATED)(SERVI
ICE_NAME=<NAME_OFTHE_PDB_SERVICE>)(CID=(PROGRAM=sqlplus) CE_NAME=<NAME_OFTHE_PDB_SERVICE>)(CID=(PROGRAM=sqlplus)(
(HOST=<HOSTNAME>)(USER=oracle)))(A HOST=<HOSTNAME>)(USER=oracle)))(AD
DDRESS=(PROTOCOL=TCP)(HOST=<IP_ADDRESS>)(PORT=<port> DRESS=(PROTOCOL=TCP)(HOST=<IP_ADDRESS>)(PORT=<port>
)(HOSTNAME=<HOSTNAME>))) )(HOSTNAME=<HOSTNAME>)))
In the above example, I have remapped only one connect string, you must remap multiple connect strings in case of a larger workload and have to repeat the step for each one of them.
Please note, for Oracle Real Application Clusters (RAC) databases, you can map all connection strings to a load balancing connection string, i.e., “(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=ON)”. This is particularly useful if the number of RAC nodes on the replay system is different from the capture system. Instead, if you want to point the workload to specific instances, you can use services or separately assign the instance identifier in the remapped connection strings.
Next, will prepare the workload replay on the replay system. For the reason of simplicity, I haven’t used any special parameters with the PREPARE_REPLAY procedure. There are some interesting flags or parameters available that you can try.
-- Parameters that you can try with dbms_workload_replay.prepare_replay( synchronization IN BOOLEAN DEFAULT TRUE, connect_time_scale IN NUMBER DEFAULT 100, think_time_scale IN NUMBER DEFAULT 100, think_time_auto_correct IN BOOLEAN DEFAULT TRUE, scale_up_multiplier IN NUMBER DEFAULT 1, capture_sts IN BOOLEAN DEFAULT FALSE, sts_cap_interval IN NUMBER DEFAULT 300); -- Prepare your replay. -- In below example, the PREPARE_REPLAY procedure prepares a replay that has been previously initialized. SQL> BEGIN DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY; END; / PL/SQL procedure successfully completed. SQL>
Once the preparation step is complete, the next step is to fire the recommended numbers of wrc (workload) clients on the target database to replay the captured workload. You must run the ‘calibrate’ option to compute the estimated number of replay clients and CPU needed to replay a given workload.
For example, if 3 is the recommended number of replay clients, you must call the same number of workload clients to replay the workload.
-- start the required number of replay clients. [oracle@oracleontario ~]$wrc <USERNAME1>/<PASSWORD1>@<PDB1_CONNECTION_STRING> replaydir=/refresh/home/replay_pdb1 mode=replay CONNECTION_OVERRIDE=TRUE Workload Replay Client: Release 19.15.0.0.0 - Production on Sun Mar 1 01:29:11 2022 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. Wait for the replay to start (01:19:22) -- In case more workload clients needed. [oracle@oracleontario ~]$ nohup wrc <USERNAME1>/<PASSWORD1>@<PDB1_CONNECTION_STRING> replaydir=/refresh/home/replay_pdb1 mode=replay CONNECTION_OVERRIDE=TRUE & [oracle@oracleontario ~]$ nohup wrc <USERNAME1>/<PASSWORD1>@<PDB1_CONNECTION_STRING> replaydir=/refresh/home/replay_pdb1 mode=replay CONNECTION_OVERRIDE=TRUE & [oracle@oracleontario ~]$ nohup wrc <USERNAME1>/<PASSWORD1>@<PDB1_CONNECTION_STRING> replaydir=/refresh/home/replay_pdb1 mode=replay CONNECTION_OVERRIDE=TRUE &
Start the replay process on the target to fire the workload on the target and, once completed, generate the replay or any comparison reports.
SQL> BEGIN DBMS_WORKLOAD_REPLAY.start_replay; END; / PL/SQL procedure successfully completed. SQL>
Monitor the progress of the replay using one of the dynamic views named DBA_WORKLOAD_REPLAYS.
— Verify its status
alter session set nls_date_format = ‘DD-MON-YYYY HH24:MI:SS’;
SELECT ID, NAME,DBNAME,
PARALLEL,STATUS, to_char(START_TIME,’dd/mm/yyyy hh24:mi’),
to_char(END_TIME,’dd/mm/yyyy hh24:mi’),DURATION_SECS/60, NUM_CLIENTS, USER_CALLS, DBTIME, RAC_MODE
AWR_BEGIN_SNAP,AWR_END_SNAP,AWR_EXPORTED
FROM DBA_WORKLOAD_REPLAYS;
where id=1;
— or use something simple.
SQL> COL NAME FOR A10
SQL> SELECT id, name, STATUS FROM dba_workload_replays where name = ‘REPLAY_FATDBA1’;
ID NAME STATUS
———- ————— —————————————-
1 REPLAY_FATDBA1 IN PROGRESS
SQL> SELECT id, name, STATUS FROM dba_workload_replays where name = ‘REPLAY_FATDBA1’;
ID NAME STATUS
———- ————– —————————————-
1 REPLAY_FATDBA1 COMPLETED
SQL>
Now when it’s completed, we can generate the REPLAY report or any other reporting like compare report, etc.
SQL> DECLARE cap_id NUMBER; rep_id NUMBER; rep_rpt CLOB; rpt_len NUMBER; BEGIN rep_rpt :=DBMS_WORKLOAD_REPLAY.REPORT(replay_id => 1,format => DBMS_WORKLOAD_REPLAY.TYPE_HTML); dbms_output.put_line(DBMS_LOB.SUBSTR(rep_rpt,32767,1)); END; / DBMS_WORKLOAD_REPLAY.REPORT(REPLAY_ID=>1,FORMAT=>'TEXT') -------------------------------------------------------------------------------- DB Replay Report for REPLAY_FATDBA1 -------------------------------------------------------------------------------- -------------------------------------------------------------------------- | DB Name | DB Id | Release | RAC | Replay Name | Replay Status | -------------------------------------------------------------------------- | FATDBA12 | 1218318131 | 19.15.0.0.0 | NO | REPLAY_FATDBA1 | COMPLETED | -------------------------------------------------------------------------- ... ... .......... ............. ......................
Once you are done, you can generate all sorts of reports covering the replay period. For example, comparison of capture/replay AWR to collect top summaries, replay stats, replay divergence (failures/errors), top SQL IDs, Wait events etc.
You can even generate AWR comparison reports to focus on the key differences in the PDBs, and focus on wait events and research improvement opportunities, make and change (few at times) and try again.
-- Below is the code to generate Capture Vs Replay reports. var report_bind clob; begin DBMS_WORKLOAD_REPLAY.COMPARE_PERIOD_REPORT (replay_id1 => 1, replay_id2=> null, format => 'HTML', result => :report_bind); end; /
To learn more about database performance and my experience as an Oracle Ace Associate visit my social media spaces where I regularly share about Oracle Database technologies.
Blog
YouTube Channel (@thefatdba)
Oracle ACEs love sharing their knowledge and helping the Oracle community. Most members have technical expertise in areas such as applications and app development, business intelligence, cloud, database management, GraalVM, Java, Linux, middleware and MySQL. These community thought leaders are speakers at top industry events such as Oracle CloudWorld, JavaOne, and Oracle User Group conferences. Besides presenting at meetups and conferences, writing blogs, articles, and books, and sharing tech tips on social media, Oracle ACEs also answer questions in forums and provide Oracle product management with valuable feedback.
Prashant Dixit
Oracle Ace Associate
ONX Canada – CBTS
www.fatdba.com