8.1 Configuring Oracle Database QoS Management to Manage Oracle Database Workloads
Before you can use Oracle Database Quality of Service (QoS) Management, you must configure the databases.
When configuring the Oracle RAC databases to work with Oracle Database QoS Management, the server pool configuration tasks are not required for administrator-managed databases.
The initial configuration tasks for the Oracle Database QoS Management administrator are covered in more detail in the following sections.
For Oracle Solaris platforms, there is an additional consideration: About Multi-CPU Binding on Solaris and Quality of Service Management
- Installing and Configuring Oracle Grid Infrastructure for a Cluster
- Creating and Configuring Server Pools
By default, a server pool called the Free pool is created during Oracle Grid Infrastructure installation. To create server pools for your Oracle RAC database, you can use SRVCTL or Oracle Enterprise Manager. - Creating and Configuring an Oracle RAC Database
The steps for creating and configuring an Oracle RAC database are not covered in this book. - Creating Oracle Database QoS Management Administrator Accounts
Before logging in to the Oracle Database QoS Management Dashboard (the Dashboard), you must create an Oracle Database QoS Management administrative user. The operating system user associated with this account must be a cluster administrator user to initially set this up. - Enabling Oracle Database QoS Management
If you have multiple databases running on the same cluster, you can specify which databases are managed by Oracle QoS Management. - About Multi-CPU Binding on Solaris and Quality of Service Management
Using Multi-CPU Binding (MCB) and Oracle Database Quality of Service (QoS) Management together requires close communication between the system administrator and the database administrator (DBA).
8.1.1 Installing and Configuring Oracle Grid Infrastructure for a Cluster
The installation and configuration of Oracle Grid Infrastructure for a cluster is not covered in this book.
Related Topics
8.1.2 Creating and Configuring Server Pools
By default, a server pool called the Free pool is created during Oracle Grid Infrastructure installation. To create server pools for your Oracle RAC database, you can use SRVCTL or Oracle Enterprise Manager.
When you use DBCA to create an Oracle RAC database, Oracle recommends that you select policy-managed for the database, and choose the server pools which the database instances should run in. If you choose the create an administrator-managed Oracle RAC database, then the database runs exclusively in the Generic server pool, which is created during the installation of Oracle Grid Infrastructure.
If you use a cluster administrator that is separate from the database administrator, then only the cluster administrator user can create server pools. The cluster administrator then grants privileges on the server pools to the operating system user that owns the Oracle RAC installation.
Note:
When creating a server pool for use with Oracle Database QoS Management, do not configure the SERVER_NAMES
attribute (the -servers
option of srvctl add svrpool
or srvctl modify svrpool
commands) for the server pool. Full resource management is not supported in such a configuration because Oracle Database QoS Management cannot change server pool sizes. This is the same limitation that exists for resource management of administrator-managed databases.
See Also:
-
Oracle Clusterware Administration and Deployment Guide for information about server pools
-
Oracle Real Application Clusters Administration and Deployment Guide for information about using SRVCTL to create a server pool
-
Oracle Real Application Clusters Installation Guide for Linux and UNIX for information about using DBCA to create an Oracle RAC database
-
Oracle Clusterware Administration and Deployment Guide for more information about the cluster administrator user.
8.1.3 Creating and Configuring an Oracle RAC Database
The steps for creating and configuring an Oracle RAC database are not covered in this book.
When creating a database, Oracle recommends that you choose to create a policy-managed Oracle RAC database and specify the server pools in which it should run. If you create an administrator-managed Oracle RAC database, then it runs exclusively in the Generic server pool.
After you have created the databases, perform the following steps to configure the databases for use with Oracle Database QoS Management:
- Modifying Database Initialization Parameters
TheCPU_COUNT
parameter for each database instance that runs in a server pool must be set to the same value if the database is managed by Oracle Database QoS Management. - Creating Database Services
Applications and users connect to the database using services.
8.1.3.1 Modifying Database Initialization Parameters
The CPU_COUNT
parameter for each database instance that runs in a server pool must be set to the same value if the database is managed by Oracle Database QoS Management.
On each server, the sum of the values for CPU_COUNT
for all database instances running on that server must be less than or equal to the physical CPU count. For example, if you have a server with eight CPUs, and there are two database instances running on this server, then, for the databases to be managed by Oracle Database QoS Management, the CPU_COUNT
parameter for each database instance must be set so that the values of the CPU_COUNT
parameters for all instances on the server add up to eight or less. For example, you could have CPU_COUNT=3
on one instance and CPU_COUNT=4
on the other instance, or CPU_COUNT=6
on one instance and CPU_COUNT=2
on the other instance.
Note:
By default, the CPU count of each database that is started on a server is set to the number of physical CPUs installed for that server.
If you are running more than one database in a server pool, then using the default settings for CPU_COUNT
will cause Oracle Database QoS Management to report a violation. To avoid this error, manually configure the CPU_COUNT
value in the SPFILE using either Oracle Enterprise Manager or SQL*Plus.
8.1.4 Creating Oracle Database QoS Management Administrator Accounts
Before logging in to the Oracle Database QoS Management Dashboard (the Dashboard), you must create an Oracle Database QoS Management administrative user. The operating system user associated with this account must be a cluster administrator user to initially set this up.
The administrative user for the Oracle Database QoS Management server is referred to as the QoS Admin user. This user has access to all the features of the Oracle Database QoS Management server, including checking and changing the account password for the QoS Admin user. You can have multiple QoS Admin users.
8.1.5 Enabling Oracle Database QoS Management
If you have multiple databases running on the same cluster, you can specify which databases are managed by Oracle QoS Management.
You enable Oracle Database QoS Management in a hierarchical manner:
-
Measuring, monitoring, or managing the cluster
-
Measuring, monitoring, or managing individual databases that run on the cluster
To manage a database, all the databases that use the same user-defined server pool must be enabled for Oracle Database QoS Management if:
-
One or more Performance Classes in that user-defined server pool are not marked "Measure-Only" in the active policy
-
There are Performance Classes that include a service hosted by that database
If you do not enable all the databases in the same user-defined server pool for Oracle Database QoS Management and any of the above conditions exist, then a violation is signaled when you try to access the Dashboard for the database. If all of the Performance Classes in the user-defined server pool are in measure–only or monitor mode and none of the Performance Classes specify a hosted service, then there is no violation reported when accessing the Dashboard for the database.
Note:
If you enable Oracle QoS Management to monitor or manage a container database (CDB), then all contained pluggable databases (PDBs) are monitored or managed as well. You cannot configure Oracle QoS Management to monitor or manage individual PDBs.To enable Oracle QoS Management for your system, perform the following steps:
8.1.6 About Multi-CPU Binding on Solaris and Quality of Service Management
Using Multi-CPU Binding (MCB) and Oracle Database Quality of Service (QoS) Management together requires close communication between the system administrator and the database administrator (DBA).
Multi-CPU binding (MCB) is an Oracle Solaris projects resource management functionality that is used to bind a project to a specific set of CPUs, but not bind the CPUs exclusively. MCB allows other processes also to use these CPUs and allows overlapping of partitions. MCB is supported on Oracle Solaris 11.3. Control groups (CGroups) on Linux systems is another system administrator methods of managing server resources by allocating CPU and server resources to specific applications.
MCB has no impact on the use of Oracle Database Quality of Service (QoS) Management when used in measure and monitor mode. When you use Oracle Database Quality of Service (QoS) Management in management mode for a group of servers, there are four resource controls that Oracle QoS Management currently supports:
-
Consumer Group Mappings: CPU shares between competing workloads within a Non-CDB or PDB.
-
Container Database (CDB) Resource Plans: CPU Shares between competing PDBs within a CDB
-
Instance Caging: CPUs/Threads between co-hosted database instances
-
Server Pool Cardinality: number of servers in a server pool offering the database
MCB becomes a problem with regards to instance caging because it is possible for Oracle Database Quality of Service (QoS) Management to recommend a change in CPU_COUNT
that would not be honored by the operating system. If the recommended action is implemented in this situation, there would probably still be some improvement to the target workload because the donor database would lose a CPU. This would cause Resource Manager to not schedule as many parallel sessions which would help out when hard partitioning is not used. However, the projected performance improvement would be overstated.