- About Us
- Events and Webinars
- Contact Us
Exadata addresses this challenge with IO Resource Management.
Let’s show an example of DBRM:
Let’s say you’ve got a database called “DBM”, and let’s say you’ve got a consumer group for Order Management OLTP functions calls “OM OLTP”. This consumer group is and should be allowed to consume more database resources than anything else in the DBM database.
Let’s also imagine you’ve got another OLTP consumer group called “Other OLTP”, and finally, a consumer group called “Reporting” in the DBM database.
On another database, XBM, you’ve got an “online query” consumer group, and a “batch query” consumer group.
We won’t go into details here on how resources are allocated between each intradatabase resource plan, nor will be show our session mapping strategy, but later in this document we’ll show an example.
IORM extends DBRM using a concept of “categories”.
While consumer groups represent collections of users and their resource allocation within a database, categories represent collections of consumer groups across *all* databases.
In the diagram below, let’s say we have two categories: one for “interactive” sessions and one for “batch” sessions.
With IORM, you can specify IO precedence to consumer groups in the “interactive” category over consumer groups in the “batch” category.
IORM manages Exadata IO resources on a per-cell basis when IO requests begin to saturate the cell. When this happens, IORM begins scheduling IO requests according to configured resource plans.
First, the database sends IO requests to the Exadata storage cells. These requests are bundled in an iDB message and include metadata indicating their consumer group and optionally, resource category assigned to the IO request.
The requests are sent to different CELLSRV IO queues, based on the order in which they were sent to the storage cell.
The IO requests are then passed to from the CELLSRV IO queue to IORM; at which point any resource plans are also evaluated.
IORM then evaluates the IO requests from each of the “input” consumer groups and databases, validates their priority against the configured resource plans, and schedules the IO into the cell disk queues.
IORM is only “engaged” when needed.
In this section, we’ll plan our IORM test case.
Once we decided what we want our resource plans to look like, it’s helpful to diagram things so we know how much IO each type of operation will consume and/or take.
So assuming 100% IO allocation to the storage cell, the first thing IORM looks at is the category plans. In our case, we’re saying that we’ve got 70% allocated to the Interactive category and 30% allocation to batch.
Next, IORM looks at the interdatabase plan. In our test, we’re allocating 60% to XBM and 40% to DBM.
Next we look at consumer group resource allocation. In our case, we’ve got in DBM: 50% to “OM OLTP”, 30% to “OTHER OLTP”, and 20% to “REPORTING”. For XBM, we’ve got 70% allocated for ONLINE QUERY and 30% for batch query.
IORM uses a probability formula to then prioritize IO to the different consumer groups based on their consumer group percentage in the intradatabase plan, their database % in the interdatabase plan, and the category assignment in the category plan.
In our case, the math ends up looking like this:
We’ve got two databases sharing our Exadata quarter rack, DBM and XBM:
Now that we have an idea how we want to setup our intradatabase plans, interdatabase plans, interdatabase plan, and category plan, let’s start putting all the pieces together. I am going to use DBMS_RESOURCE_MANAGER in these sections, but the same could be done in Grid Control or Database Control.
When we have our intradatabase plans created on our DBM and XBM databases, we now have to enable an IORM plan on the Exadata cells. This is done using an “alter iormplan” statement in CELLCLI, and in our “complete” example, we’ll set an interdatabase “dbplan” as well as a “category” catplan.
The screen print below shows the script I’ll use with dcli to enable an IORM plan on each of the three cells:
Next we’ll map consumer groups in the DBM database:
And when done, we’ll validate:
You can monitor IORM from CELLCLI and understand resource consumption using metrics and statistics. The table above shows various important metrics that you can monitor.
The “DB_IO_RQ_[SM|LG]” shows (read the table) and it’s a good indication of which databases are generating the most load. In this an the rest of the metrics, “SM” = small IO (< 128k) and “LG” = > 128K.
Testing our IORM Plan
Below, we’ll use the intradatabase, interdatabase, and category plans that comprise our IORM plan and introduce two large workloads into our environment, one for DBM and one for XBM. We’ll full-scan our largest table in each environment, simultaneously, and measure the IORM-induced waits at the storage cell level:
As you can see, we’re seeing wait times for our databases once the IORM plan is in effect. The session in DBM is part of a resource consumer group that is allowed, via a category, less IO, so it’s waiting more for resources than our XBM session.
The test cases done so far have defined a relative resource allocation for each database by implementing an interdatabase resource plan. You can also set a ceiling on the maximum amount of IO resources a database can consume, regardless of cell load.
Let’s first check our current IORM plans in place:
Let’s run a test query while connected as SYSTEM to DBM:
Now let’s change this to make DBR have a limit of 10% of IO resources:
Now let’s re-run our previous query:
As you can see from the above, it took 10 times more time to execute than previously, which is evidence that our limiting criteria was in effect.
Also, look at our utilization percentage of disks when the second query ran – they’re lower than previously.
As you can see, we witnessed steady resource utilization that’s over 5% and no waits.
Centroid is a cloud services and technology company that provides Oracle enterprise workload consulting and managed services across Oracle, Azure, Amazon, Google, and private cloud. From applications to technology to infrastructure, Centroid’s depth of Oracle expertise and breadth of cloud capabilities helps clients modernize, transform, and grow their business to the next level.