Configure the Resource Manager

 


Configure the Resource Manager

Ref: Thanks 

https://hiteshgondalia.wordpress.com/configure-the-resource-manager/#b1

Configure the Resource Manager

Go back to OCM 12c Preparation Project 

Hands On Lab – Index

[1] Setup the Resource Manager for 12c Non CDB.
[2] Test RM for elapsed time limit exceededM
[3] Test RM for parallel server limit
[4] Test RM for IO Limits (SWITCH_IO_MEGABYTES)
[5] Test RM for Active Sessions Limit(active_sess_pool_p1)
[6] Test RM for Undo Limit (undo_pool)
[7] Automate active respective plan on their schedule window.

So Resource Manager for 12c is also pretty the same as Resource Manager 
for 11g for a database plus the CDB Resource Plans, where you can control 
how many "shares" and the limitations for each PDB will have in a 
multi-container environment.

In 12c now you can also set the resource plan directive PARALLEL_STMT_CRITICAL 
to BYPASS_QUEUE for a high-priority consumer group so that parallel statements 
from the consumer group bypass the parallel statement queue.

So the most important here is you to know how to create all the Resource Manager 
objects (Plan, Consumer Groups, Directives, etc) using both SQL*Plus and Cloud Control 
(preferable because it's faster).

In the official documentation available during the exam, on section 27 and 44 
specified below, there are good examples that you may copy, adapt and paste to 
save several minuted during the exam.

Path to Documentation:
Administrator’s Guide -> 27 Managing Resources with Oracle Database Resource Manager
Administrator’s Guide -> 44 Using Oracle Resource Manager for PDBs with SQL*Plus
Administrator’s Guide -> 45 Using Oracle Resource Manager for PDBs with Cloud Control


The Database Resource Manager helps you manage how resources such as CPU 
and Parallel Queuing are allocated among pluggable databases or user sessions.

#Container Database
CDB Resource Plans : Define Resource Plans for Consolidated Databases 
which contain directives that specify how resources are allocated to 
pluggable databases.

#Pluggable Database

Consumer Groups 
-> Define Consumer Groups, which are user sessions grouped together based 
   on resource processing requirements.

Consumer Group Mappings 
-> Define consumer group mapping rules, which are used to map user sessions 
   to consumer groups.

Plans 
-> Define Resource Plans for a single database, which contain directives 
   that specify how resources are allocated to Consumer Groups.

Performance Statistics
-> Monitor the statistics for the currently enabled Resource Plan.

44 Using Oracle Resource Manager for PDBs with SQL*Plus
44.8.1 Viewing CDB Resource Plans

COLUMN PLAN FORMAT A30
COLUMN STATUS FORMAT A10
COLUMN COMMENTS FORMAT A35

SELECT PLAN, STATUS, COMMENTS FROM DBA_CDB_RSRC_PLANS ORDER BY PLAN;

PLAN STATUS COMMENTS
------------------------------ ---------- -----------------------------------
DEFAULT_CDB_PLAN Default CDB plan
DEFAULT_MAINTENANCE_PLAN Default CDB maintenance plan
ORA$INTERNAL_CDB_PLAN Internal CDB plan
ORA$QOS_CDB_PLAN QOS CDB plan


44.8.2 Viewing CDB Resource Plan Directives

COLUMN PLAN HEADING 'Plan' FORMAT A26
COLUMN PLUGGABLE_DATABASE HEADING 'Pluggable|Database' FORMAT A25
COLUMN SHARES HEADING 'Shares' FORMAT 999
COLUMN UTILIZATION_LIMIT HEADING 'Utilization|Limit' FORMAT 999
COLUMN PARALLEL_SERVER_LIMIT HEADING 'Parallel|Server|Limit' FORMAT 999

SELECT PLAN,
PLUGGABLE_DATABASE,
SHARES,
UTILIZATION_LIMIT,
PARALLEL_SERVER_LIMIT
FROM DBA_CDB_RSRC_PLAN_DIRECTIVES
ORDER BY PLAN;

Parallel
Pluggable Utilization Server
Plan Database Shares Limit Limit
-------------------------- ------------------------- ------ ----------- --------
DEFAULT_CDB_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1 100 100
DEFAULT_CDB_PLAN ORA$AUTOTASK 90 100
DEFAULT_MAINTENANCE_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1 100 100
DEFAULT_MAINTENANCE_PLAN ORA$AUTOTASK 90 100
ORA$INTERNAL_CDB_PLAN ORA$DEFAULT_PDB_DIRECTIVE
ORA$INTERNAL_CDB_PLAN ORA$AUTOTASK
ORA$QOS_CDB_PLAN ORA$DEFAULT_PDB_DIRECTIVE 1 100 100
ORA$QOS_CDB_PLAN ORA$AUTOTASK 90 100

======================
Path to Documentation:
======================
Documentation
Oracle Resource Manager to allocate resources in a non-CDB.
27 Managing Resources with Oracle Database Resource Manager
27.1.3 About Resource Manager Administration Privileges

BEGIN
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SYSTEM_PRIVILEGE(
GRANTEE_NAME => 'HR',
PRIVILEGE_NAME => 'ADMINISTER_RESOURCE_MANAGER',
ADMIN_OPTION => FALSE);
END;
/

You can revoke this privilege using the REVOKE_SYSTEM_PRVILEGE procedure.

Note:
The ADMINISTER_RESOURCE_MANAGER system privilege can only be granted or 
revoked using the DBMS_RESOURCE_MANAGER_PRIVS package. It cannot be granted 
or revoked through the SQL GRANT or REVOKE statements.

27.2.3.2 Creating Consumer Group Mapping Rules

For example, the following PL/SQL block causes user SCOTT to map to the 
DEV_GROUP consumer group every time that he logs in:

BEGIN
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING 
(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'SCOTT', 'DEV_GROUP');
END;
/

27.2.6.2 Granting the Switch Privilege

The following example grants user SCOTT the privilege to switch to 
consumer group OLTP.

BEGIN
DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP (
GRANTEE_NAME => 'SCOTT',
CONSUMER_GROUP => 'OLTP',
GRANT_OPTION => TRUE);
END;
/
User SCOTT is also granted permission to grant switch privileges 
for OLTP to others.

27.2.6.3 Revoking Switch Privileges

The following example revokes user SCOTT's privilege to switch to 
consumer group OLTP.

BEGIN
DBMS_RESOURCE_MANAGER_PRIVS.REVOKE_SWITCH_CONSUMER_GROUP (
REVOKEE_NAME => 'SCOTT', 
CONSUMER_GROUP => 'OLTP');
END;
/

27.3 The Types of Resources Managed by the Resource Manager
27.3.1 CPU

To manage CPU resources, Resource Manager allocates resources among 
consumer groups and redistributes CPU resources that were allocated 
but were not used. You can also set a limit on the amount of CPU resources 
that can be allocated to a particular consumer group.

27.3.1.1 Management Attributes(MGMT_Pn where n is 1..8)
27.3.1.2 Utilization Limit (UTILIZATION_LIMIT )

27.3.2 Exadata I/O
-> Management attributes enable you to specify CPU resource allocation 
for Exadata I/O.(IORM)

27.3.3 Parallel Execution Servers
-> Resource Manager can manage usage of the available parallel execution 
servers for a database.

27.3.3.1 Degree of Parallelism Limit (PARALLEL_DEGREE_LIMIT_P1 )
27.3.3.2 Parallel Server Limit (PARALLEL_SERVER_LIMIT )
27.3.3.3 Parallel Queue Timeout (PARALLEL_QUEUE_TIMEOUT )

27.3.4 Runaway Queries
-> Runaway sessions and calls can adversely impact overall performance if 
they are not managed properly. Resource Manager can take action when a 
session or call consumes more than a specified amount of CPU, physical I/O, 
logical I/O, or elapsed time. 

-> Resource Manager can either switch the session or call to a consumer 
group that is allocated a small amount of CPU or terminate the session 
or call.

27.3.4.1 Automatic Consumer Group Switching
27.3.4.2 Canceling SQL and Terminating Sessions
27.3.4.3 Execution Time Limit

27.3.5 Active Session Pool with Queuing
-> You can control the maximum number of concurrently active sessions 
allowed within a consumer group. This maximum defines the active session pool.

27.3.6 Undo Pool
-> You can specify an undo pool for each consumer group. 
An undo pool controls the total amount of undo for uncommitted transactions 
that can be generated by a consumer group.

27.3.7 Idle Time Limit
-> You can specify an amount of time that a session can be idle, after which 
it is terminated. You can also specify a more stringent idle time limit that 
applies to sessions that are idle and blocking other sessions.

27.4 Creating a Simple Resource Plan

Example: Creating a Simple Plan with the CREATE_SIMPLE_PLAN Procedure

The following PL/SQL block creates a simple resource plan with two user-specified consumer groups:

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_SIMPLE_PLAN(SIMPLE_PLAN => 'SIMPLE_PLAN1',
CONSUMER_GROUP1 => 'MYGROUP1', GROUP1_PERCENT => 80,
CONSUMER_GROUP2 => 'MYGROUP2', GROUP2_PERCENT => 20);
END;
/

Executing the preceding statements creates the following plan:

Consumer Group Level 1 Level 2 Level 3

SYS_GROUP      100%
MYGROUP1                80%
MYGROUP2                20%
OTHER_GROUPS                    100%

27.5 Creating a Complex Resource Plan

Step 1: Create a pending area.
27.5.1 About the Pending Area

27.5.2 Creating a Pending Area

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
END;
/


Step 2: Create, modify, or delete consumer groups.

27.5.3 Creating Resource Consumer Groups

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
CONSUMER_GROUP => 'OLTP',
COMMENT => 'OLTP applications');
END;
/


