jump to navigation

Indexes On Small Tables Part I (One Of The Few) April 16, 2009

Posted by Richard Foote in Oracle Indexes, Small Indexes.
trackback

A common question I get asked is when is a table too small to benefit from being indexed.

If a table only has a few blocks for example, which could all be potentially read via a single multiblock read operation, surely there’s no benefit in indexing such a table (except perhaps to police an associated PK constraint). It must take at least 2 Logical I/O (LIO) operations to read data from the table via an index, at least one LIO to read an index block and at least one LIO to read the associated table block referenced by the ROWID in the index. If a Full Table Scan (FTS) can be effectively performed via a single multiblock read operation, hence reading the entire table with just one LIO , surely an index will always be a more expensive option and so ultimately useless with such small tables.

Well not necessarily …

The first thing to point out is that generally speaking, a Full Table Scan is a relatively expensive operation. Tables can be big, really really big, consisting of potentially many many 1,000s of data blocks, potentially requiring many 1,000s of multiblock read operations to be performed. Therefore, generally speaking, if we’re going to perform a relatively expensive FTS, we’re not going to be too concerned if we use an extra I/O or two, as we potentially have to perform 1,000s of I/Os anyways. A shortcut here or there is not going to generally make much of a difference one way or the other.

Note also that with a FTS being this relatively expensive operation, we’re not likely to generally speaking want to perform 1,000s of such FTS operations every minute within our databases. Generally speaking, a FTS is a much less common event than an Index Range Scan operation and so we wouldn’t take advantage of any possible short cuts here or there very often.

However, generally speaking, an index scan is a relatively inexpensive operation, potentially consisting of just a few LIO operations. We may have an index that has a blevel of say 2 (height of 3) and we may typically only want to select a row or two. That would therefore consist of just 3 LIOs of read the index related blocks (the index root block, an index branch block and an index leaf block) plus an I/O or two to read a row or two from the table. It’s potentially just a handful of blocks, just a few little LIOs but if we could somehow save an I/O or two in the process, this could in fact make a huge difference to the relative costs of the Index Range Scan.

Note also that with an Index Range Scan being this relatively inexpensive operation, we’re quite like to generally speaking want to perform lots and lots of such Index operations each and every minute in our databases. Generally speaking, an Index Range scan is a very very common event and so any short cut here or there can be extremely useful and significant and be taken advantage of frequently within the database.

So a FTS has a tendency to be relatively expensive and is not performed anywhere near as as frequently as Index Range Scan operations which have a tendency to be relatively inexpensive. Generally speaking of course.

But Oracle takes this generalisation very much to heart in how it goes about processing these operations.

The next point to make is that if a table has just a few rows and say consists of  just the one data block below its High Water Mark (HWM), it doesn’t necessarily mean we only need just the one I/O operation to read the entire table. For example, how does Oracle know there’s just one block worth of data ? How does Oracle know where to actually physically locate this one block worth of data ? How does Oracle know that once its read this block, there aren’t any other data blocks of interest ?

The answer is that it can’t without referencing data dictionary objects and without accessing the table segment header where the extent map is located. Even for a tiny table with only a handful of rows that can reside in only the one table block, it therefore requires more than just the one consistent get operation to read data from the table via a FTS. However, as a FTS is usually a relatively expensive operation, these few little consistent reads here and there to determine the actual number of blocks in the table and the actual location of these blocks is generally going to be a relatively trivial overhead. Oracle though doesn’t differentiate between a small and a larger table when it comes to a FTS, so these extra few consistent reads can potentially be a significant overhead for FTS operations on smaller tables.

As an example, let’s create a little table and see what consistent gets are required to read it via a FTS …

Let’s begin by creating a small table that consists of just 100 little rows.

SQL> CREATE TABLE small AS SELECT rownum id, ‘BOWIE’ name FROM dual CONNECT BY LEVEL <= 100;

Table created.

