Looking for something? Try here..

Friday, February 19, 2016

Oracle Real Application Testing - Demo Part 2

As the Workload Capture has been explained in the first part of the post, now lets see how to proceed with the rest of the replay activity.

2. Workload processing

Workload processing or analyzer is mainly used to rectify any issues that the Capture has or to prepare for the replay that we are about to perform on the target database.
-- If an error can be prevented, the Workload Analysis report displays available preventive actions that can be implemented before attempting replay.
-- If an error cannot be corrected, the report provides a description of the error so that it can be accounted for during the replay.
-- An instance identical to the instance used for the capture should exist - by identical, it should be logically the same. You should also consider setting the system time on the replay machine to be the same as that prior to the capture being run on the original instance to ensure that time sensitive operations (operations that depend on sysdate, etc) replay succeed accurately without issues.
-- So in our case, SH schema and it's objects should be available in the target database with its relevant data.

Prerequisites
  1. Java runtime environment (JRE) version 1.5 or above should be present along with ojdbcNN.jar file located in the $ORACLE_HOME/jdbc/lib/ directory -- NN represents the version number
  2. EXECUTE privileges for the DBMS_WORKLOAD_CAPTURE package
  3. SELECT_CATALOG role on the target database (to user who performs Replay).
  4. Workload directory should also contain exported AWR data for the workload capture. -- this is explained in part 1, step 6.
Step 1: Analyze and import the AWR collected from source
Workload Analyzer is composed of two JAR files dbranalyzer.jar & dbrparser.jar located in the $ORACLE_HOME/rdbms/jlib/

Start by the below command.
java -classpath $ORACLE_HOME/jdbc/lib/ojdbc7.jar:$ORACLE_HOME/rdbms/jlib/dbrparser.jar:$ORACLE_HOME/rdbms/jlib/dbranalyzer.jar: oracle.dbreplay.workload.checker.CaptureChecker /home/oracle/RAT_workload jdbc:oracle:thin:@192.168.56.131:1521:susedb12

or if you would like to skip analyzer and just import the AWR data use the below

select DBMS_WORKLOAD_CAPTURE.IMPORT_AWR (capture_id => 1, staging_schema => 'opensuse') FROM DUAL; -- to import exported AWR data from source.

Step 2: Preparing for the Replay
1. Preprocess the Workload directory 
-- using DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE procedure

2. Find the number of client processes required to start the Replay process
-- wrc utility will be used to find the processes required

3. Put the database in a state of readiness for replaying the workload and prepare for the replay
-- DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY and DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY procedures will be used

4. Start the clients
-- wrc utility will be used to start the client
See the implementation section for the commands in action.

3. Workload Replay

Once all the preparations are complete, we can start the Replay on the target database. 
-- DBMS_WORKLOAD_REPLAY.START_REPLAY procedure will be used to start the replay.
-- dba_workload_replays view can be used to check the status of the replay.

4. Analysis and reporting

A complete report of the replay can be obtained by running the following query
select dbms_workload_replay.report(21,'TEXT') from dual;

Now lets see the practical implementation and later a small note on connection mapping

Practicals starts here
oracle@opensuse:~/RAT_workload> 
oracle@opensuse:~/RAT_workload> pwd
/home/oracle/RAT_workload
oracle@opensuse:~/RAT_workload> ls -lrt
total 892
drwxr-xr-x 2 oracle oinstall    175 Feb 19 10:23 cap
drwxr-xr-x 3 oracle oinstall     19 Feb 19 10:23 capfiles
-rw-r--r-- 1 oracle oinstall 912327 Feb 19 10:23 rat_capture1.html
oracle@opensuse:~/RAT_workload> echo $ORACLE_HOME
/u01/app/oracle/product/12.1/dbhome
oracle@opensuse:~/RAT_workload> echo $PATH
/home/oracle/bin:/usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/games:/u01/app/oracle/product/12.1/dbhome/bin:/u01/app/oracle/product/12.1/oggcore_1:/u01/app/oracle/product/12.1/dbhome/bin:/u01/app/oracle/product/12.1/oggcore_1:/u01/app/oracle/product/12.1/dbhome/bin:/u01/app/oracle/product/12.1/oggcore_1
oracle@opensuse:~/RAT_workload> # Step 1
oracle@opensuse:~/RAT_workload> java -classpath $ORACLE_HOME/jdbc/lib/ojdbc7.jar:$ORACLE_HOME/rdbms/jlib/dbrparser.jar:$ORACLE_HOME/rdbms/jlib/dbranalyzer.jar: oracle.dbreplay.workload.checker.CaptureChecker /home/oracle/RAT_workload jdbc:oracle:thin:@192.168.56.131:1521:susedb12
Enter database username: 
opensuse
Enter password: 
 