MGMT_MTH
The resource allocation method for distributing CPU among sessions in the consumer group. 
The default is 'ROUND-ROBIN', which uses a round-robin scheduler to ensure that sessions are 
fairly executed. 
'RUN-TO-COMPLETION' specifies that long-running sessions are scheduled ahead of other sessions. 
This setting helps long-running sessions (such as batch processes) complete sooner.


Step 3: Map sessions to consumer groups.

27.5.4 Mapping Sessions to Consumer Groups

BEGIN
DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING( 
ATTRIBUTE => DBMS_RESOURCE_MANAGER.ORACLE_USER, 
VALUE => 'OE', 
CONSUMER_GROUP => 'OLTP');
END; 
/ 



Step 4: Create the resource plan.

27.5.5 Creating a Resource Plan

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN(
PLAN => 'DAYTIME',
COMMENT => 'More resources for OLTP applications');
END;
/

Step 5: Create resource plan directives.

27.5.6 Creating Resource Plan Directives

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
PLAN => 'DAYTIME',
GROUP_OR_SUBPLAN => 'OLTP',
COMMENT => 'OLTP group',
MGMT_P1 => 75);
END;
/

This directive assigns 75% of CPU resources to the OLTP consumer group at level 1.

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
PLAN => 'DAYTIME', 
GROUP_OR_SUBPLAN => 'REPORTING',
COMMENT => 'Reporting group',
MGMT_P1 => 15,
PARALLEL_DEGREE_LIMIT_P1 => 8,
ACTIVE_SESS_POOL_P1 => 4);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
PLAN => 'DAYTIME', 
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'This one is required',
MGMT_P1 => 10);
END;
/

