Skip to content

Using rwloadsim for scripting

Bjørn Engsig edited this page Dec 4, 2024 · 14 revisions

Scripting using RWP*Load Simulator

Whether your are a developer, a database administrator, performance engineer, or do some other work where you directly interact with the Oracle Database, you are likely to use standard tools like sqlplus or sqldeveloper as your daily routine. You may use such tools to execute SQL and/or PL/SQL, and you may frequently use the command like with a shell such as /bin/bash. Typing commands, typing SQL, and writing code or scripts are likely to be part of your routine.

This is exactly where the RWP*Load Simulator fits well into your existing habits. In addition to its original design purpose of simulating load on an Oracle Database, it has also evolved to be a very useful scripting tool. You can think of it a bit as if you could write shell scripts that directly can execute SQL or PL/SQL, or as if PL/SQL could run on the client side. It effectively is a programming language that effectively combine typical scripting features of e.g. the shell with an ability to execute SQL.

This page take you through a set of cases from the simple to the complex.

Install rwloadsim for scripting use

When the RWP*Load Simulator is being used purely for scripting, the installation is straight forward as described at the installation wiki. A repository database is not required.

General comments about rwl scripts

An rwl script will typically need to deal connections to a single database schema at a time, and the actual username, password and connect string should be provided when the script is executed. The -l (--default-database) is used for exactly that purpose and if you have an rwl script in a file called myscript.rwl, you can therefore execute

rwloadsim -l username/{password}@connect myscript.rwl

which causes your script to be executed with the default database provided on the command line. As a result, any SQL executed in your script will be executed against this database. Note that when you execute rwloadsim in this way, the memory where the actual password is stored will be erased immediately such that is is not visible to others on the system. You can alternatively omit the password in which case rwloadsim will prompt for it. The connect string can be URL style such as //hostname/service or it can be the name of an entry in tnsnames.ora.

Often, your script will need to take arguments, which can be positional or named as options, i.e. using one of these two models, or a combination of them:

rwloadsim -l username/{password}@connect myscript.rwl arg1 arg2 arg3 ...
rwloadsim -l username/{password}@connect myscript.rwl --argument1=value1 --argument2=value2

As rwloadsim in the former case by default takes all arguments provided on the command line to be names of rwl script files, you need to explicitly tell rwloadsim that only the first argument is an rwl file; the rest will then be positional. This is done by putting this directive somewhere in your script file:

$longoption:file-count=1

It is recommended that you routinely do this in all rwl scripts and as a result, positional arguments will be available as $1, $2, $3 etc with the count available as $#. There is also a shift statement that shifts these to the left and reduces $#. See ARGUMENTS.html for details.

An alternative way to provide arguments to your rwl script is by adding long options that match one of your declared variables. If you e.g. add this code:

integer xyz; $useroption:xyz:"Set the value of xyz"

to your script, you declare an integer variable called xyz, that can be set on the command line using the option --xyz. You could therefore call

rwloadsim -l username/{password}@connect myscript.rwl --xyz=42

See the useroption rwlman page for details.

An EMP and DEPT example

The EMP and DEPT tables have been part of the Oracle distribution since the very early times. Although they have been replaced with far more appropriate test schemas, they are still extremely well known in the Oracle community. If you do not have these two tables in an existing schema, they can be found in your rwloadsim installation in the file test/testschema.sql.

Let us initially show a script, that takes a --deptno option on the command line and displays all employees in that department. Some important comments about this code is:

  • You declare variables like you would in many other programming languages. Details are at the simple declaration rwlman page.
  • In addition to a variable declaration, you can add a $useroption directive which makes the script take that variable name as a long option providing a value. See the user option rwlman page for details.
  • SQL statements are simply embedded between other statements in your rwl script and are terminated similar to how they are in sqlplus.
  • Since the SQL statement is a query, it is wrapped in a for loop which effectively becomes a cursor loop. The sql execution rwlman page explains this in details.
  • Rwloadsim will implicitly match declared variables to select list elements and to place holders (bind variables). In the case when a select list element does not have a simple name (e.sal/12 in this case), you must provide an alias that can be matched to a variable.
  • In the printf statement that is inside the loop, most ordinary formatting characters etc are known. Additionally, since rwloadsim handles NULL like Oracle does it, a received variable from the query (comm) can be NULL. The "t" modifier used here means a text string will be output when the variable is NULL. See the printf statement rwlman page for further information.

