This chapter describes the basic administrative tasks you perform when using Oracle Database QoS Management to manage performance of your Oracle RAC cluster.
Oracle Database QoS Management can be enabled at three different levels: the cluster, server pool, and database. If you have multiple databases within the same Oracle RAC cluster, they might not all be managed by Oracle Database QoS Management.
To determine if your database is managed by Oracle Database QoS Management, perform the following steps:
To determine if a cluster is managed by Oracle Database QoS Management, perform the following steps:
After you have enabled Oracle Database QoS Management and created a default policy set, as described in "Enabling Oracle Database QoS Management", you can start to use Oracle Database QoS Management to monitor the performance of your system.
In measure-only or monitor mode, all the Performance Classes in user-defined Performance Policies have the measure-only box checked. You can set Performance Objectives, and Oracle Database QoS Management displays the Performance Satisfaction Metric (PSM) on the dashboard. If the response time of the system exceeds the Performance Objective specified, the PSM bar changes to red and an optional alert generated, as shown in Figure 4-1. Oracle Database QoS Management does not make recommendations if the measure-only check box is selected.
Figure 4-1 Performance Satisfaction Metrics for Measure-Only Performance Classes
Running Oracle Database QoS Management in measure-only or monitor mode allows you to understand how various workloads perform when sharing resources. Measure-only mode and monitor mode (available with Oracle Database 12c Release 22.214.171.124) assists you in determining the baseline Performance Objectives to use for each Performance Class. You can also use this mode to identify performance bottlenecks in your system.
Starting with the Oracle Database 11g Release 2 (126.96.36.199) release, you can use Oracle Database QoS Management with Oracle RAC on systems in measure-only or monitor mode.
If you are using Oracle RAC on an Exadata system, then you can use Oracle Database QoS Management in active management mode. In active management mode, you specify Performance Objectives for each Performance Class, and Oracle Database QoS Management can make recommendations for improving performance when a Performance Class is not meeting its objectives.
The Oracle Database QoS Management Dashboard (the Dashboard) provides an easy to use interface for managing the Oracle Database QoS Management system.
The Dashboard has four main sections:
Resource Wait Time Breakdown
The General section of the Dashboard gives you a quick overview of the system. This section lists the QoS Status (Enabled or Disabled), the Current Active Policy, a link for checking the Performance Class details, and a notification for available recommendations. There is also a button that enables you to quickly change the current active policy.
In the Performance Overview section, there is a table that lists the Performance Classes, the server pools where work is occurring, their rank, the Performance Objective being measured, and whether the Performance Objectives are being monitored only, or are being monitored and managed. For each Performance Class there are bar graphs that provide an overview of the Performance Class metrics. See "Interpreting the Performance Overview Graphs" for more information.
In the Recommendation section you can view the recommendations that are available when a Performance Class is not meeting its Performance Objectives. You can also view any violations that prevent the recommendations from being made.
At the bottom of the Dashboard is the Resource Wait Times Breakdown section. This section contains a table that provides a breakdown of resource wait times by Performance Class. For each Performance Class, the bottlenecked resource is the one that has the most wait time. This data is used by QoS Management to produce Recommendations. The data can also be used to make manual adjustments to the system. If you expand each Performance Class listed in the table, then you can see the server pools for that Performance class, and the resource wait times for each server pool.
Figure 4-2 Oracle Database Quality of Service Management Dashboard
On the Dashboard, in the Performance Overview section, there is a list of the current Performance Classes, some basic information about each Performance Class, and two bar graphs that show the most recent trend for the performance metrics for that class, as shown in Figure 4-3.
Figure 4-3 Example of the Performance Overview Graphs
Figure 4-3 shows the Performance Overview section of the Dashboard, which consists of a table with embedded bar graphs. The rows in the table list each performance class, the server pools associated with that performance class, its rank and objective types. Also included in the row for each performance class are two bar graphs, one showing the resource use compared to the wait time, and the other graph showing the performance satisfaction metric over the last 5 minutes. The Resource Use vs. Wait Time bar graph has three sections of varying size and color that illustrate the resource usage (blue), wait time (gray or red), and headroom (green) portions of the Performance Objective. The Performance Satisfaction Metric bar graph is displayed as a single bar, growing from left to right, with the green and red segments representing five second time slices in which the performance class was either exceeding (green) or violating (red) its performance objectives. If you place your cursor over a section of the Resource Use vs. Wait Time bar graph, then a description of that measurement appears by your cursor.
Resource Use vs. Wait Time
The Resource Use vs. Wait Time graph is refreshed only when you refresh the page contents. In this graph:
The blue section represents the portion of the average time spent for all database requests by that Performance Class that are using resources in the last five seconds
The gray section represents the portion of the average time spent for all database requests by that Performance Class that are waiting on resources in the last five seconds
The green section represents the headroom for that Performance Class (proportion of the average time for all database requests below the specified Performance Objective) in the last five seconds
If a Performance Class is not meeting its Performance Objectives, then the gray and green sections disappear and the resource wait time is shown in red with a line to indicate where the Performance Objective is relative to the actual response time.
If you place your mouse cursor over any section of this bar graph, then the actual values of Use, Wait and Headroom are displayed
The point between the gray and the green sections of the bar graph is the Performance Objective value. If you set this value below the resource use time, then you will never meet that objective. When configuring the Performance Objectives for a Performance Class, you must set the Performance Objective high enough to produce sufficient headroom (shown in green) to be able to share resources between Performance Classes to meet service levels as demand changes.
If a red section appears in the bar graph for a Performance Class, then you know that the Performance Class is not meeting its Performance Objectives. Oracle Database QoS Management issues a recommendation and an action to implement, if possible, to correct the problem. The recommendations generated by Oracle Database QoS Management occur once each minute, so they correspond to an earlier time than the current Performance Overview graphs.
Performance Satisfaction Metric
The Performance Satisfaction Metric changes to show red and green segments for specific five second samples. Using this you can spot trends in the performance of your system.
For the Performance Satisfaction Metric bar graph:
The red section represents the periods of time the Performance Class was not meeting its Performance Objectives during the sampling period
The green section represents the periods of time the Performance Class was meeting its Performance Objectives during the sampling period
Example 4-1 Example Output For Performance Overview Page and Recommended Actions
Figure 4-4 Performance Overview Page Showing Performance Classes Not Meeting Their Performance Objectives
If Oracle Database QoS Management has generated a recommendation, then you can click the Recommendation Details button to view more information about why the recommendation was made, and the expected performance improvements to be gained if you implement the recommendations.
You can select different recommendations using the Action drop-down list. Oracle Database QoS Management tries to provide the best recommendation to the QoS administrator, but you could decide that a different action would produce better results. If you select a different action, then the information in the Recommendation Actions and the Situation Analysis sections are updated to reflect the impact of the alternate recommendation. You cannot implement an alternate recommendation if that recommendation has been rejected by the Oracle Database QoS Management System for not providing enough benefit to the system as a whole.
The Recommended Actions page also has a section called Situation Analysis. If you display the output in this section, then you can see a description the projected impact implementing the Recommendation will have on the Performance Classes and the server pool, as shown in the following screenshot.
After you have implemented a recommendation, the display on the Dashboard changes to show that there is an action in progress. No new recommendations are displayed until the system has settled after the resource change. You can determine the amount of time you have to wait before any new recommendations are made available by viewing the Details page before you implement a recommendation.
After implementing a Recommended Action, view the Performance Overview charts on the Dashboard to determine if the Performance Class is now meeting its Performance Objectives.
Whether you are configuring the Oracle Database QoS Management system for the first time, or want to create a new Policy Set, you use the Create Policy Set wizard to create your Policy Set. You use the Edit Policy Set wizard to modify your existing Policy Set.
If you decide to create a new Policy Set, then all existing Performance Policies and user-added Performance Classes must be re-created. The changes you make to the stored Policy Set are not saved until you click Submit Policy Set in the last page of the Policy Set wizard.
To modify an existing Policy Set, perform the following steps:
The Policy Set Editor wizard is started.
This page lets you specify which server pools are managed by Oracle Database QoS Management. If the Manage box for a server pool is unchecked, then none of the servers, databases or workloads that use that server pool are displayed or managed by Oracle Database QoS Management. The Details column displays any configuration violation details and the corrective action to take to enable the server pool to be managed.
When finished, click Next. The Policy Set Editor: Performance Classes page appears.
You can use the Expand All link to show the classifiers for each Performance Class, or expand an individual Performance Class entry to show the classifiers for only that Performance Class.
Select the Performance Class you want to edit, and then click the appropriate action button (Edit, Rename, or Delete). If you want to create a Performance Class for this Policy Set, then click the Add Performance Class button. See "Creating a Performance Class" for more information on creating a Performance Class.
When you have finished, click Next.
This step is very important, because the classifiers determine which Performance Class a work request is placed into. Select a Performance Class and use the arrow keys to the right of the classifiers to move the entries up and down in the list.
As a work request enters the system, the work request is evaluated against the classifiers. The first expression that evaluates to TRUE determines which Performance Class the work request belongs to. You should put the more fine-grained classifiers at the top, and the more generic classifiers at the bottom of the list.
On this page you can:
Create a new Performance Policy
Edit, copy, or delete an existing Performance Policy
Specify which resource allocation methods can be automatically implemented by QoS Management.
The DefaultPolicy Performance Policy cannot be modified, but the policy can be copied and used as the basis for a new Performance Policy.
Refer to the section "Managing Performance Policies" for details on how to perform the tasks on this page.
Select the preferred Performance Policy, then click Set Policy. When finished, click Next.
After clicking Submit Policy Set, you are returned to the Dashboard.
Server pools are created by the cluster or database administrator, using either Oracle Enterprise Manager Cloud Control or Server Control (SRVCTL). Refer to Oracle Clusterware Administration and Deployment Guide for instructions on how to create a server pool.
After the cluster administrator has created a server pool, you can add the server pool to the Oracle Database QoS Management system. See "Creating a Performance Policy and Specifying Performance Objectives".
Oracle Clusterware Administration and Deployment Guide for more information about modifying the size of a server pool
Oracle Real Application Clusters Administration and Deployment Guide for more information about configuring a recently allocated server to be a part of an existing Oracle RAC database.
To modify the server pool settings, you configure a server pool directive override for a Policy Set.
You can change the minimum and maximum settings for the listed server pools, but you cannot create additional server pools using this interface. Any changes made here, after they are submitted, alter the current server pool properties set in the Manage Server Pools section of Oracle Enterprise Manager Cloud Control when the associated Performance Policy is active.
By creating additional services, you can monitor your workload performance at a finer level, by limiting use of the new services to specific applications or users. To add database services to a Policy Set, you must create or modify Performance Classes for each new database service.
Oracle Database 2 Day + Real Application Clusters Guide for more information on using Enterprise Manager Cloud Control to create services
Database services appear in Oracle Database QoS Management as
service_name, so the service names must be unique within a database.
If a new database is added to your cluster, you can configure Oracle Database QoS Management to manage or monitor the workload on this database.
Each policy set contains one or more performance classes. Each performance class defines a type of workload on your servers.
On the Edit Policy Set: Performance Classes page, the available Performance Classes are displayed. If this is the first time configuring the system, then a Performance Class for each database service is shown along with a Default Performance Class.
To specify a classifier for a work request, you must specify at least one service name. If you specify multiple services, then use a comma-delimited list. Optionally, you can also specify any of the following filters:
A comma-delimited list of module names and whether the work request uses a module in this list
A comma-delimited list of actions, and whether the work request performs an action in this list
A comma-delimited list of user names, and whether the work request uses a user name in this list
A comma-delimited list of programs, and whether the work request is running a program in this list
When evaluating a classifier for a Performance Class, all of the specified values are compared to the work request attributes using an AND operation; if you specify multiple classifiers for the Performance Class, then the results of each classifier evaluation for that Performance Class are combined using an OR operation.
You can delete a Performance Class that is no longer needed.
You can rename a Performance Class using the Edit Policy Set wizard.
When editing Performance Classes, you can create, edit, rename, or delete performance classes for the system. You can use the Edit Performance Class button to modify the classifiers for an existing performance class.
The classifiers generate Boolean expressions that are evaluated each time a work request enters the system. The first classifier that evaluates to TRUE determines the Performance Class for that work request. To ensure that the work requests are put in the correct Performance Classes, you must be careful in specifying the order in which the classifiers are evaluated.
To set the order of evaluation for the classifiers, perform the following steps:
For proper classification of work requests, you should put the Performance Classes with the strictest classifiers at the top of the list, and the Performance Classes with most lax classifiers near the bottom of the list. The
Default_pc Performance Class, which has the most general classifiers, should always be at the bottom of the list.
Use Oracle Enterprise Manager Cloud Control to create a Performance Policy.
To create and configure a Performance Policy, perform the following steps:
For each Performance Policy you must specify a unique name. You can also provide a description of the policy and its intent. Then you must configure the Performance Classes for the policy.
To configure the Performance Classes, you must do the following:
Set the rank for each Performance Class, from highest to lowest. A higher rank gives that Performance Class higher priority when there is contention for resources.
Specify a value for the Performance Objective.
The Performance Objective value is the appropriate length of time in seconds in which the work request, or database request, should complete, for example, 0.008 seconds, or eight milliseconds.
You should not use the service-level agreements (SLAs) or target response times as the Performance Objective values. Instead, choose a value that is reasonable, sustainable, and greater than your target response time. Using a higher value gives you time to implement the recommendations from Oracle Database QoS Management regarding the allocation of resources before the Performance Objective is exceeded.
You can also select whether the Performance Class is only measured or monitored, not managed. If you select Measure Only, then Oracle Database QoS Management measures or monitors the Performance Class, but does not provide any recommendations for improving performance.
On the Edit Policy page, you can change the rank of each Performance Class, or change the Measure Only setting for a Performance Class. You can also select the type of resource allocation actions that can be automatically implemented by QoS Management and set server pool directive overrides.
"Setting Server Pool Directive Overrides" for more information on server pool directive overrides
A Performance Policy is a collection of Performance Objectives, one for each Performance Class, that are in force at the same time. There are multiple ways to modify the active Performance Policy for Oracle Database QoS Management.
At the end of the Policy Set Editor wizard, you can review the settings you specified, then click Submit Policy Set to configure Oracle Database QoS Management.
Many Oracle Database QoS Management policies are calendar based. You can switch the active performance policy automatically through a job scheduler such as Enterprise Manager Cloud Control, Task Scheduler or CRON by using QOSCTL to set the active policy.
qosctlcommand with the
-activatepolicyoption. The command must use the following syntax:
qosctl qos_admin_username -activatepolicy policy_name
If the Performance Policy name includes spaces, then you must enclose the Performance Policy name within double quotes, for example:
qosctl qosadmin -activatepolicy "Business Hours"
"QOSCTL Utility Reference" for more information about QOSCTL.
You can use the appropriate check boxes to specify which of the following actions can be implemented automatically by Oracle Database QoS Management:
Promote or demote a performance class consumer group
Move a CPU between databases within a server pool
Move a server between server pools
If you do not authorize any of these actions, then Oracle Database QoS Management does not implement any changes to the active system until you review the current Recommendations for a Performance Class and click the Implement button.
A server pool directive override gives you the ability to enforce different settings for server pool sizes, or change the importance of server pools. For example, if you are expecting a surge in demand, such as during an advertised sale period, then you could use a server pool directive override to allocate more resources to the accounting applications.
payroll. You create a server pool directive override to increase the minimum server pool size for the
payrollserver pool. When the server pool directive override is active, a server could be removed from the
webappsserver pool to satisfy the higher minimum server requirement of the
"Modifying Server Pool Settings" for more information about configuring the server pool settings
To override the current value for the minimum number of servers in a server pool, enter a new value in the Min:Override field for that server pool. Valid values are 0 to the maximum number of servers in that server pool.
To override the current value for the maximum number of servers in a server pool, enter a new value in the Max:Override field for that server pool. Valid values are from the minimum number of servers in that server pool to the size of the cluster.
To override the current value for the Importance of the server pool, enter a new value in the Importance:Override field for that server pool. Valid values are 0 to 1000; higher values indicate greater importance.
You can view a variety of performance metrics for the Oracle Database QoS Management system as a whole, or for individual Performance Classes.
You can view the performance metrics for your system on the Performance Class Quality of Service page.
After you have configured Oracle Database QoS Management, a short period of time is required for Oracle Database QoS Management to gather performance data and evaluate the performance of the system. After this period of time has passed, you can view the performance metrics for your system. To view the current performance metrics, perform the following steps:
Log in to Oracle Enterprise Manager Cloud Control as the cluster administrator. Go to the cluster target page.
From the cluster target menu, select Administration, then Quality of Service Management, then View Performance Class Quality of Service.
The Performance Class Quality of Service page displays three charts measuring the current performance of each Performance Class that is being monitored:
The Performance Satisfaction Metric chart
The Demand chart
The Average Response Time chart
After you have configured Oracle Database QoS Management, and a short period of time has passed, you can view the performance metrics for a specific Performance Class. To view the current performance metrics for a Performance Class, perform the following steps:
It is not convenient or efficient to require constant manual monitoring of the Quality of Service Management Dashboard. Instead you can use the Enterprise Manager Cloud Control notification system for reporting negative Performance Satisfaction Metrics (PSMs) that persist for user-specified times.
For example, if Quality of Service Management uses the database service
sales_svc_pc, then under Negative PSM Duration (seconds), for that service, you might configure a warning alert when the duration is more than 120 seconds and a critical alert if the duration is more than 180 seconds.
At the bottom of the Dashboard is the Resource Wait Times Breakdown table. This table provides breakdown of resource wait times by Performance Class. For each Performance Class, the bottlenecked resource is the one that has the most wait time. This data is used by Oracle Database QoS Management to produce recommendations. You can also use this data to make manual adjustments to your system.
Oracle Database QoS Management provides a command line utility named QOSCTL to help you manage users. This utility is installed on each node of the cluster but runs properly only if executed as the Oracle Clusterware administrator user on the same node on which the Oracle Database QoS Management server (the OC4J container) is running. The correct node to run the utility on can be determined by issuing the following command:
srvctl status oc4j
The QOSCTL utility supports the creation of Oracle Database QoS Management administrative users. The account information is stored in the local
system-jazn-data.xml file with encrypted credentials. The account information is also stored in the Oracle Clusterware Repository (OCR) to support failover of the Oracle Database QoS Management Server.
QOSCTL is a command-line utility that allows you to perform certain configuration tasks for Oracle Database QoS Management.
The QOSCTL utility supports the creation of Oracle Database QoS Management administrative users.
To use the QOSCTL utility, you must be logged in as an Oracle Clusterware administrator user.
When you enter the user name of an OC4J administrator user, you do not include the password. When you have submit the command for execution, you are prompted for the password associated with the specified
Displaying Help for the QOSCTL Utility
To display the help for the
qosctl utility, use the following command:
The following code example shows the general format of QOSCTL commands:
qosctl qos_admin_user command
In place of the
qos_admin_user argument, you would put the user name of an OC4J administrator user. Do not specify the password on the command line.
When configuring the initial accounts for your Oracle Database QoS Management system, the command uses operating system authentication.
Table 4-1 Summary of Commands for the QOSCTL Utility
Sets the current policy. This command enables you to change policies through a CRON or scheduling job.
Policy names that include spaces must be encapsulated in double-quotes.
Adds the specified user and prompts for a password. This user is automatically granted the role required to use the Oracle Database QoS Management application and execute any of these commands.
Lists the users that are authorized to run the Oracle Database QoS Management application
Removes the specified user account and all associated permissions
Note: This command is not recoverable. Use caution, because you can delete your own account.
Updates the password of a specified user. The QOSCTL utility prompts you for the current password for the user. You must supply the correct value for the old password to change the password for a user.
If the password for a user has been forgotten or lost, then you should remove the user account and create a new account for the user, with a new password.
Displays the syntax for QOSCTL commands.
You can perform limited editing of the Resource Manager plan used by Oracle Database Quality of Service Management.
Oracle Database QoS Management activates a resource plan named
APPQOS_PLAN, which is a complex, multilevel resource plan. Oracle Database QoS Management also creates consumer groups that represent Performance Classes and resource plan directives for each consumer group.
You can modify some of the sections of the
APPQOS_PLAN, but only as specified in the following table:
DO NOT EDIT
This section must not be edited as it is key to the QoS Management modeling and recommendation engine. If you modify this section, the change will be detected. In that case, Oracle Enterprise Manager Cloud Control reports a database error on the QoS Management dashboard.
Limited edits allowed
This section can be edited however all values set for the
Limited edits allowed
This section can be edited however all values set for the
Limited edits allowed
This section can be edited however all values set for the