oltp − The RWP*Load Simulator oltp Workload
The oltp workload is a general purpose workload that executes a mix of different "business transactions" that emulate an oltp workload. Some transactions have resemblance to transactions from actual applications while others are complete artificial.
To use in your own project, copy the two files named oltp.env and oltp.rwl to your working directory giving them names after your project. Modify the files according to the comments in them. The second file (copy of oltp.rwl) is frequently referred to as the parameter file as it contains all parameters specifying runs.
It is recommended that you set RWLOADSIM_PATH to (include) the directory where you have your copy of oltp.rwl. Otherwise, to execute any of the oltp commands, your current directory must be the directory where you have your project specific copy of oltp.rwl. You should normally set all needed environment variables such as RWLOLTP_NAME and RWLOADSIM_PATH in your project specific copy of oltp.env. If you have several related projects, it is suggested that you have each projects .env and .rwl file in the same directory as that allows you to easily switch simply by sourcing the appropriate .env file.
The only environment variable that must be set in your copy of oltp.env is RWLOLTP_NAME which is your project name.
Typing oltpverify without any options is a fast way to verify settings are fine.
Note that the project specific .rwl file often contains passwords and that you therefore should limit permissions to directories and files appropriately.
Further details about setup is available in oltpsetup(2rwl).
The oltp workload contains the following simulated business transactions that are fast and of true oltp nature:
makeorder
Simulates creating an order by choosing a random customer, inserting one line to the order table, and a random number of lines into the order_items table.
queryorder
Simulates querying an order by choosing a random order, querying the order and order_items tables, the latter with a join to the products table.
searchproducts
Simulates a product search by doing a full table scan with a like condition on the product table.
aw_transactions
A completely artificial transaction that does a select for update, and update and an insert, and in 20% of the cases does a few more DML statements. It is designed to potentially cause scalability issues on RAC.
awindex_query
Does an index-based query against a table using an index with an extremely poor clustering factor. During initial setup, the table size is defined; it if is much less than the available buffer cache, the whole table is likely to be cached, otherwise the query is likely to always case physical reads.
The oltp workload additionally contains the following simulated business transactions that have heavy resource consumption (compared to those above) and that therefore a more of a batch nature:
makeinvoices
Picks a random customer and emulates printing/mailing of invoices for all orders that have been shipped.
complex_query
A completely artificial query with high CPU usage compared to the above. It also has potential for changes in execution plans.
shiporder
Attempts fulfilling one order from a single warehouse, and if not possible, uses multiple warehouses. While doing so, inventories are locked potentially causing waits; on purpose, this part may occasionally cause deadlocks producing ORA-00060. It will also update the status in the orders table to "shipped" It is always executed in a loop that attempts shipping all orders with status "ordered".
stockup
Simulates adding inventory to many products in a random warehouse.
The oltp directory contains an experimental shell script, maketags.sh, that will create a tags file for use by vi. The tags file itself is also included. If you change current directory to the oltp directory, you can e.g. do
vi -t runrun
to start vi at the main run command for the workload found in the run.rwl file. Note that the first "run" above is generated by the rwloadsim tags generation and the second "run" is the name of the file without the .rwl suffix. You can similarly do
vi -t runrunsys
to start vi at the run command from the runsys.rwl file, which is everything related to system such as gathering of awr snapshots.
The following environment variables are used by the oltp workload:
RWLOLTP_NAME
Sets your project name and will also be the default key used when the various oltp commands save data in your repository. This variable must exist.
RWLOLTP_GNUPLOT1 RWLOLTP_GNUPLOT2
When using the -g option to display running graphs using X-Windows, these environment variables will be given to the gnuplot command line for respectively the plot of resource usage such as database CPU and throughput.
The oltp workload is created with scripting and post-analysis of results in mind, and except for the -g option to oltprun and oltpcore, not much output is generated. To do live demonstrations, typically showing much more live graphs, the oltp workload can be running using "External Control". When enabled, several parameters that normally require changes to your {key}.rwl file can be changed while a run is in progress, using either the oltpxcset(2rwl) command or direct updates to the rwlrun_xc table in the repository.
Copyright
© 2023 Oracle Corporation
Licensed under the Universal Permissive License v 1.0 as
shown at https://oss.oracle.com/licenses/upl
oltpcheckkey(2rwl), oltpcore(2rwl), oltpcreate(2rwl), oltpday(2rwl), oltpdrop(2rwl), oltpfilloe(2rwl), oltpforever(2rwl), oltpforever2(2rwl), oltpparams(2rwl), oltpplot(2rwl), oltprun(2rwl), oltpscale(2rwl), oltpscalereport(2rwl), oltpsetup(2rwl), oltpverify(2rwl), oltpplus(2rwl), rwloadsim(1rwl), oltpdirectory(2rwl), oltprunnumber(2rwl)