Tag: IO Resource Manager

Exadata and IORM by Examples

 

The Exadata Machine is frequently used to consolidate the database infrastructure, and such kind of environments must guarantee performance stability and governance. On Exadata the IO Resource Manager extends the capabilities available also on the other platforms to allocate, cap and prioritize the resources among databases and categories.

Available since the the first version of the Storage Cell software, IORM has been recently enhanced to cope with the new Multitenant and Cloud requirements.  The IORM Plan can optimize the workload with one of the following objectives: basic, auto, low_latency,  balanced or high_throughput.

 

I/O Resource Manager Overview

IORM allows to execute I/O Requests based on their priority, this is achieved handling separated queues which manage High and Low priority requests as shown on the image below.

 

IORM_Overview

 

Default IORM status

Automatically enabled it cannot be completely disabled. The default mode, protects critical operations like  flash cache and flash log  I/Os

CellCLI> list iormplan detail
name: tvdceladm06_IORMPLAN
catPlan:
dbPlan:
objective: basic
status: active

CellCLI>

 

Per Database IORM definition

This configuration is suitable on environments with a small number of databases, where the I/O resources are individually defined for each database.

alter iormplan objective=auto

ALTER IORMPLAN -
dbplan=((name=ERP01, level=1, allocation=75, limit=95, role=primary), -
(name=ERP01, level=1, allocation=5, limit=25, role=standby),          -
(name=TREP, level=1, allocation=2, limit=5, flashCacheSize=1G),       -
(name=EPA01, level=2, allocation=40, limit=80),                       -
(name=DHJ01, level=3, allocation=50, flashCacheSize=20G),             -
(name=other, level=3, allocation=30))

The above plan regulates: the database level, allocation (%), soft and hard limits (%), the amount of flash cache and the role (primary or standby).

 

DBaaS and IORM

This configuration is suitable for Cloud like environments, where a large number of databases are consolidated on the same infrastructure. The database services are standardized in few categories (for example Gold, Silver and Bronze) and the I/O resource plan regulates the same service categories.

CellCLI> ALTER IORMPLAN
dbplan=((name=gold, share=20,limit=100, type=profile), 
        (name=silver, share=10, limit=60, type=profile),
        (name=bronze, share=5, limit=20, type=profile))
The datase parameter db_performance_profile allows to associate the corresponding IORM service category to the instance:
SQL> alter system set db_performance_profile=silver scope=spfile;

New Resource Manager options on Oracle 12c R2 Muntitenant

Since its introductions Oracle Resource Manager has been a key tool to guarantee performance stability and predictability, regulating the access to the database resources.

In the era of extreme consolidation, the role of Oracle Resource Manager is more important then ever, and Oracle Multitenant has extended its functionalities.

In Oracle 12.1 the resource manager was capable to handle:

  • Parallel server processes
  • CPU

The version 12.2 the following options have been introduced:

  • I/O rate limits for PDBs (Not supported on Exadata enviroment where I/O Resource Manager exists)
  • Memory  Management
  • Performance Profiles
  • Resource Monitoring

 

How to limit a PDB I/O consumption

--Limiting the number of I/O per second.
Alter System set MAX_IOPS = 9500 scope = BOTH;

--Limiting the amount of MB per second.
Alter System set MAX_MBPS = 70 scope = BOTH;

 

Memory  Management

Optionally starting from Oracle 12.2 it is possible to limit the PDB memory utilization using the following parameters:

  • SGA_Target                       – Max SGA size for PDB
  • SGA_Min_Size                   – Amount of guaranteed SGA size for PDB.
  • DB_Cache_Size                  – Amount of guaranteed Buffer Cache size for PDB.
  • DB_Shared_Pool_Size      – Amount of guaranteed Shared Pool size for PDB.
  • PGA_Aggregate_Limit      – Max PGA size for PDB.
  • PGA_Aggregate_Target    – Target PGA size for PDB.

 

Performance Profiles

The Profiles allows to create standard and common  Resouce Profiles, based for example on the different SLA levels (platinum, gold, silver), to grant directly to the PDBs.

DBMS_RESOURCE_MANAGER.CREATE_CDB_PROFILE_DIRECTIVE(
 plan => 'PDB_Silver', 
 profile => 'silver', 
 shares => 2, 
 utilization_limit => 30, 
 parallel_server_limit => 20,
 memory_limit=> 30
);

 

Resource Monitoring

The new system view  V$RSRCPDBMETRIC can be used to monitor  chargeback the I/O and memory consumpion.

SELECT r.CON_ID, p.PDB_NAME, r.IOPS,r.SGA_BYTES, r.SHARED_POOL_BYTES
FROM V$RSRCPDBMETRIC r, CDB_PDBS p WHERE r.CON_ID = p.CON_ID;