HP-UX Workload Manager Toolkits User's Guide

HP-UX WLM Oracle Database Toolkit: Providing Database Metrics to WLM
How do I use the metrics?
Chapter 258
Time-driven goals
An important element of goal-based workload management is a
time-driven goal: WLM determines the correct CPU allocation so that
some action is performed in n seconds. This action could be a transaction
for an instance doing OLTP, a query that is run frequently, or any unit of
work with a completion time that can be used as a proxy to gauge the
overall performance of the instance.
ODBTK helps you create time-driven goals through the wlmoradc
command, which can measure the walltime consumed by SQL code. To
form such goals:
Step 1. Choose representative SQL code to form a “proxy transaction” or “proxy
unit of work”.
For information on fine-tuning your SQL statements with the SQL trace
and TKPROF utilities, see the manual Oracle 8i Tuning.
Step 2. Follow the steps in “Executing a query to get response time” on page 53
to establish a baseline measurement of the time needed for the
transaction.
Step 3. Create a WLM goal that is based either on the baseline or a
Service-Level Agreement. (In some cases, there may be a Service-Level
Agreement that the proxy transaction take less than Y seconds.)
Successful proxy transactions typically have the following
characteristics:
• The proxy transaction is sensitive to CPU allocation changes.
Remember that WLM modifies the CPU allocation to adjust the
performance of the Oracle instance. Thus, a transaction that is
mainly disk or memory work does not help WLM understand the
instance’s need for more or less CPU resources.
• The Proxy transaction SQL does not have side effects on the
instance.
The SQL will be repeatedly executed, so the proxy should not include
statements—such as UPDATE, DELETE, or INSERT—that affect
the integrity of the data.