Oracle资源管理2----并行度、活动session和会话空闲时间(原创)

原创

wx6323d657ef2b3 2022-11-28 16:05:38 ©著作权

©著作权归作者所有：来自51CTO博客作者wx6323d657ef2b3的原创作品，请联系作者获取转载授权，否则将追究法律责任

通过资源管理器设置并行度

并行度：指定不同的资源用户组里的用户在执行操作时，同一个资源用户组中的所有用户所能指定的最大并行度的总额，笔者理解成同一用户组所能使用的进程数，待验证

配置并行度

设置并行度的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