Pages

Tuesday, January 14, 2014

Simple Test on RAC Cache Fusion

RAC: Oracle Database 11.2.0.3
OS: Solaris 10 Sparc 64bit

Create a test table 'TEST_CACKE' and find out its OBJECT_ID
system@RACDB.RACDB_1.rac01> create table TEST_CACHE as select * from dba_tables;
Table created.
system@RACDB.RACDB_1.rac01> create index TEST_CACHE_IDX on TEST_CACHE(owner,table_name);
Index created.
system@RACDB.RACDB_1.rac01> select object_id from dba_objects where object_name='TEST_CACHE';
OBJECT_ID
----------
1707649 <--- Object id of table TEST_CACHE

Here, I also created a two-column composite index on columns (owner, table_name) to avoid full table scan and get the block cached normally. I will use the row which columns owner='SYS' ans table_name='CON$' in table TEST_CACHE as specimen:
system@RACDB.RACDB_1.rac01> select dbms_rowid.rowid_to_absolute_fno(rowid,'SYSTEM','TEST_CACHE') file#,
2 dbms_rowid.rowid_block_number(rowid) block#,
3 owner,table_name,tablespace_name
4 from test_cache
5* where owner='SYS' and table_name='CON$';

FILE# BLOCK# OWNER TABLE_NAME TABLESPACE_NAME
---------- ---------- ------- --------------- ------------------------------
1 122897 SYS CON$ SYSTEM <--- file# and block# which contains specified row

We can find that the row specimen is stored in BLOCK 122897 of FILE 1.
Find out if the block is cached:
system@RACDB.RACDB_1.rac01> select inst_id,status from gv$bh where file#=1 and block#=122897 and objd=1707649;
INST_ID STATUS
---------- ----------
1 scur <--- block#122897 is cached on instance 1

STATUS='scur' means block#122897 containing specified row is cached on instance 1. Yes, it was loaded from disk to memory by 'create index TEST_CACHE_IDX' statement.
To empty buffer cache:
system@RACDB.RACDB_1.rac01> alter system flush buffer_cache;
System altered.
system@RACDB.RACDB_1.rac01> select inst_id,status from gv$bh where file#=1 and block#=122897 and objd=1707649;
INST_ID STATUS
---------- ----------
1 free <--- cache memory of block#122897 is freed

The cache memory of block#122897 of table TEST_CACHE(object#1707649) is freed (not cached any more)
Re-load  block#122897 into cache on instance 1:
system@RACDB.RACDB_1.rac01> set autotrace on statistics
system@RACDB.RACDB_1.rac01> select owner,table_name,tablespace_name from test_cache where owner='SYS' and table_name='CON$';
OWNER TABLE_NAME TABLESPACE_NAME
------- --------------- ------------------------------
SYS CON$ SYSTEM

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
3 physical reads <--- Incur physical reads
0 redo size
297 bytes sent via SQL*Net to client
247 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

The query caused '3  physical reads' to load table & index blocks into memory from disk.
Check cached data:
system@RACDB.RACDB_1.rac01> select inst_id,status from gv$bh where file#=1 and block#=122897 and objd=1707649;
INST_ID STATUS
---------- ----------
1 scur
1 free

block#122897 is cached on instance 1 again.
Now re-run the query and check the physical read:
system@RACDB.RACDB_1.rac01> select owner,table_name,tablespace_name from test_cache where owner='SYS' and table_name='CON$';

OWNER TABLE_NAME TABLESPACE_NAME
------------------------- ------------------------------ ------------------------------
SYS CON$ SYSTEM

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
312 bytes sent via SQL*Net to client
247 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

It is '0 physical reads' because of cache on instance 1. Ok, that is what is expected. What if I run the query from other instance of the RAC?
Check if the block is cached on instance 2:
system@RACDB.RACDB_2.rac02> select inst_id,status from gv$bh where file#=1 and block#=122897 and objd=1707649;
INST_ID STATUS
---------- ----------
1 scur
1 free

No, it is not cached on instance 2. Run the query on instance 2 and see what will happen:
system@RACDB.RACDB_2.rac02> select owner,table_name,tablespace_name from test_cache where owner='SYS' and table_name='CON$';

OWNER TABLE_NAME TABLESPACE_NAME
------- --------------- ------------------------------
SYS CON$ SYSTEM

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
295 bytes sent via SQL*Net to client
247 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

There is no cache of the block on instance 2, but Oracle did not read the data from disk (0 physical reads). Instead, it read/copy the block from cache on instance 1.
Let's see the current cache status:
system@RACDB.RACDB_2.rac02> select inst_id,status from gv$bh where file#=1 and block#=122897 and objd=1707649;

INST_ID STATUS
---------- ----------
2 scur <--- block#122897 is cached on instance 2
1 scur <--- block#122897 is cached on instance 1
1 free

Both instances caches the block block#122897.

No comments: