Oracle DBMS_RESOURCE_MANAGER 用法例子

9rky7r39s2

General
Note:Resource Manager requires a massive over-demand on CPU before theexpected behavior starts to show: Generally speaking a 300% or400% CPU load to see that the actual split gets closer toexpectation, with the lower-privileged processes losing time in await state whose name includes 'resmgr: ....{something}..'
Source	{ORACLE_HOME}/rdbms/admin/dbmsrmad.sql
FirstAvailable	8.1.5
ConsumerGroup Mapping Constants 　　Name 　　DataType 　　Value 　　client_machine 　　VARCHAR2(30) 　　'CLIENT_MACHINE' 　　client_os_user 　　VARCHAR2(30) 　　'CLIENT_OS_USER' 　　client_program 　　VARCHAR2(30) 　　'CLIENT_PROGRAM' 　　module_name 　　VARCHAR2(30) 　　'MODULE_NAME' 　　module_name_action 　　VARCHAR2(30) 　　'MODULE_NAME_ACTION' 　　oracle_user 　　VARCHAR2(30) 　　'ORACLE_USER' 　　performance_class 　　VARCHAR2(30) 　　'PERFORMANCE_CLASS' 　　service_module 　　VARCHAR2(30) 　　'SERVICE_MODULE' 　　service_module_action 　　VARCHAR2(30) 　　'SERVICE_MODULE_ACTION' 　　service_name 　　VARCHAR2(30) 　　'SERVICE_NAME'
ConsumerGroup Mapping Constants 　　Name 　　DataType 　　Value 　　client_machine 　　VARCHAR2(30) 　　'CLIENT_MACHINE' 　　client_os_user 　　VARCHAR2(30) 　　'CLIENT_OS_USER' 　　client_program 　　VARCHAR2(30) 　　'CLIENT_PROGRAM' 　　module_name 　　VARCHAR2(30) 　　'MODULE_NAME' 　　module_name_action 　　VARCHAR2(30) 　　'MODULE_NAME_ACTION' 　　oracle_user 　　VARCHAR2(30) 　　'ORACLE_USER' 　　service_module 　　VARCHAR2(30) 　　'SERVICE_MODULE' 　　service_module_action 　　VARCHAR2(30) 　　'SERVICE_MODULE_ACTION' 　　service_name 　　VARCHAR2(30) 　　'SERVICE_NAME'

CLEAR_PENDING_AREA
Clears thework area for the resource manager	dbms_resource_manager.clear_pending_area;
execdbms_resource_manager.clear_pending_area;

CREATE_PENDING_AREA
Creates awork area for changes to resource manager objects	dbms_resource_manager.create_pending_area;
See Demo

