Block Change Tracking Internals: X$ Tables Research

With this post, I’m starting a series about Oracle Block Change Tracking internals. The feature was introduced in Oracle 10 Release 1. I have already published my past presentations and the white paper about that, so what pushed me to get back to this topic again? A few things:

• During my presentations, the audience wasn’t always able to absorb all the information. Reading the white paper later requires quite a bit of dedication and focus to go through many pages of in-depth details. Reading small blog posts is easier and it allows the reader go through the material slowly.
• I would like to share how I came up with the details and what research methods I used.
• The research is not over, so I would like to continue it here and, perhaps, someone will be interested enough to join the club.

When I first started, I tried to dig at least something from Metalink, but the public notes contained no implementation details. What I extracted is some pieces of bug texts, and from there I concluded that fixed tables starting with X$KRC are most probably related to the BCT feature. I quickly extracted all X$KRC tables:

SQL> select name from V$FIXED_TABLE where name like 'X$KRC%';

NAME                           
------------------------------ 
X$KRCFH                        
X$KRCEXT                       
X$KRCCDE                       
X$KRCCDS                       
X$KRCCDR                       
X$KRCGFE                       
X$KRCFDE                       
X$KRCFBH                       
X$KRCBIT                       
X$KRCSTAT                      

10 rows selected

I tried to watch them closely and see how they evolve, but it was too difficult to see the trend. So, I decided to create a kind of Statspack for X$KRC tables, a.k.a. X$KRC “Snappack”.

Here is the schema creation script. Note that I used the SYSTEM schema. You might want to change it if you use it on a real database rather than a playground.

-- tables to hold snapshots
create table system.SNAP_X$KRCBIT as select 0 snap_id, t.* from sys.X$KRCBIT t where rownum < 1;
create table system.SNAP_X$KRCCDE as select 0 snap_id, t.* from sys.X$KRCCDE t where rownum < 1;
create table system.SNAP_X$KRCCDR as select 0 snap_id, t.* from sys.X$KRCCDR t where rownum < 1;
create table system.SNAP_X$KRCCDS as select 0 snap_id, t.* from sys.X$KRCCDS t where rownum < 1;
create table system.SNAP_X$KRCEXT as select 0 snap_id, t.* from sys.X$KRCEXT t where rownum < 1;
create table system.SNAP_X$KRCFBH as select 0 snap_id, t.* from sys.X$KRCFBH t where rownum < 1;
create table system.SNAP_X$KRCFDE as select 0 snap_id, t.* from sys.X$KRCFDE t where rownum < 1;
create table system.SNAP_X$KRCFH as select 0 snap_id, t.* from sys.X$KRCFH t where rownum < 1;
create table system.SNAP_X$KRCGFE as select 0 snap_id, t.* from sys.X$KRCGFE t where rownum < 1;
create table system.SNAP_X$KRCSTAT as select 0 snap_id, t.* from sys.X$KRCSTAT t where rownum < 1;
create table system.SNAP_V$DATABASE as select 0 snap_id, t.* from V$DATABASE t where rownum < 1;

-- sequence for numbering snaps and table to hold snaps attributes
create sequence system.ctwr_snap_id start with 1;
create table system.ctwr_snap (
snap_id number not null,
snap_timestamp timestamp default systimestamp not null,
snap_name varchar2(100),
primary key (snap_id));
CREATE OR REPLACE
TRIGGER SYSTEM.CTWR_SNAP_BEFORE_INSERT BEFORE INSERT ON SYSTEM.CTWR_SNAP
FOR EACH ROW
begin
  SELECT ctwr_snap_id.nextval INTO :new.snap_id FROM DUAL;
end;
/

-- Create snap procedure in SYS schema to avoid additional permission grants/views
create or replace procedure sys.do_ctwr_snap(snap_name in varchar2) is
  pragma autonomous_transaction;
  this_snap number;
