jump to navigation

Index Block Dump: Block Header Part II and Read Consistency (I Can’t Read) July 28, 2010

Posted by Richard Foote in Block Dumps, Index Internals, Oracle Indexes, Read Consistency.

OK, let’s look at the next portion of the index block dump.
Following the hex dump of the block (as we ended Part I of the series) is the second part of the block header (see below):   

Block header dump:  0x0201490a
 Object id on Block? Y
 seg/obj: 0x1c205  csc: 0x00.2d11214  itc: 2  flg: -  typ: 2 - INDEX
     fsl: 0  fnx: 0x0 ver: 0x01
 Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.02d11214


The following details are listed initially:
0x0201490a – rdba again
seg/obj: 0x1c205 – data_object_id again
csc: 0x00.2d11214 – initial scn of block
itc: 2 – number of Interested Transaction List (ITL) entries in the block
typ: 2 – INDEX  – denotes this specific block as indeed an Index block
Then follows the Interested Transaction List (Itl) which is used by Oracle to store critical information of any transaction making changes within the block. An index leaf block is basically assigned 2 ITL entries by default (but only 1 if it’s an index Branch block). However the number of pre-assigned ITL entries is set by the INITRANS physical attribute parameter of the index. 

A ITL entry basically consists of:
A unique slot ID (starting with 01)
Transaction ID
Undo Block Address (Uba) which denotes the Undo segment assigned to the specific transaction
Flag and Lock Bytes
System Change Number (SCN) 

Any transaction wishing to make a change to the block must first be allocated to an ITL entry, with the first ITL entry (01) reserved by Oracle for internal recursive operations. If there’s an ITL entry that’s not being used by a current transaction, great, a new transaction will simply use a free ITL entry. However, if all ITL slots are already assigned to current transactions within the index block, then an additional ITL entry is created and allocated to the new transaction by Oracle, providing there’s sufficient free space within the block or the 255 limit hasn’t been reached (or the MAXTRANS parameter is not reached in older versions of Oracle).
As a simple demo to see this in action, I’m going to insert two additional rows into the table (and so create two new index entries in the index): 

SQL> insert into bowie values (4, 'THIN WHITE DUKE');
1 row created.
SQL> insert into bowie values (5, 'ALADDIN SANE');
1 row created.
SQL> commit;
Commit complete.

If we now dump the index block and look at the ITL slots:

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x000a.006.000065d4  0x00c00441.2fb5.09  --U-    2  fsc 0x0000.02d120c8


We see the transaction has used ITL slot number 02 as relevant details are now assigned.
Points of interest includes the Flag is now set to U (Unclean) and that the Lock attribute is assigned the value 2. This means the transaction has been committed but the index block has not been cleaned out and is currently “unclean” in that the 2 new index rows associated with the transaction still have the lock byte set (as we’ll see in a later post).
The other important piece of information is the value of the SCN within the ITL (0x0000.02d120c8). The SCN is basically a timestamp that denotes when the transaction made the associated changes to the index block. As this is the last transaction to have changed the block, this SCN effectively denotes the point of time when the block was last changed.
If we now also look at the start of the block dump and the block header details discussed in Part I of this series:

Start dump data blocks tsn: 8 file#:8 minblk 84234 maxblk 84234
Block dump from cache:
Dump of buffer cache at level 4 for tsn=8, rdba=33638666
BH (0x157E78EC) file#: 8 rdba: 0x0201490a (8/84234) class: 1 ba: 0x1548C000
  set: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0 lid: 0x00000000,0x00000000
  dbwrid: 0 obj: 115205 objn: 115205 tsn: 8 afn: 8
  hash: [0x292CCE8C,0x292CCE8C] lru: [0x19FFB3BC,0x2930A050]
  obj-flags: object_ckpt_list
  ckptq: [0x1A3EA500,0x18FF0A90] fileq: [0x293178F0,0x19FFB398] objq: [0x2183AA3C,0x2183AA3C]
  st: XCURRENT md: NULL tch: 2
  flags: buffer_dirty redo_since_read gotten_in_current_mode
  LRBA: [0x627.e9dd.0] LSCN: [0x0.2d120c8] HSCN: [0x0.2d120c8] HSUB: [1]
  cr pin refcnt: 0 sh pin refcnt: 0
  buffer tsn: 8 rdba: 0x0201490a (8/84234)
  scn: 0x0000.02d120c8 seq: 0x03 flg: 0x02 tail: 0x20c80603
  frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

