注册 登录  
 加关注
查看详情
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

Oracle、MySQL资料及经验

.

 
 
 

日志

 
 

验证RAC的缓存融合机制  

2013-02-28 16:28:19|  分类: 集群 |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

RAC的缓存融合机制简单的说就是:如果A实例已经有某表的数据块了,B实例请求该表的数据时,优先通过内联网络从A实例中获取。

下面验证一下这个机制,验证思路是:
1.建一个大表t,然后把RAC的实例都关掉,这样任何实例的内存中都没有表t的内容了。
2.启动实例1,其他实例不启动,跟踪以下操作:对表t全表扫描两次,保证表t的内容都进入了实例1的内存中。
3.启动实例2,跟踪以下操作:对表t全表扫描一次。
4.打开第3步中的的跟踪文件,查看实例2对表t全表扫描时,是否有很多IO操作及当时的等待事件,如果没有IO操作,则表明表t的数据是从实例1传过去的,也就验证了RAC的缓存融合机制。

先建一个稍大一点的测试表t:
[oracle@logdb ~]$ sqlplus
u1/orcl707@rac1

SQL> create table t as select * from dba_segments;

Table created.

SQL> insert into t select * from t;

6625 rows created.

SQL> insert into t select * from t;

13250 rows created.

SQL> commit;

Commit complete.

查询表t大小:
SQL> select segment_name,bytes/1024/1024 M,blocks from user_segments where segment_name='T';

SEGMENT_NAME     M       BLOCKS
----------    ---------  -----------
T               4        512
表t当前有4M大小,512个块。

当前rac的实例内存中有表t的内容,为保证内存中完全没有表t内容,我们把rac的每个节点的实例都关掉。
实例1关掉:
[oracle@rac1 trace]$ sqlplus / as sysdba                                       
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.


实例2关掉:
[oracle@rac2 trace]$ sqlplus / as sysdba             
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

重新启动实例1,为验证实例2是通过内联网络读取表t的,此时实例2不启动。
[oracle@rac1 trace]$ sqlplus / as sysdba
SQL> startup
ORACLE instance started.

Total System Global Area 5044088832 bytes
Fixed Size                  2237048 bytes
Variable Size            1023413640 bytes
Database Buffers         4009754624 bytes
Redo Buffers                8683520 bytes
Database mounted.
Database opened.


[oracle@logdb ~]$ sqlplus u1/orcl707@rac1

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 28 11:35:46 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application ting options

检查登录的实例:
SQL> select instance_number,instance_name from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
              1 orcl1

开始用10046事件跟踪
SQL> alter session set events '10046 trace name context forever, level 12';

通过全表扫描,把表所有数据块都读入内存
SQL> select /*+ rac1 first time */ count(*) from t;

  COUNT(*)
----------
      106000

再执行一次,确保表所有数据块都读入了内存
SQL> select /*+ rac1 second time */ count(*) from t;

  COUNT(*)
----------
      106000

结束跟踪
SQL> alter session set events '10046 trace name context off';

Session altered.

查跟踪文件:
SQL> SELECT pa.value || '/' || i.instance_name || '_ora_' || pr.spid || '.trc' AS trace_file
  2   FROM v$session s, v$process pr, v$parameter pa, v$instance i
  3   WHERE s.username = USER
  4   AND s.paddr = pr.addr
  5   AND pa.name = 'user_dump_dest';

TRACE_FILE
--------------------------------------------------------------------------------
/u01/oracle/diag/rdbms/orcl/orcl1/trace/orcl1_ora_23231.trc

 

到实例1上执行:
[oracle@rac1 ~]$ cd /u01/oracle/diag/rdbms/orcl/orcl1/trace/
[oracle@rac1 trace]$ tkprof orcl1_ora_23231.trc 1.txt

TKPROF: Release 11.2.0.3.0 - Development on Thu Feb 28 11:41:11 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

查看1.txt,只把查询表t的部分找出来
SQL ID: 9dprdd03260rg Plan Hash: 2966233522

