4 Administering the Oracle Database QoS Management System

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.

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.
   
   Description of the illustration qos_cluster_status_en.gif

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.
   
   Description of the illustration cc_select_dashboard_12c.gif
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.
   
   Description of the illustration appqos_login_02.gif
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 Oracle Database Quality of Service Management Dashboard"

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.

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.

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 Charts

Description of "Figure 4-3 Example of the Performance Overview Charts"

In Figure 4-3, there are bar graphs for the salescart_pc, erp_pc, sales_pc, hr_pc, shipping_pc, etl_pc, and Default_pc Performance Classes. There are two bar graphs displayed for each Performance Class. The two bar graphs are titled Resource Use vs. Wait Time (Last 5 sec.) and Performance Satisfaction Metric (Last 5 min.). The Resource Use vs. Wait Time bar graph has three sections of varying size that illustrate the resource use, wait and headroom 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 the five second time slices that were exceeding or violating the Performance Objective respectively. If you place your cursor over a section of the bar graph, then a description of that measurement appears by your cursor.

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 Performance Overview Page Showing Performance Classes Not Meeting Their Performance Objectives"

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.
   
   Description of the illustration cc12102_policy_edit_1.png

   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.
   
   Description of the illustration cc12_policy_edit_2.gif

   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.
   
   Description of the illustration cc12_policy_edit_3.gif

   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.
   
   Description of the illustration cc12_policy_edit_4.gif

   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.
   
   Description of the illustration cc12_policy_edit_5.gif

   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.

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

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

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

If a new policy-managed database is added to your cluster, you may want 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.

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.

   
   Description of the illustration cc12_policy_edit_2.gif

   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.
   
   Description of the illustration cc12_add_perf_class_1.gif
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.

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.

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.

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.

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.

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 Create Policy page appears.
   
   Description of the illustration cc12102_add_perf_policy.png

   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 monitored, not managed. If you select Measure Only, then Oracle Database QoS Management 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 "Specifying Authorized Actions 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.

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.
   
   Description of the illustration cc12102_edit_perf_policy_1.png
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.

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.

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.

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.

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

qosctl -help

qosctl qos_admin_user command