We notice the header has also been changed and now has the SCN from this transaction stamped in the header. Therefore any process needing this index block to be at a specific point in time (say a Select statement during a consistent read operation) can now quickly check the block header to see “when” it was last changed.
If the block has indeed changed since the (say) Select statement started, it need now only check the ITL slots to see which transaction has matching SCN details and so find the specific transaction that last changed the index block. Knowing the transaction in question, Oracle can then reference the Undo Block Address in the ITL entry and so determine the specific undo segment that contains details of the previous state of the block and rollback all changes made by this transaction and create a new consistent image of the block as it was prior to the transaction making the block changes. The Select statement can now check the previous SCN in the header and repeat the same process as necessary until the index block is at a point in time prior to the Select statement starting.
In short,  basically how consistent reads are implemented in Oracle.

Everything I’ve discussed so far in Parts I & II are just as applicable to tables (and other segments) as they are to indexes. In the next post, we’ll look at the next portion of an index block dump which is unique to index segments …

Oracle OpenWorld 2010 Agenda (Breakfast In America) July 22, 2010

Posted by Richard Foote in OOW.

I was all set and ready to go Oracle OpenWorld this year when things took a turn for the worse and it looked very much as if I wouldn’t be able to make it but things have thankfully changed yet again and it now looks like I will finally be able to make it to OOW this year after all.

Which is a good thing as I have a number of presentations scheduled for this year’s conference. Firstly, I was invited by the IOUG to do a presentation for the Oracle OpenWorld User Group Forum on the Sunday:

Session S318615: “IOUG: Oracle Indexing Tips Trick and Traps” – Sunday September 19, 12:30pm Moscone West L2 Room 2005

Secondly, my proposal in the Oracle Mix Suggest-A-Session got enough votes to be selected. Confirmed details are:

Session S319069: “Oracle Indexing New Features In Oracle11g R1 and R2” – Monday September 20, 2:00pm Moscone South Room 303

I would like to sincerely thank all of you who voted. Hopefully you can now make it to the session and maybe even pick up a thing or two.

Note many of these sessions at OOW  have a nasty habit of filling up quickly so if any of these are of interest, I would strongly recommend booking them in and reserving your spot (once Oracle get their Schedule Builder up and running that is). That said, the three of us in attendance should have plenty of room to stretch out 😉

Finally, I’ve decided to put my name down for the Oracle Unconference again this year. Partly because it was relatively successful last year when I attempted this in that it generated a number of interesting discussions, partly because it’s in the spirit of the Unconference where unstructured and interactive presentations are the go but mainly because it requires absolutely no preparation on my part whatsoever, I’ve decided to host another question and answer session on all things Oracle Indexes. This is your chance and opportunity to ask those questions on how Oracle indexes work, how they should be used, how they should be maintained, on indexing issues you want to share or solve or dispel or whatever, with a whole bunch of other Oracle professionals. As always, if the questions dry up, no worries, we can spend the rest of the time discussing which of David Bowie’s albums is the most influential 😉 Details are:

“Oracle Indexing Q & A With Richard Foote” Tuesday 21 September, 10:00am Hotel Parc 55 Mason Room

The Oracle Unconference is always one those secret little highlights of the whole Oracle OpenWorld event where you often get the opportunity to listen, meet and speak with some great speakers in relatively quiet and intimate (for OOW) surroundings. Always worth checking out the agenda each day.

Now I’ve posted all this, my plans are now very likely to change yet again but hopefully I’ll get the opportunity to catchup with a whole bunch of you again at OOW 2010 in September. If you see me wondering around, don’t forget to say Gidday !!

UPDATE: The Schedule Builder is now available from the Oracle OpenWorld website so those attending can now book and reserve places at their presentations of interest. I’ve got in early and booked all my favourites.

Index Block Dump: Block Header Part I (Editions Of You) July 20, 2010

Posted by Richard Foote in 11g, Block Dumps, Index Internals, Oracle Indexes.
1 comment so far

