Shulou(Shulou.com)05/31 Report--

This article introduces the knowledge of "how to create a resource plan with pending area". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Two DBMS packages

DBMS_RESOURCE_MANAGER Management Resource Plan

DBMS_RESOURCE_MANAGER_PRIVS grants users permission to manage resources

Plan schema:

Includes a top plan and the following subplans and consumption groups

Self-planned quotas are also allocated on a 100% basis.

Create a simple resource plan:

You can build up to 8 groups in CREATE_SIMPLE_PLAN.

BEGIN

DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN (SIMPLE_PLAN = > 'simple_plan1'

CONSUMER_GROUP1 = > 'mygroup1', GROUP1_CPU = > 80

CONSUMER_GROUP2 = > 'mygroup2', GROUP2_CPU = > 20)

END

In this way, there is no need to create pending area.

Create a resource plan with pending area

Before creating a resource plan, you must create a pending area, and after you create a resource plan, you must verify and submit the pending area

Create pending_area

EXEC DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA

Verification

EXEC DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA

Submit (pending area will be automatically released after submission)

EXEC DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA

Manual release

EXEC DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA

Parameters of the resource plan

PLAN Resource Plan name

COMMENT description

CPU_MTH CPU allocation methods, including EMPHASIS (as a percentage) and RATIO (proportionately, several to several). Where EMPHASIS is the default

The maximum number of active session ACTIVE_SESS_POOL_MTH can have. Default is ACTIVE_SESS_POOL_ABSOLUTE.

PARALLEL_DEGREE_LIMIT_MTH parallelism, default PARALLEL_DEGREE_LIMIT_ABSOLUTE

QUEUEING_MTH queue resource allocation method to determine which session is executed first. Default FIFO_TIMEOUT

Create a resource plan

EXEC DBMS_RESOURCE_MANAGER.CREATE_PLAN (PLAN = > 'great_bread', COMMENT = >' great plan')

Modify the resource plan

EXEC DBMS_RESOURCE_MANAGER.UPDATE_PLAN (PLAN = > 'great_bread', NEW_COMMENT = >' great plan for great bread')

Delete resource plan

EXEC DBMS_RESOURCE_MANAGER.DELETE_PLAN (PLAN = > 'great_bread');-- only delete the schedule, not the corresponding resource group

Cascaded deletion with DELETE_PLAN_CASCADE

Ratio strategy

Determine the percentage of CPU available to each resource group in a level.

The following sets the ratio of 10 to 5 to 2 to 1 for Gold service,Silver service,Bonze service,Lowest service.

If only gold service and sliver service currently exist, they are allocated at a ratio of 10 to 5.

DBMS_RESOURCE_MANAGER.CREATE_PLAN

(PLAN = > 'service_level_plan'

CPU_MTH-> 'RATIO'

COMMENT = > 'service level plan')

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE

(PLAN = > 'service_level_plan'

GROUP_OR_SUBPLAN = > 'GOLD_CG'

COMMENT = > 'Gold service level customers'

CPU_P1 = > 10)

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE

(PLAN = > 'service_level_plan'

GROUP_OR_SUBPLAN = > 'SILVER_CG'

COMMENT = > 'Silver service level customers'

CPU_P1 = > 5)

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE

(PLAN = > 'service_level_plan'

GROUP_OR_SUBPLAN = > 'BRONZE_CG'

COMMENT = > 'Bonze service level customers'

CPU_P1 = > 2)

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE

(PLAN = > 'service_level_plan'

GROUP_OR_SUBPLAN = > 'OTHER_GROUPS'

COMMENT = > 'Lowest priority sessions'

CPU_P1 = > 1)

Create a resource consumption group

Parameters:

CONSUMER_GROUP name

COMMENT description

The CPU_MTH CPU allocation method, which defaults to ROUND_ROBIN, uses ROUND_ROBIN cheduler to session the correct execution. There is also the RUN_TO_COMPLETION option.

Special resource consumption group (cannot be modified and deleted)

DEFAULT_CONSUMER_GROUP all users who do not explicitly specify a consumption group and the default consumption group for session.

OTHER_GROUPS cannot be explicitly assigned to users and applies to all users of consumption groups that are not in the current plan schema.

At the same time, there are two consumption groups: SYS_GROUP and LOW_GROUP under the SYSTEM_PLAN resource plan provided by ORACLE.

Create a consumption group

You must create a pending area before creating it

EXEC DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP = > 'sales', COMMENT = >' retail and wholesale sales')

Modify consumption group

UPDATE_CONSUMER_GROUP

Delete consumption group

DELETE_CONSUMER_GROUP

Create a resource plan directive resource plan directives

The resource plan directive assigns consumption groups to the resource plan and specifies parameters for various allocation methods

Parameters:

PLAN Plan name

GROUP_OR_SUBPLAN consumption Group Live Sub-Plan name

COMMENT remarks

CPU_P1 to CPU_P8 for EMPHASIS mode, specify the percentage of the current level, for RATIO, specify the allocation ratio, RATIO is only valid for CPU_P1, not for subsequent levels.

ACTIVE_SESS_POOL_P1 maximum number of active session. Default is UNLIMITED.

Timeout in QUEUEING_P1 queue, default UNLIMITED

PARALLEL_DEGREE_LIMIT_P parallelism, default UNLIMITED

SWITCH_GROUP

SWITCH_TIME

SWITCH_ESTIMATE

MAX_EST_EXEC_TIME

UNDO_POOL

Maximum idle time for MAX_IDLE_TIME session

Maximum idle time for blocked session of MAX_IDLE_BLOCKER_TIME

SWITCH_TIME_IN_CALL

Create resource plan directive

BEGIN

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (

PLAN = > 'great_bread'

GROUP_OR_SUBPLAN = > 'sales'

COMMENT = > 'sales group'

CPU_P1 = > 60

PARALLEL_DEGREE_LIMIT_P1 = > 4)

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (

PLAN = > 'great_bread'

GROUP_OR_SUBPLAN = > 'market'

COMMENT = > 'marketing group'

CPU_P1 = > 20)

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (

PLAN = > 'great_bread'

GROUP_OR_SUBPLAN = > 'develop'

COMMENT = > 'development group'

CPU_P1 = > 20)

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (

PLAN = > 'great_bread'

GROUP_OR_SUBPLAN = > 'OTHER_GROUPS'

COMMENT = > 'this one is required'

CPU_P1 = > 0

CPU_P2 = > 100)

END

Modify resource plan directive

EXEC DBMS_RESOURCE_MANAGER.UPDATE_PLAN_DIRECTIVE (

PLAN = > 'great_bread'

GROUP_OR_SUBPLAN = > 'develop'

NEW_CPU_P1 = > 15)

Delete

DELETE_PLAN_DIRECTIVE

Manage consumption group

Stored procedures used

SET_CONSUMER_GROUP_MAPPING

SET_CONSUMER_GROUP_MAPPING_PRI

Change the consumption group of the session being executed

You can change quotas such as CPU without kicking out session.

The parameter is the sid,serial# and consumption group name of the session

EXEC DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_SESS (

'17'

'12345'

'high_priorty')

Change the user's consumption group

EXEC DBMS_RESOURCE_MANAGER.SWITCH_CONSUMER_GROUP_FOR_USER (

'scott'

'low_group')

Switch consumption groups with DBMS_SESSION package

Users can change their consumption groups if they have been granted switch permission

Parameters:

NEW_CONSUMER_GROUP new group

OLD_CONSUMER_GROUP old group (this is the parameter of out type)

Behavior when an error occurs in INITIAL_GROUP_ON_ERROR handover. TRUE indicates that when a handover error occurs, the user switches to the initial consumption group. FALSE indicates that an error occurs and an error is reported.

Example:

SET serveroutput on

DECLARE

Old_group varchar2 (30)

BEGIN

DBMS_SESSION.SWITCH_CURRENT_CONSUMER_GROUP ('sales', old_group, FALSE)

DBMS_OUTPUT.PUT_LINE ('OLD GROUP =' | | old_group)

END

Allow users to switch themselves to a specified consumption group

EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (

'scott'

'group name', TRUE (SCOTT can also authorize others to switch to this group)

Withdraw the right to switch

EXEC DBMS_RESOURCE_MANAGER_PRIVS.REVOKE_SWITCH_CONSUMER_GROUP (

'scott'

'bug_batch_group')

Automatically map session to consumption group

Through the automatic connection of session attributes and consumption group, it is divided into login attributes and runtime attributes attributes.

Stored procedures used:

SET_CONSUMER_GROUP_MAPPING

SET_CONSUMER_GROUP_MAPPING_PRI

Create Mappin

Parameters:

ATTRIBUTE

VALUE

CONSUMER_GROUP

The attributes supported by ATTRIBUTE are:

Login attribute:

ORACLE_USER

SERVICE_NAME

CLIENT_OS_USER

CLIENT_PROGRAM

CLIENT_MACHINE

Runtime attribute:

MODULE_NAME

MODULE_NAME_ACTION

SERVICE_MODULE

SERVICE_MODULE_ACTION

Example:

Map sys to backup_cg

BEGIN

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA ()

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING

(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'sys',' backup_cg')

DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA ()

DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA ()

END

Priority when setting the session property as a mapping condition

Where EXPLICIT must be established, and EXPLICIT must be 1

BEGIN

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING_PRI (

EXPLICIT = > 1

SERVICE_MODULE_ACTION = > 2

SERVICE_MODULE = > 3

MODULE_NAME_ACTION = > 4

MODULE_NAME = > 5

SERVICE_NAME = > 6

ORACLE_USER = > 7

CLIENT_PROGRAM = > 8

CLIENT_OS_USER = > 9

CLIENT_MACHINE = > 10)

END

Make the resource plan effective

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'mydb_plan'

Add FORCE: you can disable scheduler from automatically switching resource plans

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:mydb_plan'

Here is an example of an exercise given in the official documentation

BEGIN

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA ()

DBMS_RESOURCE_MANAGER.CREATE_PLAN (PLAN = > 'bugdb_plan', COMMENT = >' Resource plan/method for bug users sessions')

DBMS_RESOURCE_MANAGER.CREATE_PLAN (PLAN = > 'maildb_plan', COMMENT = >' Resource plan/method for mail users sessions')

DBMS_RESOURCE_MANAGER.CREATE_PLAN (PLAN = > 'mydb_plan', COMMENT = >' Resource plan/method for bug and mail users sessions')

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP = > 'Online_group', COMMENT = >' Resource consumer group/method for online bug users sessions')

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP = > 'Batch_group', COMMENT = >' Resource consumer group/method for batch job bug users sessions')

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP = > 'Bug_Maint_group', COMMENT = >' Resource consumer group/method for users sessions for bug db maint')

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP = > 'Users_group', COMMENT = >' Resource consumer group/method for mail users sessions')

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP = > 'Postman_group', COMMENT = >' Resource consumer group/method for mail postman')

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (CONSUMER_GROUP = > 'Mail_Maint_group', COMMENT = >' Resource consumer group/method for users sessions for mail db maint')

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN = > 'bugdb_plan'

GROUP_OR_SUBPLAN = > 'Online_group'

COMMENT = > 'online bug users sessions at level 1', CPU_P1 = > 80, CPU_P2= > 0

PARALLEL_DEGREE_LIMIT_P1 = > 8)

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN = > 'bugdb_plan'

GROUP_OR_SUBPLAN = > 'Batch_group'

COMMENT = > 'batch bug users sessions at level 1', CPU_P1 = > 20, CPU_P2 = > 0

PARALLEL_DEGREE_LIMIT_P1 = > 2)

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN = > 'bugdb_plan'

GROUP_OR_SUBPLAN = > 'Bug_Maint_group'

COMMENT = > 'bug maintenance users sessions at level 2', CPU_P1 = > 0, CPU_P2 = > 100

PARALLEL_DEGREE_LIMIT_P1 = > 3)

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN = > 'bugdb_plan'

GROUP_OR_SUBPLAN = > 'OTHER_GROUPS'

COMMENT = > 'all other users sessions at level 3, CPU_P1 = > 0, CPU_P2 = > 0

CPU_P3 = > 100)

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN = > 'maildb_plan'

GROUP_OR_SUBPLAN = > 'Postman_group'

COMMENT = > 'mail postman at level 1', CPU_P1 = > 40, CPU_P2 = > 0

PARALLEL_DEGREE_LIMIT_P1 = > 4)

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN = > 'maildb_plan'

GROUP_OR_SUBPLAN = > 'Users_group'

COMMENT = > 'mail users sessions at level 2, CPU_P1 = > 0, CPU_P2 = > 80

PARALLEL_DEGREE_LIMIT_P1 = > 4)

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN = > 'maildb_plan'

GROUP_OR_SUBPLAN = > 'Mail_Maint_group'

COMMENT = > 'mail maintenance users sessions at level 2, CPU_P1 = > 0, CPU_P2 = > 20

PARALLEL_DEGREE_LIMIT_P1 = > 2)

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN = > 'maildb_plan'

GROUP_OR_SUBPLAN = > 'OTHER_GROUPS'

COMMENT = > 'all other users sessions at level 3, CPU_P1 = > 0, CPU_P2 = > 0

CPU_P3 = > 100)

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN = > 'mydb_plan'

GROUP_OR_SUBPLAN = > 'maildb_plan'

COMMENT= > 'all mail users sessions at level 1', CPU_P1 = > 30)

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN = > 'mydb_plan'

GROUP_OR_SUBPLAN = > 'bugdb_plan'

COMMENT = > 'all bug users sessions at level 1, CPU_P1 = > 70)

DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA ()

DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA ()

END

Example 2

BEGIN

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA ()

DBMS_RESOURCE_MANAGER.CREATE_PLAN (PLAN = > 'erp_plan', COMMENT = >' Resource plan/method for ERP Database')

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')

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN = > 'erp_plan'

GROUP_OR_SUBPLAN = > 'oltp', COMMENT = >' OLTP sessions', CPU_P1 = > 80

SWITCH_GROUP = > 'batch', SWITCH_TIME = > 3J SWITCHESTIMATE = > TRUE

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 = > 5, QUEUEING_P1 = > 600

MAX_EST_EXEC_TIME = > 3600)

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (PLAN = > 'erp_plan'

GROUP_OR_SUBPLAN = > 'OTHER_GROUPS', COMMENT = >' mandatory', CPU_P3 = > 100)

DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA ()

DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA ()

END

Monitor and adjust resource manager

View user consumption group permissions

In the following example, SCOTT can correspond to two groups: MARKET and SALES. He can say that other users are assigned to the SALES group. Neither MARKET nor SALES is the initial group of SCOTT.

SELECT * FROM DBA_RSRC_CONSUMER_GROUP_PRIVS GRANTEE GRANTED_GROUP GRANT_OPTION INITIAL_GROUP-- PUBLIC DEFAULT_CONSUMER_GROUP YES YESPUBLIC LOW_GROUP NO NOSCOTT MARKET NO NOSCOTT SALES YES NOSYSTEM SYS_GROUP NO YES

View the resource plan defined in the database

SQL > SELECT PLAN,COMMENTS,STATUS FROM DBA_RSRC_PLANS PLAN COMMENTS STATUS- SYSTEM_PLAN Plan to give system sessions priority ACTIVEBUGDB_PLAN Resource plan/method for bug users sessions ACTIVEMAILDB_PLAN Resource plan/method for mail users sessions ACTIVEMYDB_PLAN Resource plan/method for bug and mail users sessions ACTIVEGREAT_BREAD Great plan for great bread ACTIVEERP_PLAN Resource plan/method for ERP Database ACTIVE

View the consumption group of the active session

SQL > SELECT SID,SERIAL#,USERNAME,RESOURCE_CONSUMER_GROUP FROM video session Sid SERIAL# USERNAME RESOURCE_CONSUMER_GROUP-... 11 136 SYS SYS_GROUP 13 16570 SCOTT SALES

View the plan for the current activity

SQL > ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = mydb_plan;System altered.SQL > SELECT NAME, IS_TOP_PLAN FROM V$RSRC_PLAN NAME IS_TO--MYDB_PLAN TRUEMAILDB_PLAN FALSEBUGDB_PLAN FALSEViewDescriptionDBA_RSRC_CONSUMER_GROUP_PRIVS

USER_RSRC_CONSUMER_GROUP_PRIVS

DBA view lists all resource consumer groups and the users and roles to which they have been granted. USER view lists all resource consumer groups granted to the user.DBA_RSRC_CONSUMER_GROUPSLists all resource consumer groups that exist in the database.DBA_RSRC_MANAGER_SYSTEM_PRIVS

USER_RSRC_MANAGER_SYSTEM_PRIVS

DBA view lists all users and roles that have been granted Database Resource Manager system privileges. USER view lists all the users that are granted system privileges for the DBMS_RESOURCE_MANAGER package.DBA_RSRC_PLAN_DIRECTIVESLists all resource plan directives that exist in the database.DBA_RSRC_PLANSLists all resource plans that exist in the database.DBA_RSRC_GROUP_MAPPINGSLists all of the various mapping pairs for all of the session attributesDBA_RSRC_MAPPING_PRIORITYLists the current mapping priority of each attributeDBA_USERS

USERS_USERS

DBA view contains information about all users of the database. Specifically, for the Database Resource Manager, it contains the initial resource consumer group for the user. USER view contains information about the current user, and specifically, for the Database Resource Manager, it contains the current user's initial resource consumer group.V$ACTIVE_SESS_POOL_MTHDisplays all available active session pool resource allocation methods.V$BLOCKING_QUIESCELists all sessions that could potentially block a quiesce operation. Includes sessions that are active and not in the SYS_GROUP consumer group.V$PARALLEL_DEGREE_LIMIT_MTHDisplays all available parallel degree limit resource allocation methods.V$QUEUEING_MTHDisplays all available queuing resource allocation methods.V$RSRC_CONS_GROUP_HISTORYFor each entry in the view V$RSRC_PLAN_HISTORY, contains an entry for each consumer group in the plan showing the cumulative statistics for the consumer group.V$RSRC_CONSUMER_GROUPDisplays information about active resource consumer groups. This view can be used for tuning.V$RSRC_CONSUMER_GROUP_CPU_MTHDisplays all available CPU resource allocation methods for resource consumer groups.V$RSRC_PLANDisplays the names of all currently active resource plans.V$RSRC_PLAN_CPU_MTHDisplays all available CPU resource allocation methods for resource plans.V$RSRC_PLAN_HISTORYShows when Resource Manager plans were enabled or disabled on the instance. It helps you understand how resources were shared among the consumer groups over time.V$RSRC_SESSION_INFODisplays Resource Manager statistics for each session. Shows how the session has been affected by the Resource Manager. Can be used for tuning.V$SESSIONLists session information for each current session. Specifically, lists the name of the resource consumer group of each current session. This is the end of how to create a resource plan with pending area. Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.