select /*+ rac1 first time */ count(*)
from
 t


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          2          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.06       0.12       1709       2046          1           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.07       0.12       1711       2048          1           1
--第一次查询有1711个物理读和2048个逻辑读。
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=2046 pr=1709 pw=0 time=122141 us)
    106000     106000     106000   TABLE ACCESS FULL T (cr=2046 pr=1709 pw=0 time=25383 us cost=551 size=0 card=126971)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  row cache lock                                  2        0.00          0.00
  Disk file operations I/O                        1        0.00          0.00
  db file sequential read                         9        0.00          0.00
  SQL*Net message to client                       2        0.00          0.00
  db file scattered read                         94        0.00          0.05
  SQL*Net message from client                     2       18.18         18.18
********************************************************************************
SQL ID: 8xvgzh5swhfwx Plan Hash: 2966233522

select /*+ rac1 second time */ count(*)
from
 t


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.02       0.02          0       2046          1           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.02       0.02          0       2047          1           1
--第二次查询时没有物理读,有102个逻辑读,表明表t已经全部读到内存中了。
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=2046 pr=0 pw=0 time=27346 us)
    106000     106000     106000   TABLE ACCESS FULL T (cr=2046 pr=0 pw=0 time=35715 us cost=551 size=0 card=126971)


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
  SQL*Net message from client                     2        5.76          5.76
********************************************************************************

 

此时到第二个节点把实例2打开:
[oracle@rac2 trace]$ sqlplus / as sysdba
SQL> startup
ORACLE instance started.

Total System Global Area 5044088832 bytes
Fixed Size                  2237048 bytes
Variable Size            1107299720 bytes
Database Buffers         3925868544 bytes
Redo Buffers                8683520 bytes
Database mounted.
Database opened.


登录实例2开始测试:
[oracle@logdb ~]$ sqlplus
u1/orcl707@rac2

SQL*Plus: Release 11.2.0.1.0 Production on Thu Feb 28 11:35:46 2013

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application ting options

检查登录的实例:
SQL> select instance_number,instance_name from v$instance;

INSTANCE_NUMBER INSTANCE_NAME
--------------- ----------------
              2 orcl2


开始用10046事件跟踪
SQL> alter session set events '10046 trace name context forever, level 12';

通过全表扫描,把表所有数据块都读入内存
SQL> select /*+ rac2 other node */ count(*) from t;

  COUNT(*)
----------
      106000

结束跟踪
SQL> alter session set events '10046 trace name context off';

Session altered.

查跟踪文件:
SQL> SELECT pa.value || '/' || i.instance_name || '_ora_' || pr.spid || '.trc' AS trace_file
  2   FROM v$session s, v$process pr, v$parameter pa, v$instance i
  3   WHERE s.username = USER
  4   AND s.paddr = pr.addr
  5   AND pa.name = 'user_dump_dest';

TRACE_FILE
--------------------------------------------------------------------------------
/u01/oracle/diag/rdbms/orcl/orcl2/trace/orcl2_ora_30837.trc

 

到实例2上执行:
[oracle@rac2 ~]$ cd /u01/oracle/diag/rdbms/orcl/orcl2/trace/
[oracle@rac2 trace]$ tkprof orcl2_ora_30837.trc 2.txt

TKPROF: Release 11.2.0.3.0 - Development on Thu Feb 28 11:41:11 2013

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

打开文件2.txt,只把查询t的部分找出来
SQL ID: ac6ynbq7amc05 Plan Hash: 2966233522

select /*+ rac2 other node */ count(*)
from
 t


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.11       0.19          0       2046          1           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.12       0.20          0       2048          1           1
在实例2上第一次读取表t数据,发现没有物理读,有2048个逻辑读,说明实例2获取表t的数据没有读盘柜。
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=2046 pr=0 pw=0 time=197813 us)
    106000     106000     106000   TABLE ACCESS FULL T (cr=2046 pr=0 pw=0 time=16751 us cost=551 size=0 card=126971)


Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  row cache lock                                  2        0.00          0.00
  Disk file operations I/O                        1        0.00          0.00
  gc cr block 2-way                               1        0.00          0.00
  gc current block 2-way                          8        0.00          0.00
  SQL*Net message to client                       2        0.00          0.00
  gc cr multi block request                      94        0.00          0.13
  SQL*Net message from client                     2       13.68         13.68

看在实例2上执行select /*+ rac2 other node */ count(*) from  t时的等待事件,等待次数最多的是gc cr multi block request,而gc cr multi block request就是通过内联网进行数据传输。

  评论这张
 
阅读(473)| 评论(0)
推荐 转载

历史上的今天

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2018