Indexes On Small Tables Part II (The Mysteries)

I’m a bit pushed for time at the moment but I thought I might quickly just expand a little on the observations in Part I of this no doubt soon to be epic series (there’s at least another 3 parts to come) !!

In Part I we saw how even with a tiny table that consists of just one block containing 100 rows of application data, Oracle requires 4 consistent get operations to retrieve just the one row via a Full Table Scan (FTS). This surprises some folks as they expect Oracle to perhaps only need the 1 or maybe 2 consistent gets to access this one block containing application related row data.

If we were to flush the buffer cache before running the SELECT statement and trace the associated session, the resultant trace file shows how Oracle needs to first visit the Segment Header of the table before it can read the actual table block containing the row of interest. Oracle needs to read the table segment header in order to determine what blocks need to be accessed to begin the FTS operation. In this specific case, there’s only the one data block but Oracle also has to check to ensure there indeed are no more blocks it needs to visit. Oracle also has to perform another fetch operation to confirm there are indeed no more rows it needs to return after it fetches the first (and in this case only) row.

The following should help to show what’s going on during the FTS of our little 100 row table where all 100 rows nicely resides in the one data block.

We first flush the buffer cache in order to force Oracle to perform physical I/Os (note the following example was run on 11.1.0.6).

SQL> alter system flush buffer_cache;

System altered.

 

Next, we trace our session …

SQL> exec dbms_monitor.session_trace_enable(waits=> true);

PL/SQL procedure successfully completed.

 

Before running the SELECT statement that returns our one row of interest …

SQL> select * from small where id = 42;

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

 

SQL> exec dbms_monitor.session_trace_disable();

 

If we look at an example trace file:

=====================
PARSING IN CURSOR #19 len=33 dep=0 uid=88 oct=3 lid=88 tim=24079367090 hv=2225904586 ad=’23bdd320′ sqlid=’7pjs08q2at6ya’
select * from small where id = 42
END OF STMT
PARSE #19:c=0,e=18142,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=24079367080
EXEC #19:c=0,e=35,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=24079367262
WAIT #19: nam=’SQL*Net message to client’ ela= 5 driver id=1111838976 #bytes=1 p3=0 obj#=62986 tim=24079367301
WAIT #19: nam=’db file sequential read’ ela= 16020 file#=7 block#=117897blocks=1 obj#=99586 tim=24079383391
WAIT #19: nam=’db file sequential read’ ela= 16820 file#=7 block#=117898blocks=1 obj#=99586 tim=24079416967
FETCH #19:c=15625,e=49760,p=2,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=24079417101
WAIT #19: nam=’SQL*Net message from client’ ela= 445 driver id=1111838976 #bytes=1 p3=0 obj#=99586 tim=24079417615
FETCH #19:c=0,e=27,p=0,cr=1,cu=0,mis=0,r=0,dep=0,og=1,tim=24079417699
STAT #19 id=1 cnt=1 pid=0 pos=1 obj=99586 op=’TABLE ACCESS FULL SMALL (cr=4 pr=2 pw=2 time=0 us cost=2 size=9 card=1)’
WAIT #19: nam=’SQL*Net message to client’ ela= 6 driver id=1111838976 #bytes=1 p3=0 obj#=99586 tim=24079439168

 

Notice how the first highlighted wait event performs a ‘db file sequential read’ to first  access the segment header as denoted by file#=7 block#=117897 in order to determine which blocks need to be read during the FTS operation. You can easily confirm the file# and block # corresponds to the table segment header by querying DBA_SEGMENTS.

This is then immediately followed by another ‘db file sequential read’ wait event to access the only data block of interest as denoted by file#=7 block#=117898. Notice how this block is simply the block that follows the segment header, as all 100 rows were inserted at one time by the one transaction. Note this is the only data block in the table that contains rows and is the only table block that needs to be accessed during the FTS operation.

Notice how the first FETCH operation resulted in 3 consistent gets (cr=3), 2 consistent gets that correspond to the 2 physical I/O waits events already identified plus an extra consistent read to confirm there were no more table blocks of interest.  This FETCH returns the first and (in this case) only row of interest (r=1).

A second FETCH was required resulting in an additional consistent get (cr=1) to confirm to the client that there are indeed no more rows of interest to be returned after the first row was fetched (r=0). We might know there’s only one row but Oracle doesn’t until it performs this second fetch.

Note BTW that if the query returned no rows at all, this second fetch would not have been required as the first empty fetch would have confirmed to Oracle there were no more rows to come. The total CR count would have been just 3 in this case (but would still have been bettered by an index if present).

This is a small lookup table and we’re generally interested in just the one row. As discussed in Part 1 and now expanded upon here, a FTS requires at least 4 CR behind the scenes when retrieving just the one row of interest, even if the table is tiny and can potentially store all its rows in just the one data block.

You can’t really get a table smaller than one block and yet as we’ll see, an index can beat the 4 CR overhead of reading a row from this tiny table via a FTS.

Next installment coming soon … 🙂