Oracle RAC Study之--Cache Fusion

Oracle RAC Study之--Cache Fusion

Concept of cache fusion

Cache Fusion basically is about fusing the memory buffer cache of multiple instance into one single cache. For example if we have 3 instance in a RAC which is using the same datafiles and each instance is having its own memory buffer cache in there own SGA, then cache fusion will make the database behave as if it has a single instance and the total buffer cache is the sum of buffer cache of all the 3 instance. Below figure shows what I mean.

This behavior is possible because of high speed interconnect existing in the cluster between each instance. Each of instance is connected to other instance using a high-speed interconnect. This makes it possible to share the memory between 2 or more servers. Previously only datafile sharing was possible, now because of interconnect, even the cache memory can be shared.

But how this helps? Well, for example if we have a data block in one of the instance and its updating the block and other instance needs the same data block then this data block can be copied from holding instance buffer cache to requesting instance buffer cache using this high-speed interconnect. This high speed interconnect is a private connection made just for sending data blocks and more by instances. External users cannot use this connection. It is this interconnect which makes multiple server behave like a cluster. These servers are bind together using this interconnect.

Moving further, now we know how the cluster is formed and what is the back bone of cluster and what exactly we call "cache fusion". Next we will see how cache fusion works. But before that we need to discuss few important headings which is very important to understand.

We will discuss following topics before discussing Cache Fusion

（1）Cache Coherency

（2）Multi-Version consistency model

（3）Resource Co-ordination - Synchronization

（4）Global Cache Service (GCS)

（5）Global Enqueue Service

（6）Global Resource Directory

（7）GCS resource modes and roles

（8）Past Images

（9）Block access modes and buffer states

I promise this wont be too heavy. Lets look into the overview of these concepts. I wont be going into the details, just sufficient for you to understand cache fusion.

案例分析：

数据库环境：Oracle 10g RAC

[root@aix211 /]#crs_stat -t

Name           Type           Target    State     Host------------------------------------------------------------ora....11.lsnr application    ONLINE    ONLINE    aix211ora.aix211.gsd application    ONLINE    ONLINE    aix211ora.aix211.ons application    ONLINE    ONLINE    aix211ora.aix211.vip application    ONLINE    ONLINE    aix211ora....12.lsnr application    ONLINE    ONLINE    aix212ora.aix212.gsd application    ONLINE    ONLINE    aix212ora.aix212.ons application    ONLINE    ONLINE    aix212ora.aix212.vip application    ONLINE    ONLINE    aix212ora.prod.db    application    ONLINE    ONLINE    aix211ora....d1.inst application    ONLINE    ONLINE    aix211ora....d2.inst application    ONLINE    ONLINE    aix212

[oracle@aix211 admin]$sqlplus '/as sysdba'SQL*Plus: Release 10.2.0.1.0 - Production on Mon Nov 17 15:25:11 2014Copyright (c) 1982, 2005, Oracle.  All rights reserved.Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit ProductionWith the Partitioning, Real Application Clusters, OLAP and Data Mining options15:25:11 SYS@ prod1>select instance_name,status from gv$instance;INSTANCE_NAME    STATUS---------------- ------------prod1            OPENprod2            OPEN

1、在Instance 1上执行select查询