Importing AWR data from directory '/home/oracle/RAT_workload'
        Capture ID: 61
        AWR dbid: 1392077117
        Snapshots range: 50 -> 51
AWR import is done!
Analysis done!
oracle@opensuse:~/RAT_workload> ls -lrt
total 900
drwxr-xr-x 3 oracle oinstall     19 Feb 19 10:23 capfiles
-rw-r--r-- 1 oracle oinstall 912327 Feb 19 10:23 rat_capture1.html
drwxr-xr-x 2 oracle oinstall    195 Feb 19 10:26 cap
-rw-r--r-- 1 oracle oinstall   1148 Feb 19 10:27 wcr_cap_analysis.xml
-rw-r--r-- 1 oracle oinstall   3881 Feb 19 10:27 wcr_cap_analysis.html
oracle@opensuse:~/RAT_workload> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 19 10:41:30 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> -- Step 2 (1)
SQL> -- Create directory object for further activities
SQL> create or replace directory RAT_WORKLOAD as '/home/oracle/RAT_workload/';

Directory created.

SQL> exec DBMS_WORKLOAD_REPLAY.PROCESS_CAPTURE (capture_dir => 'RAT_WORKLOAD');

PL/SQL procedure successfully completed.

SQL> !
oracle@opensuse:~/RAT_workload> ls -lrt
total 900
drwxr-xr-x 3 oracle oinstall     19 Feb 19 10:23 capfiles
-rw-r--r-- 1 oracle oinstall 912327 Feb 19 10:23 rat_capture1.html
drwxr-xr-x 2 oracle oinstall    195 Feb 19 10:26 cap
-rw-r--r-- 1 oracle oinstall   1148 Feb 19 10:27 wcr_cap_analysis.xml
-rw-r--r-- 1 oracle oinstall   3881 Feb 19 10:27 wcr_cap_analysis.html
drwxr-xr-x 2 oracle oinstall    268 Feb 19 10:52 pp12.1.0.2.0
oracle@opensuse:~/RAT_workload> # A new directory pp12.1.0.2.0 has been created containing info
oracle@opensuse:~/RAT_workload> # Step 2 (2)
oracle@opensuse:~/RAT_workload> wrc replaydir=/home/oracle/RAT_workload mode=calibrate

Workload Replay Client: Release 12.1.0.2.0 - Production on Fri Feb 19 22:14:27 2016

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


Report for Workload in: /home/oracle/RAT_workload
-----------------------

Recommendation:
Consider using at least 1 clients divided among 1 CPU(s)
You will need at least 150 MB of memory per client process.
If your machine(s) cannot match that number, consider using more clients.

Workload Characteristics:
- max concurrency: 40 sessions
- total number of sessions: 57

Assumptions:
- 1 client process per 50 concurrent sessions
- 4 client processes per CPU
- 256 KB of memory cache per concurrent session
- think time scale = 100
- connect time scale = 100
- synchronization = TRUE

oracle@opensuse:~/RAT_workload> exit
exit

SQL> -- Step 2 (3)
SQL> execute DBMS_WORKLOAD_REPLAY.INITIALIZE_REPLAY('test_replay_1', 'RAT_WORKLOAD');

PL/SQL procedure successfully completed.

SQL> execute DBMS_WORKLOAD_REPLAY.PREPARE_REPLAY(SYNCHRONIZATION => 'OBJECT_ID');

PL/SQL procedure successfully completed.

SQL> !
oracle@opensuse:~/RAT_workload> # Step 2 (4)
oracle@opensuse:~/RAT_workload> # Starting 1 client process here. If you would like to start more, do so by starting using another session
oracle@opensuse:~/RAT_workload> # This session will be used to monitor until Replay completes.
oracle@opensuse:~/RAT_workload> wrc replaydir=/home/oracle/RAT_workload

Workload Replay Client: Release 12.1.0.2.0 - Production on Fri Feb 19 22:19:29 2016

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


Username:opensuse
Password:
Wait for the replay to start (22:19:44)
 
 

oracle@opensuse:~/RAT_workload> # Step 3. Workload Replay
oracle@opensuse:~/RAT_workload> echo $ORACLE_HOME
/u01/app/oracle/product/12.1/dbhome
oracle@opensuse:~/RAT_workload> echo $ORACLE_SID
susedb12
oracle@opensuse:~/RAT_workload> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Feb 19 22:23:00 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> execute DBMS_WORKLOAD_REPLAY.START_REPLAY;

PL/SQL procedure successfully completed.

SQL> 
 

Meanwhile in the Terminal-1 you can see the progress..
oracle@opensuse:~/RAT_workload> wrc replaydir=/home/oracle/RAT_workload

Workload Replay Client: Release 12.1.0.2.0 - Production on Fri Feb 19 22:19:29 2016

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


