Oracle resource manager
oracle resource manager 是对数据库包括主机资源的再分配
二. 两个软件包
dbms_resource_manager
dbms_resource_manager_privs
示例：
dbms_resource_manager.create_pending_area();
创建一个草稿区
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_RESOURCE
建消费组
dbms_resource_manage.create_plan ()
创建计划
也可以利用
dbms_resource_manage.create_simple_plan 创建一个简单计划
例如
oadb
--------------------
| | |
sales组 market组 develop组
60％cpu 20%cpu 20%
dbms_resource_manager.create_simple_resource(
simple_plan =>'oadb_plan',
consumer_group1=>'sales',group1_cpu=>60,
consumer_group2=>'market',group1_cpu=>20,
consumer_group3=>'develop',group1_cpu=>20
)
就可以自动创建一个简单资源计划
三 资源分配
利用dbms_resource_manager.create_plan_directive 可以指定各个组的资源的分配，包括session_pool,cpu,并行度, undo pool ，切换组 等信息
1.DBA 可以为每个组设定active session pool,每个cousumer group 同时并发的session 数量，如果超过 了这个数量则会自动进行排队。
active session pool parameter:
active_sess_pool_p1 :
该consumer group 的并发session 上限 default 1000000
queueing_p1:
session 在队列中等待的时间上限，超过该时间则会自动cancel. default 1000000 秒
2. oracle resource manager 可以评估事务最大执行时间，可以设定max_est_exec_time ，如果resource manager 评估的时间超过了这个时间，系统将不会执行，避免占用过多的系统资源。
3. automotic consumer group switch ：
directive 参数：
switch_group : group switch to ,default is null.
swtich_time : active time 活动时间，过了这个时间就会自动切换到其他组中，default 1000000
swtich_estimate : true-> oracle 使用在切换前的评估执行时间，如果评估时间大于switch_time ，则直接切换，否则就是达到了switch_time 后才进行切换。 DEFAUL : FALSE
4. undo pool 的限额
限制某个consumer group 的undo size : 如果大于的话，则会阻止DML 的执行
default :1000000kB
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.
四：验证草稿区
验证 pending area
dbms_resource_manager.validate_pending_area()
提交
dbms_resource_manager.SUBMIT_pending_area()
默认调用dbms_resource_manager.validate_pending_area()
注意:
但是如果修改错误，则submit_penging_area过程会失败，自create_pending_area过程以来所有对资源计划的修改都将丢失，pending区也被清空。想要进行修改，只能重新create_pengding_area，并重做前面的步骤。所以最好在submit_pending_area前加上validate_pending_area。
oracle resource 最多32 个group
五. 分配用户到consumer group
assign user to group:
将用户OE 分配到组oltp 中
dbms_resource_manager_privs.grant_switch_comsumer_group (
grantee_name=>'OE',
consumer_group =>'OLTP',
grant_option=> false
)
set initial group to user:
就是用户登陆后，自动属于那个consumer_group ,如果不指定，则系统自动认为属于default_consumer_group 。
dbms_resource_manager.set_initial_consumer_group (
user =>'OE',
consumer_group=>'OLTP'
)
六： 指定系统参数－spfile 参数
show parameter resource_manager_plan
alter system set resource_manager_plan=erp_plan ;
如果该计划不存在，则会在启动的alert日志中，报告出错
在session 级别可以切换consumer group
dbms_session.switch_current_consumer_group() 将当前session用户切换到另一个consumer组中
DBA用权限切换其他的session 到另一个consumer group ，将session 7 切换到oltp 组
dbms_resource_manager.switch_consumer_group_for_sess (
session_id=>7,
serial => 13,
consumer_group=>'OLTP'
)
也可以将某个user 的全部session 切换到一个组
dbms_resource_manager.switch_consumer_group_for_user()
七. 与resource manager 有关的 视图
dba_rsrc_*
dba_user 中的 initial_rsrc_consumer_group
