Friday, August 8, 2008

Oracle:RM

RM Ensure how resources are accessed at mixed load like DSS and OLTP
Component of RM:
Resource consumer group: Group of resource with similar need
Resource Plan: Actual Plan How the resource will be divided
Resource directive: Link between Resource Consumer and Resource plan. Make sure how Resource consumer uses resource plans

What all resources can be controlled?
1. CPU utilization
2. Degree of parallelization
3. Session polling with queing: Control active sessions per resource consumer group
One queue per resource consumer group and FIFO is used for resource manager queue management with timeout

queuing_p1: Time after a session will timeout from the queue
active_session_pool_p1: How many concurrent session can be on a queue?
4. Execution time
5. Idle Time

Automatic Consumer Group Switch

Switch_time: after being idle for some time switch to the specified switch_group
switch_group: Group to switch when certain criteria meet.
switch_time_in_call : Mainly used for 3 tire application it is almost same as switch time but after switch session will switch back to the top call program.
switch_estimate: If set estimate is calculated and if database estimation is longer then the time specified in switch_time it does not even start the operation
and switch happens
You can not specify Switch_time and switch_time_in_call both the parameter for same resource group

We can also use automatic consumer group switch in switching in case of long running query and killing session
"cancel_sql" and "kill_session"

You can not set switch_group to either "cancel_sql" and "kill_session" through EM
You can not set switch_time_in_call from EM console
Adaptive Consumer group mapping:
We can use resource manger to configure session to map to the specific consumer group based on the module/application or session attribute.
Two type of session attribute:
Login attribute: meaning full at session login time
Run Time Attribute: Meaningful during run time based on the session attribute it can change/switch consumer group.

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING.use pending area for these procedure.
Oracle provides one default manager resource plan called system_plan and is basically used for system sessions. It contains directive for following
1. sys_group : Primary consumer group for sys and system users
2. other_goup : used for all sessions who belongs to consumer group not part of any active resource plan
3. low_group :No user associated with this consumer group. Low priority then sys_group and other_group

You can create a new Resource plan with the help of
1.EM
2.DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN
3.DBMS_RESOURCE_MANAGER procedure to create complex plan. steps to do that....
It provides the greatest level of customization and flexibility in terms of configuration....

Create Pending area prior to using RM command pending area need to be created for the work with the help of DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA procedure
Create resource plan
Create resource consumer group
Create resource directive
Validate the pending area

To configure the resource manager consumer group and plans you need to have a DBA role or
ADMINISTER_RESOURCE_MANAGER SYSTEM Privilege

No comments: