The "What is RAT?" answer goes as below
Oracle Real Application Testing comprises two main components: Database Replay and SQL Performance Analyzer(SPA). The purpose of these are to analyze the impacts of change on workload throughput and impact of change on SQL response time.
In this post, we will be practically looking at how to make use of the Database Replay to analyze the changes in advance before moving to production.
Database Replay Workflow is as follows:
1. Workload Capture
2. Workload Processing
3. Workload Replay – including DML and SQL queries
4. Analysis and Reporting
- This will place database automatically in unresricted mode after capture is started.
Oracle Real Application testing (RAT) enables you to fully assess the outcome of a system change on a test environment and to test and verify any corrective actions needed before migrating the change safely to production systems.You can learn more about RAT from this link (for 11g) and this link (for 12c) or from Oracle support note "Master Note for Real Application Testing Option (Doc ID 1464274.1)"
Oracle Real Application Testing comprises two main components: Database Replay and SQL Performance Analyzer(SPA). The purpose of these are to analyze the impacts of change on workload throughput and impact of change on SQL response time.
In this post, we will be practically looking at how to make use of the Database Replay to analyze the changes in advance before moving to production.
Database Replay Workflow is as follows:
1. Workload Capture
2. Workload Processing
3. Workload Replay – including DML and SQL queries
4. Analysis and Reporting
Post Part 1 will be comprising of the Workload Capture and Part 2 will cover the rest. Now lets begin the practical demo.
Source system information:
Server: Linux 6.7 - 64 bit limited to 1 CPU
Database: 12.1.0.1
sga_max_size set at 1G with memory_max_size 0
Target system information:
Server: OpenSuSE 13.2 (Harlequin) - 64 bit limited to 2 CPU
Database: 12.1.0.2
Memory_max_target set at 1.2G
1. Workload Capture
I'll explain the steps involved and then the practical application of the same.
Step 1: Create a directory object on the database
- This directory object is to hold the work load information
Step 2: Start the database in restricted mode.
- To avoid logons when capture is starting.- This will place database automatically in unresricted mode after capture is started.
Step 2a: Required if capture or replay performed on a database version below 12.1.0.1. In our case, this step can be skipped. Reference article: Doc ID 1901276.1
Set the correct timezones to avoid "No data exists for this section of the report."
One can find the difference in time zone and set the time zone as below.
SQL> select dbtimezone, sessiontimezone from dual; -- see the time difference DBTIME SESSIONTIMEZONE ------ -------------------- +00:00 +05:30 SQL> alter database set time_zone='-05:30'; Database altered SQL>Now don't forget to bounce the database for the changes to take effect.
Note: It may not be possible to change the dbtimezone if the database includes columns of types of TIMESTAMP WITH LOCAL TIME ZONE that relay on the dbtimezone. In this case, the below error would return.
ERROR at line 1:
ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH
LOCAL TIME ZONE columns
Step 3: Start the capture
- dbms_workload_capture package will be used to start the capture
Step 3a: Using filters with workload capture
- This is an optional step if you intend to filter any of the schemas in the database from capturing. Usage examples are as below.
execute dbms_workload_capture.ADD_FILTER('my_filter','USER','SH');execute dbms_workload_capture.start_capture('RAT_CAPTURE','RAT_WORKLOAD',default_action=>'EXCLUDE');
- First argument is the name of the capture (any name you desire)
- Second argument is the directory object name
- By default no user request to the database will be captured, except for the part of the workload defined by the filters. e.i only the sessions of SH user will be captured.
execute dbms_workload_capture.start_capture('RAT_CAPTURE','RAT_WORKLOAD',default_action=>'INCLUDE');
- This will include the filter created, so sessions carried out by SH will be eliminated from the capture and all the other database sessions will be captured. This is default value.
Step 3b: Generate a work load.
- This is optional step in a real time environment as application sessions will contribute to the load. Since I'm using lab environment for this demo post, I'm using Swingbench load generator to generate work load in the source database. Please refer to this post on usage of Swingbench and its usage. The load can be generated for around 20 to 30 minutes for testing purpose and its recommended to generate the load for the whole peak time to capture all the database activity in real time which can then be replayed on the target.
Step 4: Stop the capture
Once you are good with the capture for the period, you can stop the capture for further processing.
- dbms_workload_capture package will be used to stop the capture
Step 5: Displaying information on a capture
select id,name,status,start_time,end_time,connects,user_calls,dir_path from dba_workload_captures where id = (select max(id) from dba_workload_captures) ;
Step 6: Generate/Export AWR data for the capture time.
- Manual generation of AWR report can be done
- DBMS_WORKLOAD_CAPTURE.EXPORT_AWR procedure can be used to export the AWR statistics and this is used to run with workload analyzer which will be explained later.
Step 7: Send the files to target
- All the files are placed in the physical directory pointed by database directory object. All these files should be transferred to the target site where the testing is intended to be done.
- Usage of any file transfer mechanism is fine.
Let's see the practical implementation now.
[oracle@12r1-rac1 RAT_workload]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Thu Feb 18 10:18:52 2016
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
SQL> -- Step 1
SQL> create or replace directory RAT_WORKLOAD as '/u02/RAT_workload/';
Directory created.
SQL> -- Step 2
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
System altered.
SQL> -- Step 3a
SQL> execute dbms_workload_capture.ADD_FILTER('RAT_FILTER','USER','SH');
PL/SQL procedure successfully completed.
SQL> -- Step 3 in combination with Step 3a (Capturing SH sessions)
SQL> execute dbms_workload_capture.start_capture('RAT_CAPTURE','RAT_WORKLOAD',default_action=>'EXCLUDE');
PL/SQL procedure successfully completed.
SQL> /*
SQL> -- Step 3b - Generate work load using Swingbench
SQL> -- See http://orabliss.blogspot.in/2016/02/swingbench-load-generator.html to check how to use Swingbench
SQL> -- swingbench.bat -c "E:\Program Files\swingbench\configs\shconfig.xml" -cs //192.168.56.101:1522/nonplug.selvapc.com
SQL> -- Generate load for upto 20 minutes
SQL> */
SQL>
SQL> -- Step 4
SQL> execute dbms_workload_capture.finish_capture();
PL/SQL procedure successfully completed.
SQL> -- Step 5
SQL> col name for a12
SQL> col status for a10
SQL> col dir_path for a25
SQL> set lines 300
SQL> select id,name,status,start_time,end_time,connects,user_calls,dir_path from dba_workload_captures
2 where id = (select max(id) from dba_workload_captures) ;
ID NAME STATUS START_TIM END_TIME CONNECTS USER_CALLS DIR_PATH
---------- ------------ ---------- --------- --------- ---------- ---------- -------------------------
12 RAT_CAPTURE COMPLETED 18-FEB-16 18-FEB-16 99 16588 /u02/RAT_workload/
SQL> -- Step 6
SQL> set pagesize 0 long 30000000 longchunksize 1000
SQL> select dbms_workload_capture.report(12,'TEXT') from dual;
Database Capture Report For NONPLUG
DB Name DB Id Release RAC Capture Name Status
------------ ----------- ----------- --- -------------------------- ----------
NONPLUG 3503558271 12.1.0.1.0 YES RAT_CAPTURE COMPLETED
Start time: 18-Feb-16 09:10:29 (SCN = 1124948)
End time: 18-Feb-16 09:50:25 (SCN = 1133291)
Duration: 39 minutes 56 seconds
Capture size: 4.31 MB
Directory object: RAT_WORKLOAD
Directory path: /u02/RAT_workload/
Directory shared in RAC: TRUE
Filters used: 1 INCLUSION filter
Captured Workload Statistics DB: NONPLUG Snaps: 50-51
-> 'Value' represents the corresponding statistic aggregated
across the entire captured database workload.
-> '% Total' is the percentage of 'Value' over the corresponding
system-wide aggregated total.
Statistic Name Value % Total
---------------------------------------- ------------- ---------
DB time (secs) 33451.41 99.83
Average Active Sessions 13.96
User calls captured 16588 85.82
User calls captured with Errors 0
Session logins 99 45.62
Transactions 0 0.00
-------------------------------------------------------------
Top Events Captured DB: NONPLUG Snaps: 50-51
Avg Active
Event Event Class % Event Sessions
----------------------------------- --------------- ---------- ----------
direct path read temp User I/O 63.00 9.49
direct path write temp User I/O 26.61 4.01
buffer busy waits Concurrency 1.36 0.20
CPU + Wait for CPU CPU 1.08 0.16
-------------------------------------------------------------
Top Service/Module Captured DB: NONPLUG Snaps: 50-51
Service Module % Activity Action % Action
-------------- ------------------------ ---------- ------------------ ----------
nonplug.selvap TopSalesWithinQuarter 58.27 UNNAMED 58.27
SalesRollupByMonth 8.53 UNNAMED 8.53
SalesCubeByMonth 8.22 UNNAMED 8.22
SalesByQuarterCountry 7.39 UNNAMED 7.39
SalesRollupByWeek 3.88 UNNAMED 3.88
-------------------------------------------------------------
Top SQL Captured DB: NONPLUG Snaps: 50-51
SQL ID % Activity Event % Event
----------------------- -------------- ------------------------------ -------
6vrsbutr8h1mk 6.65 direct path read temp 5.40
SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_n
ame, customers.cust_last_name, customers.cust_id, SUM(sales.amoun
t_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY S
UM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers,
direct path write temp 1.02
gmq00as592t0q 5.23 direct path read temp 4.21
SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_n
ame, customers.cust_last_name, customers.cust_id, SUM(sales.amoun
t_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY S
UM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers,
cw3ytzbdwd7n9 4.46 direct path read temp 3.54
SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_n
ame, customers.cust_last_name, customers.cust_id, SUM(sales.amoun
t_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY S
UM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers,
crhyk6rfu7kb8 4.18 direct path read temp 3.30
SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_n
ame, customers.cust_last_name, customers.cust_id, SUM(sales.amoun
t_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY S
UM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers,
cwtznzgvf29hh 4.18 direct path read temp 3.35
SELECT * FROM (SELECT times.calendar_quarter_desc, customers.cust_first_n
ame, customers.cust_last_name, customers.cust_id, SUM(sales.amoun
t_sold), rank() over(PARTITION BY times.calendar_quarter_desc ORDER BY S
UM(amount_sold) DESC) AS rank_within_quarter FROM sales, customers,
-------------------------------------------------------------
Top Sessions Captured DB: NONPLUG Snaps: 50-51
-> '# Samples Active' shows the number of ASH samples in which the session
was found waiting for that particular event. The percentage shown
in this column is calculated with respect to wall clock time
and not total database activity.
-> 'XIDs' shows the number of distinct transaction IDs sampled in ASH
when the session was waiting for that particular event
-> For sessions running Parallel Queries, this section will NOT aggregate
the PQ slave activity into the session issuing the PQ. Refer to
the 'Top Sessions running PQs' section for such statistics.
Sid, Serial# % Activity Event % Event
--------------- ---------- ------------------------------ ----------
User Program # Samples Active XIDs
-------------------- ------------------------------ ------------------ --------
77, 139 2.46 direct path read temp 1.72
SH JDBC Thin Client 62/240 [ 26%] 0
79, 15 2.46 direct path read temp 1.72
SH JDBC Thin Client 62/240 [ 26%] 0
102, 7 2.46 direct path read temp 1.86
SH JDBC Thin Client 67/240 [ 28%] 0
61, 69 2.41 direct path read temp 1.66
SH JDBC Thin Client 60/240 [ 25%] 0
70, 197 2.41 direct path read temp 1.72
SH JDBC Thin Client 62/240 [ 26%] 0
-------------------------------------------------------------
Top Events containing Unreplayable Calls DB: NONPLUG Snaps: 50-51
No data exists for this section of the report.
-------------------------------------------------------------
Top Service/Module containing Unreplayable Calls DB: NONPLUG Snaps: 50-51
No data exists for this section of the report.
-------------------------------------------------------------
Top SQL containing Unreplayable Calls DB: NONPLUG Snaps: 50-51
No data exists for this section of the report.
-------------------------------------------------------------
Top Sessions containing Unreplayable Calls DB: NONPLUG Snaps: 50-51
No data exists for this section of the report.
-------------------------------------------------------------
Top Events Filtered Out DB: NONPLUG Snaps: 50-51
No data exists for this section of the report.
-------------------------------------------------------------
Top Service/Module Filtered Out DB: NONPLUG Snaps: 50-51
No data exists for this section of the report.
-------------------------------------------------------------
Top SQL Filtered Out DB: NONPLUG Snaps: 50-51
No data exists for this section of the report.
-------------------------------------------------------------
Top Sessions Filtered Out DB: NONPLUG Snaps: 50-51
No data exists for this section of the report.
-------------------------------------------------------------
Top Events (Jobs and Background Activity) DB: NONPLUG Snaps: 50-51
Avg Active
Event Event Class % Event Sessions
----------------------------------- --------------- ---------- ----------
control file sequential read System I/O 2.74 0.41
log file parallel write System I/O 1.74 0.26
-------------------------------------------------------------
Top Service/Module (Jobs and Background Activity) DB: NONPLUG Snaps: 50-51
Service Module % Activity Action % Action
-------------- ------------------------ ---------- ------------------ ----------
SYS$BACKGROUND UNNAMED 5.57 UNNAMED 5.57
MMON_SLAVE 1.88 Automatic Report F 1.63
-------------------------------------------------------------
Top SQL (Jobs and Background Activity) DB: NONPLUG Snaps: 50-51
SQL ID % Activity Event % Event
----------------------- -------------- ------------------------------ -------
fhf8upax5cxsz 1.61 direct path write 0.75
BEGIN sys.dbms_auto_report_internal.i_save_report (:rep_ref, :snap_id, :pr_class
, :rep_id, :suc); END;
-------------------------------------------------------------
Top Sessions (Jobs and Background Activity) DB: NONPLUG Snaps: 50-51
-> '# Samples Active' shows the number of ASH samples in which the session
was found waiting for that particular event. The percentage shown
in this column is calculated with respect to wall clock time
and not total database activity.
-> 'XIDs' shows the number of distinct transaction IDs sampled in ASH
when the session was waiting for that particular event
-> For sessions running Parallel Queries, this section will NOT aggregate
the PQ slave activity into the session issuing the PQ. Refer to
the 'Top Sessions running PQs' section for such statistics.
Sid, Serial# % Activity Event % Event
--------------- ---------- ------------------------------ ----------
User Program # Samples Active XIDs
-------------------- ------------------------------ ------------------ --------
21, 1 1.77 log file parallel write 1.74
SYS oracle@12r1-ra...pc.com (LGWR) 63/240 [ 26%] 0
22, 1 1.52 control file sequential read 1.00
SYS oracle@12r1-ra...pc.com (CKPT) 36/240 [ 15%] 0
13, 1 1.27 control file sequential read 1.27
SYS oracle@12r1-ra...pc.com (LMON) 46/240 [ 19%] 0
-------------------------------------------------------------
Workload Filters DB: NONPLUG Snaps: 50-51
# Filter Name Type Attribute Value
--- ------------------------ ------- ------------ --------------------------
1 RAT_FILTER INCLUDE USER SH
-------------------------------------------------------------
End of Report
SQL> -- Export AWR data for the capture time
SQL> exec DBMS_WORKLOAD_CAPTURE.EXPORT_AWR (12);
PL/SQL procedure successfully completed.
SQL> -- Also take a manual AWR report for our analysis if required.
SQL> select id,AWR_BEGIN_SNAP,AWR_END_SNAP from dba_workload_captures;
1 41 42
12 50 51
SQL> @?/rdbma/admin/awrrpt.sql
SQL> -- Provide Begin snap = 50 and End snap = 51
SQL> -- Provide Report name = rat_capture1.html and generate the report.
SQL>
SQL> -- Step 7
SQL> !
[oracle@12r1-rac1 RAT_workload]$ ls -lrt
total 900
drwxr-xr-x. 3 oracle oinstall 4096 Feb 18 09:10 capfiles
drwxr-xr-x. 2 oracle oinstall 4096 Feb 18 09:51 cap
-rw-r--r--. 1 oracle oinstall 912327 Feb 18 09:58 rat_capture1.html
[oracle@12r1-rac1 RAT_workload]$ scp -r * oracle@192.168.56.131:/home/oracle/RAT_workload/
Password:
wcr_cr.text 100% 24KB 23.9KB/s 00:00
wcr_scapture.wmd 100% 123 0.1KB/s 00:00
wcr_cr.xml 100% 460KB 460.0KB/s 00:00
wcr_cr.html 100% 60KB 59.6KB/s 00:00
wcr_fcapture.wmd 100% 239 0.2KB/s 00:00
wcr_cahhmh000003w.rec 100% 93KB 93.2KB/s 00:00
wcr_cah2ch000002b.rec 100% 1209 1.2KB/s 00:00
....
....
....
wcr_cagv5h000001x.rec 100% 136 0.1KB/s 00:00
wcr_cag9bh0000000.rec 100% 136 0.1KB/s 00:00
rat_capture1.html 100% 891KB 890.9KB/s 00:00
[oracle@12r1-rac1 RAT_workload]$ exit
exit
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Advanced Analytics and Real Application Testing options
Happy Testing!!
No comments:
Post a Comment