Thursday Nov 06, 2014

Tools For Generating Consistent Loads

It's finally ready. The new database machine you've spent months, planning and architecting. All those shiny new components perfectly aligned for extreme performance. Will it give you the results you expect? How can you baseline and compare your new and existing systems? Pointing your application at your new machine may take some time to setup and depending on the behavior of the application, may not stress test all hardware components as you might like. You need a set of easy to configure scripts for load testing and you need tools and procedures to compare old systems to new. 

This will be the first of three blog posts to help with that effort. In this post, I'll go over some tools you can use to generate various loads. The next two posts in the series I'll talk about using Enterprise Manager to evaluate and baseline loads, and strategies to compare systems using consistent loads.

Test Suite

My current test suite includes two methods to generate loads: one leverages Swingbench, which is a well known and popular load generating tool, and the other is a solution I cooked up myself. Both sets of scripts can be altered to tailor their load characteristics. I've also included a variable load script wrapper for each, which you can use to adjust the load over time. For example: you can have a load test that runs for a total of 5 hours and within that 5 hour window your load could fluctuate every 30 minutes from heavy to light. The custom scripts are also flexible enough to support altering their behavior if you have a specific set of SQL/PLSQL commands you would like to run.

For this article, my database is running on an Exadata X2-2 quarter rack.

Using Swingbench

Swingbench is a great tool for quickly generating loads on an Oracle database. It's easy to setup and has many configurable options. Although swingbench has a nice GUI interface for creating your test schemas and running your load, I really like the command line interface. With the CLI you can create scripts to interact with Swingbench and nohup loads on remote hosts so your load can run hours or days without needing to be logged in.


If you don't have it already, download a copy of Swingbench and unzip the files on your host machine. You can run Swingbench from your database host or a remote client. If you co-locate them on your database host, take this into account during load measurement. 

There are a few different types of schemas you can create with Swingbench, and each type has an associated XML wizard file in the bin directory to help with creating that schema. I tend to use the Order Entry (OE) schema the most as it's behavior is more representative of an OLTP system, so we will be using the oewizard.xml file for this example. Open up the XML file in your favorite editor and update the connection information for the system user that will create the schema, then run oewizard on the file like this...

oewizard -c oewizard.xml -cl -cs //<your_host_or_vip>/<service> -u <test_user_name> -p <test_user_pw> -ts <tablespace_name> -create -df <asm_disk_group> -part -scale 4 -debug

You can use -scale to adjust the size of your schema which will also increase the time it takes to build. A scale of 4 gives me about a 10G schema.  


When your schema is ready, edit the supplied swingconfig.xml file with your connection info and use charbench to verify your schema. 

charbench -c swingconfig.xml

With our schema ready, now we can define our load also using the swingconfig.xml file. There are a number of parameters you can adjust to define your load. Here are the ones I find affective.

  • <NumberOfUsers>40</NumberOfUsers>
  • <MinDelay>100</MinDelay>
  • <MaxDelay>1000</MaxDelay>
  • <LogonDelay>150</LogonDelay>
  • <WaitTillAllLogon>false</WaitTillAllLogon>

