JL Computer Consultancy

One extent does less work.

Nov 2004


Arguments about whether an object is inherently more efficient when stored in a single extent have been around for many years. But I think it is now generally understood that the effort of rebuilding objects simply to pack them into a single extent is usually a pointless waste of effort.

But there really is a reason why a single extent is the most efficient option!  I realized this after listening to an extremely informative presentation on logical I/Os given by Julian Dyke of Intel Solutions at the recent UKOUG annual conference


It is well known that when you do a select {something} from dual, the cost is four current gets and one consistent get in Oracle 8; but the same query in Oracle 9 results in three consistent gets.

This fact has been extrapolated  (by me, amongst others) to the general, and incorrect, claim that a tablescan or index fast full scan has a special overhead of 4 current gets in Oracle 8 with 2 consistent gets in Oracle 9. This is true some of the time, but the precise effect depends on the Oracle version and the number of extents in the segment being scanned.

In Oracle 8, there will be 4 current gets on the segment header block until the segment grows beyond 10 extents – after which the number of gets grows by one for every 10 extents added.  In Oracle 9 (and above), there will be 2 consistent gets on the segment header block if the segment consists of one extent, but three consistent gets if the segment has between 2 and 10 extents, and then one extra consistent get for every 10 extents.  (Things change again if you have so many extents that you have multiple extent map blocks scattered around the object – but I believe that was already common knowledge).

You can check this observation by building a few small tables with very small extents, and counting logical I/Os whilst doing lots of tablescans in a pl/sql loop. If you want to dig in a little deeper you can see what types of logical I/O are taking place by checking x$kcbsw and x$kcbwh.

The most significant points when you check closely are that Oracle 8 does one of the starting calls twice (this is a bug), whilst Oracle 9 does the same call only once. Then Oracle 8 always does a call to read the extent map; but Oracle 9 doesn’t if there is only one extent – after all, it must know where the extent is because it’s just read the first block of the extent! This explains the difference of two reads for single-extent tables.


Conclusion

Oracle 9 really does do less work in a tablescan if your data is in a single extent; and Oracle 8 really does do less work if your data spans less than 10 extents. If you had a table of about 500 extents that you were scanning regularly, then it would be doing about 50 fewer logical I/Os if it had been built as a single extent.

Of course, if you are doing frequent tablescans on a table of 500 extents, I don’t think you would notice the benefit of avoiding those 50 (rather lightweight) logical I/Os. You will probably have far more important areas of optimization to worry about. So, even though it is an interesting curiosity that “one extent” really does do less work, anyone with a realistic sense of perspective would probably decide that the benefit gained in a rebuild is still not worth the effort required.


Back to Index of Topics