通过资源管理器设置并行度
并行度:指定不同的资源用户组里的用户在执行操作时,同一个资源用户组中的所有用户所能指定的最大并行度的总额,笔者理解成同一用户组所能使用的进程数, 待验证
配置并行度
设置并行度的SQL如下
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan( ' MY_DAY_PLAN ', '');
DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(PLAN => 'MY_DAY_PLAN',GROUP_OR_SUBPLAN => ' OLTP_GROUP ',NEW_PARALLEL_DEGREE_LIMIT_P1 => 3);
dbms_resource_manager.submit_pending_area();
dbms_resource_manager.switch_plan( plan_name => ' MY_DAY_PLAN ', sid => 'orcl' );
END;
验证并行度是否生效
SQL> show user;
USER is "HR"
SQL> create table emp_czm as
2 select * from employees
3 where 1=2;
SQL> declare
2 i int :=1;
3 begin
4 while i<=15 loop
5 insert into emp_czm
6 select * from employees;
7 i :=i+1;
8 end loop;
9 end;
10 /
在会话1中执行下列命令,进行并行度为5的查询
SQL>select /*+ parallel(emp_czm,5) */ * from emp_czm;
在会话2中执行下列命令
SQL> select sid,qcsid,degree from v$px_session where qcsid=141;
SID QCSID DEGREE
---------- ---------- ----------
102 141 3
104 141 3
100 141 3
141 141
其中sid为从属进程所对应的SID,QCSID为这些从属进程所对应的父SID,DEGREE表示并行度。
禁用MY_DAY_PLAN资源计划
SQL> alter system set resource_manager_plan='';
System altered
在会话1中执行下列命令,进行并行度为5的查询
SQL>select /*+ parallel(emp_czm,5) */ * from emp_czm;
在会话2中执行下列命令
SQL> select sid,qcsid,degree from v$px_session where qcsid=141;
SID QCSID DEGREE
---------- ---------- ----------
101 141 5
105 141 5
100 141 5
104 141 5
102 141 5
141 141
如上所示,设置并行度生效。
通过资源管理器设置活动seesion个数
由于活动的session必然消耗CPU或者I/O资源,因此通过控制当前的活动session的最大个数,能够对资源间接的进行控制。当用户组的某个用户所产生的session个数达到最大的时候,如果该用户还要在产生 活动的session ,则将该session放入队列,直到当前正在活动的session变为不活动时,等待活动的session才能开始活动。该队列为先进先出规则,也就是先进入队列的session先出队列。注意:并行执行所产生的session并不算作活动session。如果在队列里的session的等待时间超过指定时间,则该session会被切断。
设置活动session
设置活动session的sql如下
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan( ' MY_DAY_PLAN ', '');
DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(PLAN => 'MY_DAY_PLAN',GROUP_OR_SUBPLAN => 'BATCH_GRP',NEW_ACTIVE_SESS_POOL_P1 => 2,new_queueing_p1 => 5);
dbms_resource_manager.submit_pending_area();
dbms_resource_manager.switch_plan( plan_name => ' MY_DAY_PLAN ', sid => 'orcl' );
END;
验证设置活动session是否成功
在会话1和会话2中使用scott分别执行dead.sql
在会话3中使用scott用户连接
SQL> conn scott/scott;
ERROR:
ORA-07454: queue timeout, 5 second(s), exceeded
这个参数控制的是资源用户组内的用户同时可以运行的最大的活动SESSION的数量。这里值得强调的是ACTIVE_SESS_POOL_P1并不限制那些非活动的SESSION,仅仅对那些活动的SESSION有限制,因为一般说来只有那些活动的SESSION才会消耗系统的资源。
可以发现此时SESSION3被阻塞了,仅仅当SESSION1的SQL执行完毕,变成INACTIVE状态后,SESSION3才可以连接到数据库。那么这这个时候就有两个SESSION连接到数据库,但当一个执行SQL的时候,另一个SESSION会立刻被挂起。
可见设置活动session为3成功。
设置undo产生的数量
为了防止用户进行DML操作过程中消耗过多的UNDO,可以通过资源管理器控制用户组所能产生的undo的数据总量。
上图给OLTP_GRP用户组设置了200M的undo数据量,这200M undo数据包括active和inactive状态的undo数据。但不包括expired的undo数据。
设置活动undo数据量的sql如下
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan( ' MY_DAY_PLAN ', '');
DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(PLAN => 'MY_DAY_PLAN',GROUP_OR_SUBPLAN => 'OLTP_GRP',new_undo_pool => 204800);
dbms_resource_manager.submit_pending_area();
dbms_resource_manager.switch_plan( plan_name => ' MY_DAY_PLAN ', sid => 'orcl' );
END;
设置执行时间上限
有时候我们不希望用户组的用户执行消耗过长时间的操作。比如,在白天OLTP_GRP用户执行的操作都属于OLTP应用,当执行时间过长的时候OLTP_GRP组的用户就具有了BATCH_GRP批处理组用户的特性。这样我们就有必要将OLTP_GRP组的用户切换到BATCH_GRP。
其中Switch back to original group after call:表示较长时间的操作结束以后,session切换回原先的用户组。这里为OLTP_GRP。
有时候当某个session达到最大执行时间时,可能已经消耗了过多的资源,我们可以设定估计时间如下图设定估计的执行时间上限,并在上图勾选“Use estimate“选项来限定估计的时间上限。
限定执行时间上限的sql如下
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan( ' MY_DAY_PLAN ', '');
DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE(PLAN => 'MY_DAY_PLAN',GROUP_OR_SUBPLAN => 'OLTP_GRP', NEW_SWITCH_GROUP => ‘BATCH_GRP’, NEW_SWITCH_TIME_IN_CALL => 30, NEW_SWITCH_ESTIMATE => ‘true’,new_max_est_exec_time => 20,);
dbms_resource_manager.submit_pending_area();
dbms_resource_manager.switch_plan( plan_name => ' MY_DAY_PLAN ', sid => 'orcl' );
END;
另外一个和NEW_SWITCH_TIME对立的参数是NEW_SWITCH_TIME_IN_CALL,如果使用了这个参数,那么执行完成后,不切换回原来的资源用户组。
设置空闲时间上限
某个session即便没有活动,也会消耗一定的PGA内存。因此,如果某个session连接上来什么都没做也一定会消耗资源。那么我们可以选择切断连接。
如果空闲时间超过5秒则会话会自动断开,当设置"Max Idle Time If Blocking Another Session(sec)“参数时如果有会话被阻塞的时间超过了设定值时,则阻塞会话将自动被切断。例如当会话1执行DML操作且未提交导致会话2被DML事务锁定,始终不能进行下去。那么当会话1不提交的时间超过30妙钟后,会话1被切断。会话1占用的锁被释放,从而会话2的事务能继续进行下去。注意,只有在会话1阻止了其他会话的DML事务该参数才生效。
创建资源计划的相关sql
1.DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
创建一挂起区域,每次对资源计划进行操作之前都必须要执行挂起操作,申请一块内存。
2,DBMS_RESOURCE_MANAGER.CREATE_PLAN
创建一个资源计划,参数PLAN表示资源计划的名字,COMMENT为该资源计划的注释信息
3,DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP
创建一个资源用户组,参数CONSUMER_GROUP为资源用户组名字,COMMENT为该资源用户组的注释信息
4,DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE
创建一个资源分配方法,参数PLAN为资源计划的名字,,GROUP_OR_SUBPLAN为下层资源用户的名字,COMMENT为资源分配方法的注释信息,CPU_P1表示该资源用户组在LEVEL上的CPU分配方案。
5,DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA()
验证用户资源计划的有效性
6,DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA()
提交用户资源计划
(2)当建立好用户资源计划之后,就需要将特定的用户与特定的资源计划相关联。
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING (DBMS_RESOURCE_MANAGER.ORACLE_USER,'HR', ' OLTP_GRP ')
通过这条命令就可以将用户CNODS分配到资源组DB_DEV下。
(3)关联好之后就可以将新建立的用户资源管理置为有效
dbms_resource_manager.switch_plan( plan_name => 'MY_DAY_PLAN', sid => 'ORCL' )
通过这条命令将当前的用户资源管理计划设置为DW_PLAN
参考至:《教你成为10g OCP》韩思捷著
http://2jliu.itpub.net/post/21805/487037
本文原创,转载请注明出处、作者
如有错误,欢迎指出
邮箱:czmcj@163.com