MinDelay and MaxDelay specify the wait time between transactions in milliseconds. A LogonDelay helps avoid connection storms (unless that's what you want to test) and I like setting WaitTillAllLogin to false so my load starts right away and there is a nice ramp up over time. If I want to push the system hard I set Min/MaxDelay low and increase the number of users.

Further down the swingconfig.xml file you will find descriptions of the actual transactions that will be executed. Each transaction type can be turned on/off and it's weighted value compared to other transactions can be adjusted. This section is were you will do most of your tweaking to get the load profile you want.


Here's a Top Activity graph in Enterprise Manager showing two consecutive tests. The first test had 300 users with a Min/MaxDelay of 15/20. I decreased the Min/MaxDelay to 10/15 for an increased load which you can see below.  

Here's an example of a heavily overloaded system in which the application doesn't scale. I've setup Swingbench with 800 users connecting every 2 seconds for a slow buildup, Min/MaxDelay of 5/15, and I'm only using the "Order Products" transactions. These transactions perform single row inserts with no batching. Around 11:30am there are ~500 sessions and the system starts to overload. CPU has become saturated and other background processes like the database writers start to slowdown causing excessive Concurrency and Configuration waits in the buffer cache. Our SGA for this test was 10G.

overload example

Variable Loads With Swingbench

In order to generate variable swingbench loads over time, I've created a small wrapper script, written in Perl that can be used to define how long your load should run and also the variation in that load. To adjust the load you define how many users will be connected. Here's a snippet of the script which describes each parameter.

### how long you want the test to run in hourse:minutes
$runtime = "00:30";

### your swingbench config file
$conf_file = 'swingconfig.xml';

### Adjust your vaiable loads here
### RunTime = how ling this load will run in hours:minutes
### UserCount = how many user connections
### SleepTime = how many seconds to sleep before running the load, if needed
###              RunTime  UserCount  SleepTime
@swing_list = ( ["00:02", 400,       120],
                ["00:05", 200,         0],
                ["00:05", 100,         0] ); 

With these settings here's what our load profile looks like.

variable load swingbench

Custom Load Generation

There have been times during my own performance testing in which I needed to generate a very specific type of load. Most recently, I needed to generate a heavy large block IO load, so I put together these scripts in response to that need. I tried to keep them easy to setup, run and alter if necessary. The load uses a single schema and creates a test table for each session that will be connected, so the load needs to be initialized based on the maximum number of sessions expected for testing.

Setup and Execution

  1. Download the package to your host and unzip/tar in an empty directory.
  2. Edit the file to setup variables for your test. This is where you will define the maximum number of test tables you will need.
  3. Run the script to setup your user test schema and test tables. You will be prompted for the SYSTEM user password.
  4. Run the script to begin your test. This script requires two parameters, the low and high values for the test tables to use and thus the number of sessions. This was done to allow running a small load and then ramping up and running additional loads as needed. Examples...
    • 1 10 : Will run ten sessions, using test tables 1 through 10.
    • 16 20 : Will run 5 sessions, using test tables 16 through 20.
    • 1 1 : Will run 1 session.
  5. Running will kill all sessions and thus stop the load. 

Here's what a load of 20 users looks like. Lots of large block IO!

io load example

Custom variable loads can also be run using the script found in the package. It has the same parameters to adjust as in the Swingbench script. Here's an example of a variable load that ramps up, overloads the system, then drops back down again.

variable load io

As the IO gets heavy we start seeing more contention in the log buffer. 

variable io waits


It's possible to design your own custom loads with these scripts, as you may need to execute a particular PL/SQL package or perhaps test how well SQL will scale against a large partitioned table. This can be achieved by editing the and files. : Edit this script to create or initialize any objects needed for your test. This script gets executed multiple times depending on how many sessions you plan to run concurrently. If you don't have a session specific setup, you can leave an empty code block. : This is the code that gets executed for each session you define. If you had PL/SQL you wanted to test, this is where you would put it.

As an example, for this test I created some database links for each instance and altered the script to select from our test table using the database links, thus creating a heavy network load. I've included this example script in the package zip file as well.

network load


With a set of tools to define consistent, predictable loads we are now ready to baseline our systems. Next in the series I will go over the tools available in Enterprise Manager which will help in that effort. 

Wednesday Apr 03, 2013

Using Advanced Notifications in Oracle Enterprise Manager 12c

When using an enterprise monitoring tool such as Oracle Enterprise Manager 12c, one of the most critical components is notification. Once an alert or issue has been identified, how do you tell the right people at the right time? Most enterprises use e-mail or open a trouble ticket. As you can imagine, no two enterprises are the same when it comes to their tools and processes. Many customers use one of the more common and well known trouble ticketing systems but quite a few use non-standard or custom (homegrown) trouble ticketing systems. Some customers have special routing requirements or corporate standards and have custom applications which handle all emailing functions instead of directly emailing using an SMTP server.

Oracle Enterprise Manager 12c can handle all of these situations by utilizing one of the various notification methods provided: E-mail, 3rd party connectors and advanced notification methods. There are three types of advanced notifications: SNMP, OS Command or PL/SQL. This blog will introduce you to the OS Command and PL/SQL notification methods available in EM 12c and provide an example of using a custom OS script for notifications.

Advanced Notification Methods: OS Command and PL/SQL

With the advanced notification methods, you can write a notification directly to a table or an OS log for further processing or push a notification to any trouble ticketing system using their command line tools providing the data and variables from EM 12c as input.  This method is used by some customers whose corporate standard requires that all alerts be written to a log file, which the ticketing system can poll on a regular basis for alerts. Additionally, advanced notifications allow you to call a procedure whose interface is PL/SQL or access additional data in the Enterprise Manager repository.  For example, for a database alert on % of Processes or Sessions Used, you could use PL/SQL to notify the proper application team (stored in a custom target property) that they have too many sessions.   To create and configure advanced notifications, the user must have Super Admin privileges.

Creating OS or PL/SQL Scripts

When creating an advanced notification the first step is to create the OS or PL/SQL script. It’s highly recommended to include debugging and logging information so you can fully understand what information EM 12c is passing and assist in troubleshooting.  When using an OS Command in a multi-OMS environment, the script needs to reside on all OMS servers or preferably in a shared location. If you plan to use PL/SQL, the procedure must be created in the repository database before configuring it as a notification method.   Of course, any custom objects created in the repository should be created under a separate schema and privileges granted to the SYSMAN user. 

The Oracle Enterprise Manager Cloud Administrator’s Guide chapter on Notifications has detailed examples of OS Scripts and PL/SQL that can be utilized in different situations.  This chapter also provides detailed information on using passing information to the OS or PL/SQL script and troubleshooting.

Creating Custom Notification Methods

After defining the OS Command or PL/SQL Script, you need to add it to EM 12c as a notification method.  In this example, we will create a simple OS script that logs events to a log file, which can be further processed by a custom ticketing system.

if test -f $LOG_FILE
   exit 100

To create a notification method login as a user with Super Admin privileges and select Setup / Notifications / Notification Methods.  Under the Scripts and SNMP Traps section, click the Add drop down box and select OS Command and click Go.

Enter a name and provide the fully qualified script location.  Use the Test button to validate and click Save.

Adding Notification Methods to Incident Rules

To receive notifications, you will need to create an Incident Rule set and select relevant targets and events to notify and then select the OS Command advanced notification method we created earlier. In this section we will go through the steps to create a simple incident rule.  For full details on how to configure your Incident Rules, see the Oracle Enterprise Manager Cloud Control Administrator’s Guide.

Go to Setup / Incidents / Incident Rules.   At this point you can either select a rule set to edit, or create a new rule set.  In this example we are going to create a new rule set by clicking Create

On the first screen, enter a user friendly name and description. Since this example is planning to use an advanced notification choose Enterprise

Since we only want notifications on our Production targets, we’re going to narrow down the list of targets. On Targets tab select Specific targets, select Groups in the drop down box and click Add. Search for the desired group and select.

Click on the Rules tab, and select Create.

Select the type of rule to create, in this example we are using Incoming events and updates to events.

Enter filter criteria, in this example we are filtering on all Metric Alerts in Severity Warning and Critical, click Next.

On the Add Actions screen you will define the action that you wish the notification to perform.  Click Add.

In the Create Incident or Update Incident section you can choose to create an Incident, assign Incidents to an administrator, set priority/status or escalate (if update is selected).  Under the Notifications section, you can select Basic Notifications to send an e-mail or page to a particular user or users, on top of any methods you might select in the Advanced Notifications section.  In Advanced Notifications you will see the method we previously created, select that method and click Continue.

Click Next.

Provide a user friendly name for your rule and a description, click Next

Review the details of your rule and click Continue

Note the rules are not saved until the Save button is clicked. Click OK.

Review your rule set and click Save.

Click OK.

Validating Incident Rules

Once you have configured your rule set, you should trigger a critical alert on one of the targets included in the rule set. Verify that the rule set triggers the notification method you created and logs the event in the log file.  It's helpful to keep the e-mail option turned on during testing so you can validate when the rule was triggered as well. 

Identify a target in the group you configured notifications for in the previous section. For this example, we will use a database instance and trigger a Process Limit Usage (%) event. From Oracle Database / MonitoringAll Metrics, I’ve identified a metric that I can lower thresholds on to trigger an event. Click on Process Limit Usage (%) to drill down to this metric. Notice our Real Time Value of Processes is 16.4.

Click on Modify Thresholds to set thresholds.

Set the Warning and Critical thresholds lower than the Real Time Value we made note of earlier. Ours was 16.4, so setting the Warning to 5, and Critical to 10. In the Occurrences before Alert field, you may wish to change the value to 1. Since the collection frequency is 10 minutes, if occurrences are set to 3, you will have to wait 30 minutes for it to trigger and again to clear. Click Save Thresholds.

Close the confirmation window.

Navigate to Oracle Database / Monitoring / Incident Manager. Since we have the out-of-box rule set enabled we get an Incident for every critical metric alert and we can see the incident for Process Limit in the Unacknowledged incidents view. If you have disabled the out-of-box rule set or don’t see the incident, check the Events without incidents view. Select the appropriate line and in the lower pane click on the Events tab.

On the Events tab click on the Message link to drill into the event details.

Here you’ll see the notification method was called under the Last Comment field. From here, click on Updates tab to see more details.

In the Updates tab, you will see the details of the alert and the notification method that was called to run our /tmp/ script.

Finally, we can check the /tmp/event.log file and see the information that was reported.   Be sure to go back and set your threshold to its regular value to clear the false alert you triggered!


The options for notifications in Oracle Enterprise Manager 12c are very flexible. You can choose e-mail, one of the available connectors to integrate with a 3rd party ticketing system, or use one of the advanced notification methods (SNMP, OS Commands or PL/SQL). In this blog, we’ve shown you how to create an advanced OS Command notification method, how to configure an incident rule set to call that method and how to validate by triggering an alert. Once you are familiar with how to create advanced notification methods and rule sets, you can customize your notifications to suit your needs. 

For additional information on configuring your environment for enterprise monitoring, see the whitepaper Strategies for Scalable, Smarter Monitoring using Oracle Enterprise Manager Cloud Control 12c.  

Stay Connected with Oracle Enterprise Manager:

Twitter | Facebook | YouTube | Linkedin | Newsletter

Network Ports Used in Oracle Enterprise Manager 12c

When planning and configuring your Oracle Enterprise Manager 12c implementation, you will have many infrastructure considerations. One of the most often discussed pieces is the network ports that are used and how to configure load balancers, firewalls and ACLs for communication.

This blog post will help identify the typical default port and range for each component, how to identify it and how to modify the port usage.

To modify most ports during installation, select the Advanced Installation and set the appropriate ports on the Port Configuration Details screen.

Once the system is installed, you can use the following EMCTL or OMSVFY commands to validate components and port assignment:

$emctl status oms -details
$omsvfy show opmn
$omsvfy show ports

To verify if a port is free, run the following command:

On Unix:
$netstat -an | grep <port no>

On Microsoft Windows:
>netstat -an|findstr <port_no>

For more information on OMSVFY (part of the EMDIAG toolkit) see MOS Note 421053.1: EMDIAG Troubleshooting Kits Master Index

External Ports

These ports will be used in every Enterprise Manager 12c installation and will require firewall and/or ACL modifications if your network is restricted.  These are also the components that will be added to your load balancer configuration.

Default Port






4889 – 4898

Enterprise Manager OHS Upload HTTP

Agent Communication to OMS (unsecure). Used in load balancer.

To modify after install follow notes 1381030.1 and 1385776.1. Requires changes on all Agents.


1159, 4899 – 4908

Enterprise Manager OHS Upload HTTP SSL

Agent Communication to OMS (secure). Used in load balancer.

To modify after install follow notes 1381030.1 and 1385776.1. Requires changes on all Agents.


7788 – 7798

Enterprise Manager OHS Central Console HTTP (Apache/UI)

Web browser connecting to Cloud Control Console (unsecure). Used in load balancer and for EM CLI.

To modify after install follow notes 1381030.1.


7799 - 7809

Enterprise Manager OHS Central Console HTTP SSL (Apache/UI)

Web browser connecting to Cloud Control Console (secure). Used in load balancer and for EM CLI.

To modify after install follow note 1381030.1.


7101 - 7200

EM Domain WebLogic Admin Server HTTP SSL Port

Cloud Control Admin Server.

To modify after install follow note 1109638.1.


3872, 1830 – 1849

Cloud Control Agent

Only the OMS will connect to this port, to either report changes in the monitoring, submit jobs, or to request real-time statistics.

Port can be provided during Agent install.

If the agent port needs to be changed at a later date this can be done with the following command on the agent:
emctl setproperty agent -name EMD_URL -value https://hostname.domain:port/emd/main/

This will allow the agent to run on the new port, however the target does not get renamed so continues to show the original port.


Depends on Listener Configuration

Database Targets -  SQL*Net Listener

For Repository database, only the OMS will connect to store management data from

the agents. For all monitored target databases OMS will retrieve information requested by browser clients.

To modify this port for the repository database:

Change the listener.ora file for the EM repository. Restart the listener. Then for every OMS machine using that repository run the following:

emctl stop oms
emctl config oms -store_repos_details -repos_conndesc <connect descriptor of database> -repos_user sysman
emctl start oms
emctl config emrep -agent <agent name> -conn_desc <connect descriptor of database> 

To modify this port for monitored targets, change the listener configuration on the target, then update Monitoring Configuration in EM.


7101 - 7200

FMW Targets – Admin Console

Outgoing from OMS, used for managing FMW targets.

To modify after install follow note 1109638.1.




Outgoing from OMS to host servers if the Agent is unreachable. Validates if server is up or down.


Internal Ports

These ports are required for internal Enterprise Manager communication and typically do not require additional firewall/ACL configuration.

Default Port






7201 – 7300

EM Domain WebLogic Managed Server HTTP Port

Used for Fusion Middleware communication.

Configured during installation


7301 – 7400

EM Domain WebLogic Managed Server HTTP SSL Port

Used for Fusion Middleware communication.

Configured during installation


7401 – 7500

Node Manager HTTP SSL Port

Used for Fusion Middleware communication.

Configured during installation


6100 - 6199

Oracle Notification Server (OPMN) Local

Ports used by OPMN  can be verified from <MW_HOME> /gc_inst/WebTierIH1

/config /OPMN/opmn/opmn.xml:

<debug comp="" rotation-size="1500000"/>
<notification-server interface="any">
<port local="6700" remote="6701"/>

Modify the opmn.xml to use free ports as below:

1. Stop OMS

2. Take a backup of the existing opmn.xml and ports.prop in the <MW_HOME>/ gc_inst/WebTierIH1/ config /OPMN/opmn directory.

3. Edit the opmn.xml file, under the <notification-server> element, modify the local / remote port, as necessary to the free port available and save the file.

4. Edit the ports.prop file and modify the remote / local port parameters as necessary and save the file.

5. Start the OMS


6200 - 6201

Oracle Notification Server (OPMN) Remote

Ports used by OPMN  can be verified from <MW_HOME> /gc_inst/WebTierIH1


<debug comp="" rotation-size="1500000"/>
<notification-server interface="any">
<port local="6700" remote="6701"/>

Modify the opmn.xml to use free ports as below:

1. Stop OMS

2. Take a backup of the existing opmn.xml and ports.prop in the <MW_HOME> /gc_inst/WebTierIH1/ config/OPMN/opmn directory.

3. Edit the opmn.xml file, under the <notification-server> element, modify the local / remote port, as necessary to the free port available and save the file.

4. Edit the ports.prop file and modify the remote / local port parameters as necessary and save the file.

5. Start the OMS


These ports required only if certain components are to be used and firewall/ACL changes may be needed.

Default Port






Secure web connection (https - 443) to

Outgoing from OMS used for online communication with Oracle for OCM, MOS, Patching, Self-Updates, ASR

Proxy settings defined via the UI (Setup -> Proxy Settings)
Do not use the OMS parameters!  


Application Dependency and Performance RMI Registry Port


Configured during installation


Application Dependency and Performance Java Provider Port


Configured during installation


Application Dependency and Performance Remote Service Controller Port


Configured during installation




Configured during installation


SSL Listen Port


Configured during installation


JVM Managed Server Listen


Configured during installation


JVM Managed Server SSL Listen


Configured during installation



BI Publisher HTTP

BI Publisher

During install can modify with configureBIP script.  Post-install can be modified per Note 1524248.1



BI Publisher HTTP SSL Port

BI Publisher

During install can modify with configureBIP script.  Post-install can be modified per Note 1524248.1

Stay Connected with Oracle Enterprise Manager:

Twitter | Facebook | YouTube | Linkedin | Newsletter




« August 2016