The actual code is shown here:

# Show all employees in some given department.
#
# Declare some variables to receive output
string ename, dname;
integer empno;
double monthsal;
double comm;
# Declare a variable to provide input
integer deptno; $useroption:deptno
# Declare a variable that will count the rows in initialize it
integer ecount := 0;

# Execute a sql cursor loop
# Note that the names of select list elements and of placeholders
# must match declared variables
for
  select e.empno, e.ename, d.dname
  , e.sal/12 monthsal
  , e.comm 
  from emp e join dept d
  on e.deptno = d.deptno
  where e.deptno = :deptno
  /
loop
  printf "%5d %10s %6.2f %10s %t6.0f\n", empno, ename, monthsal, dname, "No Com", comm ;
  ecount += 1;
end loop;

if !ecount then
  printline "no employees in department " deptno;
end if;

If you save the above in a file called empsindept.rwl, you can execute:

rwloadsim --deptno=30 -l username/{password}@connect empsindept.rwl

RWP*Load Simulator Release 3.1.0.6 Development on Wed, 11 Oct 2023 13:14:18 UTC
RWL-206: warning: OCI compile environment (21.11) is different from runtime (21.9)
Connected default database to:
Oracle Database 23c Enterprise Edition Release 23.0.0.0.0 - Production
 7499      ALLEN 133.33      SALES    300
 7521       WARD 104.17      SALES    500
 7654     MARTIN 104.17      SALES   1400
 7698      BLAKE 237.50      SALES No Com
 7844     TURNER 125.00      SALES      0
 7900      JAMES  79.17      SALES No Com

The next example is very similar and is included to show a few other important features of rwloadsim:

  • You can declare procedures that take arguments and possibly has local variables. See the procedure declaration rwlman page for details.
  • There is a $longoption:file-count=1 directive to cause all but the first argument to be taken as positional arguments.

The code of this is:

# Show all employees in departments given on command line
#
# All options are positional except this file itself
$longoption:file-count=1

# Declare a procedure that take a deptartment number as argument
procedure showemps(integer deptno)
  # Declare some variables to receive output
  string ename, dname;
  integer empno;
  double monthsal;
  double comm;
  # Declare a variable that will count the rows in initialize it
  integer ecount := 0;
  # Execute a sql cursor loop
  # Note that the names of select list elements and of placeholders
  # must match declared variables
  for
    select e.empno, e.ename, d.dname
    , e.sal/12 monthsal
    , e.comm 
    from emp e join dept d
    on e.deptno = d.deptno
    where e.deptno = :deptno
    /
  loop
    printf "%5d %10s %6.2f %10s %t6.0f\n", empno, ename, monthsal, dname, "No Com", comm ;
    ecount += 1;
  end loop;

  if !ecount then
    printline "no employees in department " deptno;
  end if;
end showemps;

# Did the user provide department numbers?
if not $# then
  writeline stderr, "Please provide department numbers on command line";
  exit 1;
end if;

# loop through all arguments
while $#
loop
  showemps $1;
  shift;
end loop;

Again, if this is saved in a file called empsindept2.rwl, you can execute:

rwloadsim -l username/{password}@connect empsindept2.rwl 10 20 42

