wlmoradc(1M)wlmoradc(1M)
ms.acc 1.5 88/02/08 SMI; from UCB 4.2).
NAMEwlmoradc - HP-UX WLM data collector for Oracle database instances
SYNOPSISwlmoradc
[-c <configfile> ⎪ --configfile <configfile>]
[-T <interval> ⎪ --interval <interval>]
[-v ⎪ --sqlvalue ⎪ -t ⎪ --walltime]
[-I <database instancename> ⎪ --instance <database instancename>]
[-h <oracle_home> ⎪ --home <oracle_home>]
[-u <database username> ⎪ --username <database username>]
[-p <database password> ⎪ --password <database password>]
[-f <filename> ⎪ --sqlfile <filename> ⎪
-q <string> ⎪ --sqlstring <string>]
[-e <filename> ⎪ --stderrfile <filename>]
[-D ⎪ --debug ⎪ -D <debuglevel> ⎪ --debug <debuglevel>]
[-s ⎪ --single ⎪ -i <iterations> ⎪ --iterations <iterations>]
Required settings
Although all the arguments above are optional, several settings are
needed for wlmoradc to function. The arguments above are optional
because the required information can be provided through the wlmoradc
configuration file. This required information is listed below,
with the various methods for specifying the information.
* Oracle home (for example: /u01/app/oracle/product/8.1.5)
$ORACLE_HOME environment variable
-h <home>
--home <home>
$opt_home="<home>"; in .oradc file
* Instance name (for example: instance1)
$ORACLE_SID environment variable
-I <instancename>
--instance <instancename>
$opt_instance="<instancename>"; in .oradc file
* username/password (for example: scott/tiger )
$ORACLE_USER environment variable
-u <username> and -p <passwd>
--username <username> and --password <passwd>
-u <username/passwd>
--username <username/passwd>
$opt_username="<username>"; in .oradc file
$opt_password="<passwd>"; in .oradc file
$opt_username="<username/passwd>"; in .oradc file
* SQL code
--q <cmds>
--f <filename>
--sqlstring <cmds>
--sqlfile <filename>
$opt_sqlstring="<cmds>";
$opt_sqlfile="<filename>";
DESCRIPTION
NOTE: As of WLM A.02.02, it is recommended that you use the cntl_smooth
WLM keyword instead of the smooth tool. For information on cntl_smooth,
see the wlmconf(4) man page.
wlmoradc is a data collector for the HP-UX Workload Manager and is
designed to provide an easy building block for Oracle instance manage‐
ment with wlmd(1M). It is part of the WLM Oracle Database Toolkit, or
ODBTK.
It takes one or more SQL statements and uses the Oracle tool sqlplus to
connect to an Oracle instance and execute the statements, returning
either the raw value returned from the SQL or the elapsed execution
time. The results are sent to stdout for human viewing, logging, or
most often, for use by wlmd(1M) by means of the wlmrcvdc(1M) or wlm‐
send(1M) utilities.
You can set the wlmoradc options on the command line or in a wlmoradc
configuration file using perl statements. Specify this file using the
-c (--configfile) command-line option. The perl versions of the
options, for example "$opt_configfile="<configfile>";, are included in
the option descriptions below. Follow perl's quoting rules in the con‐
figuration file. Be sure to place a backslash (\) before dollar signs
($) when the dollar sign actually means a dollar sign, and there is a
possibility of variable interpolation.
OPTIONS
HP-UX WLM data collector options
-c <configfile>
--configfile <configfile>
$opt_configfile="<configfile>";
Specifies a wlmoradc configuration file. This file contains option
settings, SQL statements, etc. If specified, it must be the first
argument. For information on precedence of arguments, see the
"Argument ordering" section below.
-T <interval>
--interval <interval>
$opt_interval="<interval>";
Indicates in seconds how often to report metrics. The default
<interval> is determined by the environment variable WLM_INTERVAL
if set, or 30 seconds.
-v
--sqlvalue
$opt_sqlvalue=1;
Reports SQL values. The wlmoradc utility reports either SQL values
or elapsed walltime (-t ⎪ --walltime). The default is to report
SQL values. For more information, see the section "Principle
measurement types" below.
-t
--walltime
$opt_walltime=1;
Reports elapsed walltime. The wlmoradc utility reports either SQL
values (the default) or elapsed walltime. For more information,
see the section "Principle measurement types" below.
database arguments
-I <database instancename>
--instance <database instancename>
$opt_instance="<database instancename>";
Specifies <database instancename> as the database instance to
monitor. The default instance is named by the $ORACLE_INSTANCE or
$ORACLE_SID environment variables.
-h <oracle_home>
--home <oracle_home>
$opt_home="<oracle_home>";
Specifies <oracle_home> as the home directory of the Oracle
installation to use.
-u <database username>
--username <database username>
$opt_username="<database username>";
Specifies that wlmoradc should connect to the database under the
name <database username>. As with normal sqlplus use, if
<database username> contains a '/' character, the component
following the '/' is used as the password. The default
<database username> is hp_wlmuser/hp_wlmuser.
-p <database password>
--password <database password>
$opt_password="<database password>";
Specifies that wlmoradc should connect to the database using the
password <database password>. This option has no default.
Note that it is an error to specify a password with both the -p
(--password) and -u (--username) options.
SQL arguments
-f <filename>
--sqlfile <filename>
$opt_sqlfile="<filename>";
Instructs wlmoradc to use the SQL statements in <filename>.
--q <string>
--sqlstring <string>
$opt_sqlstring="<string>";
Instructs wlmoradc to use the SQL statements in <string>.
The default <string> is the empty string "".
NOTE: Specify either -f (--sqlfile) or -q (--sqlstring), but
not both.
NOTE: Always use quotes to form a single argument out of the SQL
statements in <string>. Use the quoting rules appropriate for the
situation:
* In a WLM configuration file, see wlmconf(4) for quoting
information
* In a wlmoradc configuration file, follow perl's quoting rules,
which are described at www.cpan.org
* On the command line, follow the quoting rules for the UNIX shell
being used, as described in the shell's man page
debugging options
-e <filename>
--stderrfile <filename>
$opt_stderrfile="<filename>";
Redirects stderr to <filename>. The default <filename> is "".
-D
--debug
Sets debugging to level 1. For information on the various levels,
see the next option.
-D <debuglevel>
--debug <debuglevel>
$opt_debug="<debuglevel>";
Sets debugging to <debuglevel>. Valid values are 1 or 2 and are
described below.
Debug level 1
Prints debugging information during the wlmoradc run to stderr
(and syslog, if that is enabled, see the section "Error
handling" below), including wlmoradc's settings. This allows
you to confirm that the command-line arguments and configfile
were digested as intended.
Debug level 2
Prints all information from debug level 1 plus the input to, and
output from, the database connection.
For security, any occurrences of the supplied username and
password are replaced with the string 'NOT_PRINTED'.
-s
--single
$opt_single=1;
Causes wlmoradc to collect the metric once and exit. The
default is to loop forever. Use this option to perform simple
debugging.
--i <iterations>
--iterations <iterations>
$opt_iterations="<iterations>";
Causes wlmoradc to loop <iterations> times, then exit. The
default is to loop forever. Use this option to debug over a range
of values.
NOTE: Specify either -s (--single) or -i (--iterations),
but not both.
Argument ordering
If specified, the -c (--configfile) option must be the first argument.
Other than the -c (--configfile) option, arguments can be in any order,
but are evaluated left to right, so any repeated arguments (for exam‐
ple, --interval 10 --interval 5) will use the last value specified.
Configuration files
Along with specifying options on the command line, you can specify one
or more of the same options in a configuration file. This file is writ‐
ten in perl and consists of a series of assignments to variables, each
variable being $opt_command_line_option_name. For example, if a config‐
uration file '/tmp/myconfig.oradc' contains
# a comment
$opt_iterations=5;
$opt_debug=1;
$opt_sqlstring='select count(*) from v$sessions;';
1;
then the command line
wlmoradc--configfile /tmp/myconfig.oradc
is equivalent to
wlmoradc--iterations 5 --debug 1 --sqlstring 'select count(*) from v$sessions;'
Both HP-supplied and user-written configuration files are discussed
below. Examples are available in /opt/wlm/toolkits/oracle/config/.
Having a configuration file instead of all command-line options pre‐
vents sensitive settings such as passwd from being shown as part of the
wlmoradc command line, which is visible via the HP-UX 'ps' command.
Two items of note: the configuration file is perl code that is executed
by wlmoradc, so, following perl conventions, it is necessary to have
the configuration file return a nonzero value. That is why a '1;' is
used at the end of the example. Second, perl allows the use of '#' to
denote comments, as in the example above. For more information on perl,
see the REFERENCES section at the end of this man page.
Dumping arguments
If the debug level is set, the current wlmoradc settings are dumped to
stderr at the beginning of the run. This is useful to confirm that the
command-line settings or configfile syntax is correct.
SQL statement sources
The SQL statements, which are the heart of the performance measurement,
can be specified two ways: directly as a string or indirectly as a path
to a file containing SQL statements.
Directly: specifying SQL as a string (--sqlstring or $opt_sqlstring)
If the SQL is to be supplied directly, it can either be passed on the
command line via the -sqlstring option, or in a configuration file as
an assignment to $opt_sqlstring variable.
Indirectly: specifying SQL from separate file (--sqlfile or $opt_sql‐
file)
If the SQL is to be supplied indirectly from a file, the path to the
file can either be passed on the command line via the --sqlfile option,
or in a configuration file as an assignment to $opt_sqlfile variable.
NOTE: The statements are read once at wlmoradc invocation. Changes to
the SQL file during execution have no effect on the running wlmd or
wlmoradc.
Principle measurement types
Two major types of measurements can be performed, returning a single
value from a piece of SQL code and timing a sample transaction.
SQL value (-v or --sql_value)
For condition/exception and parametric SLOs, a different approach is
used--the SQL queries one or more instance metrics and does appropriate
joins or other computations to reduce the output to a single floating-
point value, which is returned to wlmoradc and from there to wlmd. For
this type of query, the elapsed time is not important, but the output
format for the SQL is. For example, to understand how many processes
are currently running, a common DBA query might be
select name from V$PROCESS;
which returns a human-readable list of processes. For wlmoradc,
select count(*) from V$PROCESS;
is more appropriate as it causes the SQL to return a single value that
can be passed to wlmd.
Elapsed time (-t or --walltime)
For response-time goals, any type of SQL statements that are represen‐
tative of the workload are potential candidates to run with wlmoradc
and collect the elapsed wall time. However, SQL with side effects, like
UPDATE or DELETE statements, should be eliminated because using them to
measure performance would modify the database itself. For more informa‐
tion on selecting appropriate SQL, see the HP-UX Workload Manager Tool‐
kits User's Guide.
Measurements of SQL statement execution times do not include the time
to connect to the database or disconnect from the database.
Because the SQL statements are being timed, the actual results of the
statements are discarded. Consequently, the output format is not impor‐
tant.
Connecting to the database
After initial setup and reading configuration file and/or command-line
arguments, wlmoradc constructs a command file containing, among other
items, the SQL statements specified. At each iteration, the command
file is submitted to the sqlplus tool, results are collected, parsed,
and returned to wlmoradc's stdout.
The connection of sqlplus to the database instance is governed by all
the normal Oracle connection rules: the username and passwd specified
to wlmoradc are used to connect. If the installation script cre‐
ate_hp_wlmuser.sql has been run against the instance, the default user‐
name 'hp_wlmuser' and default passwd 'hp_wlmuser' can be used to con‐
nect and query the V$ tables of the instance. Otherwise, a username and
passwd that is valid for the particular instance must be used.
Error handling
Errors are reported via stderr unless wlmoradc determines it has no
attached controlling tty, in which case errors are logged to syslog(3C)
via the logger(1M) command. Errors are logged as LOG_ERR, warnings as
LOG_WARNING, and debug messages (if enabled) as LOG_DEBUG. Messages to
syslog are also prefixed with the wlmoradc name, user ID, and process
ID.
Any errors encountered during wlmoradc setup result in an error message
and an exit with a nonzero return code. Introduced with WLM A.02.01,
the coll_stderr keyword provides an easy way to have all coll_argv com‐
mands' stderr redirected to syslog. You may find this useful in diag‐
nosing any wlmrcvdc or wlmoradc startup problems.
If the error occurs during wlmoradc's run of sqlplus, the error is
reported, but execution is not stopped. This is to allow wlmoradc to
run even if the target database instance is stopped or not mounted.
How to use wlmoradc to manage your instances
The following steps outline how to use Oracle Database metrics with
WLM:
1. Place your Oracle instances in WLM workload groups
2. Decide the metrics you want to collect
3. Implement the SQL statements to retrieve the desired metrics
4. Use wlmoradc to pass the metrics to WLM, which then adjusts the
CPU for the workload groups based on their metrics
For information on setting up the WLM Oracle Database toolkit, creating
wlmoradc configuration files, and creating WLM configuration files, see
the HP-UX Workload Manager Toolkits User's Guide (/opt/wlm/tool‐
kits/doc/WLMTKug.pdf).
NOTE: For optimal performance when managing Oracle instances, do not
set the wlm_interval keyword in the WLM configuration to less than 10
seconds.
DB toolkit use cases
The intended use for wlmoradc and related utilities is to extract
information for an Oracle database instance and provide that data as a
metric to a running wlmd. The metric can then be used as the objective
in a service-level objective (SLO) or can be used to report if a par‐
ticular database condition exists, driving a condition or exception
that determines whether wlmd enforces a particular SLO at a particular
moment. The variations on these use cases are discussed below.
wlmoradc metric drives condition/exception for existing SLO
A particular SLO may only be valid when a particular condition exists
in the Oracle database instance. For instance, a larger allocation or
'boost' may be desired if a certain user connects. Using wlmoradc to
return a metric that is used with the WLM keyword condition can achieve
this.
Example 1 - number of users via command line
To know the number of connected users to an Oracle instance, use a very
simple SQL SELECT to the Oracle V$ table to determine the number of
connections that have real usernames:
/opt/wlm/lbin/coll/wlmoradc \
--configfile /opt/wlm/toolkits/oracle/config/user_cnt.oradc \
--instance instance1 \
--debug 1 \
Example 2 - number of users in wlmd configuration file
Same measurement as Example 1, but now in a WLM configuration file. Run
wlmrcvdc with wlmoradc as a child, returning the number of connected
users to instance instance1 to wlmd,
[Don't forget that the wlmoradc script counts as one connection!]
from a WLM configuration file:
tune user_cnt {
coll_argv =
wlmrcvdc
wlmoradc--configfile /opt/wlm/toolkits/oracle/config/user_cnt.oradc
--instance instance1;
coll_stderr = syslog; # send stderr to syslog
}
If you desire some variation on user_cnt.oradc, modify the SQL from
/opt/wlm/toolkits/oracle/config/user_cnt.oradc. Then, specify the modi‐
fied SQL using the --sqlfile option or the --sqlstring option.
wlmoradc metric drives a WLM parametric SLO
A second, slightly more dynamic SLO is to have an external metric
directly drive the SLO's allocation. Because the allocation is then a
function of the metric, this is referred to as a 'parametric' SLO. For
this, a wlmoradc metric is coupled with the cpushares keyword in a WLM
configuration file, causing WLM to drive the allocation appropriately.
See below for an example.
wlmoradc metric supplies timing information for WLM objective
The most dynamic type of SLO supported by the wlmoradc tool is a goal-
based SLO, generally in the form of a goal based on the response time
of user-supplied SQL. Use the -t (--walltime) option to capture
response (elapsed) time. This response time is fed back to wlmd, which
compares it to the response-time goal and makes appropriate allocation
changes. See below for an example.
Getting started: using a supplied configurations for wlmoradc
To get the wlmoradc user started, a series of supplied configurations
for wlmd and wlmoradc are supplied in /opt/wlm/toolkits/oracle/config/.
Some users may find that these meet their needs, and no further steps
are needed. Others will find these to be working examples they can use
as a basis for their own wlmd or wlmoradc configurations.
NOTE: Do not edit or modify the items in /opt/wlm/toolkits/oracle/con‐
fig/ directly. To customize, copy to a new location, edit the copy, and
change the paths in the copies.
Supplied configuration to drive a wlmd condition
Example of a supplied WLM configuration that implements a condi‐
tion/exception use case is
user_cnt_boost.wlm
which uses wlmoradc metrics derived from the wlmoradc configuration
file
user_cnt.oradc
Supplied configuration to drive a parametric allocation
Examples of a supplied WLM configuration that implement a parametric
use case are
shares_per_process.wlm
shares_per_user.wlm
which use the metrics derived from the wlmoradc configuration files
user_cnt.oradc
process_cnt.oradc
Supplied configuration to time a ghost transaction
Examples of a supplied configuration that implement a response time use
case are
timed_select_scott.wlm
timed_sys_table.wlm
which use the metrics derived from the wlmoradc configuration files
sys_table_cart_sel_resp_time.oradc
select_scott_resptime.oradc
The next step: user written SQL statements
Besides using supplied configuration files, a user may also create
their own configuration files or sets of command-line arguments, and
accompanying SLOs. See the HP-UX Workload Manager Toolkits User's Guide
for more information and hints on how to build and test such sets.
In general, when first familiarizing yourself with this tool, invoke
wlmoradc from the command line and view the measurements directly. This
provides instant feedback, allowing you to quickly run, edit your SQL,
and rerun with no system level impact.
For instance, if a common transaction for database 'sesame' was to
check the price history of bananas, that could become the ghost or sam‐
ple transaction for performance measurement. The SQL that comprises
the search would be placed in /home/bert/banana_price_history.sql, and
the following command could then be used to check that wlmoradc was
able to connect to the database and run the transaction.
On the command line:
/opt/wlm/lbin/coll/wlmoradc \
--instance sesame \
--username bert \
--password ernie \
--sqlfile /home/bert/banana_price_history.sql \
--walltime
The --debug option can be used to get additional information during the
sample transaction testing.
Once you are comfortable with the metrics being collected, place your
wlmoradc command line in a WLM configuration file (for example,
myfile.wlm) and invoke wlmd with that file, which will run wlmoradc as
a child of wlmd.
From a WLM configuration file:
tune my_metric {
coll_argv =
wlmrcvdc
/opt/wlm/lbin/coll/wlmoradc \
--instance sesame
--username bert
--password ernie
--sqlfile /home/bert/banana_price_history.sql \
--walltime
coll_stderr = syslog; # send stderr to syslog
}
SECURITY
The hp_wlmuser user and role must be created in the Oracle instance
(with the create_hp_wlmuser.sql script) before the default username and
password will work correctly.
Usernames, passwords, and SQL statements are all stored in clear text
in files for use with the --configfile or --sqlfile arguments. As with
any stored SQL scripts, use appropriate UNIX file permissions to safe‐
guard sensitive data. For further security information, see the HP-UX
Workload Manager Toolkits User's Guide.
LIMITATIONS
The supplied SQL code is run repeatedly, which may cause Oracle to
encache the SQL statements. For timed SQL statements, care must be
taken to assure that the sample SQL statements share caching character‐
istics with the statements they are designed to represent.
If WLM starts before a database instance that it manages resources for,
it sets the CPU allocation for the instance's workload group based on
the mincpu and maxcpu values in the WLM configuration file. The
instance's workload group remains at that initial CPU allocation until
the instance starts, allowing wlmoradc to gather metrics on it and pass
the metrics to WLM.
wlmoradc uses the sqlplus 'timing start' and 'timing stop' commands to
collect walltime measurements. Consequently, the use of the 'timing
start' or 'timing stop' commands within user-supplied SQL statements
with the --walltime option is not supported.
PL/SQL code has not been tested and should be considered unsupported by
this tool.
DEPENDENCIESwlmoradc requires /opt/perl/bin/perl.
AUTHORwlmoradc was developed by HP.
FEEDBACK
If you would like to comment on the current WLM Toolkits functionality
or make suggestions for future releases, please send email to:
wlmfeedback@rsn.hp.com
Version information
@(#) HP WLMTK A.01.10.01 (2006_11_05_16_54_54) hpux_ipf
SEE ALSOwlm(5)wlmtk(5)wlmd(1M)wlmrcvdc(1M)wlmsend(1M)libwlm(3)
HP-UX Workload Manager User's Guide (/opt/wlm/share/doc/WLMug.pdf)
HP-UX Workload Manager Toolkits User's Guide (/opt/wlm/tool‐
kits/doc/WLMTKug.pdf)
Example WLM and wlmoradc configuration files /opt/wlm/toolkits/ora‐
cle/config/
http://www.hp.com/go/wlm WLMTK updates and information
perl information
www.perl.com
www.perl.org
TODO and future enhancements
Test with more types of SQL beyond SELECT statements.
wlmoradc(1M)
