The purpose of this blog is to show how different performance strategies can be implemented using the Oracle Database Resource Manager. Some practical examples are looked at to illustrate the point.
Resource Manager is designed to maximize throughput, so the resource plan directive given is no hard limit. In a CPU constrained system, we can allocate more CPU resources to OLTP users than to batch-jobs. With Resource Manager, the DBA can plan for a more proactive and predictable performance perspective – meeting service level agreements
In previous blog “2014-01 – The Oracle Resource Manager“ we created our customer resource manager plan called ‘Z_DEFAULT_PLAN’.
The plan directives were set as follows:
SQL> SET LINESIZE 100
SQL> COL plan FORMAT a15
SQL> COL group_or_subplan FORMAT a15
SQL> SELECT plan,
2 group_or_subplan,
3 type,
4 cpu_p1,
5 status
6 FROM dba_rsrc_plan_directives
7 WHERE plan='Z_DEFAULT_PLAN'
8 ORDER BY 1,2,3,4;
PLAN GROUP_OR_SUBPLA TYPE CPU_P1 STATUS
--------------- --------------- -------------- ---------- ----------
Z_DEFAULT_PLAN OTHER_GROUPS CONSUMER_GROUP 5
Z_DEFAULT_PLAN SYS_GROUP CONSUMER_GROUP 15
Z_DEFAULT_PLAN Z_TEST_APPL CONSUMER_GROUP 30
Verify if Z_DEFAULT_PLAN is turned on:
SQL> SELECT name, is_top_plan
2 FROM v$rsrc_plan
3 WHERE is_top_plan = 'TRUE';
NAME IS_TO
-------------------------------- -----
DEFAULT_MAINTENANCE_PLAN TRUEWe want to activate the Z_DEFAULT_PLAN immediately as our default plan:
SQL> ALTER SYSTEM SET resource_manager_plan = z_default_plan;
System altered.
SQL> SELECT name, is_top_plan
2 FROM v$rsrc_plan
3 WHERE is_top_plan = 'TRUE';
NAME IS_TO
-------------------------------- -----
Z_DEFAULT_PLAN TRUEWe will need some executable code capable to load the CPU close to 100% (CPU BOUND).
The “TEST” database user will be used to perform the practical samples.
Create the following PL/SQL store procedure as sample code:
SQL> CREATE OR REPLACE PROCEDURE test.z_test AS
2 t_inicio DATE;
3 elapsed_time NUMBER DEFAULT 0;
4 t_final DATE;
5 contador NUMBER DEFAULT 0;
6 BEGIN
7
8 dbms_output.put_line(' ') ;
9 dbms_output.put_line('executing z_test ...') ;
10
11 t_inicio := sysdate ;
12 dbms_output.put_line('Process Start: ') ;
13 dbms_output.put_line(to_char(t_inicio,'yyyy-mm-dd hh24:mi:ss')) ;
14
15 /* test code - begin */
16
17 WHILE elapsed_time < 20 LOOP
18 t_final := SYSDATE;
19
20 elapsed_time := (t_final - t_inicio)*24*60*60;
21 contador := contador + 1 ;
22
23 END LOOP;
24 /* test code - end */
25
26 dbms_output.put_line('Process End: ');
27 dbms_output.put_line(to_char(t_final,'yyyy-mm-dd hh24:mi:ss')) ;
28 dbms_output.put_line(' ');
29 dbms_output.put_line('------------------------------------------- ');
30 dbms_output.put_line('Summary: ');
31 dbms_output.put_line('Elapsed Time: '|| round(elapsed_time)) ;
32 dbms_output.put_line('Count#: '|| contador) ;
33 dbms_output.put_line(' ');
34 dbms_output.put_line('------------------------------------------- ');
35
36 EXCEPTION
37 WHEN OTHERS THEN
38 dbms_output.put_line('------------------------------------------- ');
39 dbms_output.put_line('Errors during execution.. ');
40 dbms_output.put_line('------------------------------------------- ');
41
42 ROLLBACK;
43 END;
44 /
Procedure created.The Z_TEST program is a CPU intensive PL/SQL – procedure with little or no I/O. The test is done by running one session of the program, with a duration of 20 seconds. This loads the CPU close to 100%.
Execute the code and monitor system load.
SQL> CONNECT test/test
Connected.
SQL> SET TIMING ON
SQL> SET SERVEROUTPUT ON
SQL> execute z_test
executing z_test ...
Process Start:
2014-04-26 21:35:55
Process End:
2014-04-26 21:36:16
-------------------------------------------
Summary:
Elapsed Time: 21
Count#: 4476654
-------------------------------------------
PL/SQL procedure successfully completed.
Elapsed: 00:00:20.19In a Linux environment, you can monitor system load executing the “top -c” command.
Tasks: 217 total, 2 running, 215 sleeping, 0 stopped, 0 zombie
Cpu(s): 95.0%us, 5.0%sy, 0.0%ni, 0.0%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 4208812k total, 4159116k used, 49696k free, 33152k buffers
Swap: 3145724k total, 2960k used, 3142764k free, 3098520k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
11159 oracle 20 0 1047m 58m 54m R 92.4 1.4 1:47.37 oracledb01For this execution CPU load was almost 95%.
Now we will perform “CPU CAPPING” for “TEST” user.
Using an administrator account assign “TEST” user to “Z_TEST_APPL” consumer group and set this group as the initial consumer group. Perform this operation as follows:
SQL> BEGIN
2 dbms_resource_manager_privs.grant_switch_consumer_group(
3 grantee_name => 'TEST',
4 consumer_group => 'Z_TEST_APPL',
5 grant_option => FALSE
6 );
7 END;
8 /
PL/SQL procedure successfully completed.
SQL> BEGIN
2 dbms_resource_manager.set_initial_consumer_group(
3 user => 'TEST',
4 consumer_group => 'Z_TEST_APPL'
5 );
6 END;
7 /
PL/SQL procedure successfully completed.Connect again with “TEST” user and verify the name of the session’s current resource consumer group.
SQL> SELECT username, resource_consumer_group
2 FROM v$session
3 WHERE username = 'TEST';
USERNAME RESOURCE_CONSUMER_GROUP
------------------------------ --------------------------------
TEST Z_TEST_APPLForce “CPU CAPPING” by updating Z_DEFAULT_PLAN directives. Update Z_TEST_APPL to use only 30% of available CPU.
SQL> EXEC dbms_resource_manager.create_pending_area;
PL/SQL procedure successfully completed.
SQL> BEGIN
2 dbms_resource_manager.update_plan_directive(
3 'Z_DEFAULT_PLAN', 'Z_TEST_APPL', NEW_MAX_UTILIZATION_LIMIT=>30);
4 END;
5 /
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.As “TEST” database user execute the sample code again:
SQL> execute z_test
executing z_test ...
Process Start:
2014-04-26 21:45:55
Process End:
2014-04-26 21:46:16
-------------------------------------------
Summary:
Elapsed Time: 20
Count#: 4476754
-------------------------------------------
PL/SQL procedure successfully completed.
Elapsed: 00:00:20.53And monitor CPU usage:
Tasks: 226 total, 1 running, 225 sleeping, 0 stopped, 0 zombie
Cpu(s): 29.2%us, 4.9%sy, 0.0%ni, 65.9%id, 0.0%wa, 0.0%hi, 0.0%si, 0.0%st
Mem: 4208812k total, 3346332k used, 862480k free, 104092k buffers
Swap: 3145724k total, 0k used, 3145724k free, 2222028k cached
PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
14532 oracle 20 0 1045m 27m 25m S 29.5 0.7 0:10.08 oracledb01For this execution CPU load was only 30%.
Oracle’s Database Resource Manager is no compensation for a poorly tuned system, but given an overloaded system, it provides the DBA the ability to manage the total amount of available processing resources amongst concurrent user sessions or groups of user sessions based on importance.
Link to script that contains the examples in this post.