In this plan, consumer group REPORTING has a maximum degree of parallelism of 8 for any operation, 
while none of the other consumer groups are limited in their degree of parallelism. In addition, 
the REPORTING group has a maximum of 4 concurrently active sessions.

Step 6: Validate the pending area.

BEGIN
DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
END;
/

Step 7: Submit the pending area.

BEGIN
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/


Note: You can clear the pending area at any time using the CLEAR_PENDING_AREA procedure.
(27.5.9 Clearing the Pending Area)

BEGIN
DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();
END;
/


27.6 Enabling Oracle Database Resource Manager and Switching Plans


ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'mydb_plan';


Note:
Note that by default a set of automated maintenance tasks run during maintenance windows, 
which are predefined Scheduler windows that are members of the MAINTENANCE_WINDOW_GROUP 
window group and which specify the DEFAULT_MAINTENANCE_PLAN resource plan. Thus, the 
Resource Manager activates by default during maintenance windows. You can modify these 
maintenance windows to use a different resource plan, if desired.

27.7.5 An Oracle-Supplied Mixed Workload Plan

Oracle Database includes a predefined resource plan, MIXED_WORKLOAD_PLAN, that prioritizes interactive 
operations over batch operations, and includes the required subplans and consumer groups recommended by 
Oracle.


27.11 Monitoring Oracle Database Resource Manager
V$RSRC_PLAN
V$RSRC_CONSUMER_GROUP
V$RSRC_SESSION_INFO
V$RSRC_PLAN_HISTORY
V$RSRC_CONS_GROUP_HISTORY
V$RSRCMGRMETRIC
V$RSRCMGRMETRIC_HISTORY

=====================================================================
Multitenant : Resource Manager with Container Databases (CDB) and Pluggable Databases (PDB) in Oracle Database 12c Release 1 (12.1)
=====================================================================

In a multitennent environment Resource Manager performs two separate tasks. At the CDB level it controls 
the resources allocated to each PDB, allowing you to prioritise some PDBs over others.

At the PDB level it controls the resources allocated to each session connected to the PDB, allowing you 
to prioritise some sessions over others, just as it does in pre-12c instances.

Container Database (CDB)
-> The following sections describe how resource manager can be used to control the resource usage 
between pluggable databases (PDBs). Resource manager does not currently have the ability to control 
memory usage between PDBs.


Create CDB Resource Plan

