Gurpartap Singh's Blog: Post 57 of series : Using DBRM (Database Resource Manager) on RAC 11gR2

SOOHEE, THIRD MEHL: The Dear Lord is subtle and inaccessible; how can we ever meet Him? Through the Word of the Guru's Shabad, doubt is dispelled, and the Carefree Lord comes to abide in the mind. || 1 || The Gurmukhs chant the Name of the Lord, Har, Har. I am a sacrifice to those who chant the Glorious Praises of the Lord in their minds forever. || 1 || Pause ||

Index of all the posts of Gurpartap Singh's Blog

There were many perfromance problems on the server as there were so many applications that were connecting to this clustered database.
Some people have habbit to use crazy number of parallel processes in the hint and can stall your database.

To stop this, I just thought to deploy DBRM i.e. database resource manager. Here are all the steps to deploy database resource manager DBRM.
I simulated the problem on this 2 node RAC cluster on 11gR2 and solved it using Database Resource Manager.

Create the new table:

sqlplus /

create table test as select * from dba_tables;

insert into test select * from dba_tables;

connect gurpartap/gurpartap

select /*+parallel(a,16)*/ * from ops$oracle.test a;

create user prabgun identified by prabgun;

grant dba to prabgun;

connect prabgun/prabgun

select /*+parallel(a,16)*/ * from ops$oracle.test a;

Using the following commands you can see the status of the sessions in the database:

select inst_id ins, sid, serial# ser,username, substr(osuser,1,12) osuser, substr(machine,1,25) machine,substr(module,1,15) module, substr(action,1,25) action,sql_id,

substr(event,1,20) event, seconds_in_wait siw, blocking_session ||','|| blocking_instance blocks, LAST_CALL_ET lst_cl_et, service_name srvc ,logon_time from gv$session

where wait_class!='Idle' order by inst_id,username,sql_id,event;

and

for parallel connections use:

SELECT a.qcinst_id, a.qcsid, a.qcserial# qcser, a.sid||','||a.serial# sid_serial, substr(y.osuser,1,13) osuser, y.username,

substr(y.module,1,25) module, substr(y.action,1,15) action, substr(y.machine,1,20) machine, a.degree, a.req_degree, y.inst_id,

y.sql_id, y.status, y.logon_time

FROM gv$px_session a, gv$session y

WHERE y.sid = a.sid

and y.inst_id = a.inst_id

order by a.qcsid, y.username, y.sql_id, y.inst_id, y.status

SQL> l

1 SELECT a.qcinst_id, a.qcsid, a.qcserial# qcser, a.sid||','||a.serial# sid_serial, substr(y.osuser,1,13) osuser, y.username,

2 substr(y.module,1,25) module, substr(y.action,1,15) action, substr(y.machine,1,20) machine, a.degree, a.req_degree, y.inst_id,

3 y.sql_id, y.status, y.logon_time

4 FROM gv$px_session a, gv$session y

5 WHERE y.sid = a.sid

6 and y.inst_id = a.inst_id

7* order by a.qcsid, y.username, y.sql_id, y.inst_id, y.status

SQL> /

QCINST_ID QCSID QCSER SID_SERIAL OSUSER USERNAME MODULE ACTION MACHINE DEGREE REQ_DEGREE INST_ID SQL_ID STATUS LOGON_TIM

--------- ---------- ---------- --------------- ------------ ------------------- --------------- -------------------- -------------------- ---------- ---------- ---------- ------------- -------- ---------

1 62 21 57,1455 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 34,1173 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 33,1189 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 71,2195 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 67,125 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 69,1723 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 61,707 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 65,31 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 68,1235 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

62 62,21 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 1 58xq49rpnt254 INACTIVE 17-MAR-14

1 62 21 65,255 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 55,2763 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 54,2607 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 63,1335 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 1,1775 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 51,2307 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 62 21 52,1849 oracle PRABGUN SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 79,3 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 78,3 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 76,3 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 75,3 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 74,3 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 73,3 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 72,33 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 77,3 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 1 58xq49rpnt254 ACTIVE 17-MAR-14

64 64,1759 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 1 58xq49rpnt254 INACTIVE 17-MAR-14

1 64 1759 64,19 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 70,3 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 69,3 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 59,2023 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 60,1033 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 48,1527 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 66,11 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 64 1759 67,5 oracle GURPARTAP SQL*Plus rac70.rac.meditate.c 16 16 2 58xq49rpnt254 ACTIVE 17-MAR-14

1 70 755 54,479 oracle OPS$ORACLE SQL*Plus rac70.rac.meditate.c 2 2 1 50nywwwt9tg51 ACTIVE 17-MAR-14

70 70,755 oracle OPS$ORACLE SQL*Plus rac70.rac.meditate.c 1 50nywwwt9tg51 ACTIVE 17-MAR-14

1 70 755 71,5 oracle OPS$ORACLE SQL*Plus rac70.rac.meditate.c 2 2 2 50nywwwt9tg51 ACTIVE 17-MAR-14

37 rows selected.

SQL>

SQL> select sql_text from v$sql where sql_text='58xq49rpnt254';

select /*+parallel(a,16)*/ * from ops$oracle.test a;

SQL>

22:01 : oracle : rac70.rac.meditate.com : @SDIEGO1 : /u01/app/oracle/diag/rdbms/prabgun/PRABGUN1/trace

$ top

top - 22:01:57 up 4 days, 10:09, 4 users, load average: 14.42, 5.38, 2.22

Tasks: 298 total, 3 running, 295 sleeping, 0 stopped, 0 zombie

Cpu(s): 4.2%us, 3.4%sy, 0.0%ni, 88.6%id, 2.1%wa, 0.7%hi, 0.9%si, 0.0%st

Mem: 1491784k total, 1424300k used, 67484k free, 2052k buffers

Swap: 5242872k total, 1490988k used, 3751884k free, 497652k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

3538 oracle RT 0 692m 151m 80m S 27.6 10.4 49:03.84 ocssd.bin

21730 oracle 20 0 97.8m 3316 732 S 24.0 0.2 0:33.68 sshd

21337 oracle 20 0 99744 2864 732 S 7.4 0.2 0:30.24 sshd

23258 oracle 20 0 71452 2764 1796 R 7.4 0.2 1:55.66 sqlplus

23901 oracle 20 0 71452 5872 2064 R 7.4 0.4 1:16.43 sqlplus

28 root 20 0 0 0 0 D 1.8 0.0 1:29.87 kswapd0

3480 oracle 20 0 546m 26m 13m S 1.8 1.8 97:33.24 gipcd.bin

4383 root 20 0 711m 29m 17m S 1.8 2.0 10:04.62 orarootagent.bi

4396 root 20 0 661m 18m 13m S 1.8 1.2 20:34.67 octssd.bin

5049 oracle -2 0 1369m 4524 4348 S 1.8 0.3 91:33.19 asm_vktm_+asm1

5059 oracle 20 0 1375m 7896 7424 S 1.8 0.5 5:21.50 asm_diag_+asm1

5067 oracle 20 0 1384m 11m 5884 S 1.8 0.8 16:11.33 asm_lmd0_+asm1

6313 root 20 0 788m 37m 21m S 1.8 2.5 26:28.04 crsd.bin

6435 root 20 0 695m 17m 12m S 1.8 1.2 56:48.63 orarootagent.bi

7133 oracle -2 0 614m 34m 27m S 1.8 2.4 47:09.21 oracle

23745 oracle 20 0 601m 25m 21m S 1.8 1.7 0:00.06 oracle

24169 oracle 20 0 603m 40m 34m D 1.8 2.8 0:00.13 oracle

24390 oracle 20 0 15164 1280 844 R 1.8 0.1 0:00.01 top

1 root 20 0 19360 1064 888 S 0.0 0.1 0:04.43 init

22:01 : oracle : rac70.rac.meditate.com : @SDIEGO1 : /u01/app/oracle/diag/rdbms/prabgun/PRABGUN1/trace

Got error: Out of memeory

Normal CPU usage is like:

$ top

top - 23:39:58 up 4 days, 11:47, 3 users, load average: 0.46, 0.28, 0.61

Tasks: 328 total, 2 running, 326 sleeping, 0 stopped, 0 zombie

Cpu(s): 4.3%us, 3.4%sy, 0.0%ni, 87.9%id, 2.8%wa, 0.7%hi, 0.9%si, 0.0%st

Mem: 1491784k total, 1404692k used, 87092k free, 7020k buffers

Swap: 5242872k total, 1532504k used, 3710368k free, 566896k cached

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND

7 root 20 0 0 0 0 R 1.9 0.0 21:01.73 events/0

3480 oracle 20 0 546m 26m 13m S 1.9 1.8 99:00.37 gipcd.bin

5090 oracle 20 0 1372m 10m 10m S 1.9 0.7 4:59.68 asm_rbal_+asm1

6435 root 20 0 695m 16m 12m S 1.9 1.2 57:30.74 orarootagent.bi

23740 oracle 20 0 601m 8148 7660 S 1.9 0.5 0:00.37 oracle

37832 oracle -2 0 599m 3820 3560 S 1.9 0.3 17:31.66 oracle

1 root 20 0 19360 1064 888 S 0.0 0.1 0:04.50 init

2 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kthreadd

3 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0

4 root 20 0 0 0 0 S 0.0 0.0 0:17.51 ksoftirqd/0