SQL> exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’SMALL’, estimate_percent=> null, method_opt=> ‘FOR ALL COLUMNS SIZE 1′);

PL/SQL procedure successfully completed.

SQL> SELECT blocks from user_tables WHERE table_name=’SMALL’;

    BLOCKS
----------
         1

Note that this table consists of just the one data block below the HWM. A table can’t really get much smaller that one block.

Let’s now select just one row from this table. Note we haven’t created an index at this point so Oracle has no choice but to read this one row via a FTS.

SQL> SELECT * FROM small WHERE id = 42;

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

 

Execution Plan
------------------------------------------
|Id  | Operation         | Name  | Rows  |
------------------------------------------
|  0 | SELECT STATEMENT  |       |     1 |
|* 1 |  TABLE ACCESS FULL| SMALL |     1 |
------------------------------------------
Statistics
--------------------------------------------
  0  recursive calls
  0  db block gets
  4  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

 

Note to read just this one row from this one block table, we have actually performed 4 consistent gets operations. Not 1 consistent get, but 4 consistent gets …

Let’s look at the actual type of consistent gets, by running the following statement in another session before and after executing the above SELECT statement (note SID 134 refers to the session SID that ran the above SELECT statement) :

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

NAME                           VALUE
----------------------------- ------
consistent gets               275851
consistent gets - examination  70901

Note the above figures were the session consistent gets before the SELECT statement and the following consistent gets statistics are after the SELECT statement was executed. 

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

NAME                           VALUE
----------------------------- ------
consistent gets               275855 (+4)
consistent gets - examination  70901 (0)

Note that yes indeed, there were 4 consistent gets performed and that none of the consistent gets were the “cheaper” consistent gets examinations. Therefore, the 4 consistent gets used in performing the FTS of the one block table required 4 x 2 = 8 latches.

Now 4 consistent reads to perform a FTS isn’t too bad, even for this little table and 8 latches isn’t exactly a huge number.

However, as we’ll see next, an index on this tiny one block table can do so much better …

About these ads

Comments»

1. Noah Yetter - April 16, 2009

“So a FTS has a tendency to be relatively expensive and is not performed anywhere near as as frequently as Index Range Scan operations which have a tendency to be relatively inexpensive. Generally speaking of course.

But Oracle takes this generalisation very much to heart in how it goes about processing these operations.”

Well, no it doesn’t, unless you tell it to. The default value of optimizer_index_cost_adj is 100, which (roughly speaking) instructs the optimizer to treat index scans as 100% as expensive as full table scans. So by default, Oracle does not assume one or the other access method is relatively more or less expensive. And you will observe that under this default parameter value, the optimizer will elect FTS more often than you might like.

2. sandro - April 16, 2009

interesting!

3. Richard Foote - April 16, 2009

Hi Noah

I think you’ve missed the point somewhat. I’m not discussing how the CBO costs FTS vs an index scan, I’m discussing how a FTS might require more CR operations than an index scan, even on a table with just one data block.

The point I’m making is that there are “hidden” overheads when performing a FTS with Oracle having to visit the segment header which are not applicable to Index scans as I’ll discuss. So indeed, Oracle does note there are extra I/Os associated with performing a FTS that are not necessary associated with an Index Scan. Setting optimizer parameters doesn’t change the fact a FTS must visit the segment header.

Note I haven’t even created an index yet in my demo, that’s all to come. Hey who knows, perhaps Oracle might favour the index, even without setting any optimizer paramaters …

Perhaps it’s just your wording, but the O_I_C_A does not in fact instruct the CBO to treat index scans as being 100% as expensive as FTS, but that the cost of an I/O associated with an index scan access path is as expensive as the I/O of a FTS access path. Big difference.

Finally, you should not be setting the O_I_C_A parameter with currently supported versions of Oracle, you should be using system statistics which are generally more “accurate” than those values set by over keen DBAs, many of whom don’t even know what they’re really doing when they set the O_I_C_A parameter.

