11g Virtual Columns and Fast Refreshable Materialized Views (What In The World) November 24, 2010
Posted by Richard Foote in 11g, 11g New features, Function Based Indexes, Oracle Bugs, Virtual Columns.15 comments
Previous to Oracle 11g Rel 2, two very common and useful features previously worked well together, they being fast refreshable materialized views and the introduction of virtual columns due to the creation of function-based indexes.
To illustrate, we create and populate a little demo table:
SQL> create table bowie (a number, b number, c number); Table created. SQL> insert into bowie select rownum, mod(rownum,10), mod(rownum,100) from dual connect by level <= 100000; 100000 rows created. SQL> commit; Commit complete. SQL> alter table bowie add primary key (a); Table altered.
We now create a simple little function-based index:
SQL> create index bowie_func_i on bowie(b+c); Index created.
If we look at the columns in the table via DBA_TAB_COLS:
SQL> select column_name, data_default, virtual_column, hidden_column from dba_tab_cols where table_name = 'BOWIE'; COLUMN_NAME DATA_DEFAULT VIR HID ------------ ------------ --- --- SYS_NC00004$ "B"+"C" YES YES C NO NO B NO NO A NO NO
We notice Oracle has introduced a new, hidden virtual column (SYS_NC00004$), required to store statistics for use by the Cost Based Optimizer.
Next we create a materialized view log on this table and a fast refreshable materialized view:
SQL> create materialized view log on bowie WITH PRIMARY KEY,SEQUENCE, ROWID (b,c) INCLUDING NEW VALUES; Materialized view log created. SQL> create materialized view bowie_mv 2 build immediate 3 refresh fast 4 with primary key 5 enable query rewrite 6 as 7 select b, count(*) from bowie group by b; Materialized view created.
Collect a few statistics and we note the Materialized View does indeed get used during a query rewrite scenario:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE', estimate_percent=>null, cascade=> true, method_opt=> 'FOR ALL COLUMNS SIZE 1')
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE_MV', estimate_percent=>null, cascade=> true, method_opt=> 'FOR ALL COLUMNS SIZE 1')
PL/SQL procedure successfully completed.
SQL> select b, count(*) from bowie having b > 3 group by b;
B COUNT(*)
---------- ----------
6 10000
4 10000
5 10000
8 10000
7 10000
9 10000
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 593592962
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 42 | 2 (0)| 00:00:01 |
|* 1 | MAT_VIEW REWRITE ACCESS FULL| BOWIE_MV | 7 | 42 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("BOWIE_MV"."B">3)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
538 bytes sent via SQL*Net to client
395 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
And indeed the materialized view is fast refreshable:
SQL> insert into bowie values (100001, 5, 42);
1 row created.
SQL> commit;
Commit complete.
SQL> exec dbms_mview.refresh('BOWIE_MV', 'F');
PL/SQL procedure successfully completed.
SQL> select b, count(*) from bowie having b > 3 group by b;
B COUNT(*)
---------- ----------
6 10000
4 10000
5 10001
8 10000
7 10000
9 10000
6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 593592962
-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 7 | 42 | 2 (0)| 00:00:01 |
|* 1 | MAT_VIEW REWRITE ACCESS FULL| BOWIE_MV | 7 | 42 | 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("BOWIE_MV"."B">3)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
4 consistent gets
0 physical reads
0 redo size
546 bytes sent via SQL*Net to client
395 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
Notice how the materialized view does indeed displayed the correct updated information via the query rewrite operation . So the materialized view behaved and worked as expected even though the underlining master table has a virtual column due to the creation of the function-based index (note that QUERY_REWRITE_INTEGRITY is set to STALE_TOLERATED)
Unfortunately, things go off the rails somewhat since Oracle 11g Rel 2 when a virtual column is introduced due to one of the 11g new features. For example, I now collect some Extended Statistics on this table:
SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE', method_opt=> 'FOR COLUMNS (A,B,C) SIZE 254');
PL/SQL procedure successfully completed.
SQL> select column_name, data_default, virtual_column, hidden_column from dba_tab_cols where table_name = 'BOWIE';
COLUMN_NAME DATA_DEFAULT VIR HID
------------------------------ --------------------------------- --- ---
SYS_STUM4KJU$CCICS9C1UJ6UWC4YP SYS_OP_COMBINED_HASH("A","B","C") YES YES
SYS_NC00004$ "B"+"C" YES YES
C NO NO
B NO NO
A NO NO
Notice how extended statistics has resulted in another hidden virtual column (SYS_STUM4KJU$CCICS9C1UJ6UWC4YP) being created to store the resultant statistics.
However, if now attempt to perform a fast refresh on the Materialized View:
SQL> insert into bowie values (100002, 5, 42);
1 row created.
SQL> commit;
Commit complete.
SQL> exec dbms_mview.refresh('BOWIE_MV', 'F');
BEGIN dbms_mview.refresh('BOWIE_MV', 'F'); END;
*
ERROR at line 1:
ORA-12008: error in materialized view refresh path
ORA-00904: "MAS$"."SYS_STUM4KJU$CCICS9C1UJ6UWC4YP": invalid identifier
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: at line 1
We get an error, complaining about the existence of this new virtual column.
If we attempted to drop and re-create the materialized view:
SQL> drop materialized view bowie_mv; Materialized view dropped. SQL> create materialized view bowie_mv 2 build immediate 3 refresh fast 4 with primary key 5 enable query rewrite 6 as 7 select b, count(*) from bowie group by b; select b, count(*) from bowie group by b * ERROR at line 7: ORA-12033: cannot use filter columns from materialized view log on "BOWIE"."BOWIE"
It fails, complaining that the materialized view log is somehow missing a filter column (which it isn’t). We get exactly the same set of issues if we add a visible virtual column via this new 11g capability:
SQL> create table bowie2 (a number, b number, c number, d as (a+b+c)); Table created. SQL> select column_name, data_default, virtual_column, hidden_column from dba_tab_cols where table_name = 'BOWIE2'; COLUMN_NAME DATA_DEFAULT VIR HID ------------ ------------ --- --- D "A"+"B"+"C" YES NO C NO NO B NO NO A NO NO SQL> insert into bowie2 (a,b,c) select rownum, mod(rownum,10), mod(rownum,100) from dual connect by level <= 100000; 100000 rows created. SQL> commit; Commit complete. SQL> alter table bowie2 add primary key (a); Table altered. SQL> create materialized view log on bowie2 WITH PRIMARY KEY,SEQUENCE, ROWID (b,c) INCLUDING NEW VALUES; Materialized view log created. SQL> create materialized view bowie2_mv 2 build immediate 3 refresh fast 4 with primary key 5 enable query rewrite 6 as 7 select b, count(*) from bowie2 group by b; select b, count(*) from bowie2 group by b * ERROR at line 7: ORA-12033: cannot use filter columns from materialized view log on "BOWIE"."BOWIE2"
Extended statistics and visible virtual columns are both potentially extremely useful new features introduced in 11g but unfortunately both can not be implemented on any table that needs to be fast refreshable within a complex materialized view.
I raised this issue with Oracle Support who have raised bug 10281402 as a result as it occurs in both 11.2.0.1 and 11.2.0.2 on various platforms I’ve tested.
Oracle11g: New Locking Modes When Policing FK Constraints (A Wolf at the Door) November 10, 2010
Posted by Richard Foote in 11g, Foreign Keys, Locking Issues, Oracle Indexes.18 comments
As I’ve been focusing mainly with Oracle 11g at work these days, thought I might look at a number of Oracle 11g related topics in the coming weeks.
To start with, there’s been a subtle but potentially significant change introduced in Oracle 11g (since 11.1.0.6) with regard to the manner in which locks are held in relation to policing Foreign Key constraints. The following has been tested on both 11.2.0.1 and 11.2.0.2.
To set the scene and replicate the issue we hit at work, I’m just going to create a little table (ALBUMS) that has 2 FK constraints pointing to two parent tables (ARTISTS and FORMATS) and populate them with a few rows.
SQL> CREATE TABLE artists (id NUMBER PRIMARY KEY, artist_name VARCHAR2(30)); Table created. SQL> CREATE TABLE formats (id NUMBER PRIMARY KEY, format_name varchar2(30)); Table created. SQL> CREATE TABLE albums (id NUMBER, album_name VARCHAR2(30), artist_id NUMBER CONSTRAINT artist_fk REFERENCES artists(id), format_id number CONSTRAINT format_fk REFERENCES formats(id)); Table created. SQL> INSERT INTO artists VALUES (1, 'DAVID BOWIE'); 1 row created. SQL> INSERT INTO artists VALUES (2, 'PINK FLOYD'); 1 row created. SQL> INSERT INTO formats VALUES (1, 'CD'); 1 row created. SQL> INSERT INTO formats VALUES (2, 'DVD'); 1 row created. SQL> INSERT INTO albums VALUES (1, 'LOW', 1, 1); 1 row created. SQL> INSERT INTO albums VALUES (2, 'DIAMOND DOGS', 1, 1); 1 row created. SQL> COMMIT; Commit complete.
OK, when running the following insert statement on the ARTISTS table in 10.2.0.3:
SQL> insert into artists values (3, 'MUSE'); 1 row created.
A check in the v$lock view will show the transaction holds a TM (DML Enqueue) lock in row-S (SS) mode 2 on the child ALBUMS table due to the FK relationship between these tables.
If another session were to either say delete a row or update the PK from the other parent FORMATS table:
SQL> update formats set id = 2 where id = 2; 1 row updated.
It will succeed with no problem for when it temporarily requires a TM share (S) mode 4 lock on the ALBUMS table, it can successfully grab it as the concurrent SS lock does not prevent this from occurring. It requires access to this mode 4 Share lock to ensure there are no transactions currently impacting the ALBUMS table that could potentially violate the constraint following the DML operations on the parent FORMATS table.
However, repeating the same exercise in Oracle 11g and we hit a subtle difference. When running the insert statement again in the ARTISTS table:
SQL> insert into artists values (3, 'MUSE'); 1 row created.
A check in the v$lock view will now show the transaction holds a TM (DML Enqueue) lock in row-X (SX) LMODE 3 on the child ALBUMS table, not a LMODE 2 SS level lock as it did in 10g. This is a “higher” level lock mode which has the following consequence on the other session now attempting to either delete or update the PK in the FORMATS table:
SQL> update formats set id = 2 where id = 2;
The session now hangs as it has to wait for the other session to release the DML Enqueue LMODE 3 SX lock before it can in turn grab the required TM mode 4 Share table lock it’s requesting. This is precisely the issue we hit with a somewhat poorly written application trying to perform something akin to the above series of updates from within two different sessions.
This change was introduced by Oracle to eliminate an ORA-600 issue that could occur when deleting a row from a table with a PK while rebuilding an associated FK index that referenced the PK.
However, introducing a more restrictive level of lock in this manner has the side-effect of increasing the likelihood of encountering new locking issues such as this, increasing the likelihood of hitting deadlock scenarios (as discussed here previously by Charles Hooper) and can therefore potentially reduce the overall concurrency capabilities of an application.
The “fix” in this case is to simply create an index on the formats_id FK column (which probably should exist anyways in this case to prevent locking issues on the child table when updating the parent FORMAT table):
SQL> CREATE INDEX albums_format_i on albums(format_id); Index created. SQL> insert into artists values (3, 'MUSE'); 1 row created.
In which case the table share lock is no longer required on the ALBUMS table (as Oracle can now use the associated index to effectively police the integrity of the child table following such an operation on a parent table) and the statement no longer hangs in the other session:
SQL> update formats set id = 2 where id = 2; 1 row updated.
This change in the locking behaviour of policing FK constraints is certainly something to be aware of when migrating to Oracle 11g if you potentially have FK constraints that don’t have associated indexes.
Index Block Dumps: Final Demo (Come Together) November 4, 2010
Posted by Richard Foote in Block Dumps, Leaf Blocks, Oracle Indexes.1 comment so far
The intent of this blog piece is just to bring together the whole discussion of block dumps and how we can use block dumps to demonstrate Oracle behaviour.
First, let’s start with a fresh little demo, creating an index on a NAME column with 500 entries (note this specific demo uses an 11.2.0.1 database running on windows). The column all have a value of ‘BOWIE’ with a distinct number concatenated on the end.
SQL> create table bowie (id number, name varchar2(20)); Table created. SQL> create index bowie_name_i on bowie(name); Index created. SQL> insert into bowie select rownum, 'BOWIE' || rownum from dual connect by level <= 500; 500 rows created. SQL> commit; Commit complete. SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>'BOWIE', cascade=> true, estimate_percent=>null, method_opt=>'FOR ALL COLUMNS SIZE 1'); PL/SQL procedure successfully completed.
We notice this index is a blevel 1 index, consisting of a root block pointing down to just 2 leaf blocks:
SQL> select blevel, leaf_blocks from dba_indexes where index_name = 'BOWIE_NAME_I'; BLEVEL LEAF_BLOCKS ---------- ----------- 1 2
I’m just going to show selected portions from the different block dumps, focusing on the dump from disk section (hence flush the buffer cache before each block dump):
SQL> alter system flush buffer_cache; System altered. SQL> select header_file, header_block from dba_segments where segment_name='BOWIE_NAME_I'; HEADER_FILE HEADER_BLOCK ----------- ------------ 6 168
The specific block of interest will be the second (or last) index leaf block, so I just add 3 to the header block value (note index is in a non ASSM LMT):
SQL> alter system dump datafile 6 block 171; System altered.
Block dump from disk:
buffer tsn: 6 rdba: 0x018000ab (6/171)
scn: 0x0000.003bb7e9 seq: 0x01 flg: 0x04 tail: 0xb7e90601
frmt: 0x02 chkval: 0x285e type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Block header dump: 0x018000ab
Object id on Block? Y
seg/obj: 0x12ad3 csc: 0x00.3bb7e9 itc: 2 flg: O typ: 2 – INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.013.00000d48 0x00c01082.0263.02 CB– 0 scn 0x0000.003bb7e1
0x02 0x0003.017.00000d3e 0x00c049a3.021a.03 C— 0 scn 0x0000.003bb7e3
Leaf block dump
===============
header address 211493468=0xc9b225c
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 300
kdxcofbo 636=0x27c
kdxcofeo 2722=0xaa2
kdxcoavs 2086
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 25165994=0x18000aa
kdxledsz 0
kdxlebksz 8036
row#0[4414] flag: ——, lock: 0, len=17
col 0; len 7; (7): 42 4f 57 49 45 32 38
col 1; len 6; (6): 01 80 00 a1 00 1b
row#1[4431] flag: ——, lock: 0, len=18
col 0; len 8; (8): 42 4f 57 49 45 32 38 30
col 1; len 6; (6): 01 80 00 a1 01 17
row#2[4449] flag: ——, lock: 0, len=18
col 0; len 8; (8): 42 4f 57 49 45 32 38 31
col 1; len 6; (6): 01 80 00 a1 01 18
…
We currently have 2 ITL entries in the index leaf block, the first entry used by Oracle to deal with the leaf block split required when loading the data, the second entry for the actual transaction loading the table/index. The kdxcronro count is 300 meaning we currently have 300 index entries in this block. Note the kdxlenxt value is 0 meaning there is no next pointer, ensuring we are indeed looking at the second (or last) index leaf block within the index structure. We’re now going to add a couple of new index entries that will have greater values than all our BOWIEs guaranteeing they’ll be inserted into this leaf block. We’re going to do this by running a couple of separate concurrent transactions running in different sessions:
In one session:
SQL> insert into bowie values (501, 'MAJOR TOM'); 1 row created.
In another session:
SQL> insert into bowie values (502, 'ZIGGY STARDUST'); 1 row created. SQL> commit; Commit complete.
Back in the first session:
SQL> commit; Commit complete.
So there were 2 concurrent transactions inserting index entries, with the transaction inserting the value “MAJOR TOM” committing last. Looking at a dump of the index block now:
Block dump from disk:
buffer tsn: 6 rdba: 0x018000ab (6/171)
scn: 0x0000.003bb95e seq: 0x01 flg: 0x06 tail: 0xb95e0601
frmt: 0x02 chkval: 0x1f40 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.013.00000d48 0x00c01082.0263.02 CB– 0 scn 0x0000.003bb7e1
0x02 0x0004.016.00000d65 0x00c00e88.029f.03 –U- 1 fsc 0x0000.003bb95e
0x03 0x0007.00a.00000d5c 0x00c02578.0261.02 –U- 1 fsc 0x0000.003bb95a
Leaf block dump
===============
header address 211493492=0xc9b2274
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 302
kdxcofbo 640=0x280
kdxcofeo 2655=0xa5f
kdxcoavs 2015
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 25165994=0x18000aa
kdxledsz 0
kdxlebksz 8012
row#0[4390] flag: ——, lock: 0, len=17
col 0; len 7; (7): 42 4f 57 49 45 32 38
col 1; len 6; (6): 01 80 00 a1 00 1b
row#1[4407] flag: ——, lock: 0, len=18
col 0; len 8; (8): 42 4f 57 49 45 32 38 30
col 1; len 6; (6): 01 80 00 a1 01 17
row#2[4425] flag: ——, lock: 0, len=18
col 0; len 8; (8): 42 4f 57 49 45 32 38 31
…
row#300[2679] flag: ——, lock: 2, len=19
col 0; len 9; (9): 4d 41 4a 4f 52 20 54 4f 4d
col 1; len 6; (6): 01 80 00 a2 00 55
row#301[2655] flag: ——, lock: 3, len=24
col 0; len 14; (14): 5a 49 47 47 59 20 53 54 41 52 44 55 53 54
col 1; len 6; (6): 01 80 00 a2 00 56
—– end of leaf block dump —–
We notice we now have an additional ITL entry. The first entry is reserved for Oracle service operations (such as block splits). The second entry was therefore grabbed by the first transaction (which inserted “MAJOR TOM”) while a new third ITL entry had to be added to accommodate the second concurrent transaction. At the bottom of the block we can see the 2 new index entries, one currently marked as locked by the transaction in ITL 2 and the other entry containing “ZIGGY STARDUST” locked by the second transaction in ITL 3. These lock bytes (which are no longer required as the transactions have now completed) will be subsequently cleaned out as we shall see …
As the transaction in ITL 2 was the last to commit, its corresponding Scn/fsc (0x0000.003bb95e) is the last transaction to have changed the block and hence is also stored in the block header (scn: 0x0000.003bb95e).
Let’s now add another index entry:
SQL> insert into bowie values (503, 'THIN WHITE DUKE'); 1 row created. SQL> commit; Commit complete.
Block dump from disk:
buffer tsn: 6 rdba: 0x018000ab (6/171)
scn: 0x0000.003c327c seq: 0x02 flg: 0x06 tail: 0x327c0602
frmt: 0x02 chkval: 0xb367 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Block header dump: 0x018000ab
Object id on Block? Y
seg/obj: 0x12ad3 csc: 0x00.3c327b itc: 3 flg: O typ: 2 – INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.013.00000d48 0x00c01082.0263.02 CB– 0 scn 0x0000.003bb7e1
0x02 0x0004.016.00000d65 0x00c00e88.029f.03 C— 0 scn 0x0000.003bb95e
0x03 0x0003.010.00000d6c 0x00c015a9.0221.08 –U- 1 fsc 0x0000.003c327c
Leaf block dump
===============
header address 211493492=0xc9b2274
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 303
kdxcofbo 642=0x282
kdxcofeo 2630=0xa46
kdxcoavs 1988
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 25165994=0x18000aa
kdxledsz 0
kdxlebksz 8012
row#0[4390] flag: ——, lock: 0, len=17
col 0; len 7; (7): 42 4f 57 49 45 32 38
col 1; len 6; (6): 01 80 00 a1 00 1b
row#1[4407] flag: ——, lock: 0, len=18
col 0; len 8; (8): 42 4f 57 49 45 32 38 30
col 1; len 6; (6): 01 80 00 a1 01 17
row#2[4425] flag: ——, lock: 0, len=18
col 0; len 8; (8): 42 4f 57 49 45 32 38 31
col 1; len 6; (6): 01 80 00 a1 01 18
…
row#300[2679] flag: ——, lock: 0, len=19
col 0; len 9; (9): 4d 41 4a 4f 52 20 54 4f 4d
col 1; len 6; (6): 01 80 00 a2 00 55
row#301[2630] flag: ——, lock: 3, len=25
col 0; len 15; (15): 54 48 49 4e 20 57 48 49 54 45 20 44 55 4b 45
col 1; len 6; (6): 01 80 00 a2 00 57
row#302[2655] flag: ——, lock: 0, len=24
col 0; len 14; (14): 5a 49 47 47 59 20 53 54 41 52 44 55 53 54
col 1; len 6; (6): 01 80 00 a2 00 56
—– end of leaf block dump —–
We notice the previous lock information has now been cleaned out with only this last transaction (reusing the ITL entry of the previously oldest transaction, ITL 3) now having a lock byte set for its corresponding row (“THIN WHITE DUKE”). This transaction’s scn/fsc (0x0000.003c327c) is now the scn marking the block header.
Let’s delete a few rows now, firstly the row containing “MAJOR TOM”:
SQL> delete bowie where name = 'MAJOR TOM'; 1 row deleted. SQL> commit; Commit complete.
And now all the rows that start with BOWIE as a separate transaction:
SQL> delete bowie where name like 'BOWIE%'; 500 rows deleted. SQL> commit; Commit complete.
Block dump from disk:
buffer tsn: 6 rdba: 0x018000ab (6/171)
scn: 0x0000.003c3e8a seq: 0x01 flg: 0x06 tail: 0x3e8a0601
frmt: 0x02 chkval: 0x139e type: 0x06=trans data
Block header dump: 0x018000ab
Object id on Block? Y
seg/obj: 0x12ad3 csc: 0x00.3c3e85 itc: 3 flg: O typ: 2 – INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.013.00000d48 0x00c01082.0263.02 CB– 0 scn 0x0000.003bb7e1
0x02 0x0005.01a.00000d73 0x00c011bf.0268.05 C-U- 0 scn 0x0000.003c3b42
0x03 0x0004.01f.00000d72 0x00c01f0a.02a1.25 –U- 300 fsc 0x171a.003c3e8a
Leaf block dump
===============
header address 211493492=0xc9b2274
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 302
kdxcofbo 640=0x280
kdxcofeo 2630=0xa46
kdxcoavs 2009
kdxlespl 0
kdxlende 300
kdxlenxt 0=0x0
kdxleprv 25165994=0x18000aa
kdxledsz 0
kdxlebksz 8012
row#0[4390] flag: —D–, lock: 3, len=17
col 0; len 7; (7): 42 4f 57 49 45 32 38
col 1; len 6; (6): 01 80 00 a1 00 1b
row#1[4407] flag: —D–, lock: 3, len=18
col 0; len 8; (8): 42 4f 57 49 45 32 38 30
col 1; len 6; (6): 01 80 00 a1 01 17
row#2[4425] flag: —D–, lock: 3, len=18
…
row#299[7995] flag: —D–, lock: 3, len=17
col 0; len 7; (7): 42 4f 57 49 45 39 39
col 1; len 6; (6): 01 80 00 a1 00 62
row#300[2630] flag: ——, lock: 0, len=25
col 0; len 15; (15): 54 48 49 4e 20 57 48 49 54 45 20 44 55 4b 45
col 1; len 6; (6): 01 80 00 a2 00 57
row#301[2655] flag: ——, lock: 0, len=24
col 0; len 14; (14): 5a 49 47 47 59 20 53 54 41 52 44 55 53 54
col 1; len 6; (6): 01 80 00 a2 00 56
The first transaction used the now oldest ITL slot 2. The second transaction then went on to use ITL slot 3, cleaning out the lock information of the first transaction in ITL 2. It deleted all 300 index entries within the block starting with BOWIE, marking them all as deleted with the D flag in all the index entries and with a 3 lock byte set. Note however the index entry for MAJOR TOM as deleted in the first transaction has already been physically removed from the leaf block …
Again, the transaction in ITL 3 being the last transaction now has its scn/fsc (0x171a.003c3e8a) in the block header (scn: 0x0000.003c3e8a).
Let’s add a couple new rows with 2 transactions to cycle through both ITL entries …
SQL> insert into bowie values (504, 'DAVID JONES'); 1 row created. SQL> commit; Commit complete. SQL> insert into bowie values (505, 'SCREAMING LORD BYRON'); 1 row created. SQL> commit; Commit complete.
Block dump from disk:
buffer tsn: 6 rdba: 0x018000ab (6/171)
scn: 0x0000.003c42b0 seq: 0x02 flg: 0x06 tail: 0x42b00602
frmt: 0x02 chkval: 0x0191 type: 0x06=trans data
Block header dump: 0x018000ab
Object id on Block? Y
seg/obj: 0x12ad3 csc: 0x00.3c42ae itc: 3 flg: O typ: 2 – INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.013.00000d48 0x00c01082.0263.02 CB– 0 scn 0x0000.003bb7e1
0x02 0x0009.001.00000d80 0x00c044f5.029a.03 C— 0 scn 0x0000.003c418d
0x03 0x0001.013.00000e05 0x00c0423b.0267.02 –U- 1 fsc 0x0000.003c42b0
Leaf block dump
===============
header address 211493492=0xc9b2274
kdxcolev 0
KDXCOLEV Flags = – – –
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=— is converted=Y
kdxconco 2
kdxcosdc 1
kdxconro 4
kdxcofbo 44=0x2c
kdxcofeo 2579=0xa13
kdxcoavs 7868
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 25165994=0x18000aa
kdxledsz 0
kdxlebksz 8012
row#0[2609] flag: ——, lock: 0, len=21
col 0; len 11; (11): 44 41 56 49 44 20 4a 4f 4e 45 53
col 1; len 6; (6): 01 80 00 a2 00 55
row#1[2579] flag: ——, lock: 3, len=30
col 0; len 20; (20): 53 43 52 45 41 4d 49 4e 47 20 4c 4f 52 44 20 42 59 52 4f 4e
col 1; len 6; (6): 01 80 00 a2 00 58
row#2[2630] flag: ——, lock: 0, len=25
col 0; len 15; (15): 54 48 49 4e 20 57 48 49 54 45 20 44 55 4b 45
col 1; len 6; (6): 01 80 00 a2 00 57
row#3[2655] flag: ——, lock: 0, len=24
col 0; len 14; (14): 5a 49 47 47 59 20 53 54 41 52 44 55 53 54
col 1; len 6; (6): 01 80 00 a2 00 56
—– end of leaf block dump —–
We now notice all the 300 BOWIE entries have now been physically cleaned out of the block as well, cleaned out as part of the block changes required for these final transactions. The leaf block now only contains these 4 index entries, as shown with a kdxconro 4. The last transaction (inserting “SCREAMING LORD BYRON”) using ITL 3 is the only transaction with its lock byte still set and has its scn/fsc (0x0000.003c42b0) in the block header (scn: 0x0000.003c42b0).
So each concurrent transaction within the index block requires an ITL entry (and Oracle will add them as necessary providing there’s sufficient free space within the block). A transaction will not only make its necessary changes, locking just those index entries associated with the transaction but will also clean out data from previous transactions if present (including index entries marked as deleted by a previous transaction). Finally, it will generally stamp the block header with the corresponding transaction scn.
Hopefully, this highlights how block dumps can be useful to both see and demonstrated Oracle behaviour.
Next, time to look at a number of 11g index related new features …
Richard Foote's Oracle Blog 