Oracle 11g ASM Intelligent Data Placement

Posted by Narasimha Rao

Intelligent Data Placement enables you to specify disk regions on Oracle ASM disks for best performance. Using the disk region settings, you can ensure that frequently accessed data is placed on the outermost (hot) tracks which have greater speed and higher bandwidth.

In addition, files with similar access patterns are located physically close, reducing latency. Intelligent Data Placement also enables the placement of primary and mirror extents into different hot or cold regions.

Intelligent Data Placement settings can be specified for a file or in disk group templates. The disk region settings can be modified after the disk group has been created. The disk region setting can improve I/O performance by placing more frequently accessed data in regions furthest from the spindle, while reducing your cost by increasing the usable space on a disk.

Intelligent Data Placement works best for the following:

Databases with data files that are accessed at different rates. A database that accesses all data files in the same way is unlikely to benefit from Intelligent Data Placement.

Disk groups that are more than 25% full. If the disk group is only 25% full, the management overhead is unlikely to be worth any benefit.
Disks that have better performance at the beginning of the media relative to the end. Because Intelligent Data Placement leverages the geometry of the disk, it is well suited to JBOD (just a bunch of disks). In contrast, a storage array with LUNs composed of concatenated volumes masks the geometry from Oracle ASM.

The COMPATIBLE.ASM and COMPATIBLE.RDBMS disk group attributes must be set to 11.2 or higher to use Intelligent Data Placement.

Intelligent Data Placement can be managed with the ALTER DISKGROUP ADD or MODIFY TEMPLATE SQL statements and the ALTER DISKGROUP MODIFY FILE SQL statement.

The ALTER DISKGROUP TEMPLATE SQL statement includes a disk region clause for setting hot/mirrorhot or cold/mirrorcold regions in a template:
```
ALTER DISKGROUP data ADD TEMPLATE datafile_hot
  ATTRIBUTE ( 
    HOT
    MIRRORHOT);
```

The ALTER DISKGROUP … MODIFY FILE SQL statement that sets disk region attributes for hot/mirrorhot or cold/mirrorcold regions:

ALTER DISKGROUP data MODIFY FILE '+data/orcl/datafile/users.259.679156903'
  ATTRIBUTE ( 
    HOT
    MIRRORHOT);

When you modify the disk region settings for a file, this action applies to new extensions of the file, but existing file contents are not affected until a rebalance operation.
To apply the new Intelligent Data Placement policy for existing file contents, you can manually initiate a rebalance. A rebalance operation uses the last specified policy for the file extents.

Information about Intelligent Data Placement is displayed in the columns of the V$ASM_DISK, V$ASM_DISK_IOSTAT, V$ASM_FILE, and V$ASM_TEMPLATE views.

Example 6-9 shows queries for Intelligent Data Placement information in the PRIMARY_REGION and MIRROR_REGION columns of the V$ASM_FILE view.

Example 6-9 Viewing Intelligent Data Placement information with V$ASM_FILE

SQL> SELECT dg.name AS diskgroup, f.file_number, f.primary_region, f.mirror_region, f.hot_reads,
       f.hot_writes,  f.cold_reads, f.cold_writes
       FROM V$ASM_DISKGROUP dg, V$ASM_FILE f 
       WHERE dg.group_number = f.group_number and dg.name = 'DATA';

DISKGROUP                      FILE_NUMBER PRIM MIRR  HOT_READS HOT_WRITES COLD_READS COLD_WRITES
------------------------------ ----------- ---- ---- ---------- ---------- ---------- -----------
DATA                                   257 COLD COLD          0          0     119770      886575
DATA                                   258 COLD COLD          0          0       1396      222282
DATA                                   259 COLD COLD          0          0       2056         199
DATA                                   260 COLD COLD          0          0      42377     1331016
DATA                                   261 COLD COLD          0          0    4336300     1331027
...

Example 6-10 displays Intelligent Data Placement information in the PRIMARY_REGION and MIRROR_REGION columns of the V$ASM_TEMPLATE view.

Example 6-10 Viewing Intelligent Data Placement information with V$ASM_TEMPLATE

SQL> SELECT dg.name AS diskgroup, t.name, t.stripe, t.redundancy, t.primary_region, t.mirror_region 
       FROM V$ASM_DISKGROUP dg, V$ASM_TEMPLATE t 
       WHERE dg.group_number = t.group_number and dg.name = 'DATA' ORDER BY t.name;

DISKGROUP                      NAME                           STRIPE REDUND PRIM MIRR
------------------------------ ------------------------------ ------ ------ ---- ----
DATA                           ARCHIVELOG                     COARSE MIRROR COLD COLD
DATA                           ASMPARAMETERFILE               COARSE MIRROR COLD COLD
DATA                           AUTOBACKUP                     COARSE MIRROR COLD COLD
DATA                           BACKUPSET                      COARSE MIRROR COLD COLD
DATA                           CHANGETRACKING                 COARSE MIRROR COLD COLD
DATA                           CONTROLFILE                    FINE   HIGH   COLD COLD
DATA                           DATAFILE                       COARSE MIRROR COLD COLD
DATA                           DATAGUARDCONFIG                COARSE MIRROR COLD COLD
DATA                           DUMPSET                        COARSE MIRROR COLD COLD
DATA                           FLASHBACK                      COARSE MIRROR COLD COLD
DATA                           FLASHFILE                      COARSE MIRROR COLD COLD
DATA                           OCRFILE                        COARSE MIRROR COLD COLD
DATA                           ONLINELOG                      COARSE MIRROR COLD COLD
DATA                           PARAMETERFILE                  COARSE MIRROR COLD COLD
DATA                           TEMPFILE                       COARSE MIRROR COLD COLD
DATA                           XTRANSPORT                     COARSE MIRROR COLD COLD

15 rows selected.

Abishek Kathiresan