ARTICLE YET TO BE REVIEWED AND IMPROVED
Prior to 11g consumers were mapped with consumer goups and the main resource plan directive was CPU. According to the consumer group a given session was mapped CPU resources were granted or not.
New in 11G is that the DBA has the choice between CPU based or IO based resource plan directives. A prerequisite is that async IO is enabled. The instance initialization parameter filesystemio_options needs to be set to SETALL or ASYNC
Resource plan directives can be IO constrained. ( see dbms_resource_manager package )
NAME TYPE VALUE
filesystemio_options string SETALL
Initially the DBA needs to run the dbms_resource_manager.calibrate_io procedure to capture io throughput info from the IO subsystem
2 max_iopersec pls_integer;
3 max_mbpersec pls_integer;
4 latency pls_integer;
5 begin
6 dbms_resource_manager.calibrate_io(
7 num_physical_disks=>1,
8 max_latency =>20,
9 max_iops =>max_iopersec,
10 max_mbps =>max_mbpersec,
11 actual_latency =>latency);
12 dbms_output.put_line('max io per sec ='
max_iopersec); |
13 dbms_output.put_line('max Mb per sec =' |
max Mb per sec =35
latency =19
Note there are several built in resource plans available in 11G, they can easily be activated using the GUI