4 Administering the Oracle Database QoS Management System

This chapter describes the basic administrative tasks you perform when using Oracle Database QoS Management to manage performance of your Oracle RAC cluster.

Determining If Oracle Database QoS Management is Enabled

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.

Checking the Enabled Status for a Database

To determine if your database is managed by Oracle Database QoS Management, perform the following steps:

  1. Log in to Cloud Control and select the database target to check.
  2. From the target's menu, select Cluster Database, then Target Information.
  3. In the Target Information window, at the bottom, the value for QoS Status should be Active if Oracle Database QoS Management is enabled for this database.

Checking the Enabled Status for the Cluster

Using Oracle Enterprise Manager Cloud Control, you can determine whether Oracle Database QoS Management is enabled for your cluster.

To determine if a cluster is managed by Oracle Database QoS Management, perform the following steps:

  1. Log in to Cloud Control and select the cluster target to check.
  2. From the target's menu, select Administration, then Quality of Service Management, and then Dashboard.
  3. If prompted, log in as the QoSAdmin user.
  4. On the Dashboard, in the General section, check the value for QoS Status. If the status value is Enabled, then the cluster is being managed by Oracle Database QoS Management.

Monitoring Performance with Oracle Database QoS Management

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

Description of Figure 4-1 follows
Description of "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 12.1.0.2) 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 (11.2.0.3) 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.

Using the Oracle Database QoS Management Dashboard

The Oracle Database QoS Management Dashboard (the Dashboard) provides an easy to use interface for managing the Oracle Database QoS Management system.

Accessing the Oracle Database QoS Management Dashboard

The Dashboard has four main sections:

  • General

  • Performance Overview

  • Recommendations

  • 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.

  1. Log in to Cloud Control and select the cluster target to check.
  2. From the target's menu, select Administration, then Quality of Service Management, and then Dashboard.
  3. A login screen appears, prompting you for the Oracle Database QoS Management administrator (QoSAdmin) credentials. After typing in the username and password, click Login.
  4. The Quality of Service Management Dashboard page, shown in Figure 4-2, is displayed after the correct credentials are entered.

    Figure 4-2 Oracle Database Quality of Service Management Dashboard

    Description of Figure 4-2 follows
    Description of "Figure 4-2 Oracle Database Quality of Service Management Dashboard"

Enabling Oracle Database QoS Management for a Cluster

  1. On the Dashboard, in the General section, next to QoS Status, click Disabled.
  2. On the Enable / Disable Quality of Service Management page, click Enable QoS Management.

Disabling Oracle Database QoS Management for a Cluster

  1. On the Dashboard, in the General section, next to QoS Status, click Enabled.
  2. On the Enable / Disable Quality of Service Management page, click Disable QoS Management.

Interpreting the Performance Overview Graphs

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

Description of Figure 4-3 follows
Description of "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

Viewing Recommendations

When viewing recommendations, there are three possible results.
  1. If the Performance Classes are meeting their Performance Objective, then a recommendation is displayed which states "No action required: all Performance Objectives are being met."
  2. If Oracle Database QoS Management determines that a Performance Class is not meeting its Performance Objective and has a recommended action for improving performance, then the Dashboard places an asterisk ('*') in front of the Performance Class name in the Performance Overview chart and displays a Recommended Action.
  3. If more than one Performance Class is not meeting its Performance Objective as shown in Figure 4-3, then only the target Performance Class displays an asterisk and a recommendation. If the recommendation has an associated action, then an Implement button appears, which you can click to have the action implemented.
    If there are no recommended actions, then you can click the Details button to view the results of the latest analysis. Oracle Database QoS Management shows what possible actions could be taken, and why these actions were not chosen.

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

Description of Figure 4-4 follows
Description of "Figure 4-4 Performance Overview Page Showing Performance Classes Not Meeting Their Performance Objectives"

Viewing Recommendation Details

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.

Implementing Recommendations

If there is a Recommended Action, and you decide to implement the action, then simply click the Implement button on the Dashboard or the Details page.

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.