A CDB resource plan is made up of CDB resource plan directives. The plan directives allocate shares, 
which define the proportion of the CDB resources available to the PDB, and specific utilization percentages, 
that give a finer level of control. CDB resource plans are managed using the DBMS_RESOURCE_MANAGER package.

Each plan directive is made up of the following elements:

pluggable_database : The PDB the directive relates to.
shares : The proportion of the CDB resources available to the PDB.
utilization_limit : The percentage of the CDBs available CPU that is available to the PDB.
parallel_server_limit : The percentage of the CDBs available parallel servers (PARALLEL_SERVERS_TARGET initialization parameter) that are available to the PDB.


PDBs without a specific plan directive use the default PDB directive.
[1] Setup the Resource Manager for 12c Non CDB.

SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- --------------------
db_name string orcl
SQL>
SQL> CREATE USER usr_day_1 identified by oracle;

User created.

SQL> r
1* CREATE USER usr_day_2 identified by oracle

User created.

SQL> CREATE USER usr_night_2 identified by oracle;

User created.

SQL> r
1* CREATE USER usr_night_1 identified by oracle

User created.

SQL> grant connect,resource to usr_day_1,usr_day_2,usr_night_1,usr_night_2;

Grant succeeded.

SQL> alter user &username default tablespace users;
Enter value for username: usr_day_1
old 1: alter user &username default tablespace users
new 1: alter user usr_day_1 default tablespace users

User altered.

SQL> /
Enter value for username: usr_day_2
old 1: alter user &username default tablespace users
new 1: alter user usr_day_2 default tablespace users

User altered.

SQL> /
Enter value for username: usr_night_1
old 1: alter user &username default tablespace users
new 1: alter user usr_night_1 default tablespace users

User altered.

SQL> /
Enter value for username: usr_night_2
old 1: alter user &username default tablespace users
new 1: alter user usr_night_2 default tablespace users

User altered.

--Create Plan "DAY_PLAN" (Created from OEM)
DECLARE
spfileValue VARCHAR2(1000);
execText VARCHAR2(1000);
scopeValue VARCHAR2(30) := 'MEMORY';
planName VARCHAR2(100) :='DAY_PLAN';
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan( plan => 'DAY_PLAN', comment => 'Testing Day Plan',max_iops => NULL,max_mbps => NULL );
dbms_resource_manager.create_plan_directive(
plan => 'DAY_PLAN',
group_or_subplan => 'CONS_GROUP_DAY',
comment => '',
switch_elapsed_time => 6000,
max_utilization_limit => 80,
mgmt_p1 => 80, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
parallel_degree_limit_p1 => 16 ,
parallel_target_percentage => NULL ,
parallel_queue_timeout => NULL ,
parallel_stmt_critical => 'false' ,
switch_io_logical => NULL ,
switch_io_reqs => NULL ,
switch_io_megabytes => 100 ,
active_sess_pool_p1 => NULL,
queueing_p1 => NULL,
switch_group => 'CONS_GROUP_NIGHT',
switch_time => NULL,
switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
undo_pool => NULL ,
max_idle_time => 120,
max_idle_blocker_time => NULL,
switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

);
dbms_resource_manager.create_plan_directive(
plan => 'DAY_PLAN',
group_or_subplan => 'CONS_GROUP_NIGHT',
comment => '',
switch_elapsed_time => 60,
max_utilization_limit => 10,
mgmt_p1 => 10, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
parallel_degree_limit_p1 => 2 ,
parallel_target_percentage => NULL ,
parallel_queue_timeout => NULL ,
parallel_stmt_critical => 'false' ,
switch_io_logical => NULL ,
switch_io_reqs => NULL ,
switch_io_megabytes => 10 ,
active_sess_pool_p1 => NULL,
queueing_p1 => NULL,
switch_group => 'KILL_SESSION',
switch_time => NULL,
switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
undo_pool => NULL ,
max_idle_time => 60,
max_idle_blocker_time => NULL,
switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

);
dbms_resource_manager.create_plan_directive(
plan => 'DAY_PLAN',
group_or_subplan => 'OTHER_GROUPS',
comment => '',
switch_elapsed_time => NULL,
max_utilization_limit => 10,
mgmt_p1 => 10, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
parallel_degree_limit_p1 => 2 ,
parallel_target_percentage => NULL ,
parallel_queue_timeout => NULL ,
parallel_stmt_critical => 'false' ,
switch_io_logical => NULL ,
switch_io_reqs => NULL ,
switch_io_megabytes => NULL ,
active_sess_pool_p1 => NULL,
queueing_p1 => NULL,
switch_group => NULL,
switch_time => NULL,
switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
undo_pool => NULL ,
max_idle_time => 60,
max_idle_blocker_time => NULL,
switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

);
dbms_resource_manager.submit_pending_area();
END;
/

