You have bought Exadata after spending a fortune and more importantly after convincing everybody in the organization and in your family that this is the ultimate resource machine which hosts the world’s best database, Oracle 12c. You are not off the mark with those statements, though one might term them bit exaggerated. The point is that you have massive resources at your disposal and obviously your data needs are challenging too.
So when you have got the Exadata machine and you have imported your data on it and put it in the production, it’s very tempting that you want to consider it as black box and would want to dump everything in it. That’s not really fair with you, your data, your expectations and the Exadata itself. It’s not infinite and resources will always be scarce, no matter what marketing tells you.
So resource management is the best way to squeeze the juice out of Exadata in an efficient manner. Yes you can put humongus amount of data on Exadata, but you need to do it with proper resource management. Oracle enables you to do that in not-so-difficult manner. You have the Database resource manager at the database level and an I/O resource manager at the cell node level to manage the resources. You have paid for them big time, and you really need them and you should be using them.
Use Exadata Database DBRM
In every enterprise, there are some applications which are of utmost important, then there are some which are routine ones, and then there are some which are of low priority. This priority system can be directly implemented within the database and at the Cell level without resorting to arcane operating system throttling scripts or employing some weird application code, or using the brute force.
Every DBA knows that Database Resource Manager (DBRM) is there, yet very few use it. All Exadata experts know that IORM is there but sadly not many use it. IORM is new as is Exadata, but DBRM is not new, and it’s a shame that it’s usage is not prevalent. The reason which is often quoted for it’s scarce use is its complexity. The truth is that DBRM is not complex to understand or implement, it’s just that things are often not kept simple and straight when implementing it. Why start with multi-level resource plans and subplans and all those fancy attributes? Why not have a simple, one plan with fewer levels and a clear goal of resource distribution? Same applies to IORM at the cell level.
There is not much to the DBRM implementation. You just need to know a few definitions, command syntax, and then there are few views. As mentioned above, there are various application with different priorities. Your users basically connect to those applications rather than directly to the database in almost all the cases. So with one application, there are associated many sessions. These sessions associated with that application accomplish similar tasks in the databases, and so we group them together and name them “Consumer Group.” Then we assign resources to these consumer groups through the plan directives. We also group various plan directives and call them a Resource Plan. That’s it. These are the foundation blocks of the DBRM.
Oracle database already has the default DBRM settings. SYS and SYSTEM use consumer group SYS_GROUP and rest of the sessions by default use OTHERS_GROUP.
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_CONSUMER_GROUP (
CONSUMER_GROUP => 'CriticalApps',
COMMENT => 'Top Priority applications');
END;
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN(
PLAN => 'SimplePlan',
COMMENT => 'Plan for the Apps');
END;
BEGIN
DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE (
PLAN => 'SimplePlan',
GROUP_OR_SUBPLAN => 'CriticalApps',
COMMENT => 'Critical Apps',
MGMT_P1 => 75);
END;
The important thing here is the MGMT_n management attribute. This is used to control CPU among consumer groups on basis of levels and percentages. The key here is that the unused and unallocated CPU is passed onto the next level.
Visit the Oracle Exadata page for more Articles.