Is it recommended practice to use uniform extent sizes in Oracle tablespaces?

Question

I've been using Oracle for quite some time since Oracle 8i was released. I was new to the database at that time and was taught that it was best to use constant sized extent sizes when defining tablespaces.

From what I have read, it seems that today using 10/11g, that Oracle can manage these extent sizes for you automatically and that it may not keep extent sizes constant. I can easily see how this can more efficiently use disk space, but are their downsides to this. I'm thinking it may be time to let go of the past on this one. (assuming my past teaching was correct in the first place)

Answer 1

If you have unlimited disk space with instant access time, you don't have to care about extents at all.

You just make every table INITIAL 100T NEXT 100T MAXEXTENTS UNLIMITED PCTINCREASE 0 and forget about extents for next 300 years.

The problems arise when your disk space is not unlimited or access time varies.

Extents are intended to cope with data sparseness: when your data are fragmented, you will have your HDD head to jump from one place to another, which takes time.

The ideal situation is having all your data for each table to reside in one extent, while having data for the table you join most often to reside in the next extent, so everything can be read sequentially.

Note that access time also includes access time needed to figure out where you data resides. If your data are extremely sparse, extra lookups into the extent dictionaries are required.

Nowadays, disk space is not what matters, while access time still matters.

That's why Oracle created extent management.

This is less efficient in term of space used than hand-crafted extent layout, but more efficient in terms of access time.

So if your have enough disk space (i. e. your database will take less than half of the disk for 5 years), then just use automatic extents.

Answer 2

Yes, except for very unusual cases it's time to let go of the past and use the new Oracle extent management features. Use locally-managed tablespaces (LMT's) and the auto extent sizing and you don't have to think about this stuff again.

As a DBA, the variable extent sizing worried me at first since in the 7.3 days I spent a lot of time reorganizing tablespaces to eliminate the fragmentation that resulted from extent allocation with non-zero percent increases. (and you needed non-zero percent increases because your maximum number of extents was capped at different levels depending on the database block size used when you created the database) However, Oracle uses an algorithm to determine the rate and magnititude of extent size increases that effectively eliminates fragmentation.

Also, forget anything you have heard about how the optimum configuration is to have a table or index fit into a single extent or that you can somehow manage i/o through extent configuration - this has never been true. In the days of dictionary-managed tablespace there was probably some penalty to having thousands of extents managed in a dictionary table, but LMT's use bitmaps and this is not an issue. Oracle buffers blocks, not segment extents.