Username:opensuse
Password:
Wait for the replay to start (22:19:44)
Replay client 1 started (22:28:50)
 
 
You can check the progress in the database using the below.
SQL> select id, name,status from dba_workload_replays;

        ID NAME                           STATUS
---------- ------------------------------ ----------------------------------------
         1 test_replay_1                  COMPLETED
        21 test_replay_1                  IN PROGRESS
        11 test_replay_2                  COMPLETED

SQL> select id, name,status from dba_workload_replays;

        ID NAME                           STATUS
---------- ------------------------------ ----------------------------------------
         1 test_replay_1                  COMPLETED
        21 test_replay_1                  COMPLETED
        11 test_replay_2                  COMPLETED

SQL> -- I have already run the tests for other combinations and hence the result.
SQL> -- We are now interested in ID 21 which is now completed.
SQL>

oracle@opensuse:~/RAT_workload> wrc replaydir=/home/oracle/RAT_workload

Workload Replay Client: Release 12.1.0.2.0 - Production on Fri Feb 19 22:19:29 2016

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


Username:opensuse
Password:
Wait for the replay to start (22:19:44)
Replay client 1 started (22:28:50)

Replay client 1 finished (22:40:36)
oracle@opensuse:~/RAT_workload> # We can see the Replay status as finished which took 
oracle@opensuse:~/RAT_workload> # only 12 minutes against our capture of 39 min 56 sec with 20 minutes regression
oracle@opensuse:~/RAT_workload> exit
exit

SQL> -- Step 4. Analysis and Reporting
SQL> set lines 300
SQL> set pagesize 0 long 30000000 longchunksize 1000
SQL> select dbms_workload_replay.report(21,'TEXT') from dual;
DB Replay Report for test_replay_1
---------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------
| DB Name  | DB Id      | Release    | RAC | Replay Name   | Replay Status |
----------------------------------------------------------------------------
| SUSEDB12 | 2345262617 | 12.1.0.2.0 | NO  | test_replay_1 | COMPLETED     |
----------------------------------------------------------------------------

Replay Information
----------------------------------------------------------------------------------
|     Information      | Replay                     | Capture                    |
----------------------------------------------------------------------------------
| Name                 | test_replay_1              | RAT_CAPTURE                |
----------------------------------------------------------------------------------
| Status               | COMPLETED                  | COMPLETED                  |
----------------------------------------------------------------------------------
| Database Name        | SUSEDB12                   | NONPLUG                    |
----------------------------------------------------------------------------------
| Database Version     | 12.1.0.2.0                 | 12.1.0.1.0                 |
----------------------------------------------------------------------------------
| Start Time           | 19-02-16 16:58:49          | 18-02-16 09:10:29          |
----------------------------------------------------------------------------------
| End Time             | 19-02-16 17:07:22          | 18-02-16 09:50:25          |
----------------------------------------------------------------------------------
| Duration             | 8 minutes 33 seconds       | 39 minutes 56 seconds      |
----------------------------------------------------------------------------------
| Directory Object     | RAT_WORKLOAD               | RAT_WORKLOAD               |
----------------------------------------------------------------------------------
| Directory Path       | /home/oracle/RAT_workload/ | /home/oracle/RAT_workload/ |
----------------------------------------------------------------------------------
| AWR DB Id            | 2345262617                 |                            |
----------------------------------------------------------------------------------
| AWR Begin Snap Id    | 82                         |                            |
----------------------------------------------------------------------------------
| AWR End Snap Id      | 83                         |                            |
----------------------------------------------------------------------------------
| Replay Schedule Name |                            |                            |
----------------------------------------------------------------------------------

Replay Options
---------------------------------------------------------
|       Option Name       | Value                       |
---------------------------------------------------------
| Synchronization         | OBJECT_ID                   |
---------------------------------------------------------
| Connect Time            | 100%                        |
---------------------------------------------------------
| Think Time              | 100%                        |
---------------------------------------------------------
| Think Time Auto Correct | TRUE                        |
---------------------------------------------------------
| Number of WRC Clients   | 1 (1 Completed, 0 Running ) |
---------------------------------------------------------

Replay Statistics
------------------------------------------------------------------
|        Statistic        | Replay           | Capture           |
------------------------------------------------------------------
| DB Time                 | 4403.231 seconds | 33451.412 seconds |
------------------------------------------------------------------
| Average Active Sessions |             8.58 |             13.96 |
------------------------------------------------------------------
| User calls              |            16588 |             16588 |
------------------------------------------------------------------

Replay Divergence Summary

-------------------------------------------------------------------
|                Divergence Type                | Count | % Total |
-------------------------------------------------------------------
| Session Failures During Replay                |     0 |    0.00 |
-------------------------------------------------------------------
| Errors No Longer Seen During Replay           |     0 |    0.00 |
-------------------------------------------------------------------
| New Errors Seen During Replay                 |     0 |    0.00 |
-------------------------------------------------------------------
| Errors Mutated During Replay                  |     0 |    0.00 |
-------------------------------------------------------------------
| DMLs with Different Number of Rows Modified   |     0 |    0.00 |
-------------------------------------------------------------------
| SELECTs with Different Number of Rows Fetched |     0 |    0.00 |
-------------------------------------------------------------------

---------------------------------------------------------------------------------------------------
Workload Profile Top Events
------------------------------------------------------
| Event                   | Event Class | % Activity |
------------------------------------------------------
| resmgr:cpu quantum      | Scheduler   |      77.75 |
------------------------------------------------------
| CPU + Wait for CPU      | CPU         |      18.22 |
------------------------------------------------------
| read by other session   | User I/O    |       1.48 |
------------------------------------------------------
| db file parallel read   | User I/O    |       1.27 |
------------------------------------------------------
| db file sequential read | User I/O    |       0.21 |
------------------------------------------------------
Top Service/Module/Action
---------------------------------------------------------------------------------------------------
| Service Name | Module Name                      | % Activity | Action Drilldown                 |
---------------------------------------------------------------------------------------------------
| SYS$USERS    | SalesRollupByMonth               |      24.36 | -------------------------------- |
|              |                                  |            | | Action Name | % Activity     | |
|              |                                  |            | -------------------------------- |
|              |                                  |            | | UNNAMED     |          24.36 | |
|              |                                  |            | -------------------------------- |
---------------------------------------------------------------------------------------------------
| SYS$USERS    | SalesCubeByMonth                 |      21.82 | -------------------------------- |
|              |                                  |            | | Action Name | % Activity     | |
|              |                                  |            | -------------------------------- |
|              |                                  |            | | UNNAMED     |          21.82 | |
|              |                                  |            | -------------------------------- |
---------------------------------------------------------------------------------------------------
| SYS$USERS    | TopSalesWithinQuarter            |      15.89 | -------------------------------- |
|              |                                  |            | | Action Name | % Activity     | |
|              |                                  |            | -------------------------------- |
|              |                                  |            | | UNNAMED     |          15.89 | |
|              |                                  |            | -------------------------------- |
---------------------------------------------------------------------------------------------------
| SYS$USERS    | SalesMovingAverage               |      12.50 | -------------------------------- |
|              |                                  |            | | Action Name | % Activity     | |
|              |                                  |            | -------------------------------- |
|              |                                  |            | | UNNAMED     |          12.50 | |
|              |                                  |            | -------------------------------- |
---------------------------------------------------------------------------------------------------
| SYS$USERS    | SalesByQuarterCountry            |       6.78 | -------------------------------- |
|              |                                  |            | | Action Name | % Activity     | |
|              |                                  |            | -------------------------------- |
|              |                                  |            | | UNNAMED     |           6.78 | |
|              |                                  |            | -------------------------------- |
---------------------------------------------------------------------------------------------------
Top SQL with Top Events
---------------------------------------------------------------------------------------------------
| SQL ID        | Planhash   | Sampled Number of         | % Activity | Event Drilldown           |
|               |            | Executions                |            |                           |
---------------------------------------------------------------------------------------------------
| 6vrsbutr8h1mk | 121580580  | 9                         |       1.91 | ------------------------- |
|               |            |                           |            | | Event     | %         | |
|               |            |                           |            | |           | Activity  | |
|               |            |                           |            | ------------------------- |
|               |            |                           |            | | resmgr:cp |      1.91 | |
|               |            |                           |            | | u quantum |           | |
|               |            |                           |            | ------------------------- |
---------------------------------------------------------------------------------------------------
| 5u3nfctgbckmr | 121580580  | 7                         |       1.48 | ------------------------- |
|               |            |                           |            | | Event     | %         | |
|               |            |                           |            | |           | Activity  | |
|               |            |                           |            | ------------------------- |
|               |            |                           |            | | resmgr:cp |      1.27 | |
|               |            |                           |            | | u quantum |           | |
|               |            |                           |            | ------------------------- |
|               |            |                           |            | | CPU +     |      0.21 | |
|               |            |                           |            | | Wait for  |           | |
|               |            |                           |            | | CPU       |           | |
|               |            |                           |            | ------------------------- |
---------------------------------------------------------------------------------------------------
| av4bt2kryg5md | 121580580  | 7                         |       1.48 | ------------------------- |
|               |            |                           |            | | Event     | %         | |
|               |            |                           |            | |           | Activity  | |
|               |            |                           |            | ------------------------- |
|               |            |                           |            | | resmgr:cp |      1.48 | |
|               |            |                           |            | | u quantum |           | |
|               |            |                           |            | ------------------------- |
---------------------------------------------------------------------------------------------------
| cw3ytzbdwd7n9 | 121580580  | 6                         |       1.27 | ------------------------- |
|               |            |                           |            | | Event     | %         | |
|               |            |                           |            | |           | Activity  | |
|               |            |                           |            | ------------------------- |
|               |            |                           |            | | CPU +     |      0.64 | |
|               |            |                           |            | | Wait for  |           | |
|               |            |                           |            | | CPU       |           | |
|               |            |                           |            | ------------------------- |
|               |            |                           |            | | resmgr:cp |      0.64 | |
|               |            |                           |            | | u quantum |           | |
|               |            |                           |            | ------------------------- |
---------------------------------------------------------------------------------------------------
| gmq00as592t0q | 1940277083 | 6                         |       1.27 | ------------------------- |
|               |            |                           |            | | Event     | %         | |
|               |            |                           |            | |           | Activity  | |
|               |            |                           |            | ------------------------- |
|               |            |                           |            | | resmgr:cp |      1.27 | |
|               |            |                           |            | | u quantum |           | |
|               |            |                           |            | ------------------------- |
---------------------------------------------------------------------------------------------------
Top Sessions with Top Events
--------------------------------------------------------------------------------------------------
| Session ID | Session Serial    | Username | Program           | % Activity | Event Drilldown   |
--------------------------------------------------------------------------------------------------
| 34         | 61837             | SH       | wrc@opensuse.selv |       3.39 | ----------------- |
|            |                   |          | apc.com (TNS      |            | | Event | % Act | |
|            |                   |          | V1-V3)            |            | |       | ivity | |
|            |                   |          |                   |            | ----------------- |
|            |                   |          |                   |            | | resmg |  2.97 | |
|            |                   |          |                   |            | | r:cpu |       | |
|            |                   |          |                   |            | | quant |       | |
|            |                   |          |                   |            | | um    |       | |
|            |                   |          |                   |            | ----------------- |
|            |                   |          |                   |            | | CPU + |  0.42 | |
|            |                   |          |                   |            | | Wait  |       | |
|            |                   |          |                   |            | | for   |       | |
|            |                   |          |                   |            | | CPU   |       | |
|            |                   |          |                   |            | ----------------- |
--------------------------------------------------------------------------------------------------
| 49         | 32961             | SH       | wrc@opensuse.selv |       2.97 | ----------------- |
|            |                   |          | apc.com (TNS      |            | | Event | % Act | |
|            |                   |          | V1-V3)            |            | |       | ivity | |
|            |                   |          |                   |            | ----------------- |
|            |                   |          |                   |            | | resmg |  1.91 | |
|            |                   |          |                   |            | | r:cpu |       | |
|            |                   |          |                   |            | | quant |       | |
|            |                   |          |                   |            | | um    |       | |
|            |                   |          |                   |            | ----------------- |
|            |                   |          |                   |            | | CPU + |  1.06 | |
|            |                   |          |                   |            | | Wait  |       | |
|            |                   |          |                   |            | | for   |       | |
|            |                   |          |                   |            | | CPU   |       | |
|            |                   |          |                   |            | ----------------- |
--------------------------------------------------------------------------------------------------
| 270        | 21487             | SH       | wrc@opensuse.selv |       2.97 | ----------------- |
|            |                   |          | apc.com (TNS      |            | | Event | % Act | |
|            |                   |          | V1-V3)            |            | |       | ivity | |
|            |                   |          |                   |            | ----------------- |
|            |                   |          |                   |            | | resmg |  2.75 | |
|            |                   |          |                   |            | | r:cpu |       | |
|            |                   |          |                   |            | | quant |       | |
|            |                   |          |                   |            | | um    |       | |
|            |                   |          |                   |            | ----------------- |
|            |                   |          |                   |            | | CPU + |  0.21 | |
|            |                   |          |                   |            | | Wait  |       | |
|            |                   |          |                   |            | | for   |       | |
|            |                   |          |                   |            | | CPU   |       | |
|            |                   |          |                   |            | ----------------- |
--------------------------------------------------------------------------------------------------
| 43         | 50193             | SH       | wrc@opensuse.selv |       2.75 | ----------------- |
|            |                   |          | apc.com (TNS      |            | | Event | % Act | |
|            |                   |          | V1-V3)            |            | |       | ivity | |
|            |                   |          |                   |            | ----------------- |
|            |                   |          |                   |            | | resmg |  2.75 | |
|            |                   |          |                   |            | | r:cpu |       | |
|            |                   |          |                   |            | | quant |       | |
|            |                   |          |                   |            | | um    |       | |
|            |                   |          |                   |            | ----------------- |
--------------------------------------------------------------------------------------------------
| 290        | 11101             | SH       | wrc@opensuse.selv |       2.75 | ----------------- |
|            |                   |          | apc.com (TNS      |            | | Event | % Act | |
|            |                   |          | V1-V3)            |            | |       | ivity | |
|            |                   |          |                   |            | ----------------- |
|            |                   |          |                   |            | | resmg |  2.33 | |
|            |                   |          |                   |            | | r:cpu |       | |
|            |                   |          |                   |            | | quant |       | |
|            |                   |          |                   |            | | um    |       | |
|            |                   |          |                   |            | ----------------- |
|            |                   |          |                   |            | | CPU + |  0.42 | |
|            |                   |          |                   |            | | Wait  |       | |
|            |                   |          |                   |            | | for   |       | |
|            |                   |          |                   |            | | CPU   |       | |
|            |                   |          |                   |            | ----------------- |
--------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------
Replay Divergence Session Failures By Application
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
Error Divergence By Application
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
By SQL
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
DML Data Divergence By Application
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
By SQL
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
By Divergence magnitude
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
SELECT Data Divergence By Application
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
By Divergence magnitude
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
---------------------------------------------------------------------------------------------------
Replay Clients Alerts
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------
Replay Filters
--------------------------------------------------
| No data exists for this section of the report. |
--------------------------------------------------