15:25:26 SYS@ prod1>select instance_name from v$instance;INSTANCE_NAME----------------prod115:26:06 SYS@ prod1>create table t1 as select * from dba_objects;Table created.做10046分析：15:27:51 SYS@ prod1>alter session set events '10046 trace name context forever,level 12';Session altered.15:28:20 SYS@ prod1>select /*+ first_time */ count(*) from t1;  COUNT(*)----------     5039215:28:55 SYS@ prod1>select /*+ second time */ count(*) from t1;  COUNT(*)----------     5039215:30:21 SYS@ prod1>alter session set events '10046 trace name context off';Session altered.通过trace后，查看分析结果:select /*+ first_time */ count(*)from t1call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          1          0           0Execute      1      0.00       0.00          0          0          0           0Fetch        2      0.04       0.08        279        697          0           1------- ------  -------- ---------- ---------- ---------- ----------  ----------total        4      0.04       0.08        279        698          0           1Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: SYSRows     Row Source Operation-------  ---------------------------------------------------      1  SORT AGGREGATE (cr=697 pr=279 pw=0 time=81436 us)  50392   TABLE ACCESS FULL T1 (cr=697 pr=279 pw=0 time=1457 us)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  db file scattered read                         42        0.00          0.04  gc cr multi block request                      14        0.00          0.01  db file sequential read                         8        0.00          0.00  gc cr grant 2-way                               6        0.00          0.00  SQL*Net message from client                     2        0.00          0.00********************************************************************************select /*+ second time */ count(*)from t1call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          1          0           0Execute      1      0.00       0.00          0          0          0           0Fetch        2      0.02       0.01          0        697          0           1------- ------  -------- ---------- ---------- ---------- ----------  ----------total        4      0.02       0.01          0        698          0           1

2、在Instance 2上做同样的操作