Administering the Policy Set

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.

Note:

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.

Editing a Policy Set

To modify an existing Policy Set, perform the following steps:

  1. From the cluster target menu, select Administration, then Quality of Service Management, then Edit Policy Set.

    The Policy Set Editor wizard is started.

  2. On the first page of the Policy Set Editor wizard you can view the current server pool settings.

    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.

  3. The second page enables you to create, edit, rename, or delete Performance Classes for the system. You can create a new Performance Class, or use the Edit Performance Class button to modify the classifiers for an existing Performance Class.

    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.

  4. After creating or modifying the Performance Classes, the next step is to set the order in which the classifiers are evaluated.

    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.

  5. After you have set the order for your Performance Classes, the next step is to edit the Performance Policies and set the Performance Objectives.

    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.

    Note:

    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.

  6. After you have configured the Performance Policies, you must choose which one to put into effect immediately after the Policy Set is submitted.

    Select the preferred Performance Policy, then click Set Policy. When finished, click Next.

  7. Before you submit a Policy Set to the Oracle Database QoS Management system, you are asked to review the Policy Set configuration. If the changes are what you intended, then click Submit Policy Set. If you must modify any of the displayed settings, then click the Back or Cancel button.

    After clicking Submit Policy Set, you are returned to the Dashboard.

Adding Server Pools to a Policy Set

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".

See Also:

Modifying Server Pool Settings

To modify the server pool settings, you configure a server pool directive override for a Policy Set.

When you configure server pool directive overrides, the new parameters you specify for the server pool are used instead of the parameters specified at the time of server pool creation.

Note:

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.

  • You can modify the server pool parameters manually using the Manage Server Pool pages of Oracle Enterprise Manager Cloud Control or SRVCTL.
    You should not use this method of altering the server pool configuration when you have Oracle Database QoS Management enabled. If you use both server pool directive overrides and manually change the server pool configuration, then the server pool directive overrides specified for the current Policy Set will override the manual settings and can result in confusion.

Adding Database Services to a Policy Set

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.

  1. The database administrator uses SRVCTL or Oracle Enterprise Manager Cloud Control to create database services that are managed by Oracle Clusterware. You can use the Availability menu on the database target page of Enterprise Manager Cloud Control to create services for an Oracle RAC database.

    See Also:

    Oracle Database 2 Day + Real Application Clusters Guide for more information on using Enterprise Manager Cloud Control to create services

  2. The QoS Administrator adds one or more Performance Classes to the Policy Set or modifies the existing Performance Classes to include the new database services

Note:

Database services appear in Oracle Database QoS Management as db_name:service_name, so the service names must be unique within a database.

Updating a Policy Set to Include a New 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.

  1. The database administrator first requests servers to host the cluster database from the cluster or system administrator.
  2. The cluster or database administrator creates the new server pools with the specified minimum number of nodes. The cluster or database administrator could also decide to grant access to existing server pools instead of creating new ones.
  3. The database administrator creates a new database on the allocated server pools. The database administrator must ensure that the new database is configured correctly for management by Oracle Database QoS Management. See "Supported Database Configurations" for details.
  4. The database administrator uses SRVCTL or Oracle Enterprise Manager Cloud Control to create database services that are managed by Oracle Clusterware for the new database.
  5. The database administrator enables the database for Oracle Database QoS Management from the Database target page of Cloud Control.
  6. Using the Edit Policy Set link in Cloud Control, the QoS Administrator adds the new server pools to the Policy Set, adds one or more Performance Classes to the Policy Set, or modifies the existing Performance Classes to include the new database services. See "Editing a Policy Set" for more information about editing a policy set.
  7. After you successfully submit the new Policy Set, the new database is monitored and managed by Oracle Database QoS Management.

Managing Performance Classes

Each policy set contains one or more performance classes. Each performance class defines a type of workload on your servers.

Creating a Performance Class

To create or edit the Performance Classes and the classifiers they use, perform the following tasks:

  1. Start the Policy Set Editor wizard. From the cluster target page in Oracle Enterprise Manager Cloud Control, select Administration, then Quality of Service Management, then Edit Policy Set.
  2. Go to the second screen in the wizard.

    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

  3. Click Add Performance Class, and the Performance Class creation page is displayed. In the Performance Class Name text field, enter a name for the Performance Class.
  4. In the Classifiers section, enter information to define a rule for classifying work requests. First select a database service, then specify matching values (In Set) for the module, action, UserName, or program name that is associated with the work request using the specified database service. You can also specify exclusion values (Not In Set) for these attributes.

    If you want to add multiple classifiers for the Performance Class, then click the Add Classifier button and enter in the appropriate information.

    Note:

    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.

  5. After you have defined all the classifiers for the Performance Class, click the Next button until you reach the end of the wizard. Review the information you specified, then click Submit Policy Set.

Deleting a Performance Class

You can delete a Performance Class that is no longer needed.

  1. Start the Edit Policy wizard.
  2. Go to the second screen in the wizard.
    On the Edit Policy Set: Performance Classes page, the available Performance Classes are displayed.
  3. Select a Performance Class and click Delete Performance Class.
  4. Advance to the end of the Edit Policy Set wizard, and click Submit Policy Set to make the change permanent.

Renaming a Performance Class

You can rename a Performance Class using the Edit Policy Set wizard.

  1. Start the Edit Policy wizard.
  2. Go to the second screen in the wizard.
    On the Edit Policy Set: Performance Classes page, the available Performance Classes are displayed.
  3. Select a Performance Class and click Rename Performance Class.
  4. On the Rename Performance Class page, enter the new name of the Performance Class, then click OK.
  5. Advance to the end of the Edit Policy Set wizard, and click Submit Policy Set to make the change permanent.

Editing an Existing Performance Class

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.

  1. Start the Policy Set Editor wizard.
    1. From the cluster target page in Oracle Enterprise Manager Cloud Control, select Administration.
    2. Select Quality of Service Management.
    3. Select Edit Policy Set.
  2. Go to the second screen in the wizard.
    On the Edit Policy Set: Performance Classes page, the available Performance Classes are displayed.
  3. Select the Performance Class you want to modify and click Edit Performance Class.
  4. When you are finished making changes, click OK.
  5. Advance to the last page of the Policy Set Editor and click Submit Policy Set.

Adding Classifiers

If you want to add a classifier to a Performance Class, then perform the following steps:

  1. Start the Policy Set Editor wizard.
  2. Go to the second screen in the wizard, the Edit Policy Set: Performance Classes page.
  3. Select the Performance Class to modify, then click Edit Performance Class.
  4. In the Classifiers section on the Edit Performance Class page, click the Add Classifier button and enter in the appropriate information. When finished, click OK.
  5. You then advance to the end of the Edit Policy Set wizard, and click Submit Policy Set to make the change permanent.

Changing Classifiers

If you want to modify one or more classifiers for a Performance Class, then perform the following steps:

  1. Start the Policy Set Editor wizard.
  2. Go to the second screen in the wizard, the Edit Policy Set: Performance Classes page.
  3. Select the Performance Class for which you want to change the classifiers, then click Edit Performance Class.
  4. In the Classifiers section on the Edit Performance Class page, modify the classifier information, then click OK.
  5. You then advance to the end of the Edit Policy Set wizard, and click Submit Policy Set to make the change permanent.

Deleting Classifiers

To delete one or more classifiers for a Performance Class, perform the following steps:

  1. Start the Policy Set Editor wizard.
  2. Go to the second screen in the wizard, the Edit Policy Set: Performance Classes page.
  3. Select the Performance Class for which you want to delete the classifiers, then click Edit Performance Class.
  4. In the Classifiers section on the Edit Performance Class page, select the classifiers you want to delete, then click Delete Classifiers. When finished, click OK.
  5. You then advance to the end of the Edit Policy Set wizard, and click Submit Policy Set to make the change permanent.