I’ve previously looked at how to generate an Oracle block dump, time to now go into a little more detail.

As I mentioned, a block dump is a formatted representation of the actual contents of an Oracle block. Producing strategic block dumps can be an extremely useful method of determining what might be going on in Oracle under the covers and over the years I’ve found them to be an invaluable aid in helping me understand Oracle behaviour, troubleshoot issues, investigate the contents of corrupted blocks, etc. The focus in this series will be Oracle block dumps from the perspective of indexes, although the contents any Oracle block can be dumped and investigated.
To setup the demo, I’m going to initially create a simple little table and associated index that has only 3 rows to begin with. As the index is so tiny, all the contents can fit within the one index leaf block resulting in an index with a blevel of 0 (height of 1). Note I’m using a database running on Windows for this specific demo. The actual format and content of a block dump differs between releases and continually changes. However, much of the useful content which I’ll focus on remains relatively consistent.

SQL> create table bowie (id number, name varchar2(20));
Table created.
SQL> insert into bowie values (1, 'BOWIE');
1 row created.
SQL> insert into bowie values (2, 'ZIGGY');
1 row created.
SQL> insert into bowie values (3, 'MAJOR TOM');
1 row created.
SQL> commit;
Commit complete.
SQL> create index bowie_name_i on bowie(name);
Index created.
SQL> select index_name, blevel, leaf_blocks from dba_indexes where index_name = 'BOWIE_NAME_I';
--------------- ---------- -----------
BOWIE_NAME_I             0           1


OK, as this is a simple blevel 0 index, all the contents of the index can be found in the block immediately following the index segment header.

SQL> select header_file, header_block from dba_segments where segment_name='BOWIE_NAME_I';
----------- ------------
          8        84233


So the block we want to dump is the block in datafile 8, block 84234 (1 more than the segment header block). Before we dump the block though, let’s just note a few other points about this index. Firstly, given we know the datafile and block of interest, we can determine its relative data block address (rdba) by plugging in these details into the dbms_utility.make_data_block_address function:

SQL> select dbms_utility.make_data_block_address(8, 84234) from dual;

We’ll note the rdba of 33638666 for future reference. The other thing we’ll just record is the Object ID of the index:

SQL> select object_id, data_object_id from dba_objects where object_name = 'BOWIE_NAME_I';
---------- --------------
    115205         115205


Again, we’ll note an Object_Id and Data_Object_Id of 115205 for future reference. Finally, I’m just going to flush the buffer cache to ensure the current contents of the block is written to disk. This is useful in 11g when the block dump differentiates between the block contents in the buffer cache and on disk.

SQL> alter system flush buffer_cache;
System altered.


OK, let’s now dump the block associated with this index leaf block:

SQL> alter system dump datafile 8 block 84234;
System altered.


Below is just the block header portion of the resultant block dump:

Start dump data blocks tsn: 8 file#:8 minblk 84234 maxblk 84234
Block dump from cache:
Dump of buffer cache at level 4 for tsn=8, rdba=33638666
BH (0x17BF3D8C) file#: 8 rdba: 0x0201490a (8/84234) class: 1 ba: 0x17A70000
  set: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0 lid: 0x00000000,0x00000000
  dbwrid: 0 obj: 115205 objn: 115205 tsn: 8 afn: 8
  hash: [0x292CCE8C,0x15BFB42C] lru: [0x167EA1EC,0x15BFB48C]
  lru-flags: on_auxiliary_list
  ckptq: [NULL] fileq: [NULL] objq: [NULL]
  st: FREE md: NULL tch: 0
  cr pin refcnt: 0 sh pin refcnt: 0
  Buffer contents not dumped
Block dump from disk:
buffer tsn: 8 rdba: 0x0201490a (8/84234)
scn: 0x0000.02d11215 seq: 0x01 flg: 0x04 tail: 0x12150601
frmt: 0x02 chkval: 0x9ae6 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x0BF39A00 to 0x0BF3BA00

BF39A00 0000A206 0201490A 02D11215 04010000  […..I……….]
BF39A10 00009AE6 001A0002 0001C205 02D11214  […………….]
BF39A20 1FE80000 00021F02 00000000 00000000  […………….]
BF39A30 00000000 00000000 00000000 00000000  […………….]
BF39A40 00000000 0000FFFF 00000000 00000000  […………….]
… Snip rest of memory dump …

