Oracle RAC Cache Fusion

What is cache fusion

• Is a new technology that uses a high speed interprocess communication (IPC) interconnect to provide cache to cache transfers of data blocks between instances in a cluster.
• This eliminates disk I/O (which is inherently slow, since it is a mechanical process) and optimizes read/write concurrency.
• Each instance of a RAC database has it’s own buffer cache in its SGA(System Global Area)
• All these caches put together forms the cache fusion

How cache fusion work

• Is maintained by Global Cache Service(GCS) and Global Enqueue Service(GES)

  • Global Enqueue Service Daemon (LMD)
  • Global Cache Service Processes (LMSn)

• When a block is requested, the buffer cache(block) is searched in local SGA

  • if found

    • send buffer to user

  • if not found

    • get buffer from other cache
    • get buffer from disk

Global Resource Directory (GRD)

• Is the internal database that records and stores the current status of the data blocks

• Is maintained by Global Cache Service(GCS) and Global Enqueue Service(GES)

  • Global Enqueue Service Daemon (LMD)

    • It holds the information on the locks on the buffers
    • The lock info is available in V$LOCK_ELEMENT & V$BH.LOCK_ELEMENT

  • Global Cache Service Processes (LMSn)

    • It provides the buffer from one instance to another instance
    • it does not know who has what type of buffer lock

• Whenever a data block is transferred out of a local cache to another instance’s cache the GRD is updated
• The GRD resides in memory and is distributed throughout the cluster.
• It list all the master instance of all the buffers

Master instance in Cache Fusion

• Every buffer has master instance and it is also called resource master
• When instance wants lock on the specific buffer, it has to reach master instance
• Master instance will grant or revoke the privileges to acquire the lock

Database storage logical struture

• Data Block > Extents > Segments > Datafiles > Smallfile Tablespaces > Bigfile Tablespaces
• Data block structure

• Extents is composed of bunch of data blocks

  • Can be changed on fly

• Segments is composed of bunch of extents

  • Types of Segments

    • Table segments - store rows of data
    • Index segments
    • Undo segments
    • Temporary segments - operating in memory of servers
    • Partition segments
    • Index partition segments
    • LOB/LOB index - LOB is stand for Large Object

• Datafiles contains segments - data01.dbf

  • Types of Datafiles

    • Non-specific data
    • Undo data
    • Temporay data(tempfiles)

  • Limit

    • Max size = 4194304 x data block size(4194304 x 8K = 32G)
    • Max number of Datafiles in a Database = 65533
    • Max database size = 8 Petabytes

• Smallfile is a logical name given to one or more physical Datafile

Data Block

• The smallest unit of space in an Oracle DB
• Blocks are read into buffers
• Defined by db_block_size parameter
• Cannot be changed after creation
• References 9i and 12c

Data Block Struture

• Header info
• Free space

• Used space

  • is a bunch of rows of data

• Size of data block

  • 2K - less often used
  • 4K - used for OnLine Transaction Processing(OLTP)
  • 8K - Default and hybrid
  • 16K - Data warehousing, binary large objects(video files, word documents, ...)
  • 32K - less often used

 

Data Block Modes

Mode	Description
NULL	A null CR mode indicates just a resource holder and has no access rights.
Exclusive	An XCUR state exclusive mode signifies exclusive access of the block. This means that the resource holder needs to perform a write operation on the block and no other resource can write over it. However, other resources can perform read operation on the block.
Shared	A SCUR state shared mode indicates that the resource holder has a shared lock on the block and is performing a read operation. As the mode name, since the lock is shared, any other resource can also read the block.

Data Block Roles

Role	Description
Local	When the block, is read into the instance’s cache and no other instance in the cluster has read the same block or has it in its cache then the block has a local role
Global	If the block which earlier had local role, and request transmitted to/ from other nodes now has a global role.

Past Image(PI)

• Is a copy of a globally dirty block image maintained in the cache.
• It is created when a modified block is modified by instance
• Will be removed when block is commited to disk

System Change Numbers

• Is a logical, internal time stamp used by Oracle Database
• Oracle records each change has made to a data block by assigning a numeric identifier to each version of a block
• Enables Oracle to generate redo logs in an orderly manner and to accommodate subsequent recovery processing
• SCNs order events that occur within the database
• Every transaction has an SCN

SELECT ORA_ROWSCN
FROM employees
WHERE employee_id = 188;

Buffer States

• Consistent Read (CR) mode when the buffer is requested to be simply be selected not updated

  • A consistent read (CR) version of a block represents a consistent snapshot of the data at a point in time

• Current mode when then buffer is requested for the intention of modifying it, even if actually not updated, e.g. in case of SELECT FOR UPDATE

Lab

Prerequisite step

• get_how_many_block.sql: check how many block for a table
• get_how_many_row.sql: check how many rows and real blocks are used in a table
• get_data_obj_id_and_obj_id.sql: get data_obj_id of table
• remove_cache.sql: remove all cache in memory

Scenario 1: READ-READ

Server 01

• sess_stats.sql: check server read from disk and from other instance
• select_all_data.sql: read all data of EMP table
• get_status_data_obj_id_and_block_status.sql: get status of blocks

Server 02

• sess_stats.sql: check server read from disk and from other instance
• select_all_data.sql: read all data of EMP table
• get_status_data_obj_id_and_block_status.sql: get status of blocks

Scenario 2: WRITE-READ

Server 01

• sess_stats.sql: check server read from disk and from other instance
• update_record.sql: update record of EMP table
• get_status_data_obj_id_and_block_status.sql: get status of blocks

Server 02

• sess_stats.sql: check server read from disk and from other instance
• get_status_data_obj_id_and_block_status.sql: get status of blocks

Scenario 1: WRITE-WRITE

Server 01

• sess_stats.sql: check server read from disk and from other instance
• update_record.sql: update record of EMP table
• get_status_data_obj_id_and_block_status.sql: get status of blocks

Server 02

• sess_stats.sql: check server read from disk and from other instance
• update_record.sql: update record of EMP table
• get_status_data_obj_id_and_block_status.sql: get status of blocks

Script to use

• using script for pl/sql

create_table.sql

• create table

create table emp (empno number, ename char(255));

create_index.sql

• create index for table

CREATE INDEX ix_emp ON emp (empno) TABLESPACE index_tbs;

insert_data.sql

• insert data into table

insert into emp values(1,'Scott FPT');
....
insert into emp values(30,'Juvetus FC');
commit;

get_how_many_block.sql

• check how many blocks in the table

# find the block_id and how many blocks of EMP table
column owner format A20
select extent_id, block_id, blocks, owner from dba_extents where segment_name = 'EMP';
EXTENT_ID   BLOCK_ID          BLOCKS       OWNER
---------   ----------    ----------   --------
        0        99544                 8   C##ADMIN

get_how_many_row.sql

• check how many blocks are use for data

# find rowid, file id and block id of EMP table
# reference https://docs.oracle.com/database/121/ARPLS/d_rowid.htm#ARPLS67764
select empno, dbms_rowid.rowid_relative_fno(rowid) file#, dbms_rowid.rowid_block_number(rowid) block# from emp;
EMPNO       FILE#     BLOCK#
-----   ----- ----------
    1           1      99545
    2           1      99545

get_data_obj_id_and_obj_id.sql and get_status_data_obj_id_and_block_status.sql

• get data_object_id and get status blocks are in buffer cache

# check if the data_object_id and object_id on the database buffer cache
# step 1:
select data_object_id, object_id from dba_objects where object_name = 'EMP';
DATA_OBJECT_ID  OBJECT_ID
--------------  ---------
         93446      93446

# step 2
select file#, block#,
    decode(class#,1,'data block',2,'sort block',3,'save undo block', 4,
    'segment header',5,'save undo header',6,'free list',7,'extent map',
    8,'1st level bmb',9,'2nd level bmb',10,'3rd level bmb', 11,'bitmap block',
    12,'bitmap index block',13,'file header block',14,'unused',
    15,'system undo header',16,'system undo block', 17,'undo header',
    18,'undo block') class_type, status, LOCK_ELEMENT_ADDR
    from v$bh
    where objd = &data_obj_id
order by 1,2,3;

trace_my_session.sql

• start tracing the session

declare
   v_sid    number;
   v_serial number;
begin
   select
      sid,
      serial#
   into
      v_sid,
      v_serial
   from
      v$session
   where
      sid=SYS_CONTEXT('USERENV','SID');

   dbms_monitor.session_trace_enable(v_sid,v_serial,TRUE,TRUE);
end;
/

sess_stats.sql

• get the statistic

column name format A30
select sn.name, st.value
from v$statname sn
join v$sesstat st
  on sn.statistic#=st.statistic#
where sn.name in ('session logical reads',
                  'physical reads',
                              'gc cr blocks received',
                              'gc current blocks received')
  and st.sid=SYS_CONTEXT('USERENV','SID')
order by sn.statistic#;

my_trace_file.sql

• get trace file of the sql command -

select
pm.value||'/diag/rdbms/'||d.name||'/'||i.instance_name||
'/trace/'||i.instance_name||'_ora_'||pr.spid||'.trc' as trace_file
from
(select
  p.spid
from
  v$session s
join
  v$process p
  on s.paddr=p.addr
where
  s.sid=SYS_CONTEXT('USERENV','SID')) pr
cross join
 (select
     value
  from
     v$parameter
  where
     name='diagnostic_dest') pm
cross join
 (select
     instance_name
  from
     v$instance) i
cross join
 (select
     lower(name) as name
  from
     v$database) d
/

remove_cache.sql

• remove data cache fusion

alter system flush buffer_cache;

.