Oracle resource manager

最新推荐文章于 2021-04-03 12:13:21 发布

叶竹

最新推荐文章于 2021-04-03 12:13:21 发布

阅读量451

点赞数

分类专栏： oracle 基础知识1 文章标签： manager oracle session resources user database

oracle 基础知识1 专栏收录该内容

25 篇文章 0 订阅

订阅专栏

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 (