End of Report.


SQL> 


You can see the report which shows we have "resmgr:cpu quantum" wait events. This is due to the Resource manager plan active in the database. You can disable the Resource manager and rerun the Replay to see the increase in performance.
Resource manager can be disabled by the below command
alter system set resource_manager_plan='' scope=both;

Connection Mapping:
What if you would like to perform the Replay activity on a RAC database? What if you would like to reroute some of the connections (DB links, etc) to other targets? Connection mapping can help you out in this case.
Connection mapping can be used as below for a RAC environment and should be done before the Replay activity is performed.
BEGIN
    DBMS_WORKLOAD_REPLAY.REMAP_CONNECTION(
         connection_id => 1
        ,replay_connection => '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=12r1-rac1-vip)(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=12r1-rac2-vip)(PORT=1521))(LOAD_BALANCE=YES)(FAILOVER=YES)(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=NONPLUG)))'
    );
END;
/

This will use the load balancing option of the RAC environment which can be used to scale the exact performance gain one would achieve without actually migrating the database to a RAC database.

Happy Testing!!

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!!

Friday, February 5, 2016

Swingbench load generator

Swingbench is a free load generator (and benchmarks) designed to stress test an Oracle database (10g,11g,12c). This very useful tool is developed by Dominic Giles and all the details can be obtained from here.
In this post, I'll just show you the basics on how to use Swingbench for DB load generation.
I'm using Windows 10 as client machine and hence the path and file names will be of Windows. If you at any point of time has any clarifications you can always refer to the actual Swingbench site referred above and also comment your queries and I'll try to provide with the necessary information.