--> Active the plan from OEM.

SQL> show parameter resource

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_limit boolean TRUE
resource_manager_cpu_allocation integer 1
resource_manager_plan string DAY_PLAN
SQL>


BEGIN
dbms_resource_manager.set_initial_consumer_group(
user => 'USR_DAY_1',
consumer_group => 'CONS_GROUP_DAY'
);

dbms_resource_manager.set_initial_consumer_group(
user => 'USR_DAY_2',
consumer_group => 'CONS_GROUP_DAY'
);


dbms_resource_manager.set_initial_consumer_group(
user => 'USR_NIGHT_1',
consumer_group => 'CONS_GROUP_NIGHT'
);

dbms_resource_manager.set_initial_consumer_group(
user => 'USR_NIGHT_2',
consumer_group => 'CONS_GROUP_NIGHT'
);
END;
/

--Create Night Plan (From OEM) 
DECLARE
spfileValue VARCHAR2(1000);
execText VARCHAR2(1000);
scopeValue VARCHAR2(30) := 'MEMORY';
planName VARCHAR2(100) :='NIGHT_PLAN';
BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.create_plan( plan => 'NIGHT_PLAN', comment => 'Testing Night Plan',max_iops => NULL,max_mbps => NULL );
dbms_resource_manager.create_plan_directive(
plan => 'NIGHT_PLAN',
group_or_subplan => 'CONS_GROUP_DAY',
comment => '',
switch_elapsed_time => 60,
max_utilization_limit => 10,
mgmt_p1 => 10, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
parallel_degree_limit_p1 => 2 ,
parallel_target_percentage => NULL ,
parallel_queue_timeout => NULL ,
parallel_stmt_critical => 'false' ,
switch_io_logical => NULL ,
switch_io_reqs => NULL ,
switch_io_megabytes => 10 ,
active_sess_pool_p1 => NULL,
queueing_p1 => NULL,
switch_group => 'KILL_SESSION',
switch_time => NULL,
switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
undo_pool => NULL ,
max_idle_time => 60,
max_idle_blocker_time => NULL,
switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

);
dbms_resource_manager.create_plan_directive(
plan => 'NIGHT_PLAN',
group_or_subplan => 'CONS_GROUP_NIGHT',
comment => '',
switch_elapsed_time => 6000,
max_utilization_limit => 80,
mgmt_p1 => 80, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
parallel_degree_limit_p1 => 16 ,
parallel_target_percentage => NULL ,
parallel_queue_timeout => NULL ,
parallel_stmt_critical => 'false' ,
switch_io_logical => NULL ,
switch_io_reqs => NULL ,
switch_io_megabytes => 100 ,
active_sess_pool_p1 => NULL,
queueing_p1 => NULL,
switch_group => 'CONS_GROUP_DAY',
switch_time => NULL,
switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
undo_pool => NULL ,
max_idle_time => 120,
max_idle_blocker_time => NULL,
switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

);
dbms_resource_manager.create_plan_directive(
plan => 'NIGHT_PLAN',
group_or_subplan => 'OTHER_GROUPS',
comment => '',
switch_elapsed_time => NULL,
max_utilization_limit => 10,
mgmt_p1 => 10, mgmt_p2 => NULL, mgmt_p3 => NULL, mgmt_p4 => NULL,
mgmt_p5 => NULL, mgmt_p6 => NULL, mgmt_p7 => NULL, mgmt_p8 => NULL ,
parallel_degree_limit_p1 => 2 ,
parallel_target_percentage => NULL ,
parallel_queue_timeout => NULL ,
parallel_stmt_critical => 'false' ,
switch_io_logical => NULL ,
switch_io_reqs => NULL ,
switch_io_megabytes => NULL ,
active_sess_pool_p1 => NULL,
queueing_p1 => NULL,
switch_group => NULL,
switch_time => NULL,
switch_estimate => case 'false' when 'false' then false when 'true' then true else false end,
undo_pool => NULL ,
max_idle_time => 60,
max_idle_blocker_time => NULL,
switch_for_call => case 'true' when 'false' then false when 'true' then true else false end

);
dbms_resource_manager.submit_pending_area();
END;
/
--HOL
As per OCM11g book modified the existing plan.

[2] Test RM for elapsed time limit exceeded


BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'DAY_PLAN',
group_or_subplan => 'CONS_GROUP_DAY',
new_comment => '',
new_active_sess_pool_p1 => 20,
new_undo_pool => NULL
);
dbms_resource_manager.update_plan_directive(
plan => 'DAY_PLAN',
group_or_subplan => 'CONS_GROUP_NIGHT',
new_comment => '',
new_active_sess_pool_p1 => 2,
new_undo_pool => 10240,
new_parallel_degree_limit_p1=>2
);
dbms_resource_manager.submit_pending_area();
END;
/

-bash-4.1$ sqlplus usr_night_1/oracle

SQL> SELECT COUNT(*) FROM TEST;

COUNT(*)
----------
92101

SQL> SELECT * FROM TEST; 
ERROR:
ORA-56736: elapsed time limit exceeded - session terminated


Sun Sep 22 16:02:01 2019
Process 11735 killed by Resource Manager SWITCH_ELAPSED_TIME directive for consumer group CONS_GROUP_NIGHT
opiodr aborting process unknown ospid (11735) as a result of ORA-56736
Sun Sep 22 16:04:36 2019
[3] Test RM for parallel server limit

12c having some changes in parallel behavior default value of parallel_degree_policy is AUTO so sometime it will not use parallel. Reference

We will revert back the parallel behavior same as 11g.
alter system set parallel_degree_policy=LIMITED;


BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'DAY_PLAN',
group_or_subplan => 'CONS_GROUP_NIGHT',
new_parallel_degree_limit_p1=>2
);
dbms_resource_manager.submit_pending_area();
END;
/

select name,pqs_completed,pq_servers_used from V$RSRC_CONSUMER_GROUP where name ='CONS_GROUP_NIGHT';
NAME PQS_COMPLETED PQ_SERVERS_USED
-------------------------------- ------------- ---------------
CONS_GROUP_NIGHT 0 0


sqlplus usr_night_1/oracle@orcl

select table_name,degree from user_tables;
TABLE_NAME   DEGREE
----------------------------------------
TEST         5

select count(1) from test a,test b where a.object_id = b.object_id;

COUNT(1)
----------
368400

select * from table(dbms_xplan.display_cursor);

NAME PQS_COMPLETED PQ_SERVERS_USED
-------------------------------- ------------- ---------------
CONS_GROUP_NIGHT 1 4


BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'DAY_PLAN',
group_or_subplan => 'CONS_GROUP_NIGHT',
new_parallel_degree_limit_p1=>1
);
dbms_resource_manager.submit_pending_area();
END;
/

NAME             PQS_COMPLETED PQ_SERVERS_USED
-------------------------------- ------------- ---------------
CONS_GROUP_NIGHT 1             0
[4] Test RM for IO Limits (SWITCH_IO_MEGABYTES)

SQL> select count(1) from test a,test b
where a.object_id = b.object_id;
2 where a.object_id = b.object_id
*
ERROR at line 2:
ORA-12805: parallel query server died unexpectedly

SQL> select name,pqs_completed,pq_servers_used from V$RSRC_CONSUMER_GROUP where name ='CONS_GROUP_NIGHT';

NAME PQS_COMPLETED PQ_SERVERS_USED
-------------------------------- ------------- ---------------
CONS_GROUP_NIGHT 1 4


Sun Sep 22 16:34:30 2019
Process 10363 killed by Resource Manager SWITCH_IO_MEGABYTES directive for consumer group CONS_GROUP_NIGHT
opidrv aborting process P003 ospid (10363) as a result of ORA-603
Sun Sep 22 16:34:30 2019
Process 10361 killed by Resource Manager SWITCH_IO_MEGABYTES directive for consumer group CONS_GROUP_NIGHT
opidrv aborting process P002 ospid (10361) as a result of ORA-603
Sun Sep 22 16:34:47 2019
Errors in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_m001_12770.trc:
ORA-28365: wallet is not open
[5] Test RM for Active Sessions Limit (active_sess_pool_p1)

BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'DAY_PLAN',
group_or_subplan => 'CONS_GROUP_DAY',
new_active_sess_pool_p1 => 3);
dbms_resource_manager.update_plan_directive(
plan => 'DAY_PLAN',
group_or_subplan => 'CONS_GROUP_NIGHT',
new_comment => '',
new_active_sess_pool_p1 => 2,
new_undo_pool => 10240,
new_parallel_degree_limit_p1=>2
);
dbms_resource_manager.submit_pending_area();
END;
/

select name,ACTIVE_SESSIONS,QUEUE_LENGTH,REQUESTS,EXECUTION_WAITERS from V$RSRC_CONSUMER_GROUP;


NAME ACTIVE_SESSIONS QUEUE_LENGTH REQUESTS EXECUTION_WAITERS
-----------------------------------------------------------------------------------------------------
CONS_GROUP_DAY 0 0 0 0
CONS_GROUP_NIGHT 2 1 8 1
OTHER_GROUPS 1 0 22 0
_ORACLE_BACKGROUND_GROUP_ 37 0 44 0


