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

DEFAULT POLICY
INTERLEAVE POLICY

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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s