Now let's get into action.

Step 1:
Download the Swingbench software from here and extract the contents to a desired directory using unzip (on Unix) or winZIP or winRAR (on Windows).
Note: You should have JVM of version at least Java 6 or higher on the client machine.

Step 2:
Go to the winbin directory (in windows) or bin directory (in Unix) under swingbench root directory and start the swingbench.bat (double click). This will automatically start a command prompt with the following commands.

The Java graphical window for the Swingbench tool will open as similar to the below screenshot.

Swingbench utilizes Sales history (SH), Order Entry (OE) and Calling Circuit (CC) schema and its objects to perform the load tests. One should install these wizards in order to use them. If the sample schemas has been installed in the database, then this installation is not required. The schema can be installed by calling the shwizard.bat, oewizard.bat and ccwizard.bat. Each of the wizards has their own schema and objects with data created. 

So this should be the prerequisite necessary to be satisfied. I'll show how to create Order Entry Schema.

Step 0.a:

Note: I have only invoked the oewizard.bat executable which in turn calls the java executable (4th line) and also brings up the below GUI. 

Click Next --> Next --> Next --> 
The below screen appears.

Edit the details as per your environment. I'm using as below which connects to my ORCL database for schema and objects creation.
Click Next -->
Enter username (of your choice and use the same in testing/load generation), password and tablespace. If tablespace is already present, datafile will be automatically captured or provide the file name.

Click Next --> Change the partitioning and other options if desired
Click Next --> Enter sizing details of tablespace and data as desired
Click Next to arrive at the final configuration screen as below.

Click Finish to begin the installation.

Schema installation begins and log screen appears as below.

This take a few minutes as to build the OE schema. The following screen confirms completion of schema creation.

Now after schema creation, we can start the Swingbench which by default uses OE config xml for the load generation. swingbench.bat will invoke OE load generation as explained in the beginning of the post (step 2).

Step 3:
In case we would like to invoke load tests with SH schema we can call the SH configurations from the command line as below. Note: shwizard.bat has to be run prior to calling SH configuration (similar to Step 0.a) as schema has to be created for successful test (can be ignored if SH is installed already). 

