5 Troubleshooting Oracle Database QoS Management

For a database to be managed by Oracle Database QoS Management, the database must be compliant, enabled, and Oracle Database QoS Management must be able to access APPQOSSYS database user:

• A compliant database is an Oracle RAC database that is running Oracle Database release 11.2.0.2 or greater.

• For a database to be enabled, Oracle Database QoS Management must be able to connect to the database. The connection is configured when you select the Enable Quality of Service Management link in the Oracle Enterprise Manager Cloud Control. When you select this link, you are prompted for the cluster credentials and the password for the APPQOSSYS account in the database. By default the APPQOSSYS account is expired. When you submit a password, the account is unlocked.

• If the password for the APPQOSSYS user is changed through other methods or the account is locked, then Oracle Database QoS Management is disabled for this database until this condition is corrected by selecting the Enable Quality of Service Management link again.

Oracle Database QoS Management installs a special Oracle Database Resource Manager plan, APPQOS_PLAN, whenever a database is enabled for management by Oracle Database QoS Management. Oracle Database QoS Management requires this resource plan to move Performance Classes to different levels of CPU scheduling. No other plan can be active while Oracle Database QoS Management is enabled on this database. If a resource plan is not enabled for the Oracle RAC database, then an error results when trying to enable the database for management by Oracle Database QoS Management. After a resource plan is enabled, then enabling the database for management by Oracle Database QoS Management succeeds.

To check that the required APPQOS_PLAN is active on the target database, connect using SQL*Plus and issue the following statement:

SQL> show parameter resource_manager_plan

You should see output similar to the following:

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan                string      APPQOS_PLAN

Server pools can be managed separately from the database by configuring special operating system groups. By default, the user that installed Oracle Grid Infrastructure for a cluster can perform operations on server pools. If you use a separate operating system for the Oracle Database installation, then execute permissions on a server pool must be granted to the database software owner before a database can be deployed in that server pool. To grant this permission, you must use the CRSCTL utility to modify the server pool ACL attribute. A database administrator can also create server pools using Server Control (SRVCTL) or Oracle Enterprise Manager.

A server pool is marked as unmanageable if Oracle Database QoS Management is not able to properly measure or predict the performance of Performance Classes deployed in that server pool. A server pool can be considered unmanageable under the following conditions:

1. The servers in the server pool have different physical CPU counts.

2. The CPU count for every database instance could not be retrieved.

3. The sum of the configured CPU counts for all database instances on a server is greater than its physical CPU count.

4. Singleton services are deployed in a server pool with a maximum size larger than one.

5. Oracle Database QoS Management is unable to collect the metrics for all Performance Classes, or the metrics collected do not contain valid data.

6. The server pool has a database that is not enabled for Oracle Database QoS Management.

If a server is added to a server pool, then Oracle starts up instances and services on the new server for the policy-managed databases in the server pool. When an instance is started on the new server, Oracle checks the SPFILE of the existing instances for the CPU_COUNT setting, and uses this value for the new instance. By default, if there is no setting for CPU_COUNT in the SPFILE, then the instances on the new server will be started with CPU_COUNT set to the number of physical CPUs of the server. This is not a supported configuration and will result in the server pool being marked as unmanageable. Also, if you modify the CPU_COUNT parameter but do not store the change in the SPFILE, then the CPU_COUNT parameter might be set to the wrong value on the newly started instance resulting in a configuration violation.

Make sure the databases and database instances that you want to be managed by Oracle Database QoS Management conform to the requirements documented in "Supported Database Configurations".

Metrics are captured by Oracle Database QoS Management by querying the managed database instances in the cluster. Metrics are not be displayed under the following circumstances:

1. The database requests (work being done) do not match the classifiers defined for a specific Performance Class and "No demand" is displayed.

2. A more general classifier of a Performance Class is evaluated first. As a result, the Performance Class with the more specific classifier is shown as having "No demand". For example, if the sales_pc Performance Class uses the classifier service=Sales and the sales_search Performance Class uses the classifier (service=Sales and action=Search), then, if the sales_pc Performance Class was listed first in the Edit Policy Set Performance Class Order screen, then any work request that uses the sales service would be placed in the sales_pc Performance Class, including those that are performing the Search action. The sales_search Performance Class does not generate any metrics and "No demand" is displayed for that Performance Class.

3. The database instance is over-utilized and the metrics query reaches the time out limit and "Incomplete data" is displayed.

4. The database is not producing mutually consistent data for the Performance Class and "Nonconforming data" is displayed. For example a Performance Class that is classifying database requests that exceed one second will cause this response.

5. The metrics collected through the query do not pass the sanity verification checks performed by Oracle Database QoS Management and "Nonconforming data" is displayed.

Recommendations are generated once a minute when logged into the Oracle Database QoS Management Dashboard (the Dashboard). Recommendations do not appear on the Dashboard in the following cases:

1. Oracle Database QoS Management is disabled.

2. An action is in progress implementing a recommendation.

3. During the first minute of enabling Oracle Database QoS Management, or when submitting a Policy Set or activating a Policy Set.

Servers are moved within a cluster by Oracle Clusterware, or as directed by an administrator. When a new server joins the cluster, Oracle Clusterware places the server in a server pool according to the placement algorithm and the state of the server pool attributes of Min, Max and Importance. See the Oracle Clusterware Administration and Deployment Guide for a complete description of this placement process.

When you install Oracle Grid Infrastructure for a cluster, the default port for the Oracle Application Server Containers for J2EE (OC4J) resource is set to 23792. This can cause a port conflict with Java Remote Method Invocation (RMI). You should set the OC4J_PORT environment variable in your environment to the number of an available port, then restart the OC4J resource using the following commands:

srvctl modify oc4j -rmiport port
srvctl stop oc4j
srvctl start oc4j