4. coskan - April 16, 2009

Hi Richard,

What version are you on and what is the block size of tablespace

This is my resultset from 10.2.0.4 with 8KB blocksize. I see 4 blocks and 4 consistent reads ???

SQL> CREATE TABLE small AS SELECT rownum id, ‘BOWIE’ name FROM dual CONNECT BY LEVEL exec dbms_stats.gather_table_stats(ownname=>null, tabname=>’SMALL’, estimate_percent=> null, method_opt=> ‘FOR ALL COLUMNS SIZE 1′);

PL/SQL procedure successfully completed.

SQL> sELECT blocks from dba_tables WHERE table_name=’SMALL’;

BLOCKS
———-
4

SQL> set autotrace on;
SQL> SELECT * FROM small WHERE id = 42;

ID NAME
———- —–
42 BOWIE

Execution Plan
———————————————————-
Plan hash value: 2557977142

—————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
—————————————————————————
| 0 | SELECT STATEMENT | | 1 | 9 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| SMALL | 1 | 9 | 3 (0)| 00:00:01 |
—————————————————————————

Predicate Information (identified by operation id):
—————————————————

1 – filter(“ID”=42)

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

5. Richard Foote - April 16, 2009

Hi Coskan

You should get the 4 consistent gets on any currently supported version of Oracle. However, the 4 blocks is likely due to the tablespace being ASSM.

6. coskan - April 16, 2009

Bingo : ) my tablespace was ASSM.

Question
When 4 blocks and 4 consistent reads, isnt this ideal scenario (except the allocated space is higher on ASSM) ?

Or can there be smiler situations, happen on ASSM ?

7. Richard Foote - April 17, 2009

Hi again Coskan

Not really an ideal scenario for two very good reasons:

1) Although 4 blocks are listed, Oracle is in actual fact still only visiting the segment header and the actual data block containing all the rows of interest. It’s a quirk with the ASSM tablespace, although if you don’t follow the demo to the letter and say have multiple sessions inserting the 100 odd rows, then more than one data block will likely contain data and indeed more blocks will be visited and the CRs will increase accordingly. So although you’re using an ASSM tablespace, the point I’m making that a table containing all it’s rows within one data block requires 4 consistent gets is still valid. If you were to flush the buffer cache before running the select statement and trace the session, you should be able to see this in the trace file.

and

2) it’s not ideal because an index on this small, effectively one (or four) block table will actually be more efficient than the FTS as we shall see … ;)

ASSM tablespaces might be worth blogging about at some stage.

8. Gary - April 19, 2009

You may cover this later, but is there a benefit in making some/most/all small tables into index-organized tables ?

9. Brian Tkatch - April 21, 2009

Richard, this is excellent, i was wondering about this for quite some time. I am looking forward to the next installment.

I have a few questions though. Probably due to ignorance.

1) Isn’t the data dictionary in memory? Making accessing the data dictionary quite cheap?

2) This test is on disk. What about in memory? Especially if the small was used recently enough cached.

3) If small is a “lookup TABLE”, that is, it is generally used with a larger TABLE and then to give text to an id, or the like, does the INDEX still provide the same benefit?

10. B. Polarski - April 22, 2009

The access to the data dictionary is serialized:one session at a time. All sessions must queue in order to access the data dictionary. Efforts are made to segment this data dictionary in order to allow multplite sessions access at the same time providing they access different regions of the data dictionary. Also Oracle try to use Mutex which are OS latches for they are faster. But all in all, if memory is 100k faster than disk, a full in-memory DB is far from being 100k faster. I re-call old figure of a ratio of 37, sound a bit weak but gives an hint of the scale we speak about.

11. Richard Foote - April 22, 2009

Hi Gary

Wow, you can read my mind :)

Yes, I plan to discuss IOTs in the last part of this discussion. However, in answer to your question, yes IOT should be given serious consideration, with a few warnings of course.

