Resource Manager
=============
Resource Manager:-
One key to a healthy database is maintaining a healthy CPU load. Excessive CPU load
can destabilize the server and expose operating system bugs. Excessive CPU load can
also prevent critical Oracle background processes from running in a timely manner,
resulting in failures such as database instance evictions on a RAC database. At such
times, your response time may be so poor that you may be unable to debug the source of
the excessive CPU load, for example by identifying and fixing a faulty SQL plan.
Using Oracle Database Resource Manager, you can ensure that your database’s CPU
load is always healthy, thus avoiding all of these problems. Resource Manager manages
your CPU load so that all CPUs are fully utilized yet there is no thrashing since no (or
very few) Oracle processes are waiting in the operating system run queue. Using a
database feature called Instance Caging, you can configure Resource Manager to
manage the CPU load to even lower levels.
You can also use Resource Manager to explicitly allocate CPU resources to the multiple
workloads or applications that are sharing the database. For example, you can prioritize
“sys” over regular users and OLTP transactions over reports and batch jobs. Or you can
configure CPU allocations and limits for each application in a consolidated database.
Scenarios for Resource Manager:-
--------------------------------
Resource Manager helps solve database problems associated with high CPU loads. The first set
of database problems occur when the overall database load is too high:
High database loads starve critical background processes. For example, if LMS is
starved, its database instance can be evicted from the RAC cluster group. Or if PMON
is starved, locks associated with dead processes won’t be quickly freed, leading to hangs.
High database loads destabilize the server. Many operating system vulnerabilities and
bugs are exposed at excessive CPU loads. In addition, excessive CPU loads can starve
critical applications and monitoring tools running on the server.
At high database loads, the DBA may be unable to diagnose and fix the problem.
Suppose the high database load is caused by a problem, such as a bad SQL plan or a bad
parallel hint, leading to an excessive number of parallel queries. The DBA’s response
time may be so poor that he is unable to login and debug.
If the database instance is sharing the server with other database instances or
applications, its high database load may impact their performance and stability.
In all of these cases, the database’s load can be managed by Resource Manager, simply by
enabling an out-of-the-box default plan
Resource Manager can manage multiple facets of session behavior on a per workload basis, such
as idle time limits, runaway queries, degree-of-parallelism, etc.
How it works:-
--------------
Resource Manager manages CPU usage by controlling the database load to a very precise level.
By default, this level is set to the number of CPUs. That means that on a 4 CPU server,
Resource Manager ensures that no more than 4 Oracle processes (more specifically, foregrounds
and non-critical, CPU-intensive backgrounds) are running at a time. By controlling the database
load, critical backgrounds are able to run in a timely manner and the load on the server is
regulated.
Resource Manager works much like the operating system scheduler. Resource Manager allows
one Oracle process per CPU to run at a given time. All other processes wait on an internal
Resource Manager run queue, under the wait event “resmgr:cpu quantum”. Resource Manager
allows an Oracle process to run for a small quantum of time (100 milliseconds). At the end of
this quantum or when the Oracle process starts a wait (e.g. for a lock or I/O), Resource Manager
selects a new Oracle process to run. Because Resource Manager uses a round-robin algorithm to
choose between all runnable processes, they all make forward progress.
Managing Multiple workloads:-
-----------------------------
In a very basic configuration, Resource Manager treats all sessions alike. However, you can
configure Resource Manager to manage workloads differently by configuring consumer groups
and resource plans
A consumer group is a collection of sessions that are managed as a unit. You can define
consumer groups for each application in your database. Or you can define consumer groups for
each type of workload, e.g. OLTP, reports, maintenance, etc.
Sessions can be automatically mapped to a consumer group by defining consumer group
mapping rules. For example, a session from the “OLTP” service can be mapped to the
“interactive” consumer group. Or a session with the username “sales” can be mapped to the
“sales_app” consumer group. The attributes that can be used in consumer group mapping rules
include the session’s service, module, action, Oracle username, client username, and program
name.
A resource plan specifies how the CPU should be shared among the consumer groups. It
allocates percentages of the CPU to consumer groups and specifies how unused CPU is
redistributed. A resource plan contains a resource plan directive for each consumer group
that specifies its CPU allocation.
Default Plan:-
--------------
Configuring Resource Manager to manage the database load requires just one step: setting the
resource_manager_plan parameter to “DEFAULT_PLAN”. (To persist this change, you must
also update your init.ora or SPFILE.) For example,
SQL> alter system set resource_manager_plan = ‘DEFAULT_PLAN‘ sid = ‘*‘;
By enabling Resource Manager with the out-of-box “DEFAULT_PLAN”, you will get the
following benefits:
Critical background processes, such as PMON and LMS, won’t be starved for CPU, due
to excessive load from foreground processes.
SYSTEM and SYS are scheduled at the highest priority. Their response time will not be
affected by runaway CPU activity from normal users, allowing them to always be able to
login and debug database problems.
Automated maintenance tasks are scheduled at the lowest priority2. These tasks include
gathering optimizer statistics and running the automatic segment and sql tuning
advisors. These tasks will therefore not compete with other sessions for CPU.
However, when the database workload is low, these maintenance tasks will be scheduled
to consume any remaining, otherwise unused CPU resources.
Instance Caging3
Resource Manager manages the database load so that all CPUs are utilized. In some
circumstances, you might want a database instance to use less than 100% of the server’s CPU:
Other applications or database instances are sharing the server. You want the database
instance to use only a portion of the server’s CPUs.
Your consolidated server is hosting multiple database instances. You want to limit a
database instance’s CPU usage so the customer sees more consistent response times.
Or you want to limit its CPU usage based on how much the customer paid.
Your server is more stable at lower CPU utilization levels. You want the database
instance’s load to stay at these lower levels.
In these cases, you can lower the database load by setting the cpu_count parameter to the
number of CPUs you want Resource Manager to use. For example, on an 8 CPU server, if you
want Resource Manager to restrict the database load to no more than 6 active Oracle processes
(and hence use no more than 6 CPUs), you would enable Resource Manager and set the
cpu_count parameter to 6.
Conguring Resource manager for multiple workloads:-
---------------------------------------------------
1. Obtain permission to administer Resource Manager
2. Create a pending area
3. Create consumer groups
4. Map sessions to consumer groups
5. Add permissions for consumer groups
6. Create a resource plan
7. Add resource plan directives
8. Submit the pending area
9. Enable the resource plan
Eg:-
In this example we will define two consumer groups named "sales_data" & "sales_report".
We can say as the session which are part of consumer group sales_data will deal with the
transaction related to sales and the consumer group sales_report will deal with the
sales report generation. Hence we will two services in the db and the consumer will
connect to their service accordingly.
a. Create two services.
SQL> show parameter service;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
service_names string layatest
SQL> alter system set service_names = 'layatest','sales_data','sales_rep' scope=both sid='*';
System altered.
SQL> show parameter service;
NAME TYPE VALUE
------------------------------------ ----------- -------------------------------
service_names string layatest, sales_data, sales_rep
b. Update the tnsnames.ora file and check whether the listeners are listening to the services
layatest1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = rhel11gr2rac1-vip.manzoor.com) (PORT = 20000)))
(CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME=layatest) (INSTANCE_NAME = layatest1)
)
)
layatest2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = rhel11gr2rac2-vip.manzoor.com) (PORT = 20000)))
(CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME=layatest) (INSTANCE_NAME = layatest2)
)
)
listener_layatest1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = rhel11gr2rac1-vip.manzoor.com) (PORT = 20000)))
(CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME=layatest) (INSTANCE_NAME = layatest1)
)
)
listener_layatest2 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = rhel11gr2rac2-vip.manzoor.com) (PORT = 20000)))
(CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME=layatest) (INSTANCE_NAME = layatest2)
)
)
layatest =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = urac1rac2-scan.manzoor.com) (PORT = 25000)))
(CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME=layatest)
)
)
sales_data =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = urac1rac2-scan.manzoor.com) (PORT = 25000)))
(CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME=sales_data)
)
)
sales_rep =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP) (HOST = urac1rac2-scan.manzoor.com) (PORT = 25000)))
(CONNECT_DATA =
(SERVER = DEDICATED) (SERVICE_NAME=sales_rep)
)
)
$ lsnrctl status lsnrlayatest
LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 13-DEC-2012 18:35:48
Copyright (c) 1991, 2011, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LSNRLAYATEST)))
STATUS of the LISTENER
------------------------
Alias LSNRLAYATEST
Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production
Start Date 13-DEC-2012 17:32:30
Uptime 0 days 1 hr. 3 min. 18 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /grid/11.2/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/rhel11gr2rac1/lsnrlayatest/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LSNRLAYATEST)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.20)(PORT=20000)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.25)(PORT=20000)))
Services Summary...
Service "layatest" has 1 instance(s).
Instance "layatest1", status READY, has 1 handler(s) for this service...
Service "sales_data" has 1 instance(s).
Instance "layatest1", status READY, has 1 handler(s) for this service...
Service "sales_rep" has 1 instance(s).
Instance "layatest1", status READY, has 1 handler(s) for this service...
The command completed successfully
$ [oracle@rhel11gr2rac1 admin]$ tnsping sales_data
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 13-DEC-2012 18:36:28
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = urac1rac2-scan.manzoor.com) (PORT = 25000))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME=sales_data)))
OK (0 msec)
[oracle@rhel11gr2rac1 admin]$ tnsping sales_rep
TNS Ping Utility for Linux: Version 11.2.0.3.0 - Production on 13-DEC-2012 18:36:31
Copyright (c) 1997, 2011, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP) (HOST = urac1rac2-scan.manzoor.com) (PORT = 25000))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME=sales_rep)))
OK (0 msec)
Now we have configured the netservice accordinlgy.
1a) Obtain permission to manage resource manager.
Administering Resource Manager requires the “ADMINISTER_RESOURCE_MANAGER”
privilege. This privilege is automatically granted to “SYS”. The following PL/SQL command
grants this privilege to the user "Manzoor".
SQL> exec dbms_resource_manager_privs.grant_system_privilege(grantee_name => 'MANZOOR', admin_option => true);
PL/SQL procedure successfully completed.
2a) Create Pending Area:-
Creating a pending area is the first step for defining or modifying consumer groups and resource
plans. The pending area is a temporary work area for Resource Management configuration. The
changes in the pending area are not visible until the pending area is submitted (see step 8).
The pending area is created as follows:
SQL> exec dbms_resource_manager.create_pending_area;
PL/SQL procedure successfully completed.
At any time, you can abandon the changes in the pending area as follows:
SQL> exec dbms_resource_manager.clear_pending_area();
3a) Create Consumer groups:-
As for this example we will create two consumer groups sales_data & sales_report, also
the oracle database comes with preconfigured consumer groups, before creating a new one
we should consider if any of the preconfigured consuer groups will meet our requirement.
Preconfigured consumer groups are:-
Consumer Group Name Description
INTERACTIVE_GROUP Consumer group for interactive, OLTP operations.
BATCH_GROUP Consumer group for batch operations.
DSS_GROUP Consumer group for decision support system (DSS) queries.
DSS_CRITICAL_GROUP Consumer group for critical DSS queries.
ETL_GROUP Consumer group for data load, or ETL jobs.
SYS_GROUP Consumer group for system administrators.
LOW_GROUP Consumer group for low-priority sessions.
OTHER_GROUPS Default consumer group for all sessions.
if the pre-configured workloads are not suitable then we can create the new consumer groups.
SQL> exec dbms_resource_manager.create_consumer_group('SALES_DATA','For Sales transaction');
PL/SQL procedure successfully completed.
SQL> exec dbms_resource_manager.create_consumer_group('SALES_REPORT','For Sales reports');
PL/SQL procedure successfully completed.
4a) Map sessions to consumer groups.
Now we need to map the session to connect to the concerned consumer groups.
Mapping rules are used to automatically place sessions into a consumer group. For example, the
following rule maps sessions in the "SALES_DATA" service to the “SALES_DATA” consumer
group.
SQL> exec dbms_resource_manager.set_consumer_group_mapping
(attribute => dbms_resource_manager.service_name,
value => 'sales_data',
consumer_group => 'sales_data'
);
PL/SQL procedure successfully completed.
SQL> exec dbms_resource_manager.set_consumer_group_mapping
(attribute => dbms_resource_manager.service_name,
value=> 'sales_rep',
consumer_group=> 'sales_report'
);
PL/SQL procedure successfully completed.
Now we have mapped the services to the concerned consumer groups. Any session which uses the
sales_data service to connect to the db those will be under the sales_data consumer group and
the sales_rep service will connect the session to the sales_report consumer groups.
There are also different attribute are these to map the session to a consumer groups, below
are they.
Session Attribute Description
service_module_action A combination of service name, module name, and action name,
in this format:
service_name.module_name.action_name
service_module A combination of service and module names in this format:
service_name.module_name
module_name_action A combination of module and action names in this format:
module_name.action_name
module_name The module name in the currently running application.
service_name The service name used by the client to establish a connection.
oracle_user The Oracle database user name.
client_program The name of the client program used to log onto the server.
client_os_user The operating system user name of the client.
client_machine The name of the computer from which the client is making the
connection.
client_id The client identifier.
Sessions can be explicitly mapped to a consumer group using
dbms_session.switch_consumer_group().
Sessions can also be configured to automatically switch from one consumer group to another,
based on the amount of CPU or I/O consumed or the expected execution time, using the
switch_time, switch_io_megabytes, switch_io_reqs, or switch_estimate directives.
5a) Add permission for consumer groups:-
In order to switch into a consumer group, a user or role must have permission. The following
PL/SQL command allows the user "manzoor" to switch into consumer group
“INTERACTIVE_GROUP”.
SQL> exec dbms_resource_manager_privs.grant_switch_consumer_group
(grantee_name => 'MANZOOR',
consumer_group => 'INTERACTIVE_GROUP',
GRANT_OPTION => false
);
PL/SQL procedure successfully completed.
The following PL/SQL command allows any user to switch into the “BATCH_GROUP”
consumer group.
SQL> exec dbms_resource_manager_privs.grant_switch_consumer_group
(grantee_name => 'public',
consumer_group => 'BATCH_GROUP',
grant_option => FALSE
);
PL/SQL procedure successfully completed.
6a) Create Resource Plan:-
We have created two consumer group which are sales_data & sales_report, let say we required
sales_data consumer will consume 30% of the cpu and sales_rep will consume only 10%.
Also the oracle database comes with differnet resource plan, we should consider these
resource plan before creatig a new plan.
They are:-
Consumer Group Name Description
DEFAULT_PLAN A general purpose plan that prioritizes SYS to allow
the DBA to debug hangs and unexpected heavy loads.
DEFAULT_MAINTENANCE_PLAN Plan for the maintenance windows. Provides some
CPU resources for automated maintenance tasks.
MIXED_WORKLOAD_PLAN Plan for managing mixed workloads (interactive and
batch).
DSS_PLAN Plan for managing DSS workloads (DSS, ETL, and
batch).
ETL_CRITICAL_PLAN Similar to DSS_PLAN but prioritizes ETL over DSS.
You can see the consumer groups that are managed by a resource plan (in this example,
MIXED_WORKLOAD_PLAN):
SQL> select group_or_subplan from dba_rsrc_plan_directives
where plan = 'MIXED_WORKLOAD_PLAN';
GROUP_OR_SUBPLAN
------------------------------
ORA$DIAGNOSTICS
SYS_GROUP
OTHER_GROUPS
INTERACTIVE_GROUP
BATCH_GROUP
ORA$AUTOTASK_SUB_PLAN
ORA$DIAGNOSTICS
SYS_GROUP
OTHER_GROUPS
INTERACTIVE_GROUP
BATCH_GROUP
ORA$AUTOTASK_SUB_PLAN
12 rows selected.
If you want to start with a new resource plan, then you can use the following PL/SQL
command. This example creates a resource plan called “SALES_PLAN”.
SQL> exec dbms_resource_manager.create_plan(plan => 'SALES_PLAN',comment => 'For Managing Sales');
PL/SQL procedure successfully completed.
7a) Add resource plan directives.
We have created the plan, now we need add the directives for this resource plan.
A resource plan directive specifies how much CPU should be allocated to the consumer group.
You can create a new directive by calling dbms_resource_manager.create_plan_directive(). You
can update or delete a directive by calling update_plan_directive or delete_plan_directive().
The parameter, mgmt_p1, specifies the percentage of the CPU to allocate for the consumer
group. For example, if you have 4 applications, you may want to allocate the CPU as follows.
Note that you are required to provide a directive for OTHER_GROUPS, the default consumer
group.
Consumer Group mgmt_p1
APP_1 40%
APP_2 25%
APP_3 25%
APP_4 5%
OTHER_GROUPS 5%
As we said we need 30% of cpu for sales_data consumers and 10% for sales_report consumer.
Hence our requirement of mgmt_p1 will be as follows.
SYS_GROUP 50%
SALES_DATA 30%
SALES_REP 10%
OTHER_GROUPS 10%
SQL> exec dbms_resource_manager.create_plan_directive(PLAN => 'SALES_PLAN',GROUP_OR_SUBPLAN => 'SYS_GROUP', COMMENT => 'CPU for SYS users',MGMT_P1=> 50);
PL/SQL procedure successfully completed.
SQL> exec dbms_resource_manager.create_plan_directive(PLAN => 'SALES_PLAN',GROUP_OR_SUBPLAN => 'SALES_DATA', COMMENT => 'CPU for SALES TRANS users',MGMT_P1=> 30);
PL/SQL procedure successfully completed.
SQL> exec dbms_resource_manager.create_plan_directive(PLAN => 'SALES_PLAN',GROUP_OR_SUBPLAN => 'SALES_REPORT', COMMENT => 'CPU for Sales rep users',MGMT_P1=> 10);
PL/SQL procedure successfully completed.
SQL> exec dbms_resource_manager.create_plan_directive(PLAN => 'SALES_PLAN',GROUP_OR_SUBPLAN => 'OTHER_GROUPS', COMMENT => 'CPU for other users',MGMT_P1=> 10);
PL/SQL procedure successfully completed.
At any moment, if any CPU is not being used by one or more consumer groups, then Resource
Manager redistributes it to the consumer groups that need it. In the example above, if APP_1 is
the only consumer group running, then Resource Manager will give it 100% of the CPU.
Therefore, mgmt_p1 specifies the amount of CPU that the consumer group is guaranteed to get.
The maximum amount of CPU that the consumer group can consume is 100%.
If you are charging the application owners for their CPU allotment and don’t want them to
consume more than they paid for, you may want to limit their CPU utilization. You can do this
by using the max_utilization_limit directive4. In the example below, each consumer group is
allowed to exceed their allocation by 5%:
Consumer Group mgmt_p1 max_utilization_limit
APP_1 40% 45%
APP_2 25% 30%
APP_3 25% 30%
APP_4 5% 10%
OTHER_GROUPS 5%
SQL> exec dbms_resource_manager.create_plan_directive(PLAN => 'SALES_PLAN',GROUP_OR_SUBPLAN => 'SALES_DATA', COMMENT => 'CPU for SALES TRANS users',
MGMT_P1=> 30,MAX_UTILIZATION_LIMIT => 35);
In some cases, you may want to allocate CPU resources to a consumer group only if higher
priority consumer groups do not use their allocation. Plan levels allow you to specify how
unused CPU is reallocated. Unused or unallocated CPU from level 1 is used by consumer
groups at level 2, using the mgmt_p2 directive. Unused or unallocated CPU from level 2 is used
by consumer groups at level 3, using the mgmt_p3 directive. And so on.
For example, suppose you have a batch consumer group and you want it to consume 5% of the
CPU, plus any CPU not consumed by the other applications. Such a plan would look like:
Consumer Group mgmt_p1 mgmt_p2
APP_1 35%
APP_2 25%
APP_3 25%
APP_4 5%
BATCH_GROUP 5% 100%
OTHER_GROUPS 5%
8a) Submit the pending area:-
Once you have configured the resource plan, you can use the following PL/SQL to persist your
changes in the database. If you are editing a resource plan that is currently active, your changes
will be immediately enabled.
SQL> exec dbms_resource_manager.submit_pending_area();
PL/SQL procedure successfully completed.
If your plan has any errors, this command will fail with an error that describes the problem.
9a) Enable the resource plan.
Now that your resource plan is defined, you can enable it by setting the resource_manager_plan
parameter with your resource plan name.
SQL> alter system set resource_manager_plan = 'SALES_PLAN' scope=both sid='*';
System altered.
SQL> Show parameter resource_manager_plan;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan string SALES_PLAN
You can also enable a resource plan for the period of time corresponding to a job scheduler
window. In this example, the DB_CONSOLIDATION_PLAN is enabled when
MONDAY_WINDOW opens and disabled when MONDAY_WINDOW closes.
SQL> exec dbms_scheduler.set_attribute('MONDAY_WINDOW', 'RESOURCE_PLAN',
'DB_CONSOLIDATION_PLAN');
Monitoring Resource manager:-
a) To determine if Resource Manager is currently enabled:
SQL> select name, cpu_managed from v$rsrc_plan where is_top_plan = 'TRUE';
NAME CPU
-------------------------------- ---
SALES_PLAN ON
b) When Was Resource Manager Enabled?
SQL> select name,
to_char(start_time, 'MON DD HH24:MI') start_time,
to_char(end_time, 'MON DD HH24:MI') end_time,
window_name
from v$rsrc_plan_history order by start_time;
NAME START_TIME END_TIME WINDOW_NAME
------------------------------ ------------ ------------ ------------------------------
DEC 13 17:38 DEC 13 19:52
SALES_PLAN DEC 13 19:52
This query returns the name of the plan, the time that it was enabled and disabled, and the
scheduler window that was used to enable it (if any). Each row corresponds to an occurrence of
Resource Manager being enabled or disabled. A history of up to 16 occurrences is maintained.
c) How Was Resource Manager Enabled?
In the query above, the window_name indicates the scheduler window that automatically enabled
the resource plan. If the window_name is NULL, then the resource plan was enabled using the
“resource_manager_plan” parameter.
To determine the scheduler windows that have associated resource plans:
SQL> select window_name, resource_plan, active
from dba_scheduler_windows
where resource_plan is not null and enabled = 'TRUE';
WINDOW_NAME RESOURCE_PLAN ACTIV
------------------------------ ------------------------------ -----
MONDAY_WINDOW DEFAULT_MAINTENANCE_PLAN FALSE
TUESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN FALSE
WEDNESDAY_WINDOW DEFAULT_MAINTENANCE_PLAN FALSE
THURSDAY_WINDOW DEFAULT_MAINTENANCE_PLAN FALSE
FRIDAY_WINDOW DEFAULT_MAINTENANCE_PLAN FALSE
SATURDAY_WINDOW DEFAULT_MAINTENANCE_PLAN FALSE
SUNDAY_WINDOW DEFAULT_MAINTENANCE_PLAN FALSE
7 rows selected.
If “active” is TRUE for a scheduler window, then that scheduler window is currently open and
its resource plan is enabled.
d) Mapping Sessions to Consumer Groups
Sessions can be automatically mapped to a consumer group, using the
dbms_resource_manager.set_consumer_group_mapping() procedure. Sessions can also be
manually switched into a consumer group, using the dbms_session.switch_consumer_group()
procedure.
This section describes how to tell if a session is mapped to the right consumer group.
Which Consumer Group Is a Session In?
This query returns the current consumer group for all sessions.
[oracle@rhel11gr2rac1 ~]$ sqlplus manzoor/manzoor@sales_data
SQL*Plus: Release 11.2.0.3.0 Production on Thu Dec 13 20:09:40 2012
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
SQL>
Now check the consumer group in which the manzoor's session is mapped.
SQL> SELECT INST_ID, USERNAME, sid, resource_consumer_group from gv$session where username is not null;
INST_ID USERNAME SID RESOURCE_CONSUMER_GROUP
---------- ------------------------------ ---------- --------------------------------
1 SYS 32 SYS_GROUP
1 SYS 34 SYS_GROUP
1 SYS 36 SYS_GROUP
1 SYS 37 SYS_GROUP
1 SYS 52 SYS_GROUP
2 SYS 1 SYS_GROUP
2 SYS 46 SYS_GROUP
2 SYS 48 SYS_GROUP
2 MANZOOR 55 OTHER_GROUPS
The manzoor's session should map to sales_data but here it has been mapped to others_groups
why?
A session may not be in the expected consumer group for several reasons
Missing Privilege
In order for a session to switch into a consumer group, its user or role must have permission to
switch into that consumer group. If a session is mapped to a consumer group but does not have
permission to switch into it, then that mapping rule is ignored.
The query below shows the permissions for all consumer groups. To verify permissions for a
specific user (i.e. the grantee), specify a where clause.
SQL> select grantee, granted_group from DBA_RSRC_CONSUMER_GROUP_PRIVS
order by granted_group;
GRANTEE GRANTED_GROUP
------------------------------ ------------------------------
PUBLIC DEFAULT_CONSUMER_GROUP
MANZOOR INTERACTIVE_GROUP
PUBLIC LOW_GROUP
SYSTEM SYS_GROUP
Lets grant the privilege to manzoor.
SQL> exec dbms_resource_manager_privs.grant_switch_consumer_group(GRANTEE_NAME=> 'MANZOOR', consumer_group => 'SALES_DATA',grant_option => false);
PL/SQL procedure successfully completed.
Now exit and reconnect the manzoor's session and check.
SQL> SELECT INST_ID, USERNAME, sid, resource_consumer_group from gv$session where username is not null;
INST_ID USERNAME SID RESOURCE_CONSUMER_GROUP
---------- ------------------------------ ---------- --------------------------------
1 SYS 32 SYS_GROUP
1 SYS 36 SYS_GROUP
1 SYS 37 SYS_GROUP
1 SYS 50 SYS_GROUP
1 SYS 52 SYS_GROUP
2 SYS 1 SYS_GROUP
2 SYS 46 SYS_GROUP
2 SYS 53 SYS_GROUP
2 MANZOOR 55 SALES_DATA
9 rows selected.
Inactive Consumer Group
If a session maps to or is manually switched to a consumer group that is not part of the current
resource plan, then the session will be switched into the default consumer group,
“OTHER_GROUPS”.
If sessions are being assigned to consumer groups using mapping rules, this query can be used to
determine the consumer group that the mapping rules selected, the mapping attribute that was
used, and the consumer group that the session originally started in. If the mapped consumer
group differs from the original consumer group, then the mapped consumer group was not part
of the resource plan.
SQL> select r.sid,
r.mapped_consumer_group,
r.mapping_attribute,
c.consumer_group original_consumer_group
from Gv$rsrc_session_info r, dba_rsrc_consumer_groups c
where r.orig_consumer_group_id = c.consumer_group_id;
SID MAPPED_CONSUMER_GROUP MAPPING_ATTRIBUTE ORIGINAL_CONSUMER_GROUP
---------- -------------------------------- -------------------------------- ------------------------------
32 SYS_GROUP ORACLE_USER SYS_GROUP
36 SYS_GROUP ORACLE_USER SYS_GROUP
37 SYS_GROUP ORACLE_USER SYS_GROUP
50 SYS_GROUP ORACLE_USER SYS_GROUP
52 SYS_GROUP ORACLE_USER SYS_GROUP
1 SYS_GROUP ORACLE_USER SYS_GROUP
46 SYS_GROUP ORACLE_USER SYS_GROUP
53 SYS_GROUP ORACLE_USER SYS_GROUP
55 SALES_DATA SERVICE_NAME SALES_DATA
Consumer Group Switch
If a session’s current consumer group does not match its original consumer group, then the
session has switched consumer groups. This query detects sessions that have been automatically
switched:
SQL> select r.sid,
c1.consumer_group original_consumer_group,
c2.consumer_group current_consumer_group
from Gv$rsrc_session_info r, dba_rsrc_consumer_groups c1,
dba_rsrc_consumer_groups c2
where r.orig_consumer_group_id = c1.consumer_group_id
and r.current_consumer_group_id = c2.consumer_group_id
and r.orig_consumer_group_id != r.current_consumer_group_id;
no rows selected
A session can switch consumer groups for several reasons:
1. The original consumer group is not part of the current resource plan. If this is the case,
the current consumer group will be “OTHER_GROUPS”.
2. The session can be automatically switched by a resource plan that includes autoswitching
directives: “switch_time”, “switch_io_megabytes”, or “switch_io_reqs”.
This query shows if the current resource plan has any auto-switching directives:
select plan, group_or_subplan, switch_time, switch_io_megabytes, switch_io_reqs
from dba_rsrc_plan_directives
where plan in (select name from v$rsrc_plan)
and (switch_time is not null
or switch_io_megabytes is not null
or switch_io_reqs is not null);
Manual Consumer Group Switch
If a session is manually switched into a consumer group, using the
dbms_session.switch_consumer_group(),
dbms_resource_manager.switch_consumer_group_for_user(), or
dbms_resource_manager.switch_consumer_group_for_sess() procedures, the consumer group
switch does not happen immediately. It occurs when the session executes the next call.
Monitoring CPU Management
This section describes how to monitor the Resource Manager for CPU management. The first
subsection describes how to monitor the effect of Resource Manager on the database instance as
a whole. The next subsection describes how to monitor the effect of Resource Manager on each
consumer group.
How Much Is Resource Manager Throttling the Database Instance?
Oracle provides many different views for monitoring CPU Resource Management.
Amount of Throttling By Wait Event
Throttling by Resource Manager can be monitored by the wait event “resmgr:cpu quantum”.
These waits can be seen by generating an AWR report and examining the “Foreground Wait
Events”.
Resource Manager can also be monitored by the “scheduler” wait class, which includes both
throttling waits and Resource Manager-related concurrency waits. The vast majority of the
“scheduler” waits should be for throttling.
This query monitors the waits in the “scheduler” wait class. “dbtime_in_wait” shows the
percentage of database time spent in Resource Manager waits. “time_waited” shows the actual
wait time in microseconds.
SQL> select to_char(h.begin_time, 'HH:MI') time,
h.average_waiter_count, h.dbtime_in_wait, h.time_waited
from v$waitclassmetric_history h, v$system_wait_class c
where h.wait_class_id = c.wait_class_id and c.wait_class = 'SCHEDULER'
order by h.begin_time;
When a database instance has a workload that far exceeds the CPU capacity of the server,
“resmgr:cpu quantum” may show up as one of the top wait events. Although Resource Manager
will be heavily throttling the Oracle processes, it is important to note that the overall throughput
of the database instance remains the same as when Resource Manager is disabled. Without
Resource Manager, the time spent in “resmgr:cpu quantum” will be spent instead as waits on the
operating system run queue. In an AWR report, the only indication of high waits on the run
queue is from the server load numbers.
Amount of Throttling Per Minute
The amount of throttling can also be monitored using Resource Manager views6. The following
query provides a minute-by-minute comparison of
total: the amount of CPU time available on the server
db_total: the amount of CPU time available for this database instance (it will be lower if
Instance Caging is enabled)
consumed: the amount of CPU time consumed
throttled: the amount of time processes were throttled
In all cases, time is expressed in seconds.
SQL> select to_char(begin_time, 'HH:MI') time,
60 * (select value from v$osstat where stat_name = 'NUM_CPUS') total,
60 * (select value from v$parameter where name = 'cpu_count') db_total,
sum(cpu_consumed_time) / 1000 consumed,
sum(cpu_wait_time) / 1000 throttled
from gv$rsrcmgrmetric_history
group by begin_time order by begin_time;
If the consumed CPU is small relative to the total CPU, then the server is not heavily utilized by
this database instance. If “throttled” is large relative to the database’s total CPU, the database is
CPU-bound.
Average Number of Sessions Throttled Per Minute
The amount of throttling can also be quantified as the average number of sessions being
throttled. The following query provides a minute-by-minute comparison of
num_cpus: the number of CPUs
num_db_cpus: the number of CPUs available for this database instance (it will be lower
if Instance Caging is enabled)
avg_running: the average number of running Oracle sessions
avg_throttled: the average number of throttled Oracle sessions
SQL>col num_db_cpus form a10;
SQL> select to_char(begin_time, 'HH:MI') time,
(select value from v$osstat where stat_name = 'NUM_CPUS') num_cpus,
(select value from v$parameter where name = 'cpu_count') num_db_cpus,
sum(cpu_consumed_time) / 60000 avg_running,
sum(cpu_wait_time) / 60000 avg_throttled
from v$rsrcmgrmetric_history
group by begin_time order by begin_time;
TIME NUM_CPUS NUM_DB_CPU AVG_RUNNING AVG_THROTTLED
----- ---------- ---------- ----------- -------------
11:37 1 1 .048033333 0
11:38 1 1 .001033333 0
11:39 1 1 0 0
11:40 1 1 0 0
11:41 1 1 .001433333 0
11:42 1 1 0 0
11:43 1 1 0 0
11:44 1 1 0 0
11:45 1 1 .0002 0
11:46 1 1 .000616667 0
11:47 1 1 0 0
11:48 1 1 0 0
11:49 1 1 .00375 0
11:50 1 1 .0055 0
14 rows selected.
If the average number of throttled sessions is equal to the number of CPUs, then on average,
each CPU had one session waiting to run. This would be an example of an overloaded database
instance and server.
What Are the Next Steps If Resource Manager Is Heavily Throttling?
If Resource Manager is throttling significantly, then the database instance is CPU-bound. If the
performance of a throttled workload is inadequate, then the resource plan should be tuned to
give a higher CPU allocation to that consumer group. This tuning process is covered in the next
sub-section. If that consumer group has already been allocated all or most of the CPU, then the
only recourse is to add CPU (assuming, of course, that the application and database have been
tuned).
How Much Is Resource Manager Throttling the Consumer Groups?
Once a resource plan has been enabled, the performance of the consumer groups should be
monitored and the CPU allocations should be adjusted as necessary.
The following query provides a minute-by-minute comparison for each consumer group of
total: the amount of CPU time available on the server
db_total: the amount of CPU time available for this database instance (it will be lower if
Instance Caging is enabled)
consumed: the amount of CPU time this consumer group consumed
cpu_utilization: the percentage of the CPU available to this database consumed by this
consumer group
throttled: the amount of time this consumer group was throttled
In all cases, time is expressed in seconds.
SQL> select to_char(begin_time, 'HH:MI') time,
consumer_group_name,
60 * (select value from v$osstat where stat_name = 'NUM_CPUS') total,
60 * (select value from v$parameter where name = 'cpu_count') db_total,
cpu_consumed_time / 1000 consumed,
cpu_consumed_time / (select value from v$parameter where name =
'cpu_count') / 600 cpu_utilization,
cpu_wait_time / 1000 throttled
from v$rsrcmgrmetric_history where consumer_group_name in ('SALES_DATA','SALES_REPORT','SYS_GROUP')
order by begin_time;
TIME CONSUMER_GROUP_NAME TOTAL DB_TOTAL CONSUMED CPU_UTILIZATION THROTTLED
----- ------------------------------ ---------- ---------- ---------- --------------- ----------
11:45 SALES_DATA 60 60 0 0 0
11:45 SALES_REPORT 60 60 0 0 0
11:46 SALES_REPORT 60 60 0 0 0
11:46 SYS_GROUP 60 60 .037 .061666667 0
11:46 SALES_DATA 60 60 0 0 0
11:47 SALES_DATA 60 60 0 0 0
11:47 SYS_GROUP 60 60 0 0 0
11:47 SALES_REPORT 60 60 0 0 0
11:48 SYS_GROUP 60 60 0 0 0
11:48 SALES_REPORT 60 60 0 0 0
11:48 SALES_DATA 60 60 0 0 0
11:49 SYS_GROUP 60 60 0 0 0
11:49 SALES_REPORT 60 60 0 0 0
11:49 SALES_DATA 60 60 .225 .375 0
11:50 SYS_GROUP 60 60 0 0 0
11:50 SALES_REPORT 60 60 .039 .065 0
11:50 SALES_DATA 60 60 .291 .485 0
11:51 SALES_DATA 60 60 0 0 0
11:51 SALES_REPORT 60 60 0 0 0
11:51 SYS_GROUP 60 60 .048 .08 0
11:52 SALES_DATA 60 60 0 0 0
11:52 SALES_REPORT 60 60 0 0 0
11:52 SYS_GROUP 60 60 0 0 0
11:53 SYS_GROUP 60 60 0 0 0
11:53 SALES_DATA 60 60 0 0 0
11:53 SALES_REPORT 60 60 0 0 0
11:54 SALES_DATA 60 60 0 0 0
11:54 SALES_REPORT 60 60 0 0 0
11:54 SYS_GROUP 60 60 .045 .075 0
11:55 SYS_GROUP 60 60 .038 .063333333 0
11:55 SALES_DATA 60 60 0 0 0
11:55 SALES_REPORT 60 60 0 0 0
Why Doesn’t the Consumer Group’s CPU Utilization Match the Resource Plan?
The query above shows the actual CPU utilization per consumer group. When monitoring
Resource Manager, it is common practice to compare this with the allocation in the resource plan
(i.e. mgmt_p1, mgmt_p2, mgmt_p3, etc.).
If all of the consumer groups have enough load to fully utilize their plan allocations, then the
CPU utilizations should match the plan allocations. However, there are several reasons why the
CPU utilizations may not match the plan allocation:
A consumer group’s utilization may be higher than its plan allocation because other
consumer groups were unable to utilize their allocation. Their allocation was reallocated
to this consumer group.
A consumer group’s utilization may be lower than its plan allocation because it was
unable to fully consume it. In this case, it should have no (or very little) throttle time.
The consumer group has a max_utilization_limit that is lower than the plan allocation.
Conclusion
By using Oracle Database Resource Manager to manage the database workload, you keep the
server running at a healthy load, ensure that critical backgrounds do not starve, and ensure that
you can always login and debug database issues.
By enabling a resource plan that allocates CPU among the consumer groups, you can also
manage the CPU usage for each consumer group.
Managing CPU is just one of the functions of Resource Manager. To understand how Resource
Manager can help you manage runaway queries, parallel queries, idle sessions, and undo space,
see the Oracle Database Administrator’s Guide. To understand how Resource Manager can help
you manage I/O, see the Oracle Exadata Storage Server User’s Guide.
Ref : An Oracle white paper on Using oracle database resource manager 11gr2
No comments:
Post a Comment