How to enable Intelligent Data Placement (IDP) for optimal performance

How to enable Intelligent Data Placement (IDP) for optimal performance


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:

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’;

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. 2011 http://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.

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

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.



Article ID: SLN310280

Last Date Modified: 08/30/2018 04:24 PM


Rate this article

Accurate
Useful
Easy to understand
Was this article helpful?
Yes No
Send us feedback
Comments cannot contain these special characters: <>()\
Sorry, our feedback system is currently down. Please try again later.

Thank you for your feedback.