优先镜像读取测试
首先设置ASM首选镜像
SQL> alter system set ASM_PREFERRED_READ_FAILURE_GROUPS=’DATA.DATA_0000′ sid=’+ASM1′;
SQL> alter system set ASM_PREFERRED_READ_FAILURE_GROUPS=’DATA.DATA_0001′ sid=’+ASM2′;
检查设置:
一节点:
SQL> select group_number,disk_number,preferred_read from v$asm_disk where group_number=1;
GROUP_NUMBER DISK_NUMBER P
------------ ----------- -
1 1 N
1 0 Y
二节点:
SQL> select group_number,disk_number,preferred_read from v$asm_disk where group_number=1;
GROUP_NUMBER DISK_NUMBER P
------------ ----------- -
1 1 Y
1 0 N
为测试的精准性,重启CRS服务进程后进行相关测试:
一节点:
查看对应的磁盘读写(未进行任何读写操作):
一节点:
select instname,disk_number,failgroup,reads,read_time from v$asm_disk_iostat where instname='ora11g1';
SQL>select instname,disk_number,failgroup,reads,read_time from v$asm_disk_iostat where instname='ora11g1'
INSTNAME DISK_NUMBER FAILGROUP READS READ_TIME
---------------------------------------------------------------- ----------- ------------------------------ ---------- ----------
ora11g1 0 DATA_0000 3686 70.533591
ora11g1 1 DATA_0001 0 0
目前一节点在DATA_0000上的读IO是3686
二节点:
SQL> select instname,disk_number,failgroup,reads,read_time from v$asm_disk_iostat where instname='ora11g2';
INSTNAME DISK_NUMBER FAILGROUP READS READ_TIME
---------------------------------------------------------------- ----------- ------------------------------ ---------- ----------
ora11g2 0 DATA_0000 1 .024885
ora11g2 1 DATA_0001 5868 56.486877
目前二节点在DATA_0001上的读IO是5868
接下来做的操作是针对IO,在一节点上查询test用户下的test表,然后刷新buffer_cache重新在二节点执行该查询操作
一节点执行:
SQL> select count(*) from test.test;
COUNT(*)
----------
9170264
SQL> select instname,disk_number,failgroup,reads,read_time from v$asm_disk_iostat where instname='ora11g1';
INSTNAME DISK_NUMBER FAILGROUP READS READ_TIME
---------------------------------------------------------------- ----------- ------------------------------ ---------- ----------
ora11g1 0 DATA_0000 4857 109.763665
ora11g1 1 DATA_0001 0 0
从没有执行之前的3686增加到4857增加了增加1171个数据块
二节点:
INSTNAME DISK_NUMBER FAILGROUP READS READ_TIME
---------------------------------------------------------------- ----------- ------------------------------ ---------- ----------
ora11g2 0 DATA_0000 1 .024885
ora11g2 1 DATA_0001 6035 61.388466
增加了167个数据块,增加不明显。
可以明显看到,ora11g1实例对应的IO基本在DATA_0000 DATA_0001 IO增量不大。
刷新双节点buffer:
一节点:
SQL> alter system flush buffer_cache;
System altered.
二节点:
SQL> alter system flush buffer_cache;
System altered.
查看双节点IO情况:
一节点:
INSTNAME DISK_NUMBER FAILGROUP READS READ_TIME
---------------------------------------------------------------- ----------- ------------------------------ ---------- ----------
ora11g1 0 DATA_0000 5202 125.504934
ora11g1 1 DATA_0001 0 0
二节点:
INSTNAME DISK_NUMBER FAILGROUP READS READ_TIME
---------------------------------------------------------------- ----------- ------------------------------ ---------- ----------
ora11g2 0 DATA_0000 1 .024885
ora11g2 1 DATA_0001 6867 80.835627
二节点做查询后查看IO情况:
一节点IO:
INSTNAME DISK_NUMBER FAILGROUP READS READ_TIME
---------------------------------------------------------------- ----------- ------------------------------ ---------- ----------
ora11g1 0 DATA_0000 5460 129.884158
ora11g1 1 DATA_0001 0 0
二节点IO:
INSTNAME DISK_NUMBER FAILGROUP READS READ_TIME
---------------------------------------------------------------- ----------- ------------------------------ ---------- ----------
ora11g2 0 DATA_0000 1 .024885
ora11g2 1 DATA_0001 8233 124.786551
二节点IO增量在1366 一节点IO增量在200左右,二节点增量较大
结论,从目前的手段来看,针对于优先镜像读取测试的话,从目前手段来看很难看出在一节点上操作时,DATA_0001 磁盘的IO是否是不受一节点操作影响的
虽然从IO增量上来看!结论似乎没有问题
------------------------------------------------------------------------------------
原博客地址:http://blog.itpub.net/23732248/
原作者:应以峰 (frank-ying)
-------------------------------------------------------------------------------------
首先设置ASM首选镜像
SQL> alter system set ASM_PREFERRED_READ_FAILURE_GROUPS=’DATA.DATA_0000′ sid=’+ASM1′;
SQL> alter system set ASM_PREFERRED_READ_FAILURE_GROUPS=’DATA.DATA_0001′ sid=’+ASM2′;
检查设置:
一节点:
SQL> select group_number,disk_number,preferred_read from v$asm_disk where group_number=1;
GROUP_NUMBER DISK_NUMBER P
------------ ----------- -
1 1 N
1 0 Y
二节点:
SQL> select group_number,disk_number,preferred_read from v$asm_disk where group_number=1;
GROUP_NUMBER DISK_NUMBER P
------------ ----------- -
1 1 Y
1 0 N
为测试的精准性,重启CRS服务进程后进行相关测试:
一节点:
查看对应的磁盘读写(未进行任何读写操作):
一节点:
select instname,disk_number,failgroup,reads,read_time from v$asm_disk_iostat where instname='ora11g1';
SQL>select instname,disk_number,failgroup,reads,read_time from v$asm_disk_iostat where instname='ora11g1'
INSTNAME DISK_NUMBER FAILGROUP READS READ_TIME
---------------------------------------------------------------- ----------- ------------------------------ ---------- ----------
ora11g1 0 DATA_0000 3686 70.533591
ora11g1 1 DATA_0001 0 0
目前一节点在DATA_0000上的读IO是3686
二节点:
SQL> select instname,disk_number,failgroup,reads,read_time from v$asm_disk_iostat where instname='ora11g2';
INSTNAME DISK_NUMBER FAILGROUP READS READ_TIME
---------------------------------------------------------------- ----------- ------------------------------ ---------- ----------
ora11g2 0 DATA_0000 1 .024885
ora11g2 1 DATA_0001 5868 56.486877
目前二节点在DATA_0001上的读IO是5868
接下来做的操作是针对IO,在一节点上查询test用户下的test表,然后刷新buffer_cache重新在二节点执行该查询操作
一节点执行:
SQL> select count(*) from test.test;
COUNT(*)
----------
9170264
SQL> select instname,disk_number,failgroup,reads,read_time from v$asm_disk_iostat where instname='ora11g1';
INSTNAME DISK_NUMBER FAILGROUP READS READ_TIME
---------------------------------------------------------------- ----------- ------------------------------ ---------- ----------
ora11g1 0 DATA_0000 4857 109.763665
ora11g1 1 DATA_0001 0 0
从没有执行之前的3686增加到4857增加了增加1171个数据块
二节点:
INSTNAME DISK_NUMBER FAILGROUP READS READ_TIME
---------------------------------------------------------------- ----------- ------------------------------ ---------- ----------
ora11g2 0 DATA_0000 1 .024885
ora11g2 1 DATA_0001 6035 61.388466
增加了167个数据块,增加不明显。
可以明显看到,ora11g1实例对应的IO基本在DATA_0000 DATA_0001 IO增量不大。
刷新双节点buffer:
一节点:
SQL> alter system flush buffer_cache;
System altered.
二节点:
SQL> alter system flush buffer_cache;
System altered.
查看双节点IO情况:
一节点:
INSTNAME DISK_NUMBER FAILGROUP READS READ_TIME
---------------------------------------------------------------- ----------- ------------------------------ ---------- ----------
ora11g1 0 DATA_0000 5202 125.504934
ora11g1 1 DATA_0001 0 0
二节点:
INSTNAME DISK_NUMBER FAILGROUP READS READ_TIME
---------------------------------------------------------------- ----------- ------------------------------ ---------- ----------
ora11g2 0 DATA_0000 1 .024885
ora11g2 1 DATA_0001 6867 80.835627
二节点做查询后查看IO情况:
一节点IO:
INSTNAME DISK_NUMBER FAILGROUP READS READ_TIME
---------------------------------------------------------------- ----------- ------------------------------ ---------- ----------
ora11g1 0 DATA_0000 5460 129.884158
ora11g1 1 DATA_0001 0 0
二节点IO:
INSTNAME DISK_NUMBER FAILGROUP READS READ_TIME
---------------------------------------------------------------- ----------- ------------------------------ ---------- ----------
ora11g2 0 DATA_0000 1 .024885
ora11g2 1 DATA_0001 8233 124.786551
二节点IO增量在1366 一节点IO增量在200左右,二节点增量较大
结论,从目前的手段来看,针对于优先镜像读取测试的话,从目前手段来看很难看出在一节点上操作时,DATA_0001 磁盘的IO是否是不受一节点操作影响的
虽然从IO增量上来看!结论似乎没有问题
------------------------------------------------------------------------------------
原博客地址:http://blog.itpub.net/23732248/
原作者:应以峰 (frank-ying)
-------------------------------------------------------------------------------------
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23732248/viewspace-1407743/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/23732248/viewspace-1407743/