1.比较单实例(让RAC只open一个实例)和多实例下,RAC对大表(不要创建索引)查询的效率(可以先将cache buffer清空)。
多实例:
SQL> select instance_name,status from gv$instance;
INSTANCE_NAME STATUS
---------------- ------------
racdb1 OPEN
racdb2 OPEN
SQL> select sum(bytes)/1024/1024 MiB from dba_segments where segment_name='T_BIG';
MIB
----------
320
SQL> select count(*) from t_big;
COUNT(*)
----------
2967104
SQL> alter system flush buffer_cache;
System altered.
SQL> set autotrace traceonly;
SQL> select count(*) from t_big;
Execution Plan
----------------------------------------------------------
Plan hash value: 3097439769
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8925 (1)| 00:01:48 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T_BIG | 2536K| 8925 (1)| 00:01:48 |
--------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
40645 consistent gets
40632 physical reads
0 redo size
518 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
单实例:
[oracle@rac4 ~]$ srvctl stop instance -d racdb -i racdb2 -o immediate
SQL> select instance_name,status from gv$instance;
INSTANCE_NAME STATUS
---------------- ------------
racdb1 OPEN
SQL> alter system flush buffer_cache;
System altered.
SQL> set autotrace traceonly;
SQL> select count(*) from t_big;
Execution Plan
----------------------------------------------------------
Plan hash value: 3097439769
--------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
--------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8925 (1)| 00:01:48 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T_BIG | 2536K| 8925 (1)| 00:01:48 |
--------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
40645 consistent gets
40632 physical reads
0 redo size
518 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
结论:可以看出,对大表t_big的查询,多实例和单实例所需时间一样,多实例并没有表现出性能上的优势。
2.比较在单实例上的并行执行和多实例上并行执行的效率。
多实例:
SQL> select instance_name,status from gv$instance;
INSTANCE_NAME STATUS
---------------- ------------
racdb1 OPEN
racdb2 OPEN
SQL> alter system flush buffer_cache;
System altered.
SQL> set autotrace traceonly;
SQL> select /*+parallel(t_big 8) */ count(*) from t_big;
Execution Plan
----------------------------------------------------------
Plan hash value: 1514025845
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1237 (1)| 00:00:15 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 2536K| 1237 (1)| 00:00:15 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| T_BIG | 2536K| 1237 (1)| 00:00:15 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
57 recursive calls
3 db block gets
41120 consistent gets
40631 physical reads
672 redo size
518 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
单实例:
[oracle@rac4 ~]$ srvctl stop instance -d racdb -i racdb2 -o immediate
SQL> select instance_name,status from gv$instance;
INSTANCE_NAME STATUS
---------------- ------------
racdb1 OPEN
SQL> alter system flush buffer_cache;
System altered.
SQL> set autotrace traceonly;
SQL> select /*+parallel(t_big 8) */ count(*) from t_big;
Execution Plan
----------------------------------------------------------
Plan hash value: 1514025845
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 1237 (1)| 00:00:15 | | | |
| 1 | SORT AGGREGATE | | 1 | | | | | |
| 2 | PX COORDINATOR | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 2536K| 1237 (1)| 00:00:15 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| T_BIG | 2536K| 1237 (1)| 00:00:15 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
18 recursive calls
0 db block gets
40946 consistent gets
40631 physical reads
0 redo size
518 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
结论:在本例中,无论单实例还是多实例情况下,并行查询的执行效率并没有多大差别。
3.按照视频中的例子,比较一下你机器上RAC两个实例的效率。
SQL> SELECT a.inst_id "Instance", (a.value+b.value+c.value+d.value)/decode(e.value,0,1,e.value) "BSP Service Time"
FROM gv$sysstat A, gv$sysstat B, gv$sysstat C, gv$sysstat D, gv$sysstat E
WHERE A.name = 'gc cr block build time'
AND B.name = 'gc cr block send time'
AND C.name = 'gc cr block flush time'
AND D.name = 'gc cr block receive time'
AND E.name = 'gc cr blocks served'
AND B.inst_id = A.inst_id
AND C.inst_id = A.inst_id
AND D.inst_id = A.inst_id
AND E.inst_id = A.inst_id
ORDER BY a.inst_id; 2 3 4 5 6 7 8 9 10 11 12
Instance BSP Service Time
---------- ----------------
1 2.79270791
2 .196566999
SQL> SELECT A.inst_id "Instance", (A.value/E.value) "Consistent Read Build",
(B.value/E.value) "Log Flush Wait",(C.value/E.value) "Send Time",(D.value/E.value) "Receive Time"
FROM gv$sysstat A, gv$sysstat B, gv$sysstat C, gv$sysstat D, gv$sysstat E
WHERE A.name = 'gc cr block build time'
AND B.name = 'gc cr block send time'
AND C.name = 'gc cr block flush time'
AND D.name = 'gc cr block receive time'
AND E.name = 'gc cr blocks served'
AND B.inst_id = A.inst_id
AND C.inst_id = A.inst_id
AND D.inst_id = A.inst_id
AND E.inst_id = A.inst_id
ORDER BY a.inst_id; 2 3 4 5 6 7 8 9 10 11 12 13
Instance Consistent Read Build Log Flush Wait Send Time Receive Time
---------- --------------------- -------------- ---------- ------------
1 .00511811 .040944882 .002755906 2.63740157
2 .005443809 .010149474 .174017346 .007842775
SQL>
4.通过本节课的学习,说一下你对RAC性能的看法。
虽然rac有多个实例,但rac却只有一个数据库,受限于实例间的Interconnect的数据传输和锁,rac不一定比同等配置单实例性能高,得益于多节点rac可能会在并行执行上占优势,但仍需要以大量测试依据为准,rac更大意义在于为高可用提供了实例冗余。