15:56:13 SYS@ prod2>select instance_name from v$instance;INSTANCE_NAME----------------prod215:46:28 SYS@ prod2>alter session set events '10046 trace name context forever,level 12';Session altered.15:46:44 SYS@ prod2>select /*+ on_the_other_node */ count(*) from t1;  COUNT(*)----------     5039215:47:24 SYS@ prod2>alter session set events '10046 trace name context off';Session altered.[oracle@aix212@ ~]$ls -lt /u01/app/oracle/admin/prod/udump/|moretotal 344-rw-r-----    1 oracle   oinstall       3920 Nov 17 15:47 prod2_ora_606458.trc-rw-r-----    1 oracle   oinstall       6220 Nov 17 15:39 prod2_ora_393270.trc查看trace结果：[oracle@aix212@ ~]$tkprof /u01/app/oracle/admin/prod/udump/prod2_ora_606458.trc /home/oracle/trc.txtTKPROF: Release 10.2.0.1.0 - Production on Mon Nov 17 15:48:03 2014Copyright (c) 1982, 2005, Oracle.  All rights reserved.select /*+ on_the_other_node */ count(*)from t1call     count       cpu    elapsed       disk      query    current        rows------- ------  -------- ---------- ---------- ---------- ----------  ----------Parse        1      0.00       0.00          0          0          0           0Execute      1      0.00       0.00          0          0          0           0Fetch        2      0.05       0.35        693        697          0           1------- ------  -------- ---------- ---------- ---------- ----------  ----------total        4      0.05       0.35        693        697          0           1Misses in library cache during parse: 0Optimizer mode: ALL_ROWSParsing user id: SYSRows     Row Source Operation-------  ---------------------------------------------------      1  SORT AGGREGATE (cr=697 pr=693 pw=0 time=356121 us)  50392   TABLE ACCESS FULL T1 (cr=697 pr=693 pw=0 time=12651 us)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  gc cr grant 2-way                               1        0.00          0.00  db file sequential read                         1        0.00          0.00  gc cr multi block request                      46        0.00          0.03  db file scattered read                         52        0.05          0.27  SQL*Net message from client                     2        0.00          0.00********************************************************************************   可以看到，在Instance 2上出现了大量'gc cr multi block request'事件，说明大部分数据块是从node 1（Instance 1）上传递过来的。   但也在node 2上有一部分磁盘I/O（disk=693）,说明一部分数据从本地读取。通过trace源文件，查看：select /*+ on_the_other_node */ count(*) from t1END OF STMTPARSE #1:c=0,e=339,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=26779237777BINDS #1:EXEC #1:c=0,e=135,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=26779237991WAIT #1: nam='SQL*Net message to client' ela= 3 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=26779238042WAIT #1: nam='gc cr grant 2-way' ela= 670 p1=1 p2=60945 p3=4 obj#=52686 tim=26779239211WAIT #1: nam='db file sequential read' ela= 5910 file#=1 block#=60945 blocks=1 obj#=52686 tim=26779245166WAIT #1: nam='gc cr multi block request' ela= 898 file#=1 block#=60952 class#=1 obj#=52686 tim=26779246461WAIT #1: nam='db file scattered read' ela= 4083 file#=1 block#=60946 blocks=7 obj#=52686 tim=26779250635WAIT #1: nam='gc cr multi block request' ela= 720 file#=1 block#=60960 class#=1 obj#=52686 tim=26779251880WAIT #1: nam='gc cr multi block request' ela= 182 file#=1 block#=60960 class#=1 obj#=52686 tim=26779252115WAIT #1: nam='db file scattered read' ela= 4897 file#=1 block#=60953 blocks=8 obj#=52686 tim=26779257069WAIT #1: nam='gc cr multi block request' ela= 880 file#=1 block#=60968 class#=1 obj#=52686 tim=26779258422WAIT #1: nam='db file scattered read' ela= 7796 file#=1 block#=60961 blocks=8 obj#=52686 tim=26779266300WAIT #1: nam='gc cr multi block request' ela= 877 file#=1 block#=60976 class#=1 obj#=52686 tim=26779267650WAIT #1: nam='db file scattered read' ela= 8525 file#=1 block#=60969 blocks=8 obj#=52686 tim=26779276258WAIT #1: nam='gc cr multi block request' ela= 948 file#=1 block#=60984 class#=1 obj#=52686 tim=26779277666WAIT #1: nam='gc cr multi block request' ela= 78 file#=1 block#=60984 class#=1 obj#=52686 tim=26779277795WAIT #1: nam='db file scattered read' ela= 4645 file#=1 block#=60977 blocks=8 obj#=52686 tim=26779282501WAIT #1: nam='gc cr multi block request' ela= 757 file#=1 block#=60992 class#=1 obj#=52686 tim=26779283757WAIT #1: nam='db file scattered read' ela= 7600 file#=1 block#=60985 blocks=8 obj#=52686 tim=26779291441WAIT #1: nam='gc cr multi block request' ela= 862 file#=1 block#=61000 class#=1 obj#=52686 tim=26779292778WAIT #1: nam='db file scattered read' ela= 4774 file#=1 block#=60993 blocks=8 obj#=52686 tim=26779297636WAIT #1: nam='gc cr multi block request' ela= 873 file#=1 block#=61008 class#=1 obj#=52686 tim=26779298953WAIT #1: nam='db file scattered read' ela= 4615 file#=1 block#=61001 blocks=8 obj#=52686 tim=26779303648WAIT #1: nam='gc cr multi block request' ela= 870 file#=1 block#=61016 class#=1 obj#=52686 tim=26779304979WAIT #1: nam='db file scattered read' ela= 2009 file#=1 block#=61009 blocks=8 obj#=52686 tim=26779307065WAIT #1: nam='gc cr multi block request' ela= 741 file#=1 block#=61024 class#=1 obj#=52686 tim=26779308335WAIT #1: nam='db file scattered read' ela= 2562 file#=1 block#=61017 blocks=8 obj#=52686 tim=26779310984WAIT #1: nam='gc cr multi block request' ela= 871 file#=1 block#=61032 class#=1 obj#=52686 tim=26779312325WAIT #1: nam='db file scattered read' ela= 2115 file#=1 block#=61025 blocks=8 obj#=52686 tim=26779314521WAIT #1: nam='gc cr multi block request' ela= 857 file#=1 block#=61040 class#=1 obj#=52686 tim=26779315819WAIT #1: nam='db file scattered read' ela= 2060 file#=1 block#=61033 blocks=8 obj#=52686 tim=26779317956WAIT #1: nam='gc cr multi block request' ela= 845 file#=1 block#=61048 class#=1 obj#=52686 tim=26779319241WAIT #1: nam='db file scattered read' ela= 2770 file#=1 block#=61041 blocks=8 obj#=52686 tim=26779322090WAIT #1: nam='gc cr multi block request' ela= 586 file#=1 block#=61055 class#=1 obj#=52686 tim=26779323145WAIT #1: nam='gc cr multi block request' ela= 94 file#=1 block#=61054 class#=1 obj#=52686 tim=26779323281WAIT #1: nam='db file scattered read' ela= 9974 file#=1 block#=61049 blocks=8 obj#=52686 tim=26779333323WAIT #1: nam='db file scattered read' ela= 5632 file#=1 block#=61057 blocks=8 obj#=52686 tim=26779339348WAIT #1: nam='db file scattered read' ela= 2081 file#=1 block#=61065 blocks=8 obj#=52686 tim=26779341785WAIT #1: nam='gc cr multi block request' ela= 1438 file#=1 block#=61208 class#=1 obj#=52686 tim=26779343792WAIT #1: nam='db file scattered read' ela= 4347 file#=1 block#=61193 blocks=16 obj#=52686 tim=26779348250WAIT #1: nam='gc cr multi block request' ela= 715 file#=1 block#=61224 class#=1 obj#=52686 tim=26779349686WAIT #1: nam='gc cr multi block request' ela= 407 file#=1 block#=61224 class#=1 obj#=52686 tim=26779350147WAIT #1: nam='db file scattered read' ela= 3475 file#=1 block#=61209 blocks=16 obj#=52686 tim=26779353704WAIT #1: nam='gc cr multi block request' ela= 1351 file#=1 block#=61240 class#=1 obj#=52686 tim=26779355740WAIT #1: nam='db file scattered read' ela= 3359 file#=1 block#=61225 blocks=16 obj#=52686 tim=26779359216WAIT #1: nam='gc cr multi block request' ela= 713 file#=1 block#=61256 class#=1 obj#=52686 tim=26779360637WAIT #1: nam='gc cr multi block request' ela= 403 file#=1 block#=61256 class#=1 obj#=52686 tim=26779361094WAIT #1: nam='db file scattered read' ela= 2935 file#=1 block#=61241 blocks=16 obj#=52686 tim=26779364111WAIT #1: nam='gc cr multi block request' ela= 1307 file#=1 block#=61272 class#=1 obj#=52686 tim=26779366106WAIT #1: nam='db file scattered read' ela= 8885 file#=1 block#=61257 blocks=16 obj#=52686 tim=26779375104WAIT #1: nam='gc cr multi block request' ela= 754 file#=1 block#=61288 class#=1 obj#=52686 tim=26779376579WAIT #1: nam='gc cr multi block request' ela= 370 file#=1 block#=61288 class#=1 obj#=52686 tim=26779377005WAIT #1: nam='db file scattered read' ela= 3442 file#=1 block#=61273 blocks=16 obj#=52686 tim=26779380530WAIT #1: nam='gc cr multi block request' ela= 1323 file#=1 block#=61304 class#=1 obj#=52686 tim=26779382541WAIT #1: nam='db file scattered read' ela= 2887 file#=1 block#=61289 blocks=16 obj#=52686 tim=26779385540WAIT #1: nam='gc cr multi block request' ela= 746 file#=1 block#=61311 class#=1 obj#=52686 tim=26779386964WAIT #1: nam='db file scattered read' ela= 3422 file#=1 block#=61305 blocks=16 obj#=52686 tim=26779390477WAIT #1: nam='db file scattered read' ela= 3364 file#=1 block#=61321 blocks=16 obj#=52686 tim=26779394422WAIT #1: nam='db file scattered read' ela= 3418 file#=1 block#=61337 blocks=16 obj#=52686 tim=26779398408WAIT #1: nam='db file scattered read' ela= 7254 file#=1 block#=61353 blocks=16 obj#=52686 tim=26779406214WAIT #1: nam='db file scattered read' ela= 3403 file#=1 block#=61369 blocks=16 obj#=52686 tim=26779410176WAIT #1: nam='db file scattered read' ela= 7044 file#=1 block#=61385 blocks=16 obj#=52686 tim=26779417779WAIT #1: nam='db file scattered read' ela= 7282 file#=1 block#=61401 blocks=16 obj#=52686 tim=26779425628WAIT #1: nam='db file scattered read' ela= 3874 file#=1 block#=61417 blocks=16 obj#=52686 tim=26779430050WAIT #1: nam='gc cr multi block request' ela= 967 file#=1 block#=61448 class#=1 obj#=52686 tim=26779431766WAIT #1: nam='db file scattered read' ela= 3433 file#=1 block#=61433 blocks=16 obj#=52686 tim=26779435300WAIT #1: nam='gc cr multi block request' ela= 1315 file#=1 block#=61464 class#=1 obj#=52686 tim=26779437308WAIT #1: nam='db file scattered read' ela= 3388 file#=1 block#=61449 blocks=16 obj#=52686 tim=26779440806WAIT #1: nam='gc cr multi block request' ela= 727 file#=1 block#=61480 class#=1 obj#=52686 tim=26779442228WAIT #1: nam='gc cr multi block request' ela= 419 file#=1 block#=61480 class#=1 obj#=52686 tim=26779442701WAIT #1: nam='db file scattered read' ela= 5163 file#=1 block#=61465 blocks=16 obj#=52686 tim=26779447957WAIT #1: nam='gc cr multi block request' ela= 1319 file#=1 block#=61496 class#=1 obj#=52686 tim=26779449987WAIT #1: nam='db file scattered read' ela= 2761 file#=1 block#=61481 blocks=16 obj#=52686 tim=26779452861WAIT #1: nam='gc cr multi block request' ela= 715 file#=1 block#=61512 class#=1 obj#=52686 tim=26779454292WAIT #1: nam='gc cr multi block request' ela= 405 file#=1 block#=61512 class#=1 obj#=52686 tim=26779454753WAIT #1: nam='db file scattered read' ela= 3434 file#=1 block#=61497 blocks=16 obj#=52686 tim=26779458272WAIT #1: nam='gc cr multi block request' ela= 1308 file#=1 block#=61528 class#=1 obj#=52686 tim=26779460269WAIT #1: nam='db file scattered read' ela= 2761 file#=1 block#=61513 blocks=16 obj#=52686 tim=26779463140WAIT #1: nam='gc cr multi block request' ela= 727 file#=1 block#=61544 class#=1 obj#=52686 tim=26779464570WAIT #1: nam='gc cr multi block request' ela= 402 file#=1 block#=61544 class#=1 obj#=52686 tim=26779465029WAIT #1: nam='db file scattered read' ela= 3372 file#=1 block#=61529 blocks=16 obj#=52686 tim=26779468487WAIT #1: nam='gc cr multi block request' ela= 1337 file#=1 block#=61560 class#=1 obj#=52686 tim=26779470506WAIT #1: nam='db file scattered read' ela= 2804 file#=1 block#=61545 blocks=16 obj#=52686 tim=26779473420WAIT #1: nam='gc cr multi block request' ela= 741 file#=1 block#=61567 class#=1 obj#=52686 tim=26779474836WAIT #1: nam='db file scattered read' ela= 7761 file#=1 block#=61561 blocks=16 obj#=52686 tim=26779482689WAIT #1: nam='db file scattered read' ela= 3177 file#=1 block#=61577 blocks=16 obj#=52686 tim=26779486442WAIT #1: nam='db file scattered read' ela= 3289 file#=1 block#=61593 blocks=16 obj#=52686 tim=26779490280WAIT #1: nam='db file scattered read' ela= 2727 file#=1 block#=61609 blocks=16 obj#=52686 tim=26779493546WAIT #1: nam='db file scattered read' ela= 2709 file#=1 block#=61625 blocks=16 obj#=52686 tim=26779496825WAIT #1: nam='db file scattered read' ela= 3334 file#=1 block#=61641 blocks=16 obj#=52686 tim=26779500720WAIT #1: nam='db file scattered read' ela= 3257 file#=1 block#=61657 blocks=16 obj#=52686 tim=26779504537WAIT #1: nam='db file scattered read' ela= 2735 file#=1 block#=61673 blocks=16 obj#=52686 tim=26779507829WAIT #1: nam='gc cr multi block request' ela= 954 file#=1 block#=61704 class#=1 obj#=52686 tim=26779509463WAIT #1: nam='db file scattered read' ela= 8587 file#=1 block#=61689 blocks=16 obj#=52686 tim=26779518146WAIT #1: nam='gc cr multi block request' ela= 1386 file#=1 block#=61720 class#=1 obj#=52686 tim=26779520274WAIT #1: nam='db file scattered read' ela= 57988 file#=1 block#=61705 blocks=16 obj#=52686 tim=26779578384WAIT #1: nam='gc cr multi block request' ela= 907 file#=1 block#=61736 class#=1 obj#=52686 tim=26779580432WAIT #1: nam='gc cr multi block request' ela= 323 file#=1 block#=61736 class#=1 obj#=52686 tim=26779580830WAIT #1: nam='db file scattered read' ela= 3431 file#=1 block#=61721 blocks=16 obj#=52686 tim=26779584371WAIT #1: nam='gc cr multi block request' ela= 2595 file#=1 block#=61752 class#=1 obj#=52686 tim=26779587917WAIT #1: nam='db file scattered read' ela= 3418 file#=1 block#=61737 blocks=16 obj#=52686 tim=26779591482WAIT #1: nam='gc cr multi block request' ela= 757 file#=1 block#=61757 class#=1 obj#=52686 tim=26779592898WAIT #1: nam='db file scattered read' ela= 928 file#=1 block#=61753 blocks=5 obj#=52686 tim=26779593962FETCH #1:c=50000,e=356115,p=693,cr=697,cu=0,mis=0,r=1,dep=0,og=1,tim=26779594203WAIT #1: nam='SQL*Net message from client' ela= 485 driver id=1650815232 #bytes=1 p3=0 obj#=52686 tim=26779594899FETCH #1:c=0,e=2,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=26779594986WAIT #1: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=52686 tim=26779595018WAIT #1: nam='SQL*Net message from client' ela= 490 driver id=1650815232 #bytes=1 p3=0 obj#=52686 tim=26779595585STAT #1 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=697 pr=693 pw=0 time=356121 us)'STAT #1 id=2 cnt=50392 pid=1 pos=1 obj=52686 op='TABLE ACCESS FULL T1 (cr=697 pr=693 pw=0 time=12651 us)'WAIT #0: nam='SQL*Net message to client' ela= 2 driver id=1650815232 #bytes=1 p3=0 obj#=52686 tim=26779595906*** 2014-11-17 15:53:32.816WAIT #0: nam='SQL*Net message from client' ela= 22469814 driver id=1650815232 #bytes=1 p3=0 obj#=52686 tim=26802065757=====================

这个例子说明，Oracle Cache Fusion技术，主要用于保证同一个数据块的版本一致。比如，一个实例内存中的数据块被修改了，其他的Instance就不能再从磁盘上读取这个数据块，必须使用当前的这个数据块，这样才能保证数据块版本的一致性。

而对于各个实例都是通过只读方式访问数据块，则可以考虑直接从磁盘上读取一部分数据，在有些时候，这样的效率反而会更高一些。