5 root RT 0 0 0 0 S 0.0 0.0 0:00.00 migration/0

6 root RT 0 0 0 0 S 0.0 0.0 0:01.30 watchdog/0

8 root 20 0 0 0 0 S 0.0 0.0 0:00.00 cgroup

9 root 20 0 0 0 0 S 0.0 0.0 0:00.00 khelper

10 root 20 0 0 0 0 S 0.0 0.0 0:00.00 netns

11 root 20 0 0 0 0 S 0.0 0.0 0:00.00 async/mgr

12 root 20 0 0 0 0 S 0.0 0.0 0:00.00 pm

13 root 20 0 0 0 0 S 0.0 0.0 0:00.90 sync_supers

14 root 20 0 0 0 0 S 0.0 0.0 0:01.28 bdi-default

15 root 20 0 0 0 0 S 0.0 0.0 0:00.00 kintegrityd/0

16 root 20 0 0 0 0 S 0.0 0.0 1:55.81 kblockd/0

So, we need to make some arrangements so that out server doesn't run out of resoruces.

===========================================================================================================================

We will use DBRM i.e. (Database resource manager)

The database parameters that we should set for DBRM are:

db_writer_processes

pga_aggregate_target

parallel_degree_policy

parallel_max_servers

parallel_min_servers

parallel_servers_target

parallel_adaptive_multi_user

optimizer_dynamic_sampling

Lets see the settings of these parameters on our database using the following query:

set line 300

set pages 100

col VALUE format a50

select INST_ID,NAME,VALUE from gv$parameter

where lower(NAME) in (

'db_writer_processes',

'pga_aggregate_target',

'parallel_degree_policy',

'parallel_max_servers',

'parallel_min_servers',

'parallel_servers_target',

'parallel_adaptive_multi_user',

'optimizer_dynamic_sampling'

)

order by 2;

Here is the output:

SQL> set line 300

set pages 100

SQL> SQL> col VALUE format a50

SQL> select INST_ID,NAME,VALUE from gv$parameter

2 where lower(NAME) in (

3 'db_writer_processes',

4 'pga_aggregate_target',

5 'parallel_degree_policy',

6 'parallel_max_servers',

7 'parallel_min_servers',

8 'parallel_servers_target',

9 'parallel_adaptive_multi_user',

10 'optimizer_dynamic_sampling'

11 )

12 order by 2;

INST_ID NAME VALUE

---------- -------------------------------------------------------------------------------- --------------------------------------------------

1 db_writer_processes 1

2 db_writer_processes 1

1 optimizer_dynamic_sampling 2

2 optimizer_dynamic_sampling 2

2 parallel_adaptive_multi_user TRUE

1 parallel_adaptive_multi_user TRUE

1 parallel_degree_policy MANUAL

2 parallel_degree_policy MANUAL

2 parallel_max_servers 40

1 parallel_max_servers 40

1 parallel_min_servers 0

2 parallel_min_servers 0

1 parallel_servers_target 16

2 parallel_servers_target 16

2 pga_aggregate_target 52428800

1 pga_aggregate_target 52428800

16 rows selected.

SQL>

DBRM

======================================

--Parameters for DBRM Plan

Read more about db_writer_processes in the following article. I will keep the default value of 1.

http://docs.oracle.com/cd/E11882_01/server.112/e40402/initparams072.htm

Read more about optimizer_dynamic_sampling in the following article. I will keep the default value of 2.

http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams141.htm

Read more about optimizer_dynamic_sampling in the following article. I will change the default value from TRUE to FALSE as we will

use DBRM to control degree of parallelism.

http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams149.htm

Execute following as a DBA user:

alter system set parallel_adaptive_multi_user=FALSE scope=both;

Output is as:

SQL> alter system set parallel_adaptive_multi_user=FALSE scope=both;

System altered.

SQL>

Read more about parallel_degree_policy in the following article. I will change the default value from MANUAL to AUTO.

http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn10310.htm

Execute the following as DBA user:

alter system set parallel_degree_policy=AUTO scope=both sid='*';

Output is as:

SQL> alter system set parallel_degree_policy=AUTO scope=both sid='*';

System altered.

SQL>

Read more about parallel_max_servers in the following article. I will change the default value from 40 to 68.

http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams153.htm

Execute the following as a DBA user:

alter system set parallel_max_servers=68 scope=both sid='*';

Output is as:

SQL> alter system set parallel_max_servers=68 scope=both sid='*';

System altered.

SQL>

Read more about parallel_min_servers in the following article. I will change the default value from 0 to 68 i.e. (max_parallel_servers).

http://docs.oracle.com/cd/B12037_01/server.101/b10755/initparams155.htm

Execute the following asd a DBA user:

alter system set parallel_min_servers=68 scope=both sid='*';

Output is as:

SQL> alter system set parallel_min_servers=68 scope=both sid='*';

System altered.

SQL>

Read more about parallel_servers_target in the following article. I will change the default value from 16 to 68 i.e. (max_parallel_servers).

http://docs.oracle.com/cd/E16655_01/server.121/e17615/refrn10314.htm

alter system set parallel_servers_target=68 scope=both sid='*';

Output is as:

SQL> alter system set parallel_servers_target=68 scope=both sid='*';

System altered.

SQL>

Read more about pga_aggregate_target in the following article. I will keep the default value of 50mb due to memmory constraints.

http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams157.htm

Lets see out parameter settings now using the follwing query as:

set line 300

set pages 100

col VALUE format a50

select INST_ID,NAME,VALUE from gv$parameter

where lower(NAME) in (

'db_writer_processes',

'pga_aggregate_target',

'parallel_degree_policy',

'parallel_max_servers',

'parallel_min_servers',

'parallel_servers_target',

'parallel_adaptive_multi_user',

'optimizer_dynamic_sampling'

)

order by 2;

Output is as:

SQL> set line 300

set pages 100

col VALUE format a50

select INST_ID,NAME,VALUE from gv$parameter

where lower(NAME) in (

'db_writer_processes',

'pga_aggregate_target',

'parallel_degree_policy',

'parallel_max_servers',

'parallel_min_servers',

'parallel_servers_target',

'parallel_adaptive_multi_user',

'optimizer_dynamic_sampling'

)

SQL> SQL> SQL> 2 3 4 5 6 7 8 9 10 11 12 order by 2;

INST_ID NAME VALUE

---------- -------------------------------------------------------------------------------- --------------------------------------------------

2 db_writer_processes 1

1 db_writer_processes 1

2 optimizer_dynamic_sampling 2

1 optimizer_dynamic_sampling 2

1 parallel_adaptive_multi_user FALSE

2 parallel_adaptive_multi_user FALSE

2 parallel_degree_policy AUTO

1 parallel_degree_policy AUTO

1 parallel_max_servers 68

2 parallel_max_servers 68

2 parallel_min_servers 68

1 parallel_min_servers 68

2 parallel_servers_target 68

1 parallel_servers_target 68

1 pga_aggregate_target 52428800

2 pga_aggregate_target 52428800

16 rows selected.

SQL>

Now, lets define the consumer groups for our system:

I will have:

1. DOP with Very High Importance

2. DOP with High Importance

3. DOP with Medium Importance

4. DOP with Low Importance

5. DOP with Very Low Importance

Lets Define these Counsumer Groups in the database witht eh following procedure:

BEGIN

DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(

CONSUMER_GROUP => 'DOP_VHI',

COMMENT => 'DOP Very High Importance');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(

CONSUMER_GROUP => 'DOP_HI',

COMMENT => 'DOP High Importance');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(

CONSUMER_GROUP => 'DOP_MI',

COMMENT => 'DOP Medium Importance');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(

CONSUMER_GROUP => 'DOP_LI',

COMMENT => 'DOP Low Importance');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(

CONSUMER_GROUP => 'DOP_VLI',

COMMENT => 'DOP Very Low Importance');

DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

END;

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Output is as:

SQL> BEGIN

2 DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

3 4

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(

5 6 CONSUMER_GROUP => 'DOP_VHI',

7 COMMENT => 'DOP Very High Importance');

9 DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(

10 CONSUMER_GROUP => 'DOP_HI',

11 COMMENT => 'DOP High Importance');

13 DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(

14 CONSUMER_GROUP => 'DOP_MI',

15 COMMENT => 'DOP Medium Importance');

17 DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(

18 CONSUMER_GROUP => 'DOP_LI',

19 COMMENT => 'DOP Low Importance');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP(

20 21 22 CONSUMER_GROUP => 'DOP_VLI',

23 COMMENT => 'DOP Very Low Importance');

25 DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

27 END;

28 /

PL/SQL procedure successfully completed.

SQL>

Now lets create a plan using teh following procedure:

BEGIN

DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

-- Creates Main Plan

DBMS_RESOURCE_MANAGER.CREATE_PLAN(

PLAN => 'HOME_DBRM_PLAN',

COMMENT => 'HOME DBRM PLAN');

-- Creates Plan Directive for sub Groups or subplan:

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

PLAN => 'HOME_DBRM_PLAN',

GROUP_OR_SUBPLAN => 'SYS_GROUP',

COMMENT=> 'Directive for SYS_GROUP',

MGMT_P1 => 30);

--Priority Group - Reserved for jobs where needed. (Service: OLTPsv1)

--14% of 68 is PARALLEL_TARGET_PERCENTAGE

