IO HOT SPOT EXADATA STORAGE LAYOUT
Default allocation policy:
In exadata celldisks ,outer region is faster and inner region is slower. So oracle recommends to create griddisks which require high IOPS on the edge of the disk platter and other diskgroups which are non critical just for storing historical archive data or non critical data which are touched not too often from disk.
By default,oracle creates the griddisks based on FIFO (First in first out). If you create griddisk for DATA diskgroup allocation first , then DATA diskgroup lie on the fastest disk spindle.
To get maximum performance out of exadata, the objects which are frequently touched from the disk like tables,indexes,redologs etc should be kept in the high priority disks
Low priority disks can be used for FRA purpose,backups and reporting objects etc
There are two strategy for disk provisioning and layout
Based on the objective of application end goal and objective, these two layouts can be chosen
- Default policy
- Interleave policy
Unless you have a reason to choose Interleave policy, dont choose it for regular performance. As every applications objective would be faster response time and from database perception, tables,indexes and redologs should have faster IO response rather than archives,backup and other obsoleted data.
Default policy gives faster access and priority to only one diskgroup which is created on top.
Interleave policy gives faster priority to atleast two diskgroups like DATA,RECO which is somewhat balanced speed for data retrieve and write
Check the diskgroup,celldisk and griddisk
SQL> select dg.name as DISKGROUP,regexp_substr(d.name,'_\S+') as CELLDISK,d.name as GRIDDISK from v$asm_diskgroup dg,v$asm_disk d where dg.group_number = d.group_number and dg.name = 'DATA' order by 1,2;
DISKGROUP CELLDISK GRIDDISK
-------------------- -------------------- --------------------
DATA _CD_CELL1_STOCELL DATA_CD_CELL1_STOCEL
L
DATA _CD_CELL2_STOCELL DATA_CD_CELL2_STOCEL
L
DATA _CD_CELL3_STOCELL DATA_CD_CELL3_STOCEL
L
DATA _CD_CELL4_STOCELL DATA_CD_CELL4_STOCEL
L
DISKGROUP CELLDISK GRIDDISK
-------------------- -------------------- --------------------
DATA _CD_CELL5_STOCELL DATA_CD_CELL5_STOCEL
L
DATA _CD_CELL6_STOCELL DATA_CD_CELL6_STOCEL
L
6 rows selected.
You have all the cell stats in your hands to diagnose or analyse the performance which can be used to detect the IOPS from disk
These are the important metrics which should be noted to detect IO’s per read and write
If you observe all IO read metrics for DATA diskgroup, which is located at the faster corner track of the disk has huge IO requests with turbo spin 🙂
Number of requests to write small blocks to celldisk
CellCLI> list metriccurrent attributes name,alertState,collectionTime,metricObjectName,metricValue where name='CD_IO_RQ_W_SM';
CD_IO_RQ_W_SM normal 2021-04-29T20:28:07+05:30 CD_cell01_stocell 11,462 IO requests
CD_IO_RQ_W_SM normal 2021-04-29T20:28:07+05:30 CD_cell02_stocell 3,996 IO requests
CD_IO_RQ_W_SM normal 2021-04-29T20:28:07+05:30 CD_cell03_stocell 20,298 IO requests
CD_IO_RQ_W_SM normal 2021-04-29T20:28:07+05:30 CD_cell04_stocell 3,864 IO requests
CD_IO_RQ_W_SM normal 2021-04-29T20:28:07+05:30 CD_cell05_stocell 3,840 IO requests
CD_IO_RQ_W_SM normal 2021-04-29T20:28:07+05:30 CD_cell06_stocell 3,484 IO requests
CD_IO_RQ_W_SM normal 2021-04-29T20:28:07+05:30 CD_cell07_stocell 1,105 IO requests
CD_IO_RQ_W_SM normal 2021-04-29T20:28:07+05:30 CD_cell08_stocell 1,751 IO requests
CD_IO_RQ_W_SM normal 2021-04-29T20:28:07+05:30 CD_cell09_stocell 1,147 IO requests
CD_IO_RQ_W_SM normal 2021-04-29T20:28:07+05:30 CD_cell10_stocell 10,688 IO requests
CD_IO_RQ_W_SM normal 2021-04-29T20:28:07+05:30 CD_cell11_stocell 18,439 IO requests
CD_IO_RQ_W_SM normal 2021-04-29T20:28:07+05:30 CD_cell12_stocell 61,334 IO requests
CD_IO_RQ_W_SM normal 2021-04-29T20:28:07+05:30 FD_00_stocell 3,834 IO requests
CD_IO_RQ_W_SM normal 2021-04-29T20:28:07+05:30 FD_01_stocell 3,639 IO requests
CD_IO_RQ_W_SM normal 2021-04-29T20:28:07+05:30 FD_02_stocell 11,625 IO requests
CD_IO_RQ_W_SM normal 2021-04-29T20:28:07+05:30 FD_03_stocell 3,473 IO requests
CD_IO_RQ_W_SM normal 2021-04-29T20:28:07+05:30 FD_04_stocell 11,769 IO requests
CD_IO_RQ_W_SM normal 2021-04-29T20:28:07+05:30 FD_05_stocell 11,545 IO requests
CD_IO_RQ_W_SM normal 2021-04-29T20:28:07+05:30 FD_06_stocell 3,541 IO requests
CD_IO_RQ_W_SM normal 2021-04-29T20:28:07+05:30 FD_07_stocell 4,152 IO requests
CD_IO_RQ_W_SM normal 2021-04-29T20:28:07+05:30 FD_08_stocell 11,994 IO requests
Number of MB read in large blocks from celldisk
CellCLI> list metriccurrent attributes name,alertState,collectionTime,metricObjectName,metricType,metricValue,objectType where name='GD_IO_BY_R_LG';
GD_IO_BY_R_LG normal 2021-04-29T23:01:09+05:30 DATA_CD_cell1_stocell Cumulative 124 MB GRIDDISK
GD_IO_BY_R_LG normal 2021-04-29T23:01:09+05:30 DATA_CD_cell2_stocell Cumulative 133 MB GRIDDISK
GD_IO_BY_R_LG normal 2021-04-29T23:01:09+05:30 DATA_CD_cell3_stocell Cumulative 125 MB GRIDDISK
GD_IO_BY_R_LG normal 2021-04-29T23:01:09+05:30 DATA_CD_cell4_stocell Cumulative 153 MB GRIDDISK
GD_IO_BY_R_LG normal 2021-04-29T23:01:09+05:30 DATA_CD_cell5_stocell Cumulative 118 MB GRIDDISK
GD_IO_BY_R_LG normal 2021-04-29T23:01:09+05:30 DATA_CD_cell6_stocell Cumulative 143 MB GRIDDISK
GD_IO_BY_R_LG normal 2021-04-29T23:01:09+05:30 FRA_CD_cell08_stocell Cumulative 0.000 MB GRIDDISK
GD_IO_BY_R_LG normal 2021-04-29T23:01:09+05:30 FRA_CD_cell09_stocell Cumulative 0.000 MB GRIDDISK
GD_IO_BY_R_LG normal 2021-04-29T23:01:09+05:30 FRA_CD_cell7_stocell Cumulative 0.000 MB GRIDDISK
GD_IO_BY_R_LG normal 2021-04-29T23:01:09+05:30 FRA_CD_cell8_stocell Cumulative 0.609 MB GRIDDISK
GD_IO_BY_R_LG normal 2021-04-29T23:01:09+05:30 FRA_CD_cell9_stocell Cumulative 0.000 MB GRIDDISK
GD_IO_BY_R_LG normal 2021-04-29T23:01:09+05:30 MGMT_CD_cell09_stocell Cumulative 71.664 MB GRIDDISK
Number of MB to read small blocks to griddisk
CellCLI> list metriccurrent attributes name,alertState,collectionTime,metricObjectName,metricType,metricValue,objectType where name='GD_IO_BY_R_SM';
GD_IO_BY_R_SM normal 2021-04-29T23:04:10+05:30 DATA_CD_cell1_stocell Cumulative 111 MB GRIDDISK
GD_IO_BY_R_SM normal 2021-04-29T23:04:10+05:30 DATA_CD_cell2_stocell Cumulative 93.603 MB GRIDDISK
GD_IO_BY_R_SM normal 2021-04-29T23:04:10+05:30 DATA_CD_cell3_stocell Cumulative 79.551 MB GRIDDISK
GD_IO_BY_R_SM normal 2021-04-29T23:04:10+05:30 DATA_CD_cell4_stocell Cumulative 95.796 MB GRIDDISK
GD_IO_BY_R_SM normal 2021-04-29T23:04:10+05:30 DATA_CD_cell5_stocell Cumulative 151 MB GRIDDISK
GD_IO_BY_R_SM normal 2021-04-29T23:04:10+05:30 DATA_CD_cell6_stocell Cumulative 116 MB GRIDDISK
GD_IO_BY_R_SM normal 2021-04-29T23:04:10+05:30 FRA_CD_cell08_stocell Cumulative 0.378 MB GRIDDISK
GD_IO_BY_R_SM normal 2021-04-29T23:04:10+05:30 FRA_CD_cell09_stocell Cumulative 0.499 MB GRIDDISK
GD_IO_BY_R_SM normal 2021-04-29T23:04:10+05:30 FRA_CD_cell7_stocell Cumulative 0.857 MB GRIDDISK
GD_IO_BY_R_SM normal 2021-04-29T23:04:10+05:30 FRA_CD_cell8_stocell Cumulative 0.817 MB GRIDDISK
GD_IO_BY_R_SM normal 2021-04-29T23:04:10+05:30 FRA_CD_cell9_stocell Cumulative 0.999 MB GRIDDISK
GD_IO_BY_R_SM normal 2021-04-29T23:04:10+05:30 MGMT_CD_cell09_stocell Cumulative 91.108 MB GRIDDISK
Number of requests to write large blocks to griddisk
CellCLI> list metriccurrent attributes name,alertState,collectionTime,metricObjectName,metricValue,objectType where name='GD_IO_BY_W_LG';
GD_IO_BY_W_LG normal 2021-04-29T23:08:11+05:30 DATA_CD_cell1_stocell 58.683 MB GRIDDISK
GD_IO_BY_W_LG normal 2021-04-29T23:08:11+05:30 DATA_CD_cell2_stocell 56.470 MB GRIDDISK
GD_IO_BY_W_LG normal 2021-04-29T23:08:11+05:30 DATA_CD_cell3_stocell 58.226 MB GRIDDISK
GD_IO_BY_W_LG normal 2021-04-29T23:08:11+05:30 DATA_CD_cell4_stocell 55.497 MB GRIDDISK
GD_IO_BY_W_LG normal 2021-04-29T23:08:11+05:30 DATA_CD_cell5_stocell 56.906 MB GRIDDISK
GD_IO_BY_W_LG normal 2021-04-29T23:08:11+05:30 DATA_CD_cell6_stocell 71.672 MB GRIDDISK
GD_IO_BY_W_LG normal 2021-04-29T23:08:11+05:30 FRA_CD_cell08_stocell 82.925 MB GRIDDISK
GD_IO_BY_W_LG normal 2021-04-29T23:08:11+05:30 FRA_CD_cell09_stocell 89.471 MB GRIDDISK
GD_IO_BY_W_LG normal 2021-04-29T23:08:11+05:30 FRA_CD_cell7_stocell 89.895 MB GRIDDISK
GD_IO_BY_W_LG normal 2021-04-29T23:08:11+05:30 FRA_CD_cell8_stocell 95.932 MB GRIDDISK
GD_IO_BY_W_LG normal 2021-04-29T23:08:11+05:30 FRA_CD_cell9_stocell 89.391 MB GRIDDISK
GD_IO_BY_W_LG normal 2021-04-29T23:08:11+05:30 MGMT_CD_cell09_stocell 0.000 MB GRIDDISK
Cumulative number of large read requests to Grid Disks:
CellCLI> list metriccurrent attributes name,alertState,collectionTime,metricObjectName,metricValue where name='GD_IO_RQ_R_LG';
GD_IO_RQ_R_LG normal 2021-04-29T23:09:11+05:30 DATA_CD_cell1_stocell 148 IO requests
GD_IO_RQ_R_LG normal 2021-04-29T23:09:11+05:30 DATA_CD_cell2_stocell 152 IO requests
GD_IO_RQ_R_LG normal 2021-04-29T23:09:11+05:30 DATA_CD_cell3_stocell 148 IO requests
GD_IO_RQ_R_LG normal 2021-04-29T23:09:11+05:30 DATA_CD_cell4_stocell 169 IO requests
GD_IO_RQ_R_LG normal 2021-04-29T23:09:11+05:30 DATA_CD_cell5_stocell 151 IO requests
GD_IO_RQ_R_LG normal 2021-04-29T23:09:11+05:30 DATA_CD_cell6_stocell 160 IO requests
GD_IO_RQ_R_LG normal 2021-04-29T23:09:11+05:30 FRA_CD_cell08_stocell 0 IO requests
GD_IO_RQ_R_LG normal 2021-04-29T23:09:11+05:30 FRA_CD_cell09_stocell 0 IO requests
GD_IO_RQ_R_LG normal 2021-04-29T23:09:11+05:30 FRA_CD_cell7_stocell 0 IO requests
GD_IO_RQ_R_LG normal 2021-04-29T23:09:11+05:30 FRA_CD_cell8_stocell 1 IO requests
GD_IO_RQ_R_LG normal 2021-04-29T23:09:11+05:30 FRA_CD_cell9_stocell 0 IO requests
GD_IO_RQ_R_LG normal 2021-04-29T23:09:11+05:30 MGMT_CD_cell09_stocell 211 IO requests
Number of requests to read small blocks to griddisk
CellCLI> list metriccurrent attributes name,alertState,collectionTime,metricObjectName,metricValue where name='GD_IO_RQ_R_SM'; GD_IO_RQ_R_SM normal 2021-04-29T23:10:11+05:30 DATA_CD_cell1_stocell 1,859 IO requests
GD_IO_RQ_R_SM normal 2021-04-29T23:10:11+05:30 DATA_CD_cell2_stocell 1,616 IO requests
GD_IO_RQ_R_SM normal 2021-04-29T23:10:11+05:30 DATA_CD_cell3_stocell 1,284 IO requests
GD_IO_RQ_R_SM normal 2021-04-29T23:10:11+05:30 DATA_CD_cell4_stocell 1,537 IO requests
GD_IO_RQ_R_SM normal 2021-04-29T23:10:11+05:30 DATA_CD_cell5_stocell 2,448 IO requests
GD_IO_RQ_R_SM normal 2021-04-29T23:10:11+05:30 DATA_CD_cell6_stocell 1,863 IO requests
GD_IO_RQ_R_SM normal 2021-04-29T23:10:11+05:30 FRA_CD_cell08_stocell 90 IO requests
GD_IO_RQ_R_SM normal 2021-04-29T23:10:11+05:30 FRA_CD_cell09_stocell 74 IO requests
GD_IO_RQ_R_SM normal 2021-04-29T23:10:11+05:30 FRA_CD_cell7_stocell 64 IO requests
GD_IO_RQ_R_SM normal 2021-04-29T23:10:11+05:30 FRA_CD_cell8_stocell 46 IO requests
GD_IO_RQ_R_SM normal 2021-04-29T23:10:11+05:30 FRA_CD_cell9_stocell 75 IO requests
GD_IO_RQ_R_SM normal 2021-04-29T23:10:11+05:30 MGMT_CD_cell09_stocell 148,062 IO requests
Number of requests to write small blocks to griddisk
CellCLI> list metriccurrent attributes name,alertState,collectionTime,metricObjectName,metricValue where name='GD_IO_RQ_W_SM';
GD_IO_RQ_W_SM normal 2021-04-29T23:13:12+05:30 DATA_CD_cell1_stocell 15,844 IO requests
GD_IO_RQ_W_SM normal 2021-04-29T23:13:12+05:30 DATA_CD_cell2_stocell 5,965 IO requests
GD_IO_RQ_W_SM normal 2021-04-29T23:13:12+05:30 DATA_CD_cell3_stocell 28,588 IO requests
GD_IO_RQ_W_SM normal 2021-04-29T23:13:12+05:30 DATA_CD_cell4_stocell 5,069 IO requests
GD_IO_RQ_W_SM normal 2021-04-29T23:13:12+05:30 DATA_CD_cell5_stocell 5,103 IO requests
GD_IO_RQ_W_SM normal 2021-04-29T23:13:12+05:30 DATA_CD_cell6_stocell 4,722 IO requests
GD_IO_RQ_W_SM normal 2021-04-29T23:13:12+05:30 FRA_CD_cell08_stocell 14,115 IO requests
GD_IO_RQ_W_SM normal 2021-04-29T23:13:12+05:30 FRA_CD_cell09_stocell 25,661 IO requests
GD_IO_RQ_W_SM normal 2021-04-29T23:13:12+05:30 FRA_CD_cell7_stocell 1,859 IO requests
GD_IO_RQ_W_SM normal 2021-04-29T23:13:12+05:30 FRA_CD_cell8_stocell 2,506 IO requests
GD_IO_RQ_W_SM normal 2021-04-29T23:13:12+05:30 FRA_CD_cell9_stocell 1,523 IO requests
GD_IO_RQ_W_SM normal 2021-04-29T23:13:12+05:30 MGMT_CD_cell09_stocell 83,533 IO requests
Number of requests to write large blocks to griddisk
CellCLI> list metriccurrent attributes name,alertState,collectionTime,metricObjectName,metricValue where name='GD_IO_RQ_W_LG';
GD_IO_RQ_W_LG normal 2021-04-29T23:13:12+05:30 DATA_CD_cell1_stocell 84 IO requests
GD_IO_RQ_W_LG normal 2021-04-29T23:13:12+05:30 DATA_CD_cell2_stocell 97 IO requests
GD_IO_RQ_W_LG normal 2021-04-29T23:13:12+05:30 DATA_CD_cell3_stocell 85 IO requests
GD_IO_RQ_W_LG normal 2021-04-29T23:13:12+05:30 DATA_CD_cell4_stocell 88 IO requests
GD_IO_RQ_W_LG normal 2021-04-29T23:13:12+05:30 DATA_CD_cell5_stocell 92 IO requests
GD_IO_RQ_W_LG normal 2021-04-29T23:13:12+05:30 DATA_CD_cell6_stocell 107 IO requests
GD_IO_RQ_W_LG normal 2021-04-29T23:13:12+05:30 FRA_CD_cell08_stocell 113 IO requests
GD_IO_RQ_W_LG normal 2021-04-29T23:13:12+05:30 FRA_CD_cell09_stocell 129 IO requests
GD_IO_RQ_W_LG normal 2021-04-29T23:13:12+05:30 FRA_CD_cell7_stocell 123 IO requests
GD_IO_RQ_W_LG normal 2021-04-29T23:13:12+05:30 FRA_CD_cell8_stocell 126 IO requests
GD_IO_RQ_W_LG normal 2021-04-29T23:13:12+05:30 FRA_CD_cell9_stocell 120 IO requests
GD_IO_RQ_W_LG normal 2021-04-29T23:13:12+05:30 MGMT_CD_cell09_stocell 0 IO requests