Resource Manager in CDB & PDB
In CDB RM can manage resource on two levels.
CDB Level :
Can manage the workloads for multiple PDB’s that are contending for
system and CDB resources.
PDB Level : Manage resource within the PDB.
PDB compete for resources: - CPU / Exadata I/O and
Parallel Servers.
This is done by granting different shares according
to the priority of the PDB’s. More shares can be
allocated for the important PDB’s.
No Consumer group nor shares can be defined for root
container.
Eg:-
CDB running with two PDB , each pdb is allocated
with 1 share.
PDB1 -> (Share = 1/2 ( 50% Guaranteed CPU), limited
to 100% CPU )
PDB2 -> (Share = 1/2 ( 50% Guaranteed CPU),
limited to 100% CPU )
Now another 2 PDBS are added then
PDB1 -> (Share = 1/4 ( 25% Guaranteed CPU),
limited to 100% CPU )
PDB2 -> (Share = 1/4 ( 25% Guaranteed CPU), limited
to 100% CPU )
PDB3 -> (Share = 1/4 ( 25% Guaranteed CPU),
limited to 100% CPU )
PDB4 -> (Share = 1/4 ( 25% Guaranteed CPU),
limited to 100% CPU )
Say PDB1 is very important and we have allocated 2
shares then total share = (2+1+1+1 =5)
PDB1 -> (Share = 2/5 ( 40% Guaranteed CPU),
limited to 100% CPU )
PDB2 -> (Share = 1/5 ( 20% Guaranteed CPU),
limited to 100% CPU )
PDB3 -> (Share = 1/5 ( 20% Guaranteed CPU), limited
to 100% CPU )
PDB4 -> (Share = 1/5 ( 20% Guaranteed CPU),
limited to 100% CPU )
The Maximum limits can be controlled using the below
parameters
Utilization_limit
( To control the maximum utilization of resource of a PDB)
Parallel_server_limit ( To override
utilization_limit only for parallel servers)
Eg.
Utilization_limit
= 30 ( Then the maximum system
resource used by this PDB will be 30%)
Parallel_server_limit = 50 ( If the parallel_server_target is set = 200
then this limit says
that 50% of parallel
servers to be used i.e. 100 )
These are the values corresponding to the default
directives that is automatically added to your plan.
It can be changed using dbms_resource_manager
package using update_cdb_default_directive procedure.
Eg.
PDB1 (2 Shares / utilization set to 50%, parallel_server_limit set to 50% (parallel
target set as 200) then
->
(Share = 2/5 (40% Guaranteed CPU), limited to 50% CPU / ExaI/O, parallel server
limited to 100)
PDB2 (1 Shares / utilization set to 30%, parallel_server_limit set to 30% (parallel
target set as 200) then
-> (Share = 1/5 (20%
Guaranteed CPU), limited to 30% CPU / ExaI/O, parallel server limited to 60)
PDB3 (1 Shares / utilization set to 20%, parallel_server_limit set to 20% (parallel
target set as 200) then
-> (Share = 1/5 (20%
Guaranteed CPU), limited to 20% CPU / ExaI/O, parallel server limited to 40)
PDB4 2 Shares / utilization set to 20%, parallel_server_limit set to 20% (parallel
target set as 200) then
-> (Share = 1/5 (20% Guaranteed
CPU), limited to 20% CPU / ExaI/O, parallel server limited to 40)
Autotask allocation default is (-1 share) which is
20% of system resource ,utilization limit is 90
And parallel limit is 100%. We can change it
accordingly.
Eg.
SQL> show con_name;
CON_NAME
------------------------------
CDB$ROOT
SQL> select name, open_mode from V$pdbs;
NAME OPEN_MODE
------------------------------ ----------
PDB$SEED READ ONLY
PDB_PURCHASE READ WRITE
PDB_HR READ WRITE
SQL> exec
dbms_resource_manager.CREATE_PENDING_AREA;
PL/SQL procedure successfully completed.
SQL> exec dbms_resource_manager.CREATE_CDB_PLAN
(plan => 'PLAN_FOR_PDBS', comment => 'RM PLANS for PDBS');
PL/SQL procedure successfully completed.
SQL> begin
2
dbms_resource_manager.CREATE_CDB_PLAN_DIRECTIVE
3 (
4 PLAN => 'PLAN_FOR_PDBS',
5 PLUGGABLE_DATABASE =>
'PDB_PURCHASE',
6 SHARES => 4,
7 UTILIZATION_LIMIT => NULL,
8 PARALLEL_SERVER_LIMIT =>
NULL
9 );
10 end;
11 /
PL/SQL procedure successfully completed.
SQL> begin
2 dbms_resource_manager.CREATE_CDB_PLAN_DIRECTIVE
3 (
4 PLAN => 'PLAN_FOR_PDBS',
5 PLUGGABLE_DATABASE =>
'PDB_HR',
6 SHARES => 1,
7 UTILIZATION_LIMIT => 20,
8 PARALLEL_SERVER_LIMIT =>
20
9 );
10 end;
11 /
PL/SQL procedure successfully completed.
SQL> exec
dbms_resource_manager.VALIDATE_PENDING_AREA;
PL/SQL procedure successfully completed.
SQL> exec dbms_resource_manager.SUBMIT_PENDING_AREA;
PL/SQL procedure successfully completed.
SQL> alter system set resource_manager_plan =
'PLAN_FOR_PDBS';
System altered.
SQL> select PLAN, PLUGGABLE_DATABASE, SHARES,
UTILIZATION_LIMIT, PARALLEL_SERVER_LIMIT from dba_cdb_rsrc_plan_directives;
PLAN
PLUGGABLE_DATABASE SHARES
UTILIZATION_LIMIT PARALLEL_SERVER_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
PLAN_FOR_PDBS
ORA$DEFAULT_PDB_DIRECTIVE 1 100 100
PLAN_FOR_PDBS ORA$AUTOTASK
90 100
PLAN_FOR_PDBS PDB_PURCHASE 4
PLAN_FOR_PDBS PDB_HR
1 20 20
12 rows selected.
Lets test it..
Vi insert_pdb_purchase.sh
export ORACLE_HOME=/oracle/product/12.1.0.2/db_1
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus -s manzoor/ahamed@pdb_purchase <<EOF
> pdb_purchase_test.log
set timing on;
set time on;
begin
for i
in 1..100000 loop
insert into emp values (i, dbms_random.string('U',30), 10000+i);
end loop;
commit;
end;
/
exit
EOF
Save and close above script.
Vi insert_pdb_hr_test.sh
export ORACLE_HOME=/oracle/product/12.1.0.2/db_1
export PATH=$ORACLE_HOME/bin:$PATH
sqlplus -s manzoor/ahamed@pdb_purchase <<EOF
> pdb_purchase_test.log
set timing on;
set time on;
begin
for i
in 1..100000 loop
insert into emp values (i, dbms_random.string('U',30), 10000+i);
end
loop;
commit;
end;
/
exit
EOF
Save and close above script.
[oracle@rhel11gr2rac1 ~]$ cat insert_pdb_hr_test.sh
nohup /home/oracle/insert_pdb_purchase.sh &
nohup /home/oracle/insert_pdb_hr.sh &
nohup ./insert_pdb_hr_test.sh &
[oracle@rhel11gr2rac1 ~]$ cat pdb_purchase_test.log
PL/SQL procedure successfully completed.
Elapsed: 00:00:46.06
[oracle@rhel11gr2rac1 ~]$ cat pdb_hr_test.log
PL/SQL procedure successfully completed.
Elapsed: 00:02:04.27
We could see that script is completed in 46 sec in
pdb_purchase db, whereas in pdb_hr it took 2 mins and 4 secs
SQL> select con_id, sql_id, sql_text,
(cpu_time/1000000)/executions "Avg CPU Time",
(ELAPSED_TIME/1000000)/executions "Execution Time", executions from V$sql where
PARSING_SCHEMA_NAME = 'MANZOOR'
CON_ID
SQL_ID SQL_TEXT Avg
CPU Time Execution Time EXECUTIONS
---------- -------------
-------------------------------------------------- ------------ --------------
----------
3
049v557vkq39j begin for i in 1..100000
loop insert into emp v 25.007198 46.049448 1
alues (i,
dbms_random.string('U',30), 10000+i); e
nd loop; commit; end;
4 049v557vkq39j begin for i in 1..100000 loop insert into emp v 24.877219 124.257585 1
alues (i,
dbms_random.string('U',30), 10000+i); e
nd loop; commit; end;
As from the query you can see that the execution
time take much longer in container 4 which is pdb_hr.
CON_ID SID SERIAL# PROGRAM EVENT
---------- ---------- ----------
------------------------------------------------ -------------------------
4 59 2970 sqlplus@rhel11gr2rac1.manzoor.com
(TNS V1-V3) resmgr:cpu quantum
3 85 143 sqlplus@rhel11gr2rac1.manzoor.com
(TNS V1-V3) db file sequential read
SQL> select con_id, plan_name,
to_char(begin_time, 'HH:MI') time,
60 * (select value from v$osstat where stat_name =
'NUM_CPUS') total,
60 * (select value from v$parameter where name =
'cpu_count') db_total,
sum(cpu_consumed_time) / 1000 consumed,
sum(cpu_wait_time) / 1000 throttled
from gv$rsrcmgrmetric_history
group by con_id, plan_name, begin_time order by
begin_time;
CON_ID
PLAN_NAME TIME TOTAL
DB_TOTAL CONSUMED THROTTLED
---------- ------------------------------ -----
---------- ---------- ---------- ----------
1
PLAN_FOR_PDBS 09:10
60 60 .081 0
2
09:10 60 60
0 0
3
09:10 60 60
13.291 .987
4 09:10 60 60
11.069 48.875
1
PLAN_FOR_PDBS 09:11 60 60
1.322 0
2
09:11 60 60
0 0
3 09:11 60 60
0 0
4
09:11 60 60
10.159 38.797
1
PLAN_FOR_PDBS 09:12 60 60
.134 0
2
09:12 60 60
0 0
3
09:12 60 60
0 0
4
09:12 60
60 0 0
From the above metric we could see that container 4
is throttled and container 3 is not during the execution time.
Managing Resouce within PDB.
Its is same like managing resource in non-cdb except
below.
Non-CBD PDB
Multilevel resource plans Single level resource plan only
Upto 32 consumer groups upto 8 consumer groups
Subplans No
subplans
No comments:
Post a Comment