resource manager tesing and explain
来源:互联网 发布:yy伴侣调视频数据效果 编辑:程序博客网 时间:2024/04/28 08:26
for the testing purpose , I assgin oracle user DD to batch group and QDL to oltp group , oltp can switch to batch group when switch condition are meet.
two consume groups are batch and oltp .
see comment details inline:
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();DBMS_RESOURCE_MANAGER.CREATE_PLAN(PLAN => 'erp_plan', COMMENT => 'Resource plan/method for ERP Database');
/* two group created */
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'oltp', COMMENT => 'Resource consumer group/method for OLTP jobs');DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(CONSUMER_GROUP => 'batch', COMMENT => 'Resource consumer group/method for BATCH jobs');
/* this statement assign cpu 80% to oltp :
when exectuion time more than 3 seconds or undo space more than 200k ,
the user belong to this group will switch to oltp group */
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'erp_plan', GROUP_OR_SUBPLAN => 'oltp', COMMENT => 'OLTP sessions', CPU_P1 => 80, SWITCH_GROUP => 'batch', SWITCH_TIME => 3, UNDO_POOL => 200);
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(PLAN => 'erp_plan', GROUP_OR_SUBPLAN => 'batch', COMMENT => 'BATCH sessions', CPU_P2 => 100, ACTIVE_SESS_POOL_P1 => 1, QUEUEING_P1 => 10, MAX_EST_EXEC_TIME => 20);
the upper statement is critical for the my testing :
/* for batch group cpu_p2 is 100% ,only one active session can connect , QUEUEING_P1 define the wait time , for any batch group user , when logon you could recevie the erro below
ERROR:
ORA-07454: queue timeout, 10 second(s), exceeded
to generate the error, I run a long run sql as QDL , and try to logon as dd user , then the error raised.
*/
/* while one DD is locking it self by exec dbms_lock.sleep(600); another dd connect , then error raised as following this prove ACTIVE_SESS_POOL_P1 => 1 works。
[oracle@ocm ~]$ sqlplus dd/oracle
SQL*Plus: Release 10.1.0.4.2 - Production on Tue Apr 23 17:13:06 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-07454: queue timeout, 10 second(s), exceeded
ERROR:
ORA-07454: queue timeout, 10 second(s), exceeded
*/
/* the state can be qurey as the statement :
SQL> SELECT username,RESOURCE_CONSUMER_GROUP,status FROM V$SESSION where username in ('DD','QDL');
USERNAME RESOURCE_CONSUMER_GROUP STATUS
------------------------------ -------------------------------- --------
DD BATCH ACTIVE
QDL OLTP INACTIVE
DD BATCH ACTIVE
*/
overall the active session count limited the group can only have 1 active session , and QUEUEING_P1will print the message when the time reached.
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
BEGIN
dbms_resource_manager_privs.grant_switch_consumer_group(
grantee_name => 'QDL',
consumer_group => 'OLTP',
grant_option => FALSE
);
dbms_resource_manager_privs.grant_switch_consumer_group(
grantee_name => 'QDL',
consumer_group => 'BATCH',
grant_option => FALSE
);
END;
BEGIN
dbms_resource_manager_privs.grant_switch_consumer_group(
grantee_name => 'DD',
consumer_group => 'BATCH',
grant_option => FALSE
);
END;
BEGIN
dbms_resource_manager.set_initial_consumer_group(
user => 'DD',
consumer_group => 'BATCH'
);
END;
there are still many other setting in grid control or through the package itself , the most hard part to understand is the queueing_p1 for me
- resource manager tesing and explain
- HGE 的Resource Manager
- HGE 的Resource Manager
- Oracle resource manager
- Resource Manager plan
- Oracle using resource manager
- Oracle Database Resource Manager
- Oracle Resource Manager 概述
- 25、Oracle resource manager
- RESOURCE MANAGER的使用
- Oracle resource manager
- Oracle Resource Manager
- 彻底禁用resource manager
- qnx resource manager概述
- RPM(Resource Power Manager)
- Virtual Resource Manager overview
- Unity Resource Manager
- Resource Manager HA配置
- python3.x 使用http-post方式调用webservice
- STLport开源库的编译和Vs2008下的配置
- 【后缀数组】【不同子串个数】DISUBSTR spoj694/705
- RedHat设置Yum源
- 请求报文模板
- resource manager tesing and explain
- vim+code_complete+ctags配置vim自动缩进函数补全环境(转)
- Google C++ Code Style
- Android软件开发之发送短信与系统短信库解析
- android异常:Can not perform this action after onSaveInstanc
- Pig Hive对比
- 使用Android自带Gallery组件实现CoverFlow,源码+解析
- ramdisk.img system.img userdata.img 分析
- 确定输入的string是否是有效的Parentheses