Resource Manager
Oracle Database Resource Manager enables you to limit database resources. Oracle Database Resource Manager lets you specify how much of the resources you want to assign. It provides granular control of database resources allocated to users, applications, and services. It can be used to guarantee certain session a minimum and maximum amount of CPU. Limiting parallelism, active session pools to control maximum number of sessions within the group. The most interesting features is manage runaway sessions and switch groups. Manage runaway sessions help us with terminating user session whenever a session or call consumes more than specified resources. Any session in a particular group can be switched to another group if the session consumes more than specified resource.
Elements of Oracle Database Resource Manager
1. Resource consumer group
2. Resource plan
3. Resource plan directive
Resource consumer group is collection of user based on their need. SYS_GROUP and OTHER_GROUPS are two predefined groups. User SYS and SYSTEM belongs to SYS_GROUP and all other users within a plan that do not belongs to any group falls in OTHER_GROUPS.
Resource plan is the directives stating how the resource should be shared among the consumer groups.
The set of Reource plan directive determines how resources will be distributed among the users with in a Resource Plan.
Create dummy table for testing.
Copying data from same table to the table.
Create Service for single instance database.
Start service.
Check if service is listed on listener or not
Create pending area. If any already existing pending area.
Create Resource Manager Plan.
Create Plan Directives
Mapping services SRV_DEV to Resource Consumer Group.
Validate pending area.
Submit pending area.
Create tnsnames.
Connecting user using tnsnames.
Check current user is logged in using the services.
Execute cpu intensive query as user DILLI.
Show oracle resource related parameters.
Noticed CPU load has increased and PID 2519 has consumed 99.9% of CPU.
Set resource manager plan to DAY_PLAN.
Noticed CPU usage has decreased to 3.7% load.
Revert back the resource manager plan.
Noticed CPU% has increased to 99.9% for PID 2519.
