Monday, January 16, 2012

I/O Resource Management(IORM) with Exadata

I/O Resource Management(IORM) with Exadata

a)
-- How to configure IORM on cell nodes.


ODS and EDW- Level 1 (40%+40%=80%)
All other(EDWSTG and any other) - Level 2 (100% - that's nothing but overall 20%)
Note: IORM have 8 levels like DBRM.


-- To create IORM, need to create the IORMPLAN on each cell node
CellCLI> alter iormplan dbplan=((name='ODS', level=1, allocation=40),(name='EDW', level=1, allocation=40),(name=other,level=2,allocation=100))
IORMPLAN successfully altered

-- How to Activate
CellCLI> alter iormplan active

-- How to Inactivate
CellCLI> alter iormplan inactive

-- Check if the IORM is Active?
CellCLI> LIST IORMPLAN
atl02cel02_IORMPLAN active

-- Check if current IORM plan in place?
CellCLI> LIST IORMPLAN detail
name: atl02cel01_IORMPLAN
catPlan:
dbPlan: name=ODS,level=1,allocation=40
name=EDW,level=1,allocation=40
name=other,level=2,allocation=100
status: active


b)
-- Monitoring IORM with cellcli command.


-- How to check Small IO load per database
CellCLI> LIST METRICCURRENT where name=DB_IO_RQ_SM
DB_IO_RQ_SM EDW 7,387,543 IO requests
DB_IO_RQ_SM ODS 5,262,853 IO requests
DB_IO_RQ_SM EDWSTG 11,526,325 IO requests

-- How to check Large IO load per database
CellCLI> LIST METRICCURRENT where name=DB_IO_RQ_LG
DB_IO_RQ_LG EDW 13,210,476 IO requests
DB_IO_RQ_LG ODS 5,865,946 IO requests
DB_IO_RQ_LG EDWSTG 4,644,494 IO requests


-- How to check Small IO load/sec. per database, last minute
CellCLI> LIST METRICCURRENT where name=DB_IO_RQ_SM_SEC
DB_IO_RQ_SM_SEC EDW 14.5 IO/sec
DB_IO_RQ_SM_SEC ODS 86.5 IO/sec
DB_IO_RQ_SM_SEC EDWSTG 135 IO/sec

-- How to check Large IO load/sec. per database, last minute
CellCLI> LIST METRICCURRENT where name=DB_IO_RQ_LG_SEC
DB_IO_RQ_LG_SEC EDW 54.4 IO/sec
DB_IO_RQ_LG_SEC ODS 47.0 IO/sec
DB_IO_RQ_LG_SEC EDWSTG 73.5 IO/sec


-- How to check Small IO Waits per database. per min.
CellCLI> LIST METRICCURRENT where name=DB_IO_WT_SM
DB_IO_WT_SM EDW 35,576,746 ms
DB_IO_WT_SM ODS 5,686,954 ms
DB_IO_WT_SM EDWSTG 7,236,957 ms

-- How to check Large IO Waits per database. per min.
CellCLI> LIST METRICCURRENT where name=DB_IO_WT_LG
DB_IO_WT_LG EDW 2,284,982,657 ms
DB_IO_WT_LG ODS 384,891,454 ms
DB_IO_WT_LG EDWSTG 394,093,606 ms