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

?

Terminal-1

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118

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)

?

Terminal-2

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

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

?

Terminal-1

1 2 3 4 5 6 7 8 9 10 11 12 13

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.

?

Terminal-2

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

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>

?

Terminal-1

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329

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.

?

1 2 3 4 5 6 7

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.

?

1 2 3 4 5 6 7 8 9 10 11

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.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330

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

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.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

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.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

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

Flash recovery area limit exceeded

Here is a simple basic troubleshooting that happened today.
I was starting the database to encounter the error below.

?

1 2 3 4 5 6 7 8 9 10 11 12

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.

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34

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

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90

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!