Terminal-1 (Active Session1 if it running query.)
sqlplus usr_night_1/oracle@orcl

Terminal-2 (Active Session1 if it running query.)
sqlplus usr_night_1/oracle@orcl


Terminal-3 (Waiting/Hanging)
sqlplus usr_night_1/oracle@orcl

# Also refer this column ACTIVE_SESSION_LIMIT_HIT
[6] Test RM for Undo Limit (undo_pool)

BEGIN
dbms_resource_manager.clear_pending_area();
dbms_resource_manager.create_pending_area();
dbms_resource_manager.update_plan_directive(
plan => 'DAY_PLAN',
group_or_subplan => 'CONS_GROUP_DAY',
new_active_sess_pool_p1 => 3);
dbms_resource_manager.update_plan_directive(
plan => 'DAY_PLAN',
group_or_subplan => 'CONS_GROUP_NIGHT',
new_comment => '',
new_active_sess_pool_p1 => 2,
new_undo_pool => 10240,
new_parallel_degree_limit_p1=>2
);
dbms_resource_manager.submit_pending_area();
END;
/

select name,ACTIVE_SESSIONS,CURRENT_UNDO_CONSUMPTION,UNDO_LIMIT_HIT from V$RSRC_CONSUMER_GROUP;

NAME ACTIVE_SESSIONS CURRENT_UNDO_CONSUMPTION UNDO_LIMIT_HIT
-----------------------------------------------------------------------------------------------
CONS_GROUP_DAY 0 0 0
CONS_GROUP_NIGHT 0 0 0
OTHER_GROUPS 1 0 0
_ORACLE_BACKGROUND_GROUP_ 31 0 0

UPDATE TEST
SET LAST_DDL_TIME=SYSDATE,CREATED=CURRENT_TIMESTAMP where rownum <10000;

9999 rows updated.

commit;

UPDATE TEST
SET LAST_DDL_TIME=SYSDATE,CREATED=CURRENT_TIMESTAMP where rownum <100000;
UPDATE TEST
*
ERROR at line 1:
ORA-30027: Undo quota violation - failed to get 340 (bytes)

SQL> !oerr ora 30027
30027, 00000, "Undo quota violation - failed to get %s (bytes)"
// *Cause: the amount of undo assigned to the consumer group of this
// session has been exceeded.
// *Action: ask DBA to increase undo quota, or wait until other
// transactions to commit before proceeding.


select name,ACTIVE_SESSIONS,CURRENT_UNDO_CONSUMPTION,UNDO_LIMIT_HIT from V$RSRC_CONSUMER_GROUP;

NAME ACTIVE_SESSIONS CURRENT_UNDO_CONSUMPTION UNDO_LIMIT_HIT
------------------------------------------------------------------------------------------------
CONS_GROUP_DAY 0 0 0
CONS_GROUP_NIGHT 0 0 1
OTHER_GROUPS 1 0 0
_ORACLE_BACKGROUND_GROUP_ 31 0 0


Mon Sep 23 10:54:34 2019
KTUDECRUSTAT: undo quota discrepancy: xcb=9aa86480, xcbusz=10240080, ResMgr=10239396
Mon Sep 23 10:54:36 2019
[7] Automate active respective plan on their schedule window.

--CLI
BEGIN
DBMS_SCHEDULER.create_window (
window_name => 'day_window1',
resource_plan => 'DAY_PLAN',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=8; byminute=0; bysecond=0;',
end_date => NULL,
duration => INTERVAL '10' HOUR,
window_priority => 'HIGH',
comments => 'Day time processing window.');

DBMS_SCHEDULER.create_window (
window_name => 'night_window1',
resource_plan => 'NIGHT_PLAN',
start_date => SYSTIMESTAMP,
repeat_interval => 'freq=daily; byhour=18; byminute=0; bysecond=0;',
end_date => NULL,
duration => INTERVAL '14' HOUR,
window_priority => 'HIGH',
comments => 'Night time processing window.');

DBMS_SCHEDULER.create_window_group (
group_name => 'processing_window_group',
window_list => 'day_window1, night_window1',
comments => '24 hour processing window group');
END;
/
--Using OEM

BEGIN
DBMS_SCHEDULER.CREATE_WINDOW(
window_name=>'"DAY_WINDOW1"',
resource_plan=>'DAY_PLAN',
start_date=>systimestamp at time zone 'Asia/Calcutta',
duration=>numtodsinterval(720, 'minute'),
repeat_interval=>'FREQ=DAILY;BYHOUR=7',
end_date=>null,
window_priority=>'HIGH',
comments=>'Day time processing window.');
END;