DELETE_CONSUMER_GROUP
Deleteentries that define resource consumer groups	dbms_resource_manager.delete_consumer_group(consumer_groupIN VARCHAR2);
See Demo
DELETE_PLAN
Deletes thespecified plan as well as all the plan directives to which itrefers	dbms_resource_manager.delete_plan(planIN VARCHAR2);
execdbms_resource_manager.delete_plan('UW_PLAN');
DELETE_PLAN_CASCADE
Deletes thespecified plan as well as well as its descendants (plandirectives, subplans, consumer groups)	dbms_resource_manager.delete_plan_cascade(planIN VARCHAR2);
See Demo
DELETE_PLAN_DIRECTIVE
Deleteresource plan directives	dbms_resource_manager.delete_plan_directive( plan            IN VARCHAR2, group_or_subplan IN VARCHAR2);
See Demo
SET_CONSUMER_GROUP_MAPPING
Adds,deletes, or modifies entries that map sessions to consumer groups,based on the session's login and runtime attributes	dbms_resource_manager.set_consumer_group_mapping( attribute     IN VARCHAR2, value         IN VARCHAR2, consumer_group IN VARCHAR2 DEFAULT NULL);
TBD
SET_CONSUMER_GROUP_MAPPING_PRI
Createsthe session attribute mapping priority list The highestpriority is 1, the lowest 10 (the docs are incorrect)	dbms_resource_manager.set_consumer_group_mapping_pri( explicit             IN NUMBER, oracle_user          IN NUMBER, service_name         IN NUMBER, client_os_user       IN NUMBER, client_program       IN NUMBER, client_machine       IN NUMBER, module_name          IN NUMBER, module_name_action    INNUMBER, service_module       IN NUMBER, service_module_action IN NUMBER);
descdba_rsrc_mapping_priority SELECT * FROMdba_rsrc_mapping_priority; execdbms_resource_manager.create_pending_area; execdbms_resource_manager.set_consumer_group_mapping_pri(1, 2, 3, 4,5, 6, 7, 8, 9, 10); execdbms_resource_manager.validate_pending_area; exec dbms_resource_manager.submit_pending_area; SELECT * FROM dba_rsrc_mapping_priority; execdbms_resource_manager.create_pending_area; execdbms_resource_manager.set_consumer_group_mapping_pri(1, 7, 6, 9,8, 10, 5, 4, 3, 2); execdbms_resource_manager.validate_pending_area; exec dbms_resource_manager.submit_pending_area;
SET_INITIAL_CONSUMER_GROUP
Assigns theinitial resource consumer group for a user: Obsolete	dbms_resource_manager.set_initial_consumer_group( user          IN VARCHAR2, consumer_group IN VARCHAR2);
Deprecated
SUBMIT_PENDING_AREA
Submitspending changes for the resource manager	dbms_resource_manager.submit_pending_area;
See Demo
SWITCH_CONSUMER_GROUP_FOR_SESS
Changesthe resource consumer group of a specific session	dbms_resource_manager.switch_consumer_group_for_sess( session_id    IN NUMBER, session_serial IN NUMBER, consumer_group INVARCHAR2);
SELECT username,initial_rsrc_consumer_group FROM dba_users ORDER BY 1; colschemaname format a15 col program format a20 SELECT sid,serial#, schemaname, osuser, program FROM gv$session WHEREservice_name NOT LIKE '%BACK%'; execdbms_resource_manager.switch_consumer_group_for_sess(147, 1883, 'SYS_GROUP'); SELECT username,initial_rsrc_consumer_group FROM dba_users ORDER BY 1; execdbms_resource_manager.switch_consumer_group_for_sess(147, 1883, 'DEFAULT_CONSUMER_GROUP');
SWITCH_CONSUMER_GROUP_FOR_USER
Changes theresource consumer group for all sessions with a given user name	dbms_resource_manager.switch_consumer_group_for_user( user          IN VARCHAR2, consumer_group IN VARCHAR2);
execdbms_resource_manager.switch_consumer_group_for_user( 'UWCLASS', 'rpt_writers_grp');
SWITCH_PLAN
Sets thecurrent resource manager plan	dbms_resource_manager.switch_plan( plan_name                    IN VARCHAR2, sid                          IN VARCHAR2 DEFAULT '*', allow_scheduler_plan_switches INBOOLEAN DEFAULT TRUE);
TBD

