Oracle 10.2.0.4 , Linux AS 5.4 64bit , 4 Nodes RAC
设置上因为节点4是作为report用处的,所以我们将它与其他3个节点彻底分割开(通过remote_listener=''来设置,其他节点
都有设置), 同样的一个report SQL ,在4个节点上的cost及执行步骤,执行计划一模一样, 但是在专用于report的节点4上
运行比较快 (大约1分钟), 但是在其他节点居然需要 8 分多钟, 我是在平常生产阶段做的测试, 其他同事在非生产阶段(比较
少的DML) 做测试也是一样 。 4个节点的前端Server配置及OS配置都一样。 节点1,2,3用于OLTP生产系统, 而节点4主要
用于报表查询, web中也有很少量的insert, update维护动作, 而且1,2,3的负载比较低, 平均只有 2.5 左右 (24核CPU),
节点4负载稍微高一些,平均 8.5 左右 。
sga设置了20G, 节点1,2,3 buffer cache 命中率约为 99.5% 左右,节点4为 99.91% .
查看一下sql trace统计,不一样的地方是主要是 physical reads , 节点4上是9w多, 节点1,2,3是30w左右 。
以下统计前提都是在每个节点上事先有执行过这个相同SQL .
在节点4上的2次统计:
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1124242 consistent gets
96969 physical reads
220 redo size
1234109 bytes sent via SQL*Net to client
15789 bytes received via SQL*Net from client
721 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10798 rows processed
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1125569 consistent gets
93146 physical reads
860 redo size
1258173 bytes sent via SQL*Net to client
15910 bytes received via SQL*Net from client
732 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10957 rows processed
在节点1上的2次统计:
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1135486 consistent gets
289516 physical reads
116 redo size
1234703 bytes sent via SQL*Net to client
15800 bytes received via SQL*Net from client
722 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10802 rows processed
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1133003 consistent gets
306163 physical reads
0 redo size
1258394 bytes sent via SQL*Net to client
15909 bytes received via SQL*Net from client
732 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10961 rows processed
在节点3上的2次统计:
Statistics
----------------------------------------------------------
759 recursive calls
0 db block gets
1141367 consistent gets
338301 physical reads
552 redo size
1263503 bytes sent via SQL*Net to client
15844 bytes received via SQL*Net from client
726 SQL*Net roundtrips to/from client
31 sorts (memory)
0 sorts (disk)
10865 rows processed
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1150321 consistent gets
347188 physical reads
0 redo size
1281392 bytes sent via SQL*Net to client
15899 bytes received via SQL*Net from client
731 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
10948 rows processed
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/35489/viewspace-687128/,如需转载,请注明出处,否则将追究法律责任。