【性能优化】 之 RAC架构性能优化
生活随笔
收集整理的這篇文章主要介紹了
【性能优化】 之 RAC架构性能优化
小編覺得挺不錯(cuò)的,現(xiàn)在分享給大家,幫大家做個(gè)參考.
?? ?
1.演示通過設(shè)置不同的服務(wù)，達(dá)到RAC業(yè)務(wù)分割的效果。<br>
2.對比將并行操作放在RAC多個(gè)節(jié)點(diǎn)執(zhí)行和單個(gè)節(jié)點(diǎn)執(zhí)行的效率。<br>
3.演示RAC的cache fusion對數(shù)據(jù)塊訪問效率的影響。<br>
4.寫出你對RAC的性能以及適用場景的觀點(diǎn)。<br>
============================================================================
1.演示通過設(shè)置不同的服務(wù)，達(dá)到RAC業(yè)務(wù)分割的效果。<br>
?? ?#1.1添加服務(wù)，一個(gè)為更新 rac_inst，一個(gè)為查詢? 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啟動(dòng)服務(wù)
?? ?[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 已在運(yùn)行
?? ?#1.3確認(rèn)服務(wù)是否啟動(dòng)
?? ?[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 看到詳細(xì)內(nèi)容
?? ?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
?? ?看到因?yàn)樘砑恿?-a 參數(shù)，在RACT1 故障時(shí)，會(huì)自動(dòng)切換到RACT2,
?? ?后面的查詢也看到都在實(shí)例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在客戶端設(shè)置TNSNAME:
?? ?原TNSNAME 訪問TDB1,TDB2 的內(nèi)容如下：
?? ?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)
?? ??? ?)
?? ?? )
?? ?#添加 以下內(nèi)容：
?? ?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]$
?? ?兩個(gè)不同的客戶端登錄。
?? ?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
?? ?#重新啟動(dòng)RAC_INST 到實(shí)例 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 在實(shí)例：tdb1中監(jiān)聽也啟動(dòng)了。
?? ?再測試一下：
?? ?[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>
?? ?看到，這時(shí)，登錄的是tdb1 實(shí)例中了。從而達(dá)到業(yè)務(wù)分離的處理。
?? ?其實(shí)如果不需要容錯(cuò)。你只要各自指定連接到 tdb1，tdb2，兩個(gè)實(shí)例，
?? ?也可以簡單的達(dá)到業(yè)務(wù)分離的功能。
---------------------------------------------------------------------
2.對比將并行操作放在RAC多個(gè)節(jié)點(diǎn)執(zhí)行和單個(gè)節(jié)點(diǎn)執(zhí)行的效率。<br>
?? ?2.1 準(zhǔn)備演示數(shù)據(jù)
?? ?[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 #登錄集群數(shù)據(jù)庫：
?? ?[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>
?? ?#在另一窗口查詢可看到，已在兩個(gè)節(jié)點(diǎn)中都有并行執(zhí)行：
?? ?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.
?? ?說明并行是在兩個(gè)節(jié)點(diǎn)中進(jìn)行的。
?? ?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 下面我們把并行只運(yùn)行在一個(gè)節(jié)點(diǎn)，看執(zhí)行的效率：
?? ?因?yàn)槲覀兩厦嫣砑恿艘粋€(gè)服務(wù)，把查詢業(yè)務(wù)進(jìn)行節(jié)點(diǎn)分離如下：
?? ?srvctl add service -d tdb -s rac_query -r tdb2 -a tdb1
?? ?那么應(yīng)該我們登錄后，只會(huì)在此節(jié)點(diǎn)中執(zhí)行，我們可以看看是否如此
?? ?[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
?? ?再次執(zhí)行代碼：
?? ?#為了對比，我們先清空緩存
?? ?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>
?? ?可以看到，這時(shí)是在同一個(gè)節(jié)點(diǎn)中執(zhí)行并行查詢的的。
?? ?查找生成日志，并生成可讀性日志文檔
?? ?[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?? ?
?? ?總耗費(fèi)時(shí)間： 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
?? ?********************************************************************************
?? ?從上面兩次的日志分析中可以看到，
?? ?第一次在兩個(gè)節(jié)點(diǎn)中進(jìn)行并行執(zhí)行中。為了方便對比，我再把最后的等待事件拿過來進(jìn)行對比 ：
?? ?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
?? ?
?? ?兩節(jié)點(diǎn)時(shí)為
?? ?CPU?? ??? ??? ?2.77?? ?（單節(jié)點(diǎn)時(shí)的2倍左右）
?? ?總耗費(fèi)時(shí)間： 67.75?? ?（單節(jié)點(diǎn)時(shí)的3倍）
?? ?#在兩個(gè)節(jié)點(diǎn)并行執(zhí)行的等待：
?? ?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?? ?#兩節(jié)點(diǎn)間數(shù)據(jù)查詢時(shí)的等待
?? ?? 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在并行查詢里被認(rèn)為是一個(gè)空閑等待，Deq = DEQUEUE，這是一個(gè)關(guān)于出隊(duì)的等待，
?? ?reap credit指在等待傳輸一個(gè)認(rèn)證信息或這說響應(yīng)信息，以確認(rèn)并行通道的暢通性。
?? ?從上面列表中也可以看到，在兩節(jié)點(diǎn)進(jìn)行并行查詢時(shí)，多出了很多的并行等待事件。
---------------------------------------------------------------------
3.演示RAC的cache fusion對數(shù)據(jù)塊訪問效率的影響。<br>
?? ?3.1為了使用演示不受到其他干擾，重新生成演示數(shù)據(jù)表，清空緩存。
?? ?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 在另一窗口查詢數(shù)據(jù)
?? ?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 進(jìn)行查詢測試
?? ?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'全局事件。
?? ?說明節(jié)點(diǎn)2 向節(jié)點(diǎn)1申請 數(shù)據(jù)讀取。進(jìn)行緩存共享。
?? ?=====================
?? ?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的性能以及適用場景的觀點(diǎn)。<br>
?? ?答：
?? ?oracle RAC 優(yōu)勢：
?? ??? ?高可用及負(fù)載均衡。
?? ?劣勢：
?? ??? ?內(nèi)存共享，全局鎖定（這會(huì)導(dǎo)致鎖增多，等待事件增加）
?? ?適用場景：
?? ??? ?1.要求數(shù)據(jù)業(yè)務(wù)冗余，不間斷。防止單點(diǎn)故障。
?? ??? ?2.數(shù)據(jù)庫響應(yīng)能力受到硬件的束縛（多節(jié)點(diǎn)進(jìn)行負(fù)載均衡）；
?? ??? ?3.大量的讀操作需要較高的性能支撐（多節(jié)點(diǎn)),使用RAC節(jié)點(diǎn)進(jìn)行擴(kuò)展。
總結(jié)
以上是生活随笔為你收集整理的【性能优化】 之 RAC架构性能优化的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 【性能优化】 之AWR 报告分析
- 下一篇： 【NOSQL ]】 redis 安装