下面的例子将显示如何部署优先读取功能与展现其一些优点。这个例子演示当asm_preferred_read_failure_groups参数没有设置时的I/O模式，以及修改参数之后对I/O的影响。

1.创建有两个故障磁盘组的ASM磁盘组 JL  其中fg1 是高性能，fg2是nas盘

2.在节点1,创建表测试表t1

SQL> create table t1 as select * from dba_tables;

Table created.

SQL> insert into t1 select * from t1;

SQL> /

...

1430528 rows created.

SQL> alter system flush buffer_cache;

System altered.

3.查询磁盘组的读写I/O累计信息，可以看到实例1中的FG1,FG2的读写分别为5816599,5837805,实例2中的FG1,FG2的读写分别为5843814,5853830

 set long 9999

 set linesize 9999

 select inst_id, failgroup, sum(reads), sum(writes) from gv$asm_disk where failgroup in ('FG1','FG2') group by inst_id, failgroup;

 set long 9999

 set linesize 9999

 select inst_id, failgroup, sum(reads), sum(writes) from gv$asm_disk where failgroup in ('FG1','FG2') group by inst_id, failgroup;

   INST_ID FAILGROUP   SUM(READS) SUM(WRITES)

---------- ------------------------------ ---------- -----------

1 FG1      5816599    56483

1 FG2      5837805    56483

2 FG1      5843814      966

2 FG2      5853830      966

4.执行查询:

SQL> show parameter asm_preferred_read_failure_groups

NAME                                 TYPE

------------------------------------ ----------------------

VALUE

------------------------------

asm_preferred_read_failure_groups    string

SQL> select count(*) from sys.t1;

  COUNT(*)

----------

   2861056

5.再次查询磁盘组的读写I/O累计信息，可以看到实例1中的FG1,FG2的读分别增加了 781,497  实例2 分别增加了2832 ，80

   INST_ID FAILGROUP   SUM(READS) SUM(WRITES)

---------- ------------------------------ ---------- -----------

1 FG1      5817380    56813

1 FG2      5838302    56813

2 FG1      5846646     1013

2 FG2      5853910     1013

6.设置 asm_preferred_read_failure_groups参数，让节点1优先从故障磁盘组FG1进行读取，让节点2优先从故障磁盘组FG2进行读取

SQL> alter system set asm_preferred_read_failure_groups='DATA_NRML.FG1' scope=both sid='+ASM1';

alter system set asm_preferred_read_failure_groups='BPM.FG1','FR.FG1','JL.FG1','KFK.FG1','SC.FG1','ZHPT.FG1' scope=both;

SQL> alter system set asm_preferred_read_failure_groups='BPM.FG1','FR.FG1','JL.FG1','KFK.FG1','SC.FG1','ZHPT.FG1' scope=both;

System altered.

SQL> show parameter asm_preferred_read_failure_groups

NAME      TYPE VALUE

------------------------------------ ----------- ------------------------------

asm_preferred_read_failure_groups    string BPM.FG1, FR.FG1, JL.FG1, KFK.F

G1, SC.FG1, ZHPT.FG1

修改前查询时间

15:04:11 SQL> set timing on

15:04:17 SQL>  select count(*) from sys.t1;

  COUNT(*)

----------

   2861056

Elapsed: 00:00:04.16

7.检查实例的优先读取磁盘组信息,从下面的查询结果可以看到，优先都是读FG1，其次是FG2:

SQL> select inst_id, failgroup, name, preferred_read from gv$asm_disk where failgroup in ('FG1','FG2') order by inst_id, failgroup;

   INST_ID FAILGROUP   NAME P

---------- ------------------------------ ------------------------------ -

1 FG1   BPM_0001 Y

1 FG1   ZHPT_0001 Y

1 FG1   FR_0001 Y

1 FG1   KFK_0001 Y

1 FG1   SC_0001 Y

1 FG1   JL_0001 Y

1 FG2   BPM_0000 N

1 FG2   KFK_0000 N

1 FG2   JL_0000 N

1 FG2   SC_0000 N

1 FG2   ZHPT_0000 N

   INST_ID FAILGROUP   NAME P

---------- ------------------------------ ------------------------------ -

1 FG2   FR_0000 N

2 FG1   SC_0001 Y

2 FG1   JL_0001 Y

2 FG1   ZHPT_0001 Y

2 FG1   BPM_0001 Y

2 FG1   FR_0001 Y

2 FG1   KFK_0001 Y

2 FG2   JL_0000 N

2 FG2   BPM_0000 N

2 FG2   ZHPT_0000 N

2 FG2   KFK_0000 N

   INST_ID FAILGROUP   NAME P

---------- ------------------------------ ------------------------------ -

2 FG2   FR_0000 N

2 FG2   SC_0000 N

12 rows selected.

8.在节点1开启会话再次执行查询

SQL> alter system flush buffer_cache;

System altered.

SQL> set timing on

15:13:53 SQL> select count(*) from SYS.t1;

  COUNT(*)

----------

   2861056

Elapsed: 00:00:00.73

Elapsed: 00:00:04.16

可以看到两次的执行时间存在明细差距，调整前为4s调整后为0.7s

9 查询当前的读写数据

 set long 9999

 set linesize 9999

 select inst_id, failgroup, sum(reads), sum(writes) from gv$asm_disk where failgroup in ('FG1','FG2') group by inst_id, failgroup;

    INST_ID FAILGROUP   SUM(READS) SUM(WRITES)

---------- ------------------------------ ---------- -----------

1 FG1      5821644    59588

1 FG2      5839272    59586

2 FG1      5869843     1504

2 FG2      5854523     1504

再次执行sql，

select count(*) from SYS.t1;

9.再次查询磁盘组的读写I/O累计信息，可以看到 读基本都到了FG1上面 852和512，FG2的读很少 都是6

SQL> select inst_id, failgroup, sum(reads), sum(writes) from gv$asm_disk where failgroup in ('FG1','FG2') group by inst_id, failgroup;

   INST_ID FAILGROUP   SUM(READS) SUM(WRITES)

---------- ------------------------------ ---------- -----------

1 FG1      5822496    59647

1 FG2      5839278    59647

2 FG1      5870355     1512

2 FG2      5854529     1512