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

This article mainly introduces "how to establish a resource plan for ORCALE". In daily operation, I believe many people have doubts about how to establish a resource plan for ORCALE. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts about "how to build a resource plan for ORCALE". Next, please follow the editor to study!

1. Concept

Use database resource management to manage the system, including the use of resource planning, resource user groups, and resource planning instructions. Including resource user groups, resource planning, resource planning directives

2. Establish a resource planning process

Grant admin permission

Execute dbms_resource_manager_privs.grant_system_privilege (grantee_name = > 'pptest',privilege_name = >' ADMINISTER_RESOURCE_MANAGER',admin_option = > FALSE)

Create a resource group

SQL > execute dbms_resource_manager.create_consumer_group ('VIP','VIP USER')

PL/SQL procedure successfully completed

SQL > execute dbms_resource_manager.create_consumer_group ('gen','gen user')

PL/SQL procedure successfully completed

Create a resource plan

Execute dbms_resource_manager.create_plan ('PLAN_TEST','TEST')

Resource allocation methodology for resource planning (CPU, focus approach)

Execute dbms_resource_manager.create_pending_area

SQL > execute dbms_resource_manager.create_plan_directive (plan = > 'PLAN_TEST',group_or_subplan = >' vip',comment = > 'cpu resource use',cpu_p1 = > 100)

SQL > execute dbms_resource_manager.create_plan_directive ('plan_test','gen','cpu resource gen user',cpu_p2= > 100)

SQL > execute dbms_resource_manager.create_plan_directive ('plan_test','other_groups',comment = >' other user',cpu_p3 = > 100)

Assign a resource allocation method to the resource plan (active session)

Execute dbms_resource_manager.create_pending_area

Execute dbms_resource_manager.update_plan_directive (plan = > 'PLAN_TEST',group_or_subplan = >' vip',new_active_sess_pool_p1 = > 100 (this resource group allows 100 active sessions, new_queueing_p1 = > 60 (in seconds))

Execute DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA ()

Assign a resource allocation method to the resource plan (estimated maximum execution time)

When the expected execution time is greater than the expected time, the statement will not be executed.

SQL > execute dbms_resource_manager.create_pending_area

PL/SQL procedure successfully completed

SQL > execute dbms_resource_manager.update_plan_directive (plan = > 'PLAN_TEST',group_or_subplan = >' vip',new_max_est_exec_time = > 1000)

PL/SQL procedure successfully completed

SQL > execute DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA ()

PL/SQL procedure successfully completed

Assign users to resource groups

1. Set the permissions of the groups that the user can switch

Execute dbms_resource_manager_privs.grant_switch_consumer_group (grantee_name = > 'pptest',consumer_group = >' vip',grant_option = > false)

2. Set the initialization group of the user

Execute dbms_resource_manager.set_initial_consumer_group (user = > 'pptest',consumer_group = >' vip')

Start the current explorer

Alter system set resource_manager_plan='plan_test' scope=both

View the settings for the resource planning group.

Select * from dba_rsrc_plan_directives where PLAN='PLAN_TEST'

3. Use mapping to make a user's session belong to a resource group

Execute dbms_resource_manager.create_pending_area

Execute dbms_resource_manager.set_consumer_group_mapping (dbms_resource_manager.oracle_user,'pptest','gen')

Execute dbms_resource_manager.submit_pending_area

4. Any user resource planning group that does not have an initial group assigned is DEFAULT_CONSUMER_GROUP, and once the plan is enabled, this part of the user will fall into the OTEHER_GROUPS group.

5. For users who have set up set_consumer_group_mapping, even if you do not have permission and dbms_resource_manager_privs.grant_switch_consumer_group settings, you cannot use the corresponding MAPPING group. If you set the MAPPING initial group, it will be MAPPING.

Execute dbms_resource_manager.set_consumer_group_mapping (dbms_resource_manager.oracle_user,'pptest1','VIP')

SQL > select username, resource_consumer_group from v$session where username is not null

USERNAME RESOURCE_CONSUMER_GROUP

PPTEST1 OTHER_GROUPS

PPTEST1 OTHER_GROUPS

It's still OTHER_GROUPS.

SQL > select * from dba_rsrc_consumer_group_privs where GRANTEE='PPTEST1'

GRANTEE GRANTED_GROUP GRANT_OPTION INITIAL_GROUP

-

SQL > select * from dba_rsrc_group_mappings where VALUE='PPTEST1'

ATTRIBUTE VALUE CONSUMER_GROUP STATUS

ORACLE_USER PPTEST1 VIP

6. If the user does not belong to any group and you want to set the initial group, an error will be reported.

SQL > execute dbms_resource_manager.set_initial_consumer_group (user = > 'pptest2',consumer_group = >' gen')

Begin dbms_resource_manager.set_initial_consumer_group (user = > 'pptest2',consumer_group = >' gen'); end

ORA-29399: user PPTEST2 does not have privilege to switch to consumer group GEN

ORA-06512: at "SYS.DBMS_RESOURCE_MANAGER", line 489

ORA-06512: at line 2

Because it must be set

Execute dbms_resource_manager_privs.grant_switch_consumer_group (grantee_name = > 'pptest2',consumer_group = >' gen',grant_option = > false)

And authority.

So here's a permission problem, set_initial_consumer_group, he says you can use the group, if it's not in it, you can't use it, even if you use set_consumer_group_mapping to modify it, but although you don't report an error, you can't use it. If you use set_initial_consumer_group to define the initial group, you can't define the view involved in the error.

Dba_rsrc_consumer_group_privs

Dba_rsrc_group_mappings

A user can only MAPPING to one resource group at a time, dba_rsrc_group_mappings can be modified, and set_initial_consumer_group can set the initial MAPPING.

So generally use set_initial_consumer_group to establish the initial group to complete the mapping, modify to use set_consumer_group_mapping, but be sure to remember that _ resource_manager_privs.grant_switch_consumer_group sets which resource groups the user can use.

At this point, the study on "how to build a resource plan for ORCALE" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.