I have only issued the following command which in turn invokes the java command and the CC GUI as below
swingbench.bat -c "E:\Program Files\swingbench\configs\shconfig.xml" -cs //192.168.1.99/orcl



Step 4:
See that I have already made the changes that i require as per my load generation (blue marker). You can change the settings as per your needs. 
Click on the start button (Green button) in the left top corner to start the load generation.
Let the tool do some operations in the database and generate load. Leave it for the amount of time you desire the load be generated. 
The screen while tool is running will look as below.

Once the load is started, the tool automatically shows the events page and the GUI starts giving the number of transactions that happens per minute and per second (blue marker). 
We can select different chart types, the above shown being the overview type and the others being 
1. Transactions per minute
2. Transaction response time
3. DML operations per minute

These charts are dynamically changeable. 

Step 5:
Once you decide to stop the load testing, you can stop the tool by clicking on the stop button (red button) near the grayed out start button at the left top corner.
Now all the sessions logged in will log out and the load generation will stop.

One most important feature of the Swingbench is that the ability to generate load using user defined stored procedures via the spconfig.xml config file. 
In order to achieve this, the storedprocedures.sql script under the <swingbench root>/sql directory should be run against the database which creates a Type, a package and a package body.
The user under which the package is created can be any user that you desire but should be granted execute on dbms_lock.
E:\Program Files\swingbench\sql>sqlplus sh/sh @storedprocedures.sql

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 4 23:47:19 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


Type created.


Package created.


Package body created.

SQL> select object_name, object_type from user_objects where object_type in ('TYPE','PACKAGE','PACKAGE BODY');

OBJECT_NAME                    OBJECT_TYPE
------------------------------ -------------------
INTEGER_RETURN_ARRAY           TYPE
SWINGBENCH                     PACKAGE
SWINGBENCH                     PACKAGE BODY

SQL>

The main content of the package body is the functions that are as below where the user changes are required to execute against the database objects. The are 6 functions in total that one can use by default.
..
..
  function storedprocedure1(min_sleep integer, max_sleep integer) return integer_return_array is
    begin
      init_dml_array();
      sleep(min_sleep, max_sleep);
      return info_array;
  end storedprocedure1;
  function storedprocedure2(min_sleep integer, max_sleep integer) return integer_return_array is
    begin
      init_dml_array();
      sleep(min_sleep, max_sleep);
      return info_array;
  end storedprocedure2;
  function storedprocedure3(min_sleep integer, max_sleep integer) return integer_return_array is
    begin
      init_dml_array();
      sleep(min_sleep, max_sleep);
      return info_array;
  end storedprocedure3;
  function storedprocedure4(min_sleep integer, max_sleep integer) return integer_return_array is
    begin
      init_dml_array();
      sleep(min_sleep, max_sleep);
      return info_array;
  end storedprocedure4;
  function storedprocedure5(min_sleep integer, max_sleep integer) return integer_return_array is
    begin
      init_dml_array();
      sleep(min_sleep, max_sleep);
      return info_array;
  end storedprocedure5;
  function storedprocedure6(min_sleep integer, max_sleep integer) return integer_return_array is
    begin
      init_dml_array();
      sleep(min_sleep, max_sleep);
      return info_array;
  end storedprocedure6;
..
..
Now I'm making the desired change in one of the function in the package body as below.
..
..
..
      return info_array;
  end storedprocedure2;
  function storedprocedure3(min_sleep integer, max_sleep integer) return integer_return_array is
    begin
      init_dml_array();
      sleep(min_sleep, max_sleep);
      insert into demo values (trunc(dbms_random.value(1,10000)), dbms_random.string('u','10'));
      commit;
      increment_inserts(1);
      increment_commits(1);
      return info_array;
  end storedprocedure3;
  function storedprocedure4(min_sleep integer, max_sleep integer) return integer_return_array is
    begin
      init_dml_array();
..
..
..
I've made the function storedprocedure3 to execute the insert statement on to a user table and increment the number of inserts and commits (highlighted in the code). One can have any operation placed in the code to be executed and the load be performed by the tool.

Now to make use of the procedure, we now need to start the Swingbench with spconfig.xml config file as below.

See the command that I used is as below.

swingbench.bat -c "E:\Program Files\swingbench\configs\spconfig.xml" -cs //192.168.1.99/orcl

The following screen is invoked

See that the user name which you intent to run should be changed and the stored procedures that can be activated as per your needs. All the SPs here do nothing but only SP3 here in our test. If you wish not to activate the other functions, you can do so by checking the tick box.
Click on start to run the test and stop when desired (similar to step 4 and step 5). The screen when the tool is running looks as below. We can view the DML that is being performed.
Note that I have selected only the SP3 to be run against the database. 

This fairly ends up on how to use the Swingbench tool to load test your database. 

