Looking for something? Try here..

Friday, February 19, 2016

Oracle Real Application Testing - Demo Part 1

The "What is RAT?" answer goes as below
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
- Information gathered on the capture can be obtained by using the dbms_workload_capture.report procedure providing the id obtained by the below query.

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
Please check on the part 2 for the rest of the process...

Happy Testing!!

No comments:

Post a Comment