The Oracle Exadata IO Resource Manager Limit Clause, part 2

In my previous post I described how the IO resource manager (IORM) in the exadata storage can be used both to guarantee a minimum amount of IO a database can get (which is what is covered in most material available), but also to set a maximum amount of IO. This is what Oracle calls an inter-database resource manager plan. This is set and configured at the cell level using the cellcli with ‘alter iormplan dbplan’.

But Randy Johnson (who is one of the authors of the upcoming book ‘Expert Oracle Exadata’) commented this is not the only way to limit IO: this can be done using the database resource manager (DBRM) too. This means you can set a limit on the CPU resource usage on the database layer, which gets pushed to the storage layer and enforces IO limits according to the limit set on the database layer. Interesting!

Let’s try it!

Here’s a simple query without any resource management:

SQL> select count(*) from cg_var;

COUNT(*)
----------
1596587000

Elapsed: 00:00:44.00


Now create a simple resource manager plan, with one resource group which limits usage to 1%:

BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();

DBMS_RESOURCE_MANAGER.CREATE_PLAN(
PLAN => 'TESTPLAN',
COMMENT => 'Testplan! Limit a user to 1 percent');

DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
CONSUMER_GROUP => 'LIMITED',
COMMENT => 'This is a limited group');

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
PLAN => 'TESTPLAN',
GROUP_OR_SUBPLAN => 'LIMITED',
COMMENT => 'This group is limited to 1 percent',
MAX_UTILIZATION_LIMIT => 1);

DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
PLAN => 'TESTPLAN',
GROUP_OR_SUBPLAN => 'OTHER_GROUPS',
COMMENT => 'Mandatory group');

DBMS_RESOURCE_MANAGER.SET_CONSUMER_GROUP_MAPPING
(DBMS_RESOURCE_MANAGER.ORACLE_USER, 'CG', 'LIMITED');

DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

Mind the “MAX_UTILIZATION_LIMIT => 1”! Using this plan, all sessions in this database are not limited, except for the Oracle User ‘CG’, which has a maximal utilization limit of 1 percent.

Now grant access to the resource group ‘LIMITED’ for the user ‘CG’:

BEGIN
DBMS_RESOURCE_MANAGER_PRIVS.grant_switch_consumer_group(
grantee_name => 'CG',
consumer_group => 'LIMITED',
grant_option => FALSE);
END;
/


And activate the resource manager plan:

ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'TESTPLAN' sid='*';


And try the same simple SQL again:

SQL> select count(*) from cg_var;

COUNT(*)
----------
1596587000

Elapsed: 00:36:10.21


This is clearly limited!

The resource management is done on the cell/storage layer. This means the waitevents do not indicate resource management (!). When looking at the waits, the process is mostly waiting for the ‘cell smart table scan’ event.

The Oracle Exadata IO Resource Manager Limit Clause

The Exadata IO Resource Manager (IORM) is a fundamental piece of the exadata database machine since the beginning. The function of the IORM is to guarantee a database and/or category (set via the database resource manager) gets the share of IO as defined in the resource plan at each storage server. The “share” here means the minimal amount of IO. This is widely known and documented in the Exadata documentation. Having a minimal amount of IO means you can guarantee a service level agreement (SLA).

But what if you want to define the maximal amount of IO a database may get? The case of a maximal amount of IO a database can get is that you can use a database on Exadata, and do not (or very limited) change the performance once more databases get onto the same database machine. So instead of having the minimal amount of IO a database must get guaranteed (which is what is documented in the exadata documentation), having a limit on the maximal amount a database can get?

This is possible through the ‘limit’ clause of the database resource plan (this option is not available to the category plan clause). This clause is not found in the documentation available to me, but is listed when calling the help function in cellcli for the ‘alter iormplan’ command.

How does such a plan look like?

CellCLI> list iormplan detail
name: dm01cel01_IORMPLAN
catPlan:
dbPlan: name=vxone,level=1,allocation=50,limit=10
name=other,level=1,allocation=50
status: active


This is the IO resource manager plan on one of the cells in a database machine.
What we see here is a plan which says we have a database plan, which says the database ‘vxone’ must always get 50% of all IO capacity at a minimum, and all others (name=other) also get 50%. But now the purpose of this blog: I’ve limited the database ‘vxone’ to only get 10%!

Does it really work? A good practice is to check all things you are told. Especially if it’s something which is widely unknown, and not in the documentation.

Let’s scan a large table without any limit set:

SQL> select count(*) from cg_var;

COUNT(*)
----------
1596587000

Elapsed: 00:00:45.97


This is the same SQL, but now with the limit set to 10% (as seen in the iormplan listed above):

SQL> select count(*) from cg_var;

COUNT(*)
----------
1596587000

Elapsed: 00:02:27.28

It is clear the limit clause limits the IO capability of this database: scanning the same database using the same SQL on the same table differs! I’ve ran the tests a few times to see if (both) times are consistent, and they are!