VALIDATE_PENDING_AREA
Validatespending changes for the resource manager	dbms_resource_manager.validate_pending_area;
See Demo
Demos
CreatePlan 1 Demonstration	conn / as sysdba descdba_users SELECT username, initial_rsrc_consumer_group FROMdba_users ORDER BY 1; GRANT select ONdba_rsrc_consumer_groups TO uwclass; --create pointy-haired boss user CREATE USERphb IDENTIFIED BY phb DEFAULT TABLESPACE uwdata TEMPORARYTABLESPACE temp QUOTA 1M ON uwdata; GRANT create sessionTO phb; SELECT username, initial_rsrc_consumer_group FROMdba_users ORDER BY 1; descdba_rsrc_consumer_group_privs SELECT * FROMdba_rsrc_consumer_group_privs; set linesize 121 col planformat a30 col cpu_method format a10 col comments formata60 desc dba_rsrc_plans SELECT plan, cpu_method,comments, mandatory FROM dba_rsrc_plans; col cpu_methodformat a15 col consumer_group format a25 col comments formata45 desc dba_rsrc_consumer_groups SELECT * FROMdba_rsrc_consumer_groups; col value format a20 descdba_rsrc_group_mappings SELECT * FROMdba_rsrc_group_mappings; execdbms_resource_manager_privs.grant_system_privilege('UWCLASS', 'ADMINISTER_RESOURCE_MANAGER', FALSE); connuwclass/uwclass -- create a pendingarea exec dbms_resource_manager.create_pending_area; -- create two consumer groups:Workers and Managers execdbms_resource_manager.create_consumer_group('Workers', 'Those that do actual work'); execdbms_resource_manager.create_consumer_group('Managers', 'Those that don''t but take all the credit'); SELECT* FROM dba_rsrc_consumer_groups; execdbms_resource_manager.update_consumer_group('Managers', 'The Cartesian products of the IT world'); SELECT* FROM dba_rsrc_consumer_groups; --create resource management plan execdbms_resource_manager.create_plan('UW_PLAN','Demo Resource Plan', 'RATIO'); --create plan directives execdbms_resource_manager.create_plan_directive(plan=>'UW_PLAN', group_or_subplan=>'Workers', comment=>'CanGrab All The CPU', cpu_p1=>100); execdbms_resource_manager.create_plan_directive(plan=>'UW_PLAN', group_or_subplan=>'Managers',comment=>'Give Managers Little Weight', cpu_p1=>1); --validate the pending area execdbms_resource_manager.validate_pending_area; exec dbms_resource_manager.create_plan_directive(plan=>'UW_PLAN', group_or_subplan=>'OTHER_GROUPS',comment=>'Testing', cpu_p2=>0); --validate the pending area execdbms_resource_manager.validate_pending_area; -- oops ... go back and redocorrectly -- delete plandirectives execdbms_resource_manager.delete_plan_directive('UW_PLAN', 'Managers'); -- recreateplan directives execdbms_resource_manager.create_plan_directive(plan=>'UW_PLAN', group_or_subplan=>'Managers',comment=>'Infinite Weight', cpu_p1=>0); --revalidate the pending area execdbms_resource_manager.validate_pending_area; -- submit the pending area exec dbms_resource_manager.submit_pending_area; SELECT plan, cpu_method, comments, status, mandatory FROMdba_rsrc_plans; SELECT * FROMdba_rsrc_consumer_groups; execdbms_resource_manager_privs.grant_switch_consumer_group( grantee_name=>'PHB', consumer_group=>'Managers',grant_option=>FALSE); execdbms_resource_manager.set_initial_consumer_group(user => 'SCHEDULE', consumer_group=>'Managers'); altersystem set resource_manager_plan = 'UW_PLAN'; connschedule/schedule /* So PHB is in agroup that should have no CPU. Does this mean PHB can't doanything? */ SELECT COUNT(*) FROMall_tables; /* PHB is fine. There'splenty of free CPU so even though the group that PHB groupbelongs to "looks" like it should have no CPU, thatdoesn't have any effect in this case. */
CreatePlan 2 Demonstration	/* ResourceManager is only effective (from a CPU point of view) once CPUutilization starts to max out. Oracle takes the attitude thatif there is spare CPU capacity, there is no need to limit usage.Only when the resource becomes scarce does Oracle restrict access. */ BEGIN --create pending area dbms_resource_manager.create_pending_area; dbms_resource_manager.create_plan(plan=>'TEST2', comment=>'Use 10gFeature'); dbms_resource_manager.create_plan_directive(plan=>'TEST2', group_or_subplan=>'HAVES',comment=>'Testing',cpu_p1=>100); dbms_resource_manager.create_plan_directive(plan=>'TEST2', group_or_subplan=>'NOTS',comment=>'Testing', cpu_p1=>0, max_est_exec_time=>0); /* Prevent the have Managers from running any operation that has an estimated execution time > 0. */ dbms_resource_manager.create_plan_directive(plan=>'TEST2', group_or_subplan=>'OTHER_GROUPS',comment=>'Testing',cpu_p2=>100); dbms_resource_manager.validate_pending_area; dbms_resource_manager.submit_pending_area; dbms_resource_manager_privs.grant_switch_consumer_group( grantee_name=>'PHB', consumer_group=>'Managers', grant_option=>FALSE); dbms_resource_manager.set_initial_consumer_group(user => 'SCHEDULE',consumer_group=>'Managers'); END; / ALTER SYSTEMSET resource_manager_plan = test2; connschedule/schedule SELECT COUNT(*) FROM huge_table; ERRORat line 1: ORA-07455: estimated execution time (56 secs),exceeds limit (0 secs) /* However,the problem here is that Oracle kinda rounds *down* the estimateexecution time so providing I'm not being too greedy with myresources, Oracle can be a little lenient ... */ SELECTCOUNT(*) FROM small_table;
Drop PlanDemonstration	conn / as sysdba --create pending area execdbms_resource_manager.create_pending_area; -- remove admin privilege from uwclass execdbms_resource_manager_privs.revoke_system_privilege( 'UWCLASS', 'ADMINISTER_RESOURCE_MANAGER'); --delete resource plan execdbms_resource_manager.delete_plan_cascade('UW_PLAN'); -- validate pending area exec dbms_resource_manager.validate_pending_area; SELECT username, initial_rsrc_consumer_group FROMdba_users ORDER BY 1; -- switch consumer group for userSCHEDULES execdbms_resource_manager.switch_consumer_group_for_user( 'SCHEDULE','DEFAULT_CONSUMER_GROUP'); BEGIN dbms_resource_manager.create_pending_area; dbms_resource_manager.delete_plan_cascade('UW_PLAN'); dbms_resource_manager.validate_pending_area; END; / -- submit pending area exec dbms_resource_manager.submit_pending_area; -- revoke system privilege fromuser execdbms_resource_manager_privs.revoke_system_privilege('UWCLASS');

 
参考至：http://hi.baidu.com/edeed/blog/item/33c6b901c36b3a07728da521.html
如有错误，欢迎指正
邮箱：czmcj@163.com