In this article we will look at the new features introduced in Resource Manager of Oracle database 11g. Just to list them first here are the four new features in resource manager. This article will be about these new features.
ORA-56708: Could not find any datafiles with asynchronous i/o capability
You can turn Asynchronous I/O for the system using the following command.
ALTER SYSTEM SET FILESYSTEMIO_OPTIONS=ASYNC SCOPE=SPFILE;
You can also use SETALL as a value for FILESYSTEMIO_OPTIONS to turn Asynchronous I/O on your system. You will have to restart the database as parameter is static.
The DBMS_RESOURCE_MANAGE.CALIBRATE_I/O takes two parameters. One is physical disks which defaults to 1 and the other is maximum tolerable latency which is 20ms. On successful completion it returns maximum I/O per seconds, maximum MBs per second and actual latency. Only user with SYSDBA privileges can run this procedure. This should be run in off peak loads so that the calibration does not affect the production workload and production workload does not affect the calibration.
You can check the status of running calibration in v$IO_CALIBRATION_STATUS view. The status is “In Progress” when calibration is running and turns into “Ready” when it is complete. The DBA_RSRC_IO_CALIBRATE view will give you information about all calibrations and their results along with Start Time and End Time.
[title type=”h2″ class=”tfuse”]Per Session I/O Limits:[/title] Before 11g there was only one measurement against a session before an action can be taken like killing or changing its resource plan and that was for how much time the session is in progress. With Oracle 11g there are two more limits that can put on a session and if session reaches those limits then a suitable action can be taken against it. On is number of I/O requests and the other is I/O in mega bytes. For example if you used to have the restriction that once a session exceeds the 60 seconds of elapsed time to switch it to Batch resource plan from OLTP resource plan. Now you can set a similar limit of like 512 I/O requests and 4MB of I/O.
These new limitations provide two advantages. One helps to keep track of runaway queries and other helps to see which sessions have fever I/O requests but data in those requests is of huge size. Both may may be affecting particular parts of production workload. The three new limits are provided using following parameters.
[title type=”h2″ class=”tfuse”]AWR Statistics for Resource Manager[/title] There are two new views for AWR statistics gathered for resource manager. Both views are created to make the history data available. The views DBA_HIST_RSRC_PLAN and DBA_HIST_RSRC_CONSUMER_GROUP contain the history data for respective views i.e. V$RSRC_PLAN_HISTORY and V$RSRC_CONS_GROUP_HISTORY.
[title type=”h2″ class=”tfuse”]Built In Resource Plans[/title] In 11g Oracle has introduced a new built in resource plan named MIXED_WORKLOAD_PLAN. The purpose of introducing this is to give OLTP transactions precedence over Batch transactions. This plan has two consumer groups named INTERACTIVE_GROUP and BATCH_GROUP. You have to set the system parameter RESOURCE_MANAGER_PLAN’s value to “MIXED_WORKLOAD_PLAN” for this to work.
ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = ’MIXED_WORKLOAD_PLAN’;
Once set every new call is assigned by default the INTERACTIVE_GROUP and is moved to BATCH_GROUP once call exceeds 60 seconds.
A DBA can check the default consumer group of user using the following command.
SELECT INITIAL_RSRC_COSUMER_GROUP FROM DBA_USERS WHERE USER = ‘FOO’;
A logged in user can check his current consumer group by issuing the following command.
WHERE AUDSID = SYS_CONTEXT('USERENV', 'SESSIONID');