ASM优先读取测试

下面的例子将显示如何部署优先读取功能与展现其一些优点。这个例子演示当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