<< Oracle高可用>>部分书面作业 - 第四课 RAC-性能分析优化
来源：互联网 发布：网络平台建设费用 编辑：程序博客网 时间：2024/05/17 07:21
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更大意义在于为高可用提供了实例冗余。
- << Oracle高可用>>部分书面作业 - 第四课 RAC-性能分析优化
- << Oracle高可用>>部分书面作业 - 第二课 RAC-原理和安装
- << Oracle高可用>>部分书面作业 - 第三课 RAC-日常管理
- << Oracle高可用>>部分书面作业 - 第五课 RAC-故障定位和处理
- << Oracle高可用>>部分书面作业 - 第九课 RAC+DG技术的应用
- << Oracle高可用>>部分书面作业 - 第八课 Data Gaurd 故障分析和处理
- << Oracle高可用>>部分书面作业 - 第十课 Oracle Golden gate 概念和机制
- << Oracle高可用>>部分书面作业 - 第十一课 Oracle Golden gate 设计及应用
- << Oracle高可用>>部分书面作业 - 第十二课 Oracle 流技术(streams)
- << Oracle高可用>>部分书面作业 - 第十三课 Oracle高级复制
- << Oracle高可用>>部分书面作业 - 第六课 Data Gaurd 概念和配置
- << Oracle高可用>>部分书面作业 - 第七课 Data Gaurd 设计和管理
- Python自然语言分析（第四期）：第1课书面作业
- Python自然语言分析（第四期）：第2课书面作业
- Python自然语言分析（第四期）：第3课书面作业
- 使用 Oracle RAC 和 PHP 实现高性能和高可用
- 《Oracle DBA 高可用、备份恢复与性能优化》目录
- 书面作业
- << Oracle高可用>>部分书面作业 - 第三课 RAC-日常管理
- 我的hadoop初学程序------简单数据排序-------Sort
- Sendmail日志中经常有这样的提示：Connection refused
- Tomcat Connector字符编码设置
- php错误提示:Call-time pass-by-reference has been deprecated
- << Oracle高可用>>部分书面作业 - 第四课 RAC-性能分析优化
- 如何设置JIRA在创建问题时将当前经办人指定为报告人
- << Oracle高可用>>部分书面作业 - 第五课 RAC-故障定位和处理
- << Oracle高可用>>部分书面作业 - 第六课 Data Gaurd 概念和配置
- jQuery数据缓存-data(name)
- 安卓入门——拨打电话和发送短信案例
- << Oracle高可用>>部分书面作业 - 第七课 Data Gaurd 设计和管理
- spring面试题集锦（收藏）
- Yii 给form表单 添加class