-- mgmt_p1 is teh priority given to this "p1" is the heigest importance

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

PLAN => 'HOME_DBRM_PLAN',

GROUP_OR_SUBPLAN => 'DOP_VHI',

COMMENT=> 'Directive for P1_VERY_HIGH_IMPORTANCE',

parallel_degree_limit_p1 => 8,

PARALLEL_TARGET_PERCENTAGE=>14,

mgmt_p1 => 70);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

PLAN => 'HOME_DBRM_PLAN',

GROUP_OR_SUBPLAN => 'DOP_HI',

COMMENT=> 'Directive for P2_HIGH_IMPORTANCE',

parallel_degree_limit_p1 => 6,

PARALLEL_TARGET_PERCENTAGE=>25,

mgmt_p2 => 50

);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

PLAN => 'HOME_DBRM_PLAN',

GROUP_OR_SUBPLAN => 'DOP_MI',

COMMENT=> 'Directive for P3_MEDIUM_IMPORTANCE',

parallel_degree_limit_p1 => 6,

PARALLEL_TARGET_PERCENTAGE=>30,

mgmt_p3 => 60

);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

PLAN => 'HOME_DBRM_PLAN',

GROUP_OR_SUBPLAN => 'DOP_LI',

COMMENT=> 'Directive for P4_LOW_IMPORTANCE',

parallel_degree_limit_p1 => 4,

PARALLEL_TARGET_PERCENTAGE=>25,

mgmt_p4 => 70

);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

PLAN => 'HOME_DBRM_PLAN',

GROUP_OR_SUBPLAN => 'DOP_VLI',

COMMENT=> 'Directive for P5_VEY_LOW_IMPORANCE',

parallel_degree_limit_p1 => 2,

PARALLEL_TARGET_PERCENTAGE=>25,

mgmt_p5 => 100

);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

PLAN => 'HOME_DBRM_PLAN',

GROUP_OR_SUBPLAN => 'OTHER_GROUPS',

COMMENT=> 'Directive for P6_ALL_OTHER_GROUPS',

parallel_degree_limit_p1 => 2,

PARALLEL_TARGET_PERCENTAGE=>20,

mgmt_p6 => 100

);

DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

END;

Output is as:

SQL> BEGIN

DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();

2 3 DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

5 -- Creates Main Plan

6 DBMS_RESOURCE_MANAGER.CREATE_PLAN(

7 PLAN => 'HOME_DBRM_PLAN',

8 COMMENT => 'HOME DBRM PLAN');

10 -- Creates Plan Directive for sub Groups or subplan:

11 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

12 PLAN => 'HOME_DBRM_PLAN',

13 GROUP_OR_SUBPLAN => 'SYS_GROUP',

14 COMMENT=> 'Directive for SYS_GROUP',

15 MGMT_P1 => 30);

16 17 --Priority Group - Reserved for jobs where needed. (Service: OLTPsv1)

18 --14% of 68 is PARALLEL_TARGET_PERCENTAGE

19 -- mgmt_p1 is teh priority given to this "p1" is the heigest importance

20 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

21 PLAN => 'HOME_DBRM_PLAN',

22 GROUP_OR_SUBPLAN => 'DOP_VHI',

23 COMMENT=> 'Directive for P1_VERY_HIGH_IMPORTANCE',

24 parallel_degree_limit_p1 => 8,

25 PARALLEL_TARGET_PERCENTAGE=>14,

26 mgmt_p1 => 70);

28 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

29 PLAN => 'HOME_DBRM_PLAN',

30 GROUP_OR_SUBPLAN => 'DOP_HI',

COMMENT=> 'Directive for P2_HIGH_IMPORTANCE',

31 32 parallel_degree_limit_p1 => 6,

33 PARALLEL_TARGET_PERCENTAGE=>25,

34 mgmt_p2 => 50

35 );

37 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

38 PLAN => 'HOME_DBRM_PLAN',

39 GROUP_OR_SUBPLAN => 'DOP_MI',

40 COMMENT=> 'Directive for P3_MEDIUM_IMPORTANCE',

41 parallel_degree_limit_p1 => 6,

42 PARALLEL_TARGET_PERCENTAGE=>30,

mgmt_p3 => 60

43 44 );

46 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

47 PLAN => 'HOME_DBRM_PLAN',

48 GROUP_OR_SUBPLAN => 'DOP_LI',

49 COMMENT=> 'Directive for P4_LOW_IMPORTANCE',

50 parallel_degree_limit_p1 => 4,

51 PARALLEL_TARGET_PERCENTAGE=>25,

52 mgmt_p4 => 70

53 );

55 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

56 PLAN => 'HOME_DBRM_PLAN',

57 GROUP_OR_SUBPLAN => 'DOP_VLI',

