Indexes And Small Tables Part V (It’s No Game)

So far in our little example, we’ve looked at how accessing a row of a one block table via a FTS required 4 consistent gets while accessing this same table via a Non-unique index reduced the consistent gets down to 3.

Time to take the next step and improve the efficiency yet further of accessing this small one block table.

We’re now going to replace the Non-unique index with a Unique Index instead. We can obviously do this because the values on the indexed ID column are indeed unique.

Now it’s always a good idea of course to document these table business rules (such as a column being unique) inside the database, however it’s somewhat alarming just how many application just don’t this. I’ve also previously discussed how a PK or Unique constraint can actually be policed via a Non-Unique index so there are many reasons why a small table might not have an associated Unique index.

Not least of course the incorrect perception that an index is not going to be much use on a small table anyways …

So let’s now replace the Non-Unique index with a Unique index instead:

SQL> drop index small_id_i;

Index dropped.

SQL> alter table small add primary key (id) using index (create unique index small_id_i on small(id));

Table altered.

 
OK, so now we have our Unique index in place. Let’s now run the same query again to see how our consistent gets related statistics might change:

 
SQL> select * from small where id = 42;

 

        ID NAME
---------- -----
        42 BOWIE

--------------------------------------------
|Id |Operation                  |Name      |
--------------------------------------------
|  1|TABLE ACCESS BY INDEX ROWID|SMALL     |
|* 2| INDEX UNIQUE SCAN         |SMALL_ID_I|
--------------------------------------------

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

 

We notice our first significant change. The number of consistent gets has reduced further down to just 2.

Why ?

Because with a Unique index, there can only be a maximum of 1 row returned. It’s simply not possible to return 2 or more rows.

Therefore, when selecting this one row, Oracle doesn’t have to perform the second fetch operation to confirm there are indeed no more rows to return. The first fetch will either return the one row of interest or none at all when resolving an equality predicate. That’s it, there are no other possibilities. We return the row of interest by simply accessing the index block (1 consistent get) followed by the table block (the second consistent get).

As we have previously, if we look at the actual consistent gets statistics of interest by running the following query in another session before/after the select statement:

SQL> SELECT n.name, s.value FROM v$sesstat s, v$statname n WHERE s.statistic# =n.statistic# AND s.sid = 141 AND n.name LIKE ‘consistent%’;

NAME                           VALUE
------------------------------ -----
consistent gets                31236
consistent gets - examination   5084

 
And again afterwards ..

 
SQL> SELECT n.name, s.value FROM v$sesstat s, v$statname n WHERE s.statistic# =n.statistic# AND s.sid = 141 AND n.name LIKE ‘consistent%’;

NAME                           VALUE
------------------------------ -----
consistent gets                31238 (+2)
consistent gets - examination   5086 (+2)

We notice we have indeed only performed the 2 consistent gets. But we also notice another significant difference, that being both consistent gets are now the “cheaper” consistent gets – examination.

This means that the latches required to now perform this select statement via the Unique index is just 2, down from 6 for the Non-unique index and 8 from the FTS.

Generally during a consistent get, Oracle needs to grab the cache buffers chain latch so it can pin the specific block in memory and then grab the latch again so that it can subsequently unpin the block once it’s finished processing the block. Each of these accesses to the latch and the subsequently pin/unpinning of the block requires CPU and is a possible source of contention within the database.

For some operations that only require a very very quick “read and get out of there” type operation and/or on blocks that are unlikely to change within a given point of time, Oracle uses a cheaper consistent get operation which doesn’t actually require the block to be pinned. There’s no point in pinning the block as it’s only going to be read and accessed for a short time (shorter than might otherwise be required when processing a block in memory) and the block is unlikely to change anyways.

So for these operations, Oracle uses a cheaper consistent get called a consistent gets – examination. These consistent gets examinations only need grab the cache buffers chain latch before quickly reading the block and releasing the latch once the read operation is complete. Therefore it only needs to grab and release the cache buffer chains latch the once without having to pin/unpin the block, which means less CPU and less latch contention overall.

Now this isn’t particularly well documented. Often discussions mention reads of undo blocks as being candidates for consistent gets examinations as these reads are likely to be both relatively quick and a specific undo block is unlikely to change as only one transaction can actually update an undo block at a given time.

Getting back to indexes, reads of index root blocks are another candidate mentioned as again a read of an index root block is going to be very quick and an index root block is unlikely to change at a given point of time.

However, what is not well documented at all is the fact that any block accessed during an index Unique Scan is accessed via a consistent get – examination, including the consistent get associated with reading the table block as well. This is because again, any such read operation is going to be relatively quick as the most that ever needs to be read is the one index related entry and the one table row.

The net result is that now accessing a row from a small table via a Unique index requires only 2 latch accesses vs. the initial FTS example which required 8 latch gets as none of the FTS consistent gets are examinations.

Now you might say that these are all very small numbers, that 4 consistent reads isn’t that much, that 8 latches isn’t really that huge a number and reducing 8 latches down to 2 latches isn’t really going to be that noticeable. Yes it is effectively a 75% reduction but it’s a 75% reduction of not very much.

And if you’re discussing a single read of a single small lookup table you would likely be right.

But what if the small table is accessed frequently by the application, perhaps many 1,000s of times per minute. What if you have many such small tables, often used in small join operations by your OLTP applications. What if you have large numbers of users in a large application with many many such small table accesses. This effectively 75% saving can potentially become very significant, both in terms of the reduction in CPU load and also in the reduction of latch contention, which in turn can further reduce CPU loads.

A small improvement multiplied by a large amount can indeed make a difference …

However, I have one more step to go yet in further improving the efficiency of these small table lookups via an index.

One which can reduce the overall overheads by yet another 50% …