extent Size and performance

What is the importance of creating objects with right extent sizes? Application folks very often places the segments in small uniform size tablespaces or small extent sizes ( in dictionary managed tablespaces, though an old story!) , causing the number of extents very high. I have seen applications using objects more thousands of extents.

I did a small test, which shows how bad a ‘db file scattered read’ with large number of small extents.

Though the time wise the difference was small, but there was a big difference in over all operation. The trace files shows the difference.

Though the db_file_multiblock_read_count was set to 128, the TEST table in 64K tablespace was never been able to use it. The maximum single read was 8 blocks (Extent size / Block size=64/8=8). Alert file shows – part of trace file

There were only 212 reads in 5M uniform sized tablespace. So, a small uniform sized tablespace can add strain on I/O when it is doing ‘db file scattered read’ on large tables. It is not just fragmentation.