Hi Dears,
In this article, we will see Resource Manager in Oracle
Resource Manager is an enterprise edition feature which will restrict sessions consuming high resources.
We can assign shares to sessions depends on priority of session.
We can restrict degree of parallelism for group of users using high amount of resources.
We can terminate long running SQL statements by using resource manager.
Check available CDB resource Plans
|
1 2 3 4 5 6 7 8 |
SELECT plan_id, plan, comments, status, mandatory FROM dba_cdb_rsrc_plans WHERE plan = 'DEFAULT_CDB_PLAN'; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
DECLARE l_plan VARCHAR2(30) := 'DEFAULT_CDB_PLAN'; BEGIN DBMS_RESOURCE_MANAGER.clear_pending_area; DBMS_RESOURCE_MANAGER.create_pending_area; dbms_resource_manager.create_plan( PLAN=> 'EXEC_TIME_LIMIT', COMMENT=>'Log statement after exceeding total execution time' ); dbms_resource_manager.create_consumer_group( CONSUMER_GROUP=>’LONG_EXEC_TIME’, COMMENT=>’THIS WILL HANDLE LONGEST RUNNING QUERIES' ); DBMS_RESOURCE_MANAGER.create_cdb_plan_directive( plan => plan_1, pluggable_database => 'TEST_1', comment => 'HIGH', shares => 3, utilization_limit => 60, parallel_server_limit => 40); DBMS_RESOURCE_MANAGER.validate_pending_area; DBMS_RESOURCE_MANAGER.submit_pending_area; END; / |
Assign users to the consumer groups
|
1 2 3 4 5 6 7 |
BEGIN DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group( grantee_name => TEST_USER, consumer_group => ’LONG_EXEC_TIME’, grant_option => FALSE); END; / |
Delete existing plan
|
1 2 3 4 5 6 7 8 9 10 11 |
BEGIN DBMS_RESOURCE_MANAGER.clear_pending_area; DBMS_RESOURCE_MANAGER.create_pending_area; DBMS_RESOURCE_MANAGER.delete_plan_cascade( plan => ‘plan_1’); DBMS_RESOURCE_MANAGER.validate_pending_area; DBMS_RESOURCE_MANAGER.submit_pending_area; END; / |
|
1 2 |
select * from dba_rsrc_consumer_groups where consumer_group=’LONG_EXEC_TIME’; select * from dba_rsrc_plans where plan=’PLAN_1’; |
Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates
KTEXPERTS is always active on below social media platforms.
Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Loading...