RWP*Load Simulator Release 3.1.0.6 Development on Wed, 11 Oct 2023 13:29:27 UTC
RWL-206: warning: OCI compile environment (21.11) is different from runtime (21.9)
Connected default database to:
Oracle Database 23c Enterprise Edition Release 23.0.0.0.0 - Production
 7782      CLARK 204.17 ACCOUNTING No Com
 7839       KING 416.67 ACCOUNTING No Com
 7934     MILLER 108.33 ACCOUNTING No Com
 7369      SMITH  66.67   RESEARCH No Com
 7566      JONES 247.92   RESEARCH No Com
 7788      SCOTT 250.00   RESEARCH No Com
 7876      ADAMS  91.67   RESEARCH No Com
 7902       FORD 250.00   RESEARCH No Com
no employees in department 42

A few DDL examples

The next examples shows a few other important features of rwloadsim:

  • For loops can loop over a list of values or over a sequence of values
  • SQL statement can be made dynamic by the use of ampersand replacement similar to how sqlplus does it. Note that this needs to be explicitly enabled via a directive.
  • All SQL being executed can be logged to a file

The first example shows how you can create a simple script that truncates a set of named tables:

$ampersand:on      # enable & similar to sqlplus
string tablename;  # declare a variable of type string
for
  # loop through a set of values
  tablename := "table1", "tableabc", "tablethree", "lasttable"
loop
  # execute a ddl statement 
  truncate table &tablename.
  /
end loop;

The next example shows how you can create 10 identically shaped tables and log all SQL to a file named /tmp/logfile.txt

# Turn on ampersand replacement
$ampersand:on
# Log all sql being executed to a named file
$sqllogging:file:"/tmp/logfile.txt"

# Declare a few variables
string tablename;
integer t;
# drop and create 10 tables
for t := 1 .. 10 loop
  # generate the tablename
  sprintf tablename, "tab%02d", t;
  # execute the ddl statements using ampersand replacement for the name of the table
  drop table &tablename. purge
  /
  create table &tablename. 
  ( a number primary key
  , b varchar2(10))
  /
end loop;

Generating an awr report

The next example shows a few more important features that are relevant for scripting:

  • Verify that the user actually provided the -l option, this also shows a simple example of conditional code. For details see the conditional compilation rwlman page.
  • Write output to a file, which is explained further in FILE.md.

Note that this example is a highly simplified version of the complete awrreport.rwl script that is shipped as part of rwloadsim.

# Did the user give us a database via -l?
$if not defined(default database) $then
  writeline stderr, "Missing or incorrect -l option";
  exit 1;
$endif

# declare some variables
integer dbid, inst;
integer bsnap, esnap;
# make them settable on command line
$useroption:dbid
$useroption:inst
$useroption:bsnap
$useroption:esnap

# and some more
string(1000) ofile, output;
file yt;
$useroption:ofile

# check values are provided
if dbid is null or inst is null
   or bsnap is null or esnap is null
then
  writeline stderr, "All of --dbid, --inst, --bsnap, --esnap must be provided";
  exit 2;
end if;

# If the user didn't give an output file name, set one
if ofile = "" then
  sprintf ofile, "%d_%d_%d.txt", dbid, bsnap, esnap;
end if;
yt >= ofile; # open the file for writing

# Execute the query
# Note that the name of the select list element (output)
# and of the placeholders are matched to variable names
for 
  select output 
  from table(dbms_workload_repository.awr_report_text
  (:dbid,:inst,:bsnap,:esnap))
  /
loop
  writeline yt, output;
end loop;

yt := null; # close the file

More examples

The public directory of your rwloadsim installation has several scripts that are available for immediate use as documented on the utilities rwlman page. A few examples of features used in these are:

  • ashplot.rwl shows several examples of how an rwl script can call and use operating system commands; shows how SQL can be dynamically generated in a more complex way than ampersand replacement.
  • ociping.rwl has an advanced example of conditional compilation; shows how multiple threads can be used.
  • awrdump.rwl shows how PL/SQL can be called as an interface to the API of Oracle Cloud Infrastructure