oracle参数配置

试验并发会话数限制

会话一：

conn z/z

insert into t values(1);

会话二：

conn z/z

insert into t values(1);

会话三：

conn z/z

insert into t values(1);

语句被阻塞

试验最大执行时间

conn z/z

create table o as select * from all_objects;

insert into o select * from o;

insert into o select * from o;

insert into o select * from o;

insert into o select * from o;

insert into o select * from o;

insert into o select * from o;

SQL> insert into o select * from o;

insert into o select * from o

*

第 1 行出现错误:

ORA-07455: 估计执行时间 (34 秒) 超出了限制 (10 秒)

试验阻塞等待时间

create table t as select 1 x, 1 y from dual;

会话一：

update t set y = 2 where x=1;

会话二：

update t set y = 2 where x=1;

当到达阻塞等待时间时，持有者自动释放

其它

停顿数据库

alter system quiesce restricted;

使得数据库只有 SYS_GROUP 组内成员可继续执行语句，其它所有用户已有事务继续执行但新的执行语句会被挂起。

alter system unquiesce;

创建一个简单计划

exec dbms_resource_manager.create_simple_plan(simple_plan => 'test',consumer_group1 => 'A',group1_cpu =>55,-

consumer_group2 => 'B',group2_cpu =>45);

col plan format a10

col group_or_subplan format a12

select plan,group_or_subplan,cpu_p1,cpu_p2,cpu_p3,cpu_p4,parallel_degree_limit_p1 parallel_limit,

active_sess_pool_p1 active_sess, max_est_exec_time max_time,undo_pool,max_idle_time,max_idle_blocker_time idle_blocker

from resource_plan_directive$ where plan='TEST';

PLAN GROUP_OR_SUB CPU_P1 CPU_P2 CPU_P3 CPU_P4 PARALLEL_LIMIT ACTIVE_SESS MAX_TIME UNDO_POOL MAX_IDLE_TIME IDLE_BLOCKER

TEST A 0 55 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295

TEST SYS_GROUP 100 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295

TEST OTHER_GROUPS 0 0 100 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295

TEST B 0 45 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295 4294967295

SYS_GROUP 和 OTHER_GROUPS 由系统创建了，选项也只能指定第二级的 CPU 分配

用户组映射设定

begin

第 42 页

dbms_resource_manager.clear_pending_area();

dbms_resource_manager.create_pending_area();

dbms_resource_manager.set_consumer_group_mapping_pri(

explicit => 1, oracle_user => 2,service_name => 3,client_os_user => 4,

client_program => 5,client_machine => 6,module_name => 7,module_name_action => 8,

service_module => 9,service_module_action => 10);

end;

/