Specifying the Evaluation Order of the Classifiers

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:

  1. Start the Policy Set Editor wizard.
  2. Proceed to the third page in the wizard, which is titled Edit Policy Set: Classifier Ordering.
  3. Use the arrow keys to the right of a classifier to move the classifier up or down in the list. The classifiers for the Performance Classes at the top of the list are evaluated first. If the work request does not match the classifiers for that Performance Class, then evaluation continues with the next Performance Class in the list, until there are no further evaluations to be made. If a work request matches the classifiers for a Performance Class, then the work request is associated with that Performance Class and evaluation stops.

    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.

  4. You then advance to the end of the Edit Policy Set wizard, and click Submit Policy Set to make the change permanent.

Managing Performance Policies

Creating a Performance Policy and Specifying Performance Objectives

Use Oracle Enterprise Manager Cloud Control to create a Performance Policy.

To create and configure a Performance Policy, perform the following steps:

  1. Start the Policy Set Editor wizard.
  2. Proceed to the fourth page in the wizard, which is titled Edit Policy Set: Performance Policies.
  3. Click the Add Policy button to create a Performance Policy. The Add Policy page appears.

    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.

      Note:

      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.

  4. Optional: Specify which actions can be performed automatically by QoS Management. See "Automatically Implementing Recommendations for a Performance Policy".
  5. Optional: Configure server pool directive overrides. See "Setting Server Pool Directive Overrides".
  6. Advance to the end of the Edit Policy Set wizard. Click Submit Policy Set to make the change permanent.

Editing an Existing Performance Policy

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.

  1. On the Edit Policy Set: Performance Policies page, select the Performance Policy you want to modify and click Edit.
  2. Specify the new ranks or objective values for the Performance Classes, or enable or disable the Measure Only setting for a Performance Class.
  3. When you have finished making your changes, click OK to return to the Policy Set Editor wizard.
  4. Click Next until you reach the end of the wizard. Review your changes, then click Submit Policy Set.

See Also:

Copying a Performance Policy

Instead of creating a new Performance Policy, you can instead copy an existing Performance Policy. A copied Performance Policy is identical to the original Performance Policy. You can then simply rename and edit the copy instead of re-creating all the details in a new Performance Policy.

Setting the Current Performance Policy

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.

Changing the Active Performance Policy from the Dashboard

  1. On the Dashboard page, in the General section, click the button Change Active Policy.
  2. On the Set Policy page, select the Performance Policy you want to use, then click OK.

Changing the Active Performance Policy from the Policy Set Editor Wizard

You can change the Performance Policy that will be active when you submit the Policy Set to Oracle Database QoS Management.

  1. Start the Policy Set Editor wizard.
  2. Proceed to the fifth page in the wizard, which is titled Edit Policy Set: Set Policy.
  3. Select the Performance Policy you want enforced, and click Set Policy.

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.

Changing the Active Performance Policy using QOSCTL

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.

  1. Log in to the operating system user as the Clusterware administrator.
  2. At the command line, or within a script, use the qosctl command with the -activatepolicy option. 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"
    

See Also:

"QOSCTL Utility Reference" for more information about QOSCTL.

Deleting a Performance Policy

To delete a Performance Policy, perform the following steps:

  1. Start the Policy Set Editor wizard.
  2. Proceed to the fourth page in the wizard, which is titled Edit Policy Set: Performance Policies.
  3. Click the Delete Policy button to delete a Performance Policy.
  4. You then advance to the end of the Edit Policy Set wizard, and click Submit Policy Set to make the change permanent.

Automatically Implementing Recommendations for a Performance Policy

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.

Setting Server Pool Directive Overrides

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.

Server pool directive overrides should be used only when necessary. Instead of using a server pool directive override, you should monitor the system over time and modify the server pool settings as needed. Using a server pool directive override can result in unexpected changes in resource allocations. For example, assume you have server pools named webapps, HR, and payroll. You create a server pool directive override to increase the minimum server pool size for the payroll server pool. When the server pool directive override is active, a server could be removed from the HR or webapps server pool to satisfy the higher minimum server requirement of the payroll server pool.

