Oracle Scratchpad

January 8, 2014

CR Trivia

Filed under: Infrastructure,Oracle,Performance — Jonathan Lewis @ 6:44 am BST Jan 8,2014

Everybody “knows” that when you do a tablescan of a table that it starts with two buffer gets on the segment header, though older versions (ca. 8i and earlier) of Oracle used to do 4 buffer gets on the segment header. The upshot of this is that many people still say that if you create a table and insert a single row then you’re going to get 3 buffer gets when you tablescan a table: two for the segment header and one for the data block:

So here’s a test, with the second set of autotrace stats which, for reasons I’ll describe shortly, may not be immediately reproducible on your system:

create table t1 (n1 number);
insert into t1 values(1);
execute dbms_stats.gather_table_stats(user,'t1');

set autotrace traceonly statistics
select * from t1;

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

That really is 22 buffer gets to find the one row in the table. It gets worse; I used another session to insert (and commit) a second row in the table and the tablescan to return the two rows took 36 buffer gets.

If you want to get the same results you have to be a bit careful and a bit lucky. I was using ASSM (automatic segment space management) on a tablespace with an 8MB uniform extent size; when I inserted the single row into the table Oracle formatted a batch of 16 consecutive blocks in the first extent, deciding which range of blocks to format based on my process id. When I inserted my row, the block I inserted into was again dictated by my process id – this happened to be the 10th block in the formatted range.

When I ran the tablescan I did 6 gets on the segment’s space management blocks to work out what parts of the segment were formatted, then 10 gets to find the first row in the 10th block of the range, then a further 6 gets to scan the rest of the formatted range. I do not know why it takes 6 gets to read the space management blocks – but it may be two each for the segment header block, L2 bitmap block, and L1 bitmap block – it may be four on the segment header and one each on the other two blocks. One day I might get around to checking this with event 10200.

When I inserted a second row from another session (which I had set up very carefully), Oracle decided to format another batch of 16 blocks. This highlighted a little variation on what I’ve said so far. Because I had 8MB uniform extents the first 18 blocks of the first extent were all space management blocks (segment header, one L2 bitmap, and 16 L1 bitmaps – in the opposite order to that description). Oracle formats on boundaries of 16 blocks in the extent and in this case formatted the 14 blocks that would take it from block 19 to block 32 of the extent. That’s basically why my tablescan after the second insert took an extra 14 gets.

If you really do need to scan a “very small” table extremely frequently (and you shouldn’t, really) then it might be a good idea to check how many blocks have been formatted compared to the number of blocks used (dbms_rowid.rowid_block_number() will help with that check) and do an “alter table move” because in this special case Oracle won’t use the “format 16 blocks somewhere” strategy, it will format only as many blocks as needed starting from the first available block in the segment. For some reason the number of gets on space management blocks is also minimised in this case so that tablescan of a “single block” takes only 2 + number of blocks in table.

For ASSM segments Oracle maintains a Low High Water Mark (LHWM) and a High High Water Mark (HHWM). Every block below the LHWM is guaranteed to be formatted, blocks between the LHWM and HHWM will be formatted in batches of 16; so when doing a segment scan Oracle uses the largest multiblock-read it can from the start of the segment to the LHWM, then keeps checking the space management blocks to identify the batches of 16 that it can then read (and it can read consecutive formatted batches in a single read, so the reads are not necessarily limited to 16 blocks at a time).

One little side thought – because the choice of block and block range is dictated by the process id, the pattern of data insertion and contention using shared servers can be very different from the pattern produced by dedicated servers.



  1. Thanks for the explanation, Jonathan.

    At the PL/SQL Challenge quiz on Database Design very recently we had a quiz on why IOT’s or even indexed tables may be a better choice for very small lookup tables, than thinking a full table scan of a one-block table will be good:

    There was a bit of discussion among players afterwards whether the full table scan sometimes could be best option. I would guess from your post here that the best you can get is if you do the “alter table move” to bring a full table scan down to three gets. (Or possible achieve the same effect by setting some parameters at table creation to force just one block to be formatted?) And even then (if I compare the tests shown in the quiz) and IOT for the lookup table can use just a single get?

    Anyway, I just learned that it might not always be a good idea to assume that full table scan of a very small lookup table will be the minimum number of gets possible ;-)

    Kim Berg Hansen

    Comment by Kim Berg Hansen (@kibeha) — January 8, 2014 @ 7:39 am BST Jan 8,2014 | Reply

    • Arguably there’s a slightly better option for the stated requirement though (as in many of these edge cases) the benefit is minimal, and addressing the wrong problem.

      The best implementation for the requirement stated in that quiz is probably a single table hash cluster with a size of “one block”, the “hash is” option identifying the primary key as the hash key, and a hashkeys values greater than the number of rows. You’d still have to include the primary key constraint, but Oracle wouldn’t use the index to access the table, instead you’d get the data at one row per block, with a direct hash access:

      The benefit is that concurrent access to different keys would use be using different buffers and (more importantly) “cache buffers chains” latches; the drawback is that you have one buffer used per key value, rather than one buffer for the entire set of values.

      (There was a time when the hash table approach was a clear winner because the get was a “consistent get – examination”, but Oracle’s been able to do those on index root blocks for a very long time now.)

      The approach is still the winner if the size of the IOT grows beyond the single root/leaf block for the index, of course – but then you’ve probably got a lot more rows (hence blocks for the hash table) and you MIGHT end up having to worry about hash collisions if your ID values are not sequential or are not numeric.

      Comment by Jonathan Lewis — January 8, 2014 @ 9:19 am BST Jan 8,2014 | Reply

  2. how can we check the number of formatted blocks?

    Comment by sule — January 6, 2015 @ 10:38 am BST Jan 6,2015 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by