58 COMMENT=> 'Directive for P5_VEY_LOW_IMPORANCE',

parallel_degree_limit_p1 => 2,

59 60 PARALLEL_TARGET_PERCENTAGE=>25,

61 mgmt_p5 => 100

62 );

64 DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(

65 PLAN => 'HOME_DBRM_PLAN',

66 GROUP_OR_SUBPLAN => 'OTHER_GROUPS',

67 COMMENT=> 'Directive for P6_ALL_OTHER_GROUPS',

68 parallel_degree_limit_p1 => 2,

69 PARALLEL_TARGET_PERCENTAGE=>20,

70 mgmt_p6 => 100

71 );

DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

73 74 END;

75 /

PL/SQL procedure successfully completed.

SQL> SQL>

Now, lets map the services of the database with the Consumer Groups we greated earlier.

Now, lets create some services :

srvctl add service -d SDIEGO -s OLTPsv1 -r SDIEGO1 -a SDIEGO2

srvctl add service -d SDIEGO -s OLTPsvb2 -r SDIEGO2 -a SDIEGO1

srvctl add service -d SDIEGO -s OLTPsvb1 -r SDIEGO2 -a SDIEGO1

srvctl add service -d SDIEGO -s HOMEsv1 -r SDIEGO1 -a SDIEGO2

srvctl add service -d SDIEGO -s HOMEsv2 -r SDIEGO2 -a SDIEGO1

After executing, lets check the services:

SQL> select SERVICE_ID,NAME,NAME_HASH from dba_services

2 ;

SERVICE_ID NAME NAME_HASH

---------- ---------------------------------------------------------------- ----------

1 SYS$BACKGROUND 165959219

2 SYS$USERS 3427055676

3 SDIEGOsv1 274715180

4 SDIEGO.rac.meditate.com 3539745710

5 SDIEGOXDB 2453147176

6 SDIEGO 2193528589

7 HOMEsv2 110420717

8 OLTPsvb1 385981247

9 OLTPsvb2 194003067

10 HOMEsv1 1644361115

11 OLTPsv1 556876437

11 rows selected.

SQL>

create user simar identified by simar;

create user SDIO identified by sdio;

grant dba to simar;

grant dba to sdio;

BEGIN

DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

begin

DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();

DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(ATTRIBUTE => DBMS_RESOURCE_MANAGER.SERVICE_NAME, VALUE => 'OLTPsv1',CONSUMER_GROUP => 'DOP_VHI');

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(ATTRIBUTE => DBMS_RESOURCE_MANAGER.SERVICE_NAME, VALUE => 'OLTPsvb2',CONSUMER_GROUP => 'DOP_HI');

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(ATTRIBUTE => DBMS_RESOURCE_MANAGER.SERVICE_NAME, VALUE => 'OLTPsvb1',CONSUMER_GROUP => 'DOP_MI');

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(ATTRIBUTE => DBMS_RESOURCE_MANAGER.SERVICE_NAME, VALUE => 'HOMEsv2',CONSUMER_GROUP => 'DOP_LI');

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(ATTRIBUTE => DBMS_RESOURCE_MANAGER.SERVICE_NAME, VALUE => 'HOMEsv2',CONSUMER_GROUP => 'DOP_VLI');

DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('GURPARTAP','DOP_MI',TRUE);

DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('PRABGUN','DOP_LI',TRUE);

DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('SIMAR','DOP_LI',TRUE);

DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('SDIO','DOP_VLI',TRUE);

DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

END;

end;

Output is as:

SQL> BEGIN

DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();

2 3 DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

5 begin

6 DBMS_RESOURCE_MANAGER.CLEAR_PENDING_AREA();

7 DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

8 DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(ATTRIBUTE => DBMS_RESOURCE_MANAGER.SERVICE_NAME, VALUE => 'OLTPsv1',CONSUMER_GROUP => 'DOP_VHI');

9 DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(ATTRIBUTE => DBMS_RESOURCE_MANAGER.SERVICE_NAME, VALUE => 'OLTPsvb2',CONSUMER_GROUP => 'DOP_HI');

10 DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(ATTRIBUTE => DBMS_RESOURCE_MANAGER.SERVICE_NAME, VALUE => 'OLTPsvb1',CONSUMER_GROUP => 'DOP_MI');

11 DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(ATTRIBUTE => DBMS_RESOURCE_MANAGER.SERVICE_NAME, VALUE => 'HOMEsv2',CONSUMER_GROUP => 'DOP_LI');

12 DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING(ATTRIBUTE => DBMS_RESOURCE_MANAGER.SERVICE_NAME, VALUE => 'HOMEsv2',CONSUMER_GROUP => 'DOP_VLI');

14 DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('GURPARTAP','DOP_MI',TRUE);

