Oracle Resource Manager

Oracle resource manager is the redistribution of database resources, including host resources.

 

II.Two software packages

Dbms_resource_manager

Dbms_resource_manager_privs

Example:

Dbms_resource_manager.create_pending_area ();

Create a draft Area

Dbms_resource_manager.create_consumer_resource

Create consumer group

Dbms_resource_manage.create_plan ()

Create a scheduler

You can also use

Dbms_resource_manage.create_simple_plan create a simple plan

 

For example

Oadb

--------------------

|

Sales Group Market Group develop group

60% CPU 20% CPU 20%

　

Dbms_resource_manager.create_simple_resource (

Simple_plan => 'oss _ plan ',

Consumer_group1 => 'sales', groupworker CPU => 60,

Consumer_group2 => 'market', groupworker CPU => 20,

Consumer_group3 => 'develop', groupworker CPU => 20

)

　

You can automatically create a simple resource plan.

　

3. Resource allocation

　

Dbms_resource_manager.create_plan_directive can be used to specify the resource allocation for each group, including session_pool, CPU, concurrency, undo pool, and switch group.

1. DBA can set an active session pool for each group, and the number of concurrent sessions of each cousumer group. If this number is exceeded, the system will automatically queue.

Active session pool parameter:

Active_sess_pool_p1:

The maximum number of concurrent sessions of the consumer group is default 1000000.

Queueing_p1:

The maximum waiting time of a session in the queue. If the waiting time exceeds this limit, the system will automatically cancel. Default 1000000 seconds.

　

2. Oracle resource manager can evaluate the maximum execution time of a transaction. You can set max_est_exec_time. If the evaluation time of resource manager exceeds this time, the system will not execute it to avoid occupying too many system resources.

　

3. Automotic consumer group switch:

Directive parameters:

Switch_group: group switch to, default is null.

Swtich_time: active time activity time. After this time, the system automatically switches to another group, default 1000000

Swtich_estimate: True-> Oracle uses the evaluation execution time before the switchover. If the evaluation time is greater than switch_time, switch directly. Otherwise, switch is performed only after switch_time is reached. Defaul: false

4. Undo pool quota

Limit the Undo size of a consumer group: If it is greater than, DML execution will be blocked.

Default: 000000kb

　

Automatic consumer group Switching

This method enables you to control resources by specifying criteria that, if met, causes the automatic switching of sessions to another consumer group. The criteria used to determine switching are:

　

Switch group -- specifies the consumer group to which this session is switched if the other (following) criteria are met

Switch time -- specifies the length of time that a session can execute before it is switched to another consumer group

Use estimate -- specifies whether oracle is to use its own estimate of how long an operation will execute

The database resource manager switches a running session to switch group if the session is active for more than switch time seconds. active means that the session is running and consuming resources, not waiting idly for user input or waiting for CPU cycles. the session is allowed to continue running, even if the Active session pool for the new group is full. under these conditions a consumer group can have more sessions running than specified by its active session pool. once the session finishes its operation and becomes idle, it is switched back to its original group.

　

If use estimate is set to true, the database resource manager uses a predicted estimate of how long the operation will take to complete. if Oracle's predicted estimate is longer than the value specified as the switch time, then oracle switches the session before execution starts. if this parameter is not set, the operation starts normally and only switches groups when other switch criteria are met.

　

 

Iv. Verification draft Area

　

Verify pending Area

Dbms_resource_manager.validate_pending_area ()

Submit

Dbms_resource_manager.submit_pending_area ()

Dbms_resource_manager.validate_pending_area () is called by default ()

Note:

However, if the modification is incorrect, the submit_penging_area process will fail. All modifications to the resource plan since the create_pending_area process will be lost, and the pending Area will be cleared. To make the changes, you can only create _ pengding_area again and redo the previous steps. Therefore, it is best to add validate_pending_area before submit_pending_area.

Oracle resource can contain up to 32 groups.

 

5.Allocate users to the consumer group

Assign user to group:

Allocate user oe to group OLTP

Dbms_resource_manager_privs.grant_switch_comsumer_group (

Grantee_name => 'oe ',

Consumer_group => 'oltp ',

Grant_option => false

)

Set initial group to user:

After a user logs in, it automatically belongs to the consumer_group. If this parameter is not specified, the system automatically considers it belongs to default_consumer_group.

Dbms_resource_manager.set_initial_consumer_group (

User => 'oe ',

Consumer_group => 'oltp'

)

　

Vi. Specify system parameters-spfile Parameters

　

Show parameter resource_manager_plan

Alter system set resource_manager_plan = erp_plan;

If the plan does not exist, an error is reported in the started Alert Log.

You can switch the consumer group at the session level.

Dbms_session.switch_current_consumer_group () switches the current session user to another consumer group.

The DBA uses the permission to switch other sessions to another consumer group and switches session 7 to the oltp group.

Dbms_resource_manager.switch_consumer_group_for_sess (

Session_id => 7,

Serial => 13,

Consumer_group => 'oltp'

)

You can also switch all sessions of a user to a group.

Dbms_resource_manager.switch_consumer_group_for_user ()

 

7.View related to resource manager

Dba_rsrc _*

　

Initial_rsrc_consumer_group in dba_user