begin
  insert into system.ctwr_snap (snap_name)
    values (snap_name)
    returning snap_id INTO this_snap;
  INSERT INTO SYSTEM.SNAP_X$KRCBIT SELECT this_snap, t.* from sys.X$KRCBIT t;
  INSERT INTO SYSTEM.SNAP_X$KRCCDE SELECT this_snap, t.* from sys.X$KRCCDE t;
  INSERT INTO SYSTEM.SNAP_X$KRCCDR SELECT this_snap, t.* from sys.X$KRCCDR t;
  INSERT INTO SYSTEM.SNAP_X$KRCCDS SELECT this_snap, t.* from sys.X$KRCCDS t;
  INSERT INTO SYSTEM.SNAP_X$KRCEXT SELECT this_snap, t.* from sys.X$KRCEXT t;
  INSERT INTO SYSTEM.SNAP_X$KRCFBH SELECT this_snap, t.* from sys.X$KRCFBH t;
  INSERT INTO SYSTEM.SNAP_X$KRCFDE SELECT this_snap, t.* from sys.X$KRCFDE t;
  INSERT INTO SYSTEM.SNAP_X$KRCFH SELECT this_snap, t.* from sys.X$KRCFH t;
  INSERT INTO SYSTEM.SNAP_X$KRCGFE SELECT this_snap, t.* from sys.X$KRCGFE t;
  INSERT INTO SYSTEM.SNAP_X$KRCSTAT SELECT this_snap, t.* from sys.X$KRCSTAT t;  
  INSERT INTO SYSTEM.SNAP_V$DATABASE SELECT this_snap, t.* from V$DATABASE t;  
  commit;
end;
/

-- to simplify calling snap as SYSTEM user
create synonym system.do_ctwr_snap for sys.do_ctwr_snap;
grant execute on sys.do_ctwr_snap to system;

It quickly became clear that I needed an automated way to track the differences, so I generated a script to produce a report of the changes between two snaps. I started assuming that the INDX column can be used as primary key, but I figured that there are more “natural” primary keys for some tables and that the INDX column is not very useful. Also, it’s filled sequentially while the content of X$ table in the query is being built from the block change tracking file (as I figured out later), so every time I query the table I can get slightly different sequencing of rows in some views.

The script is quite lengthy so I won’t include its content in the post.  It shows the list of snaps before asking for start and end snaps — just like the Statspack report script.

I usually give a comment to each snap to make it easier to track the changes later. This comment will be printed at the beginning of the report produced later. Here is a fragment of a report for changes between two snaps when I updated one row and checkpointed. (Note that I had to tweak the output a bit so make long lines readable.)

Comparing snaps 15 and 16

     15 - 08-NOV-06 12.16.52.367618 AM - after checkpoint
     16 - 08-NOV-06 12.25.50.368569 AM - after update 1 row in t2 and checkpoint

Table X$KRCBIT in snap 16 has 18 more rows than in snap 15

********* X$KRCBIT *********

*** 0 new rows in snap 15 ***

*** 18 new rows in snap 16 ***

      INDX    INST_ID     CTFBNO     VERCNT VERTIME                   CSNO        
---------- ---------- ---------- ---------- ------------------- ----------
       595          1       2304          1 2006-11-07 22:12:08          1
... skipped here ...
       646          1       2304          1 2006-11-07 22:12:08          1
      5364          1       3328          2 2006-11-08 00:16:20          1

        FNO        BNO        BCT
 ---------- ---------- ----------
          1      61560          4
...
          1      61964          4
          7          8          4

*** Changed rows ***

********* X$KRCCDE *********

I omitted some changes to datafile 1 (SYSTEM tablespace) and you can see that one chunk in datafile 7 (where the updated row is) appeared marked as “dirty”. In the next posts, I’ll show you that the BCT feature doesn’t track changes for every block but rather for 32K chunks. In this case the last chunk in the output starts from block number 8 in datafile 7 and is 4 blocks long.

As you can see, the column names are quite good, so block change tracking definitely looks like a new functionality not related to the Russian roots of Oracle. It’s easy to guess what some columns mean and then it doesn’t take much brain power to wrap different actions between a couple of snaps and analyze the changes. You might be interested to see what happens on block change, on checkpoint, on commit, or incremental backup, etc.

Alright. This seems a good starting point. More to come — stay tuned.