15 DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('PRABGUN','DOP_LI',TRUE);

16 DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('SIMAR','DOP_LI',TRUE);

17 DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('SDIO','DOP_VLI',TRUE);

18 DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();

19 END;

20 end;

21 /

PL/SQL procedure successfully completed.

SQL>

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'FORCE:HOME_DBRM_PLAN' SID = '*' SCOPE = BOTH;

Now you run the parallel commands and you will see the difference.

Here are some of the views and commands to look at the configuration of your DBRM (Database resource Manager)

To see the details of the directives that have been configured:
select * from DBA_RSRC_PLAN_DIRECTIVES where plan='HOME_DBRM_PLAN';

To see the CPU's capable
select * from DBA_RSRC_CAPABILITY;

SQL> show parameter resource_manager_plan

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
resource_manager_plan string FORCE:HOME_DBRM_PLAN
SQL>

Check all the categories that we have defined use the following:
select * from DBA_RSRC_CATEGORIES;

Result is:
SQL> select * from DBA_RSRC_CATEGORIES;

NAME COMMENTS STATU MAN
------------------------------ ---------------------------------------- ----- ---
ADMINISTRATIVE Administrative Consumer Groups NO
INTERACTIVE Interactive, OLTP Consumer Groups NO
BATCH Batch, Non-Interactive Consumer Groups NO
MAINTENANCE Maintenance Consumer Groups NO
OTHER Unclassified Consumer Groups YES

SQL>

To see how many CPU's DBRM can leverage use:
select * from DBA_RSRC_CAPABILITY;

SQL> select * from DBA_RSRC_CAPABILITY;

CPU_CAPABLE IO_CAPABLE STATU
----------- ------------------------------ -----
1

SQL>

Select the consumer groups that we have defined in the active plan:

select *
from DBA_RSRC_CONSUMER_GROUPS
where CONSUMER_GROUP in
(select GROUP_OR_SUBPLAN from DBA_RSRC_PLAN_DIRECTIVES where PLAN='HOME_DBRM_PLAN' );

In the following:

CPU_P1 is First Parameter for the CPU resource allocation method
CPU_P2 is Second Parameter for the CPU resource allocation method
and so on

and

MGMT_P1 is First parameter for the resource allocation method
MGMT_P2 is Second parameter for the resource allocation method

While creating the plan directive we didn't define "CPU resource allocation method", it picked the value of
co-responding MGMT_P. That is why the values of CPU_P1 and MGMT_P1 are same and so on.

set line 300
set pages 100
col CPU_P1 format 999
col CPU_P2 format 999
col CPU_P3 format 999
col CPU_P4 format 999
col CPU_P5 format 999
col CPU_P6 format 999
col CPU_P7 format 999
col CPU_P8 format 999
col MGMT_P1 format 999
col MGMT_P2 format 999
col MGMT_P3 format 999
col MGMT_P4 format 999
col MGMT_P5 format 999
col MGMT_P6 format 999
col MGMT_P7 format 999
col MGMT_P8 format 999
col GROUP_OR_SUBPLAN format a15
col COMMENTS format a40
col status format a5
select PLAN,GROUP_OR_SUBPLAN,TYPE,CPU_P1,CPU_P2,CPU_P3,CPU_P4,CPU_P5,
CPU_P6,CPU_P7,CPU_P8,MGMT_P1,MGMT_P2,MGMT_P3,MGMT_P4,MGMT_P5,MGMT_P6,
MGMT_P7,MGMT_P8,COMMENTS,STATUS,MANDATORY
from DBA_RSRC_PLAN_DIRECTIVES
where PLAN='HOME_DBRM_PLAN' ;

Output is as:

SQL> l
1 select PLAN,GROUP_OR_SUBPLAN,TYPE,CPU_P1,CPU_P2,CPU_P3,CPU_P4,CPU_P5,
2 CPU_P6,CPU_P7,CPU_P8,MGMT_P1,MGMT_P2,MGMT_P3,MGMT_P4,MGMT_P5,MGMT_P6,
3 MGMT_P7,MGMT_P8,COMMENTS,STATUS,MANDATORY
4 from DBA_RSRC_PLAN_DIRECTIVES
5* where PLAN='HOME_DBRM_PLAN'
SQL> /

