【性能优化】 之 RAC架构性能优化
来源：互联网 发布：怎样在淘宝买东西啊 编辑：程序博客网 时间：2024/05/22 03:33
1.演示通过设置不同的服务，达到RAC业务分割的效果。<br>
2.对比将并行操作放在RAC多个节点执行和单个节点执行的效率。<br>
3.演示RAC的cache fusion对数据块访问效率的影响。<br>
4.写出你对RAC的性能以及适用场景的观点。<br>
============================================================================
1.演示通过设置不同的服务，达到RAC业务分割的效果。<br>
#1.1添加服务，一个为更新 rac_inst，一个为查询 rac_query
[grid@ract1 bin]$ su - oracle
口令：
[oracle@ract1 ~]$ srvctl add service -d tdb -s rac_inst -r tdb1 -a tdb2
[oracle@ract1 ~]$ srvctl add service -d tdb -s rac_query -r tdb2 -a tdb1
[oracle@ract1 ~]$
[oracle@ract1 ~]$ su - grid
口令：
#1.2启动服务
[grid@ract1 ~]$ srvctl enable service -d tdb -s rac_inst
PRCC-1010 : rac_inst 已启用
[grid@ract1 ~]$ srvctl enable service -d tdb -s rac_query
PRCC-1010 : rac_query 已启用
[grid@ract1 ~]$ srvctl start service -d tdb -s rac_query
[grid@ract1 ~]$ srvctl start service -d tdb -s rac_inst
PRCC-1014 : rac_inst 已在运行
#1.3确认服务是否启动
[grid@ract1 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.CRS.dg ora....up.type ONLINE ONLINE ract1
ora.DATA.dg ora....up.type ONLINE ONLINE ract1
ora....ER.lsnr ora....er.type ONLINE ONLINE ract1
ora....N1.lsnr ora....er.type ONLINE ONLINE ract1
ora....VERY.dg ora....up.type ONLINE ONLINE ract1
ora.asm ora.asm.type ONLINE ONLINE ract1
ora.eons ora.eons.type ONLINE ONLINE ract1
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora....network ora....rk.type ONLINE ONLINE ract1
ora.oc4j ora.oc4j.type OFFLINE OFFLINE
ora.ons ora.ons.type ONLINE ONLINE ract1
ora....SM1.asm application ONLINE ONLINE ract1
ora....T1.lsnr application ONLINE ONLINE ract1
ora.ract1.gsd application OFFLINE OFFLINE
ora.ract1.ons application ONLINE ONLINE ract1
ora.ract1.vip ora....t1.type ONLINE ONLINE ract1
ora....SM2.asm application ONLINE ONLINE ract2
ora....T2.lsnr application ONLINE ONLINE ract2
ora.ract2.gsd application OFFLINE OFFLINE
ora.ract2.ons application ONLINE ONLINE ract2
ora.ract2.vip ora....t1.type ONLINE ONLINE ract2
ora....ry.acfs ora....fs.type ONLINE ONLINE ract1
ora.scan1.vip ora....ip.type ONLINE ONLINE ract1
ora.tdb.db ora....se.type ONLINE ONLINE ract2
ora....ard.svc ora....ce.type ONLINE UNKNOWN ract1
ora....ail.svc ora....ce.type ONLINE UNKNOWN ract2
ora....nst.svc ora....ce.type ONLINE ONLINE ract2
ora....ery.svc ora....ce.type ONLINE ONLINE ract2
[grid@ract1 ~]$
也可以使用 crs_stat 看到详细内容
NAME=ora.tdb.rac_inst.svc
TYPE=ora.service.type
TARGET=ONLINE
STATE=ONLINE on ract2
NAME=ora.tdb.rac_query.svc
TYPE=ora.service.type
TARGET=ONLINE
STATE=ONLINE on ract2
看到因为添加了 -a 参数，在RACT1 故障时，会自动切换到RACT2,
后面的查询也看到都在实例2中。
#/etc/hosts 配置如下：
#public ip
192.168.10.81 ract1
192.168.10.82 ract2
#priv ip
192.168.0.81 ract1-private
192.168.0.82 ract2-private
#vip ip
192.168.10.83 ract1-vip
192.168.10.84 ract2-vip
#scan ip
192.168.10.80 ract-scan
#1.4在客户端设置TNSNAME:
原TNSNAME 访问TDB1,TDB2 的内容如下：
tdb1 =
(DESCRIPTION=
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.81)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = tdb)
)
)
tdb2 =
(DESCRIPTION=
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.82)(PORT = 1521))
(CONNECT_DATA =
(SERVICE_NAME = tdb)
)
)
#添加 以下内容：
RAC_INST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.80)(PORT = 1521))
(LOAD_BALANCE = yes)
(FAILOVER = ON)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rac_inst)
)
)
RAC_QUERY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.80)(PORT = 1521))
(LOAD_BALANCE = yes)
(FAILOVER = ON)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME =rac_query)
)
)
# 1.5 测试lsnrctl， TNSPING:
[oracle@ract2 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 08-JAN-2014 14:33:03
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.82)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 18-DEC-2013 12:12:09
Uptime 21 days 2 hr. 20 min. 54 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/app/grid/network/admin/listener.ora
Listener Log File /opt/app/oracle/diag/tnslsnr/ract2/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.82)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.84)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM2", status READY, has 1 handler(s) for this service...
Service "rac_inst" has 1 instance(s).
Instance "tdb2", status READY, has 1 handler(s) for this service...
Service "rac_query" has 1 instance(s).
Instance "tdb2", status READY, has 1 handler(s) for this service...
Service "tdb" has 1 instance(s).
Instance "tdb2", status READY, has 1 handler(s) for this service...
Service "tdbXDB" has 1 instance(s).
Instance "tdb2", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@ract2 ~]$
[oracle@ractdg3 admin]$ tnsping rac_inst
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 08-JAN-2014 14:24:35
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/opt/app/oracle/product/11.2.0/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.80)(PORT = 1521)) (LOAD_BALANCE = yes) (FAILOVER = ON) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = rac_inst)))
OK (20 msec)
[oracle@ractdg3 admin]$ tnsping rac_query
TNS Ping Utility for Linux: Version 11.2.0.1.0 - Production on 08-JAN-2014 14:24:39
Copyright (c) 1997, 2009, Oracle. All rights reserved.
Used parameter files:
/opt/app/oracle/product/11.2.0/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.10.80)(PORT = 1521)) (LOAD_BALANCE = yes) (FAILOVER = ON) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME =rac_query)))
OK (10 msec)
[oracle@ractdg3 admin]$
两个不同的客户端登录。
SQL> select distinct sid from v$mystat;
SID
----------
165
SQL> select instance_name,status from v$instance;
INSTANCE_NAME
------------------------------------------------
STATUS
------------------------------------
tdb2
OPEN
SQL>
SQL> select distinct sid from v$mystat;
SID
----------
163
SQL> select instance_name,status from v$instance;
INSTANCE_NAME
------------------------------------------------
STATUS
------------------------------------
tdb2
OPEN
SQL> exit
#重新启动RAC_INST 到实例 1 中：
[grid@ract1 ~]$ srvctl stop service -d tdb -s rac_inst -i tdb2
[grid@ract1 ~]$ srvctl start service -d tdb -s rac_inst -i tdb1
[grid@ract1 ~]$ crs_stat -t
Name Type Target State Host
------------------------------------------------------------
ora.CRS.dg ora....up.type ONLINE ONLINE ract1
ora.DATA.dg ora....up.type ONLINE ONLINE ract1
ora....ER.lsnr ora....er.type ONLINE ONLINE ract1
ora....N1.lsnr ora....er.type ONLINE ONLINE ract1
ora....VERY.dg ora....up.type ONLINE ONLINE ract1
ora.asm ora.asm.type ONLINE ONLINE ract1
ora.eons ora.eons.type ONLINE ONLINE ract1
ora.gsd ora.gsd.type OFFLINE OFFLINE
ora....network ora....rk.type ONLINE ONLINE ract1
ora.oc4j ora.oc4j.type OFFLINE OFFLINE
ora.ons ora.ons.type ONLINE ONLINE ract1
ora....SM1.asm application ONLINE ONLINE ract1
ora....T1.lsnr application ONLINE ONLINE ract1
ora.ract1.gsd application OFFLINE OFFLINE
ora.ract1.ons application ONLINE ONLINE ract1
ora.ract1.vip ora....t1.type ONLINE ONLINE ract1
ora....SM2.asm application ONLINE ONLINE ract2
ora....T2.lsnr application ONLINE ONLINE ract2
ora.ract2.gsd application OFFLINE OFFLINE
ora.ract2.ons application ONLINE ONLINE ract2
ora.ract2.vip ora....t1.type ONLINE ONLINE ract2
ora....ry.acfs ora....fs.type ONLINE ONLINE ract1
ora.scan1.vip ora....ip.type ONLINE ONLINE ract1
ora.tdb.db ora....se.type ONLINE ONLINE ract1
ora....ard.svc ora....ce.type ONLINE UNKNOWN ract1
ora....ail.svc ora....ce.type ONLINE UNKNOWN ract2
ora....nst.svc ora....ce.type ONLINE ONLINE ract1
ora....ery.svc ora....ce.type ONLINE ONLINE ract2
[grid@ract1 ~]$
[oracle@ract1 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 08-JAN-2014 14:42:25
Copyright (c) 1991, 2009, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.10.81)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 18-DEC-2013 11:47:37
Uptime 21 days 2 hr. 54 min. 48 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /opt/app/grid/network/admin/listener.ora
Listener Log File /opt/app/oracle/diag/tnslsnr/ract1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.81)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.10.83)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM1", status READY, has 1 handler(s) for this service...
Service "rac_inst" has 1 instance(s).
Instance "tdb1", status READY, has 1 handler(s) for this service...
Service "tdb" has 1 instance(s).
Instance "tdb1", status READY, has 1 handler(s) for this service...
Service "tdbXDB" has 1 instance(s).
Instance "tdb1", status READY, has 1 handler(s) for this service...
The command completed successfully
[oracle@ract1 ~]$
可以看到rac_inst 在实例：tdb1中监听也启动了。
再测试一下：
[oracle@ractdg3 admin]$
[oracle@ractdg3 admin]$ sqlplus tang/sa@rac_inst
SQL*Plus: Release 11.2.0.1.0 Production on Wed Jan 8 14:44:10 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and Data Mining options
SQL> select distinct sid from v$mystat;
SID
----------
41
SQL> select instance_name,status from v$instance;
INSTANCE_NAME
------------------------------------------------
STATUS
------------------------------------
tdb1
OPEN
SQL>
看到，这时，登录的是tdb1 实例中了。从而达到业务分离的处理。
其实如果不需要容错。你只要各自指定连接到 tdb1，tdb2，两个实例，
也可以简单的达到业务分离的功能。
---------------------------------------------------------------------
2.对比将并行操作放在RAC多个节点执行和单个节点执行的效率。<br>
2.1 准备演示数据
[oracle@ractdg3 admin]$ sqlplus tang/sa@tdb
SQL*Plus: Release 11.2.0.1.0 Production on Thu Jan 9 09:56:32 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and Data Mining options
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
--------------- ---------------------
tdb1 OPEN
SQL> set linesize 500;
SQL> set pagesize 800;
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
------------------------------------------------
tdb1 OPEN
SQL> create table t15 as select * from dba_objects;
Table created.
SQL> insert into t15 select * from dba_objects;
70635 rows created.
SQL> commit;
Commit complete.
SQL> insert into t15 select * from dba_objects;
SQL> insert into t15 select * from dba_objects;
SQL> select count(0) from t15;
COUNT(0)
----------
282540
SQL> alter table t15 parallel 5;
SQL> exec dbms_stats.gather_table_stats(user,'T15');
SQL> set autotrace trace exp;
SQL> select count(*) from t15;
Execution Plan
----------------------------------------------------------
Plan hash value: 1196042110
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 245 (1)| 00:00:03 | | | |
| 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 | | 282K| 245 (1)| 00:00:03 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| T15 | 282K| 245 (1)| 00:00:03 | Q1,00 | PCWP | |
--------------------------------------------------------------------------------------------------------
SQL>
2.2 #登录集群数据库：
[oracle@ractdg3 admin]$ sqlplus tang/sa@tdb
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 10 11:17:42 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and Data Mining options
SQL> alter system flush shared_pool;
System altered.
SQL> /
System altered.
SQL> /
SQL> select distinct sid from v$mystat;
SID
----------
164
SQL> alter session set tracefile_identifier=at2node;
SQL> alter session set events '10046 trace name context forever,level 12';
SQL> declare a int;
begin
for i in 1..100 loop
select count(*) into a from t15;
end loop;
end;
/
QL> alter session set events '10046 trace name context off';
Session altered.
SQL>
#在另一窗口查询可看到，已在两个节点中都有并行执行：
select inst_id,sid,qcsid,qcinst_id,degree from gv$px_session where qcsid=43 order by sid;
SQL> /
INST_ID SID QCSID QCINST_ID DEGREE
---------- ---------- ---------- ---------- ----------
1 167 164 2 5
1 164 164 2 5
1 143 164 2 5
2 47 164 2 5
2 168 164 2 5
2 164 164
6 rows selected.
说明并行是在两个节点中进行的。
2.3#查看跟踪文档：
[oracle@ract2 trace]$ tkprof tdb2_ora_15014_AT2NODE.trc at2node.log
[oracle@ract2 trace]$ vi at2node.log
-----------------------------------------------------------------------
SQL ID: 3fxrzqh4ffn8v
Plan Hash: 1196042110
SELECT COUNT(*)
FROM
T15
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.63 47.19 0 500 0 0
Fetch 100 2.13 20.55 0 0 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 201 2.77 67.75 0 500 0 100
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 73 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=5 pr=0 pw=0 time=0 us)
5 PX COORDINATOR (cr=5 pr=0 pw=0 time=1388 us)
0 PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
0 SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)
0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=245 size=0 card=282540)
0 TABLE ACCESS FULL T15 (cr=0 pr=0 pw=0 time=0 us cost=245 size=0 card=282540)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PX Nsq: PQ load info query 96 0.49 22.18
ges message buffer allocation 831 0.00 0.01
enq: PS - contention 440 0.60 11.06
KJC: Wait for msg sends to complete 75 0.00 0.00
DFS lock handle 8 0.60 1.35
enq: PI - contention 3 0.20 0.20
latch: ges resource hash list 1 0.00 0.00
os thread startup 2 0.83 1.08
PX Deq: reap credit 12287 0.10 0.57
PX Deq: Join ACK 385 0.46 3.85
PX Deq: Parse Reply 323 0.65 2.47
PX Deq: Execute Reply 4100 0.49 15.01
reliable message 295 0.87 2.52
PX Deq: Signal ACK RSG 167 0.14 0.88
PX Deq: Signal ACK EXT 217 0.17 0.34
PX qref latch 559 0.07 0.11
IPC send completion sync 248 0.33 1.18
rdbms ipc reply 200 0.35 0.66
PX Deq: Slave Session Stats 55 0.00 0.03
latch free 1 0.00 0.00
********************************************************************************
2.4 下面我们把并行只运行在一个节点，看执行的效率：
因为我们上面添加了一个服务，把查询业务进行节点分离如下：
srvctl add service -d tdb -s rac_query -r tdb2 -a tdb1
那么应该我们登录后，只会在此节点中执行，我们可以看看是否如此
[oracle@ractdg3 admin]$ sqlplus tang/sa@rac_query
SQL*Plus: Release 11.2.0.1.0 Production on Fri Jan 10 11:32:56 2014
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and Data Mining options
SQL>
SQL> select distinct sid from v$mystat;
SID
----------
164
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
--------------------- ---------------
tdb2 OPEN
再次执行代码：
#为了对比，我们先清空缓存
SQL> alter system flush shared_pool;
System altered.
SQL> /
System altered.
SQL> /
System altered.
SQL> alter session set tracefile_identifier=test;
SQL> alter session set events '10046 trace name context forever,level 12';
SQL> declare a int;
begin
for i in 1..100 loop
select count(*) into a from t15;
end loop;
end;
/
QL> alter session set events '10046 trace name context off';
在另一窗口查询：
SQL> select inst_id,sid,qcsid,qcinst_id,degree from gv$px_session where qcsid=164 order by inst_id;
no rows selected
SQL> /
INST_ID SID QCSID QCINST_ID DEGREE
---------- ---------- ---------- ---------- ----------
2 168 164 2 5
2 46 164 2 5
2 164 164
2 47 164 2 5
2 160 164 2 5
2 163 164 2 5
6 rows selected.
SQL>
可以看到，这时是在同一个节点中执行并行查询的的。
查找生成日志，并生成可读性日志文档
[oracle@ract2 trace]$ vi tdb2_ora_14269_AT_ONENODE.trc
[oracle@ract2 trace]$ tkprof tdb2_ora_14269_AT_ONENODE.trc par_on_node.log
SQL ID: 3fxrzqh4ffn8v
Plan Hash: 1196042110
SELECT COUNT(*)
FROM
T15
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.27 12.22 0 500 0 0
Fetch 100 0.99 17.01 0 0 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 201 1.28 29.24 0 500 0 100
CPU 2.77
总耗费时间： 29.24
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 73 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=5 pr=0 pw=0 time=0 us)
5 PX COORDINATOR (cr=5 pr=0 pw=0 time=28 us)
0 PX SEND QC (RANDOM) :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
0 SORT AGGREGATE (cr=0 pr=0 pw=0 time=0 us)
0 PX BLOCK ITERATOR (cr=0 pr=0 pw=0 time=0 us cost=245 size=0 card=282540)
0 TABLE ACCESS FULL T15 (cr=0 pr=0 pw=0 time=0 us cost=245 size=0 card=282540)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
ges message buffer allocation 405 0.00 0.00
enq: PS - contention 203 0.39 8.86
KJC: Wait for msg sends to complete 10 0.00 0.00
PX Deq: Join ACK 290 0.24 0.73
PX Deq: Signal ACK RSG 141 0.17 0.36
PX Deq: Signal ACK EXT 448 0.17 0.41
rdbms ipc reply 257 0.01 0.07
PX Deq: Parse Reply 315 0.15 1.20
PX Deq: Execute Reply 5011 0.48 14.17
PX Deq: Slave Session Stats 315 0.17 0.40
PX qref latch 4 0.00 0.00
latch: ges resource hash list 1 0.00 0.00
********************************************************************************
从上面两次的日志分析中可以看到，
第一次在两个节点中进行并行执行中。为了方便对比，我再把最后的等待事件拿过来进行对比 ：
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.63 47.19 0 500 0 0
Fetch 100 2.13 20.55 0 0 0 100
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 201 2.77 67.75 0 500 0 100
两节点时为
CPU 2.77 （单节点时的2倍左右）
总耗费时间： 67.75 （单节点时的3倍）
#在两个节点并行执行的等待：
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
PX Nsq: PQ load info query 96 0.49 22.18 #两节点间数据查询时的等待
ges message buffer allocation 831 0.00 0.01
enq: PS - contention 440 0.60 11.06 #
KJC: Wait for msg sends to complete 75 0.00 0.00
DFS lock handle 8 0.60 1.35
enq: PI - contention 3 0.20 0.20
latch: ges resource hash list 1 0.00 0.00
os thread startup 2 0.83 1.08
PX Deq: reap credit 12287 0.10 0.57
PX Deq: Join ACK 385 0.46 3.85
PX Deq: Parse Reply 323 0.65 2.47
PX Deq: Execute Reply 4100 0.49 15.01
reliable message 295 0.87 2.52
PX Deq: Signal ACK RSG 167 0.14 0.88
PX Deq: Signal ACK EXT 217 0.17 0.34
PX qref latch 559 0.07 0.11
IPC send completion sync 248 0.33 1.18
rdbms ipc reply 200 0.35 0.66
PX Deq: Slave Session Stats 55 0.00 0.03
latch free 1 0.00 0.00
********************************************************************************
PX Deq: reap credit在并行查询里被认为是一个空闲等待，Deq = DEQUEUE，这是一个关于出队的等待，
reap credit指在等待传输一个认证信息或这说响应信息，以确认并行通道的畅通性。
从上面列表中也可以看到，在两节点进行并行查询时，多出了很多的并行等待事件。
---------------------------------------------------------------------
3.演示RAC的cache fusion对数据块访问效率的影响。<br>
3.1为了使用演示不受到其他干扰，重新生成演示数据表，清空缓存。
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
------------------------------------------------ ------------------------------------
tdb2 OPEN
SQL>
SQL> select distinct sid from v$mystat;
SID
----------
164
SQL> drop table t15;
Table dropped.
SQL> create table t15 as select * from dba_objects;
Table created.
SQL> exec dbms_stats.gather_table_stats(user,'T15');
PL/SQL procedure successfully completed.
SQL>
SQL> alter system flush shared_pool;
System altered.
SQL> /
/
/
System altered.
SQL>
System altered.
SQL>
System altered.
SQL> alter system flush buffer_cache;
System altered.
SQL> /
System altered.
SQL> /
System altered.
3.2 在另一窗口查询数据
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
------------------------------------------------
tdb1 OPEN
SQL> select distinct sid from v$mystat;
SID
----------
15
SQL>
SQL> set autot trace;
SQL> select count(*) from t15;
Execution Plan
----------------------------------------------------------
Plan hash value: 74626064
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 283 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T15 | 70637 | 283 (1)| 00:00:04 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
224 recursive calls
0 db block gets
1037 consistent gets
1016 physical reads
0 redo size
424 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
3.3 回到窗口1 进行查询测试
SQL> set autot trace;
SQL> alter session set events '10046 trace name context forever,level 8';
Session altered.
SQL> select count(*) from t15;
Execution Plan
----------------------------------------------------------
Plan hash value: 74626064
-------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)| Time |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 283 (1)| 00:00:04 |
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | TABLE ACCESS FULL| T15 | 70637 | 283 (1)| 00:00:04 |
-------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1012 consistent gets
1008 physical reads
0 redo size
424 bytes sent via SQL*Net to client
420 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> alter session set events '10046 trace name context off';
Session altered.
SQL>
3.4 打开跟踪文件：
[oracle@ract2 trace]$ tkprof tdb2_ora_15014_FUSION.trc fusion.log
TKPROF: Release 11.2.0.1.0 - Development on Fri Jan 10 13:41:32 2014
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
[oracle@ract2 trace]$ vi fusion.log
在原跟踪文件中，可以看到大量的 'gc cr multi block request'，'db file scattered read'全局事件。
说明节点2 向节点1申请 数据读取。进行缓存共享。
=====================
PARSING IN CURSOR #7 len=24 dep=0 uid=73 oct=3 lid=73 tim=1389333564024238 hv=4058353034 ad='ac16b754' sqlid='bywujvgsyb3ca'
select count(*) from t15
END OF STMT
PARSE #7:c=2000,e=2427,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,plh=74626064,tim=1389333564024233
EXEC #7:c=0,e=67,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=74626064,tim=1389333564024700
WAIT #7: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=1389333564024784
WAIT #7: nam='Disk file operations I/O' ela= 7 FileOperation=2 fileno=5 filetype=2 obj#=-1 tim=1389333564025097
WAIT #7: nam='ges message buffer allocation' ela= 4 pool=0 request=1 allocated=0 obj#=-1 tim=1389333564025689
WAIT #7: nam='gc cr grant 2-way' ela= 92857 p1=5 p2=4226 p3=4 obj#=76989 tim=1389333564118838
WAIT #7: nam='db file sequential read' ela= 81222 file#=5 block#=4226 blocks=1 obj#=76989 tim=1389333564200577
WAIT #7: nam='ges message buffer allocation' ela= 209 pool=1 request=1 allocated=0 obj#=76989 tim=1389333564202501
WAIT #7: nam='gc cr multi block request' ela= 78014 file#=5 block#=4231 class#=1 obj#=76989 tim=1389333564282089
WAIT #7: nam='db file scattered read' ela= 83082 file#=5 block#=4227 blocks=5 obj#=76989 tim=1389333564365346
WAIT #7: nam='ges message buffer allocation' ela= 2 pool=1 request=1 allocated=0 obj#=76989 tim=1389333564367837
WAIT #7: nam='gc cr multi block request' ela= 220124 file#=5 block#=4239 class#=1 obj#=76989 tim=1389333564589247
WAIT #7: nam='db file scattered read' ela= 2008 file#=5 block#=4232 blocks=8 obj#=76989 tim=1389333564591417
WAIT #7: nam='ges message buffer allocation' ela= 36 pool=1 request=1 allocated=0 obj#=76989 tim=1389333564593868
WAIT #7: nam='gc cr multi block request' ela= 185769 file#=5 block#=4247 class#=1 obj#=76989 tim=1389333564781103
WAIT #7: nam='db file scattered read' ela= 82448 file#=5 block#=4241 blocks=7 obj#=76989 tim=1389333564863665
WAIT #7: nam='ges message buffer allocation' ela= 2 pool=1 request=1 allocated=0 obj#=76989 tim=1389333564866072
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.32 2.12 1008 1012 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.32 2.12 1008 1012 0 1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 73
Rows Row Source Operation
------- ---------------------------------------------------
1 SORT AGGREGATE (cr=1012 pr=1008 pw=0 time=0 us)
70637 TABLE ACCESS FULL T15 (cr=1012 pr=1008 pw=0 time=157523 us cost=283 size=0 card=70637)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 2 0.00 0.00
ges message buffer allocation 35 0.00 0.00
gc cr grant 2-way 1 0.01 0.01
db file sequential read 1 0.16 0.16
gc cr multi block request 26 0.31 0.69
db file scattered read 24 0.17 0.37
SQL*Net message from client 2 4.31 4.31
********************************************************************************
---------------------------------------------------------------------
4.写出你对RAC的性能以及适用场景的观点。<br>
答：
oracle RAC 优势：
高可用及负载均衡。
劣势：
内存共享，全局锁定（这会导致锁增多，等待事件增加）
适用场景：
1.要求数据业务冗余，不间断。防止单点故障。
2.数据库响应能力受到硬件的束缚（多节点进行负载均衡）；
3.大量的读操作需要较高的性能支撑（多节点),使用RAC节点进行扩展。
0 0
- 【性能优化】 之 RAC架构性能优化
- 【性能优化】 之 RAC架构性能优化
- 优化系统架构性能
- <<Oracle数据库性能优化艺术（第五期）>> 第15周 基于Oracle RAC架构的性能优化
- 性能优化之数据库优化
- 性能优化之数据库优化
- 性能优化之布局优化
- 性能优化之布局优化
- 性能优化之数据库优化
- 性能优化之布局优化
- 性能优化之布局优化
- 性能优化之数据库优化
- 性能优化之布局优化
- 性能优化之布局优化
- 性能优化之布局优化
- 性能优化之数据库优化
- 性能优化之布局优化
- 性能优化之布局优化
- CharArrayWriter 源码分析
- 当当低估了，唯品会高估了
- mysql大小写敏感（默认为1，不敏感）
- 让UIWebView弹出键盘上的按钮显示中文
- 伟亚《2014年度企业素质提升管理培训》经典内训课程实战系列
- 【性能优化】 之 RAC架构性能优化
- 向XML插入节点
- 云计算管理工监控具
- openoffice Calc问题
- JSP中用Excel简单实现页面打印
- iOS imageIO完成渐进加载图片
- Python脚本转换成windows的exe可执行程序
- [LeetCode]89.Gray Code
- 清除UIWebView的背景色的方法