As I mentioned, the actual format and content of a block dump differs between releases and continually changes so your formatted block dump may differ somewhat. However, the main points which I’ll discuss should be found in most currently supported versions of Oracle. This post should only be considered as a basic introduction on the subject. More depth and details to come.

As this is an 11g block dump, the block header consists of three distinct sections:

1) Dump of the buffer cache details associated with the index block

2) Dump of index block from disk

3) Full raw hex dump of the associated block

The first thing to check is that one is looking at the correct block dump and that the correct segment block was indeed dumped. The start of the formated block dump states the details of the dumped block(s):
file#:8 minblk 84234 maxblk 84234
so indeed, we’re looking at the correct dump file. These details are also listed along with the hex representation of relative block address (rdba) information:
file#: 8 rdba: 0x0201490a (8/84234)
and we can also confirm that the rdba is also correct and consistent with the block we have dumped:
as this matches the results of the rdba from the rdba as previously displayed via the MAKE_DATA_BLOCK_ADDRESS function.
We can also confirm that the correct index segment was dumped as the object id of the index:
obj: 115205 objn: 115205
matches the data_object_id and object_id from dba_objects as listed previously.

Other details of interest found in block dumps in most Oracle versions include:
scn: 0x0000.02d11215 – The System Change Number (SCN) of the block when it was last modified (I’ll show you how this gets populated in a later post). This is the effective “timestamp” of the block and is used by Oracle to determine crucial information such as effectively “when” the block was last modified and by “what” transaction.
type: 0x06=trans data – denotes the type of block. 06 represents a transactional block (table/index/cluster)
seq: 0x01 – number of the block changes within the the current scn (we’ll see how this increments in a later post)
tail: 0x12150601 – which consists of the last 2 bytes of the SCN, type and seq
frmt: 0x02 – denotes the block format with 02 representing a post Oracle8 block format. An A2 block (as shown in the raw block dump) denotes a post 10g block format.
chkval: 0x9ae6 – checksum value of block as used by Oracle in part to check the consistency and validity of the block 

In later versions of Oracle (10g and beyond), the block dump includes a full hex dump of the associated memory buffer. I’ve snipped most of this in the above dump extract. However, one can see where the details of the block I’ve listed above can be found within the memory dump (hopefully, the colour code will help to highlight where each distinct piece of information can be found). Note also in 11g and beyond, more details of the buffer cache are listed and detailed as defined in the buffer cache section.
I’ll look at the following portion of the index block header, the Interested Transaction Slots, in the next post. We’ve only just begun …

World Cup, Insync10 and Coming Index Block Dumps July 12, 2010

Posted by Richard Foote in InSync10, Richard's Musings, World Cup.

Well after a month of watching the World Cup during these long cold Canberra nights, my beloved Spain have finally, at long long last, after years and years of bitter disappointment and despair, have deservedly won the World Cup !! Iniesta, you legend, you will never have to buy a drink ever again, whenever or wherever you walk into any bar in Spain !!

Life now just isn’t going to be quite the same again …

My dream now is for Australia to both host the World Cup in 2022 and to win it one day as well.

With Cadel Evans now leading the Tour de France, perhaps I have a few more weeks of sporting late nights ahead of me still …

For those of you in Australia, just a reminder that the InSync10 Conference in Melbourne is fast approaching. I’ll be both attending and presenting this year and will be joined by a host of great speakers including Tom Kyte, Mogens Norgaard, Debra Lilley, Steven Feurestein, Connor McDonald, Kyle Hailey, Guy Harrison and Penny Cookson to name but a few. Should be a great event and I strongly recommend making it to Melbourne if you can.

My session on “Indexing New Features in 11g R1 & R2” is in Room 106 on Tuesday, 17 August 10:00am-10:45am.

I’ll soon be posting a series on Index Block Dumps, discussing how to read and interpret them and how they can be incredible useful in learning not only how Oracle indexes actually work and behave but also the internals to many core Oracle concepts such as row level locking, concurrency and read consistency.

But for tonight, a long night’s sleep finally awaits. Buenos noches …