BEGIN
DBMS_SCHEDULER.CREATE_WINDOW(
window_name=>'"NIGHT_WINDOW1"',
resource_plan=>'NIGHT_PLAN',
start_date=>systimestamp at time zone 'Asia/Calcutta',
duration=>numtodsinterval(720, 'minute'),
repeat_interval=>'FREQ=DAILY;BYHOUR=19',
end_date=>null,
window_priority=>'HIGH',
comments=>'Night time processing window.');
END;

BEGIN
DBMS_SCHEDULER.CREATE_WINDOW_GROUP(
group_name=>'"PROCESSING_WINDOW_GROUP1"',
window_list=>'"DAY_WINDOW1","NIGHT_WINDOW1"');
END;
/
======================
Path to Documentation: 
======================
Administrator’s Guide 
(Oracle Resource Manager to allocate resources in a non-CDB)
-> 27 Managing Resources with Oracle Database Resource Manager        ---->27.1.3 About Resource Manager Administration Privileges
-> 44 Using Oracle Resource Manager for PDBs with SQL*Plus Administrator’s Guide 
-> 45 Using Oracle Resource Manager for PDBs with Cloud Contro




Comments

Post a Comment

Popular posts from this blog

QUERY DATAGUARD AND STANDBY STATUS

-- Find which logs were applied in the last day  SELECT SEQUENCE#, to_char(FIRST_TIME,'hh24:mi:ss dd/mm/yyyy'), to_char(NEXT_TIME,'hh24:mi:ss dd/mm/yyyy'),APPLIED FROM V$ARCHIVED_LOG where next_time>sysdate-1 ORDER BY SEQUENCE# ; -- Find last applied log   select to_char(max(FIRST_TIME),'hh24:mi:ss dd/mm/yyyy') FROM V$ARCHIVED_LOG where applied='YES';   -- What are the managed standby processes doing?   SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;   -- Are we on production or standby?  SELECT DATABASE_ROLE, DB_UNIQUE_NAME INSTANCE, OPEN_MODE, PROTECTION_MODE, PROTECTION_LEVEL, SWITCHOVER_STATUS FROM V$DATABASE;   -- Check for errors  SELECT MESSAGE FROM V$DATAGUARD_STATUS;   -- Check that the DB was openned correctly   SELECT RECOVERY_MODE FROM V$ARCHIVE_DEST_STATUS; -- important lag statistics  select * from v$dataguard_stats;  -- co...
Read more

Blocking session on RAC database

Blocking session on RAC database Blocking session of the databases Generate the report for Blocking session The SQL will provide Blocking sessions with SID, node information SELECT DISTINCT S1.USERNAME || '@' || S1.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S1.SID || ' ) IS BLOCKING ' || S2.USERNAME || '@' || S2.MACHINE || ' ( INST=' || S1.INST_ID || ' SID=' || S2.SID || ' ) ' AS BLOCKING_STATUS FROM GV$LOCK L1, GV$SESSION S1, GV$LOCK L2, GV$SESSION S2 WHERE S1.SID=L1.SID AND S2.SID=L2.SID AND S1.INST_ID=L1.INST_ID AND S2.INST_ID=L2.INST_ID AND L1.BLOCK > 0 AND L2.REQUEST > 0 AND L1.ID1 = L2.ID1 AND L1.ID2 = L2.ID2; Blockers information select s1.username || '@' || s1.machine  || ' ( SID=' || s1.sid || ' ) is blocking '  || s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status from gv$lock l1, gv$session s...
Read more

Finding object size in Oracle database

Ref: http://www.runningoracle.com/product_info.php?cPath=2_44&products_id=153 Finding object size in Oracle database is very important and common. Is it very useful to know the exact size occupied by the object at the tablespace. The object size in the following scripts is in Mbytes. The scripts have been formatted to work very easily with TOAD SQL Editor. For example you can filter with tablespace_name, or owner, or size (for example more than 1GByte)  SELECT owner, segment_name, segment_type, partition_name, ROUND(bytes/(1024*1024),2) SIZE_MB, tablespace_name  FROM DBA_SEGMENTS  WHERE SEGMENT_TYPE IN ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION',  'INDEX', 'INDEX PARTITION', 'INDEX SUBPARTITION', 'TEMPORARY')  --AND TABLESPACE_NAME LIKE 'COSTE%'  --AND SEGMENT_NAME LIKE 'P2010201%'  --AND partition_name LIKE 'P20100201%' --AND segment_type = 'TABLE' --AND OWNER = 'TARG...
Read more