PLAN GROUP_OR_SUBPLA TYPE CPU_P1 CPU_P2 CPU_P3 CPU_P4 CPU_P5 CPU_P6 CPU_P7 CPU_P8 MGMT_P1 MGMT_P2 MGMT_P3 MGMT_P4 MGMT_P5 MGMT_P6 MGMT_P7 MGMT_P8 COMMENTS STATU MAN
------------------------------ --------------- -------------- ------ ------ ------ ------ ------ ------ ------ ------ ------- ------- ------- ------- ------- ------- ------- ------- ---------------------------------------- ----- ---
HOME_DBRM_PLAN SYS_GROUP CONSUMER_GROUP 30 0 0 0 0 0 0 0 30 0 0 0 0 0 0 0 Directive for SYS_GROUP NO
HOME_DBRM_PLAN DOP_VHI CONSUMER_GROUP 70 0 0 0 0 0 0 0 70 0 0 0 0 0 0 0 Directive for P1_VERY_HIGH_IMPORTANCE NO
HOME_DBRM_PLAN DOP_HI CONSUMER_GROUP 0 50 0 0 0 0 0 0 0 50 0 0 0 0 0 0 Directive for P2_HIGH_IMPORTANCE NO
HOME_DBRM_PLAN DOP_MI CONSUMER_GROUP 0 0 60 0 0 0 0 0 0 0 60 0 0 0 0 0 Directive for P3_MEDIUM_IMPORTANCE NO
HOME_DBRM_PLAN DOP_LI CONSUMER_GROUP 0 0 0 70 0 0 0 0 0 0 0 70 0 0 0 0 Directive for P4_LOW_IMPORTANCE NO
HOME_DBRM_PLAN DOP_VLI CONSUMER_GROUP 0 0 0 0 100 0 0 0 0 0 0 0 100 0 0 0 Directive for P5_VEY_LOW_IMPORANCE NO
HOME_DBRM_PLAN OTHER_GROUPS CONSUMER_GROUP 0 0 0 0 0 100 0 0 0 0 0 0 0 100 0 0 Directive for P6_ALL_OTHER_GROUPS NO

7 rows selected.

SQL>

To check which user is granted which group use:
select * from DBA_RSRC_CONSUMER_GROUP_PRIVS;

Output is as:

SQL> l
1* select * from DBA_RSRC_CONSUMER_GROUP_PRIVS
SQL> /

GRANTEE GRANTED_GROUP GRA INI
------------------------------ ------------------------------ --- ---
PUBLIC DEFAULT_CONSUMER_GROUP YES YES
PRABGUN DOP_LI YES NO
SIMAR DOP_LI YES NO
SYSTEM SYS_GROUP NO YES
PUBLIC LOW_GROUP NO NO
SDIO DOP_VLI YES NO
GURPARTAP DOP_MI YES NO

7 rows selected.

SQL>

Check which service is given which consumer group use:

set line 300
select * from DBA_RSRC_GROUP_MAPPINGS;

Output is as:

SQL> l
1* select * from DBA_RSRC_GROUP_MAPPINGS
SQL> /

ATTRIBUTE VALUE CONSUMER_GROUP STATUS
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------ ------------------------------
SERVICE_NAME OLTPSVB2 DOP_HI
SERVICE_NAME OLTPSVB1 DOP_MI
SERVICE_NAME OLTPSV1 DOP_VHI
SERVICE_NAME HOMESV2 DOP_VLI
ORACLE_USER SYS SYS_GROUP
ORACLE_USER SYSTEM SYS_GROUP
ORACLE_FUNCTION BACKUP BATCH_GROUP
ORACLE_FUNCTION COPY BATCH_GROUP
ORACLE_FUNCTION DATALOAD ETL_GROUP

9 rows selected.

SQL>

To see your Calibration results check the following view:
select * from DBA_RSRC_IO_CALIBRATE;

To see which role in the database has privilege "" use:
select * from DBA_RSRC_MANAGER_SYSTEM_PRIVS;

Output is as:

SQL> select * from DBA_RSRC_MANAGER_SYSTEM_PRIVS;

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
DBA ADMINISTER RESOURCE MANAGER YES
EXP_FULL_DATABASE ADMINISTER RESOURCE MANAGER NO
IMP_FULL_DATABASE ADMINISTER RESOURCE MANAGER NO
APPQOSSYS ADMINISTER RESOURCE MANAGER NO
SYS ADMINISTER RESOURCE MANAGER NO

SQL>

To see all the resource plan's defined in the database use:
select * from DBA_RSRC_PLANS

MARU, SOLAHAS, FIFTH MEHL:

ONE UNIVERSAL CREATOR GOD. BY THE GRACE OF THE TRUE GURU:

The Primal, Immaculate Lord God is formless. The Detached Lord is Himself prevailing in all. He has no race or social class, no identifying mark. By the Hukam of His Will, He created the entire universe. || 1 || Out of all the 8.4 million species of be

ings, God blessed mankind with glory. That human who misses this chance, shall suffer the pains of coming and going in

reincarnation.

Gurpartap Singh's Blog

Tuesday, March 25, 2014

Post 57 of series : Using DBRM (Database Resource Manager) on RAC 11gR2

No comments:

Post a Comment