ASM Optimal Disk Placement (ODP) in Oracle 11gR2:
As of Oracle 11gR2 it is possible to specify on which disk regions the ASM Disks will be placed. This enables us to make use of faster disk zones on the outer regions of disks for better I/O performance.
In spinning disk more data passes below the read/write on the outer portion of the disk because the greater circumference, hence overall throughput is improved, if all the data is in outer most segment then seek latency may reduce as well since the read/write head moves the shorter arc.
Optimal Disk Placement can be specified by associating an ASM Template in which primary extent zone is set to HOT or COLD. If set HOT then these file are stored in faster outer most track of the disk and experience greater throughput and reduce latency. If set COLD then these file are stored in middle track of the disk and experience low throughput and high latency.
Oracle has been sitting together with storage vendors and found ways to enable ASM to make use of storage intelligence features. These settings can be made on DiskGroup Templates and are changeable afterwards with an ALTER DISKGROUP statement.
Possible options are hot/mirrorhot and cold/mirrorcold.
ALTER DISKGROUP disk_group ADD TEMPLATE template
ATTRIBUTES (
MIRROR|UNPROTECTED
FINE|COARSE
HOT|COLD
MIRRORHOT|MIRRORCOLD )
ALTER DISKGROUP diskgroup ALTER FILE ‘filename’
ATTRIBUTES (
HOT|COLD
MIRRORHOT|MIRRORCOLD )
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.ASM intelligent file placement :
Intelligent file placement has been done in non-relational databases since the 1980’s but it is new to Oracle in Oracle 11g release 2. Thecompatible.asm and compatible.rdbms disk group attributes must be set to 11.2 or higher to use intelligent data placement. Also see the v$asm_disk_iostat view to access this ASM information.
Manual file placement: Middle tracks
Here is a randomized disk file where read-write head movement cripples response time:
As I note in the plague of super-large Oracle disks, intelligent file placement can dramatically improve throughout by minimizing the movement of the read-write head by intelligently placing popular data files near the middle absolute track and unpopular files on the outer tracks.
Per the “zoned bit recording” architecture, the outer cylinders contain more sectors than the inner platters.
As shown next, high impact data files should be placed to minimize read-write head movement. In this manual method, it is intended to minimize the travel of the read-write head, whose movement comprises over 90% of the cost of a disk I/O. However, this approach does not hold true when you have disks with fixed read-write heads (a read/write head for every disk cylinder).
Oracle 11g R2 Intelligent file placement: Outer tracks :
Intelligent file placement has been done in non-relational databases since the 1980’s but it is new to Oracle in Oracle 11g release 2 and it uses a different approach than the above manual intelligent file placement.
Oracle 11g release 2 introduced “intelligent data placement” (IDP) within ASM whereby a file or in disk group template can map to these hot and cold area of the Oracle platter.
It is the “Zoned Bit Recording” method (whereby the outer cylinders hold more sectors) that drives intelligent data placement, leveraging on the hardware geometry to optimize I/O into “hot” and “cold” areas. Remember, it is the DBA who marks the files as hot and cold, Oracle does not do this for you.
On large disks where the data cannot be fully cached in the data buffers, we might consider placing high I/O data files in the middle absolute track number to minimize read-write head movement. This is a situation in which the low access data files reside on the inner and outer cylinders of the disk.
The use of intelligent disk placement 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.
Here is hot to mark an ASM datafile as “hot”. We assume that this causes Oracle to relocate this ASM file to the outermost disk cylinders where you get more data per revolution:
SQL> alter diskgroup
mygroup
modify
file ‘+data/ora/datafile/users’
attribute ( hot mirrorhot);
The Oracle documentation does not say of the above command immediately moves the data files on the disk, but it is safe to assume that this should be a DBA task that should not be done while the production database is open to the end-users.
The use of intelligent disk placement 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.
The compatible.asm and compatible.rdbms disk group attributes must be set to 11.2 or higher to use intelligent data placement.
Alter diskgroup DATA set attribute ‘compatible.asm’=’11.2’;
Alter diskgroup DATA set attribute‘compatible.rdbms’=’11.2’;
Here is hot to mark an ASM datafile as “hot” and note the new “mirrorhot” argument, indicating that the mirrored disk will also be handled:
SQL> alter diskgroup mygroup
modify
file ‘+data/ora/datafile/users’
attribute ( hot mirrorhot);
The Oracle documentation does not say of the above command immediately moves the data files on the disk, but it is safe to assume that this should be a DBA task that should not be done while the production database is open to the end-users.
The following query can be used to inspect hot reads and how writes:
display_hot_cold_files.sql
SQL> select
dg.name
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;
SQL> select
dg.name 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’;
Also note that the view v$asm_disk_iostat has new columns for hot_reads and hot_writes and cold_reads and cold_writes to correspond with this disk segregation techniques that leverages on the geometry of the disk platter to improve overall throughput. See later in this chapter for a script to measure these access speeds on an ASM disk.
Thank you for reading… This is Airy…Enjoy Learning
Airydba Blog Notes 