12. Richard Foote - April 22, 2009

Hi Brian

A quick answer to each point.

1) The key difference between a FTS and an index scan is the visit to the segment header with a FTS (as I plan to discuss). The manner in which Oracle determines which block to first visit is similar in both cases, but with a FTS it must acccess the segment header while with the index scan it does not.

2) It doesn’t matter. A consistent get is a consistent get, whether or not it involves a physical I/O. Assuming it’s a heavily accessed small table and index, then both segments are likely to be cached. I wanted to generate PIOs however as it makes tracing the session turn up useful info.

3) Yes it does. Typically many small lookup type tables are accessed very frequently to determine a specific value in a join scenario. An index on a small table to be used in such join conditions can potentially save significant resources as we’ll see.

13. Richard Foote - April 22, 2009

Hi B Porlarski

Discussions on the DD are beyond scope here as any DD overheads are somewhat similar between parsing/executing a FTS and Index scan execution plan.

The key point I will make though is that Oracle begins a FTS function call by accessing the segment header of the segment as it contains vital info required for the FTS operation.

However an index scan (but not a Fast Full Index Scan) begins by directly accessing the index root block (or single leaf block if it’s a level 0 index). there is nothing within the index segment header that’s required for an index scan, so the segment header is an “overhead” we can save and potentially taken advantage of.

Also, the type of consistent get can be “cheaper”, resulting in less block pinning and latch contention as we’ll see as well.

Next installment coming soon …
:)

14. B. Polarski - April 23, 2009

Something not mentionned here : a FTS will cycle its blocks only on a fraction of the LRU, 2% if I am correct. Thus if we have a lot of small different FTS, or a BIG FTS comes after your small FTS, all the blocks of your small FTS will be washed away from memory either by the Big FTS or my the sheer number of concurrent small FTS.
A index accesss on the contrary will have its blocks in more persistent locations of the SGA. Thus, on heavily loaded system can’t we say that it is better to have index access, even if they are marginaly costlier in order to guarantee better overall retention?

Richard Foote - April 24, 2009

Not quite. A table is deemed “small” by Oracle if it’s 2% or less of the buffer cache in size. A small table is “cached” when read via a FTS and is not immediately loaded into the least recently used end of the LRU list as becomes a “large” table read via a FTS. Some bugs in some earlier releases though didn’t perform the touch count properly on cached small tables and as such they struggled to remain cached unless under serve load.

Therefore, a FTS of a larger table only has a very minimal impact on the buffer cache as such blocks age very quickly and don’t push other buffers out.

Whether something should remain cached depends entirely on how frequently it gets accessed and what it needs to push out in order to be cached.

The good thing about an index access is that the blocks can remain cached. The bad thing about an index access is that the blocks can remain cached !! Overall better retention is not necessarily a good thing if it means retaining and caching things that don’t get reused.

Meaning, if you have a really expensive and possibly inappropriate index access that loads the buffer cache with heaps of blocks that are not likely to be accessed again, pushing out other blocks that could have been reused, that’s not really a good thing. Yes the buffer cache touch count algorithm helps and protects things to same extent but a “bad” index access can still cause issues within the buffer cache.

A FTS of a large table doesn’t have the same risks, as such blocks don’t cause much damage. Therefore, an argument could be made the other way around and a FTS is actually the safer option with regard to the buffer cache.

Again, it all comes back to how often do the associated blocks get re-accessed in cache.

Mark Brady - September 12, 2009

Can you explain this a bit more, “and is not immediately loaded into the least recently used end ”

A free buffer is found
The blocks are read into it

In a large table the buffer is placed at the LRU end of the cache.

in a small table… ? if it’s not immediate, then when, if it’s not on the cache then where is it?

15. Why don’t read error message « Siamnobita’s Blog - May 3, 2009