Some advantages and perks:
  • Swingbench can be run against a single node database and also against a RAC database.
  • Can be used to benchmark the scalability of a cluster database by invoking a coordinator and minibench utility to generate load on both the instances. Then the clusteroverview executable can be invoked to compare the scalability live.
  • With minibench and charbench, user has multiple option to load test according to their requirement. The details of these can be obtained from here
Happy load testing!!

Thursday, February 4, 2016

Flash recovery area limit exceeded

Here is a simple basic troubleshooting that happened today.
I was starting the database to encounter the error below.
SQL> startup
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1375792 bytes
Variable Size             662700496 bytes
Database Buffers          402653184 bytes
Redo Buffers                4603904 bytes
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 5636
Session ID: 5 Serial number: 3

Checking the alert log for what has just happened and why is this throwing an error, I can find the following in the alert log file.
...
...
Errors in file e:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_5636.trc:
ORA-19815: WARNING: db_recovery_file_dest_size of 4039114752 bytes is 100.00% used, and has 0 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************
Errors in file e:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_5636.trc:
ORA-19809: limit exceeded for recovery files
ORA-19804: cannot reclaim 41948672 bytes disk space from 4039114752 limit
ARCH: Error 19809 Creating archive log file to 'E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2016_02_04\O1_MF_1_229_%U_.ARC'
Errors in file e:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_5636.trc:
ORA-16038: log 1 sequence# 229 cannot be archived
ORA-19809: limit exceeded for recovery files
ORA-00312: online log 1 thread 1: 'E:\ORACLE\ORADATA\ORCL\REDO01.LOG'
USER (ospid: 5636): terminating the instance due to error 16038
ARC1: Archival started
Errors in file e:\oracle\diag\rdbms\orcl\orcl\trace\orcl_arc0_6168.trc:
ORA-01092: ORACLE instance terminated. Disconnection forced
ARC2: Archival started
Errors in file e:\oracle\diag\rdbms\orcl\orcl\trace\orcl_arc0_6168.trc:
ORA-01092: ORACLE instance terminated. Disconnection forced
Thu Feb 04 10:20:47 2016
ARC3 started with pid=23, OS id=6272 
Instance terminated by USER, pid = 5636
See the highlighted last line which states the process ID responsible for the instance termination. The flash recovery area for my test database is set at 3 GB and now it is full.

Solution:
Clear some of the files by backing up or deletion from the flash recovery area and retry to startup the database.
I now have cleared the space after taking a backup of required archive logs.

Note: Deleting files manually from OS level will not be considered as freeing up space for the recovery area as they are managed by RMAN and Oracle. In this case again the startup will fail. You need to login to RMAN, crosscheck all the recovery area files and delete expired archive log and backup files to let Oracle know that we now have free space in the flash recovery area.
After performing these above, we can start up the database.

SQL> startup mount
ORACLE instance started.

Total System Global Area 1071333376 bytes
Fixed Size                  1375792 bytes
Variable Size             662700496 bytes
Database Buffers          402653184 bytes
Redo Buffers                4603904 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\Users\Priya>rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Feb 4 10:45:20 2016

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

connected to target database: ORCL (DBID=1421168703, not open)

RMAN> crosscheck archivelog all;

using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
validation failed for archived log
archived log file name=E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_12_29\O1_MF_1_133_C855SS6F_.ARC RECID=83 STAMP=899754777
validation failed for archived log
...
...
archived log file name=E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2016_01_31\O1_MF_1_227_CBV8YSFP_.ARC RECID=177 STAMP=902576057
validation failed for archived log
archived log file name=E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2016_01_31\O1_MF_1_228_CBV903F8_.ARC RECID=178 STAMP=902576095
Crosschecked 96 objects


RMAN> delete expired archivelog all;

released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=63 device type=DISK
List of Archived Log Copies for database with db_unique_name ORCL
=====================================================================

Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
83      1    133     X 29-DEC-15
        Name: E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_12_29\O1_MF_1_133_C855SS6F_.ARC

84      1    134     X 29-DEC-15
        Name: E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_12_29\O1_MF_1_134_C855T36B_.ARC
...
...
...

178     1    228     X 31-JAN-16
        Name: E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2016_01_31\O1_MF_1_228_CBV903F8_.ARC


Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_12_29\O1_MF_1_133_C855SS6F_.ARC RECID=83 STAMP=899754777
deleted archived log
archived log file name=E:\ORACLE\FLASH_RECOVERY_AREA\ORCL\ARCHIVELOG\2015_12_29\O1_MF_1_134_C855T36B_.ARC RECID=84 STAMP=899754787
...
...
...

RMAN> exit


Recovery Manager complete.

C:\Users\Priya>sqlplus / as sysdba

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 4 10:46:15 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database open;

Database altered.

SQL>

Now my database is open again.

Happy troubleshooting!