See Also:

"Modifying Server Pool Settings" for more information about configuring the server pool settings

  1. Start the Policy Set Editor wizard.
  2. Proceed to the fourth page in the wizard, which is titled Edit Policy Set: Performance Policies.
  3. Click the Edit Policy button to edit a Performance Policy.
    The Edit policy page appears.
  4. Expand the Server Pool Directive Override section, if necessary.
  5. To set server pool directive overrides, perform the following:
    • 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.

  6. After you have finished entering the server pool directive override values, click OK to implement the changes.
  7. Advance to the end of the Edit Policy Set wizard, and click Submit Policy Set to make the change permanent.

Reviewing Performance Metrics

You can view a variety of performance metrics for the Oracle Database QoS Management system as a whole, or for individual Performance Classes.

Viewing Performance Metrics for All 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:

  1. Log in to Oracle Enterprise Manager Cloud Control as the cluster administrator. Go to the cluster target page.

  2. From the cluster target menu, select Administration, then Quality of Service Management, then View Performance Class Quality of Service.

  3. The Performance Class Quality of Service page displays three charts measuring the current performance of each Performance Class that is being monitored:

    1. The Performance Satisfaction Metric chart

    2. The Demand chart

    3. The Average Response Time chart

Viewing Performance Metrics for Individual Performance Classes

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:

  1. Log in to Oracle Enterprise Manager Cloud Control as the cluster administrator.
  2. Select the cluster target page that is configured for QoS Management.
  3. From the cluster target menu, select Administration, then Quality of Service Management, then View Performance Class Quality of Service.
  4. On the right-hand side of any graph, in the legend box, click the link that corresponds to the Performance Class for which you want to view the performance metrics.

Configuring Alerts for Quality of Service Management Events

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.

Both warning and critical levels can be alerted based upon specified durations for each performance class by setting up alert thresholds and notifications. The alerts are configured against the databases that offer the services being monitored
  1. Start Enterprise Manager Cloud Control.
  2. Navigate to the database for which you want to configure the alert.
  3. From the Cluster Database menu select Monitoring and then select| Metric and Collection Settings.
  4. Under the Metrics tab, edit the metrics in the categoryQoS Management – Performance Satisfaction Metrics.

    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.

The configured alerts will appear on the Database Home page of Enterprise Manager if a violation is detected.

Viewing the Resource Wait Times Breakdown

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.

Creating Administrative Users for Oracle Database QoS Management

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 Utility Reference

QOSCTL is a command-line utility that allows you to perform certain configuration tasks for Oracle Database QoS Management.

Purpose

The QOSCTL utility supports the creation of Oracle Database QoS Management administrative users.

Security Requirements

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 qos_admin_user.

Displaying Help for the QOSCTL Utility

To display the help for the qosctl utility, use the following command:

qosctl -help

Syntax

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.

Commands

Table 4-1 Summary of Commands for the QOSCTL Utility

Command Syntax Description

-activatepolicy policy_name

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.

-adduser username

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.

-listusers

Lists the users that are authorized to run the Oracle Database QoS Management application

-remuser username

Removes the specified user account and all associated permissions

Note: This command is not recoverable. Use caution, because you can delete your own account.

-setpasswd username

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.

-help

Displays the syntax for QOSCTL commands.

Editing the Resource Plan for Oracle Database QoS Management

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:

Plan Component Modifiable? Description

General

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.

Parallelism

Limited edits allowed

This section can be edited however all values set for the ORA$APPQOS* consumer groups must be the same within each column due to the reason stated above.

Thresholds

Limited edits allowed

This section can be edited however all values set for the ORA$APPQOS* consumer groups must be the same within each column due to the reason stated above. In addition, none of the "Switch to *" actions should be selected for these groups.

Idle Time

Limited edits allowed

This section can be edited however all values set for the ORA$APPQOS* consumer groups must be the same within each column due to the reason stated above.