Applies To:

Oracle Version(s) - 11.2.x

Author:

Carthik, Annayan

Problem:

How do I enable IDP (Intelligent Data Placement)?

Solution:

What is IDP?

The Intelligent Data Placement feature enables you to specify disk regions on Oracle ASM disks to ensure that frequently accessed data is placed on the outermost (hot) tracks which provide higher performance. “Intelligent Data Placement.” Oracle’s Online Documentation. 2011,http://download.oracle.com/docs/cd/E14072_01/server.112/e10500/asmdiskgrps.htm#CHDICBEB

NOTE Intelligent Data Placement works best for the Disk groups that are more than 25% full.

Steps to enable IDP:

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

Syntax:

Alter diskgroup DATA set attribute ‘compatible.asm’=’11.2’;

Alter diskgroup DATA set attribute‘compatible.rdbms’=’11.2’;

2) To enable IDP at the Diskgroup Level use the ALTER DISKGROUP TEMPLATE SQL statement that includes a disk region clause for setting hot/mirrorhot or cold/mirrorcold regions in a template:

Syntax:

ALTER DISKGROUP data ADD TEMPLATE datafile_hot ATTRIBUTE ( HOT MIRRORHOT);

“Intelligent Data Placement.” Oracle’s Online Documentation. 2011http://download.oracle.com/docs/cd/E14072_01/server.112/e10500/asmdiskgrps.htm#CHDICBEB

NOTE The HOT and MIRRORHOT Options depends on the redundancy level that’s used. For example if you are using an external redundancy, then the MIRRORHOT option will not work. You need to specify, HOT.

1) To enable IDP at the Datafile Level use The ALTER DISKGROUP ... MODIFY FILE SQL statement that sets disk region attributes for hot/mirrorhot or cold/mirrorcold regions:

Syntax:

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

“Intelligent Data Placement.” Oracle’s Online Documentation. 2011 http://download.oracle.com/docs/cd/E14072_01/server.112/e10500/asmdiskgrps.htm#CHDICBEB
 
 Useful views to check if  IDP is working:
 
V$ASM_DISK_IOSTAT, V$ASM_TEMPLATE, V$ASM_FILE
 

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_writesFROM V$ASM_DISKGROUP dg, V$ASM_FILE f WHERE dg.group_number = f.group_number and dg.name = 'DATA';

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;

NOTE Replace the diskgroup name with your environment variables.