[...] Why don’t read error message ห่างหายไปนาน ไม่ได้มาเขียนอะไรเพิ่มเติม แถมยังมีการลบหัวข้อที่เคย public ไปแล้วอีกต่างหาก สาเหตุที่เอาหัวข้อนั้นออกไป เนื่องจากผมได้ไปอ่านข้อเขียนของคุณ Richard Foote ว่าแม้แต่กรณีที่ตารางมีขนาดเล็กมาก ๆ (ข้อมูลแค่ 1 data block) การใช้ index ก็ยังอาจจะได้เปรียบการ full scan table อยู่ดี แถมแสดงตัวอย่างให้ดูเสร็จสรรพ ทำลายความเชื่อมั่นของผมลงในทันที แต่ก็เพิ่มความมั่นใจในคำกล่าวของThomas Kyte ที่ว่า “คุณสามารถเรียนรู้เรื่องใหม่ ๆ จาก oracle ได้ทุกวัน” ผมจึงขอดึงหัวข้อนั้นกลับมาทบทวนแก้ไขก่อน public ใหม่ เพื่อไม่ให้ผู้อ่านรับรู้ข้อมูลผิด ๆ ตามผม ใครที่อ่านไปแล้วก็ขออภัยไว้ ณ ที่นี้ครับ [...]

16. Bradley D. Brown » Exiciting Day at TUSC - the iPerspective Launch!!! - May 28, 2009

[...] No Such Thing as a Table Too Small [...]

17. [번역] Indexes On Small Tables Part 1 « sleeperBus's Oracle - August 23, 2009

[...] 포스트는 Richard Foote's Oracle Blog를 번역한 것입니다. 의미 전달 상 의역이 있을 수 [...]

18. Richard Foote - September 16, 2009

Hi Mark

When a block is read from a “large” table via a FTS, the blocks are basically loaded into the LRU end of the LRU list, even though they’ve only just been loaded into the buffer cache. As such, the chances of them remaining in the buffer cache is minimal and will likely be immediately overwritten once processed.

However, when a block is read via an index, the blocks are loaded somewhere near the “middle” of the LRU list, not at the LRU end of the list. This means that the blocks have a way to go before being aged out and if subsequently accessed and the touch count increments sufficiently, have a reasonable chance of remaining cached in the buffer cache.

This is all designed to protect the buffer cache from the possible effects of a massive table being read into the buffer cache via a FTS. It by designed can only a minimal impact on the caching of other objects as the FTS will itself age out it’s own previously cached blocks.

The blocks from an index scan (or small table) on the other are given a reasonable chance of survival in the buffer cache, providing they are indeed subsequently accessed.

I’ve simplfied things but hopefully explained what I meant.

Mark Brady - September 17, 2009

Perfectly explained. It was just the “not immediately loaded” that had me wondering what happened to the block.

How do the three buffer pools play into this formula? Keep, Recycle, Default, right?

Richard Foote - September 23, 2009

Hi Mark

Very briefly, most of the pools basically behave the same as the default. There are a few slight differences, for example the Keep pool has the default behaviour of “cache” even for larger segments, but the basic LRU and touch based costings apply to all the pools.

19. Cary Millsap - September 24, 2009

Richard,

Do you think that the number of CRs executed during the FTS is a function of the value of _bump_highwater_mark_count?

Cary

20. _SMALL_TABLE_THRESHOLD Parameter and Buffer Cache – What is Wrong with this Quote? « Charles Hooper's Oracle Notes - June 17, 2010

[...] final blog article, from the comments section: “A table is deemed ‘small’ by Oracle if it’s 2% or [...]

21. Book Review: Oracle Performance Firefighting (Fourth Printing) « Charles Hooper's Oracle Notes - June 26, 2010

[...] [...]

22. ORACLE ALWAYS FULL SCANS A TABLE SMALLER THAN DB_FILE_MULIBLOCK_READ_COUNT : A MYTH | ORACLE IN ACTION - April 15, 2014

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 1,713 other followers

%d bloggers like this: