How to determine minimal extent size ?

[efrijters]

Sorry, but that wasn't the question.

In the SQL-example I try to determine the optimal extent size for reading data when looking at the OS.

I'm trying to proof that my theory is correct:
- The segments have an extent size of 64K;
- db_file_multiblock_read_count=128; block size 8KB
- when reading data, the trace file says that Oracle reads in chunks of 32 blocks, so 32 blocks * 8K = 256KB. So, probably, this is the OS's limit.

To rephrase my question:
-If Oracle can optimally read 256KB at a time, isn't it better to have extent sizes that are 256KB? or a multiple of 256KB? So that the extent size corresponds with the optimal OS-read-size?

I hope this clarifies it.

Erik

[efrijters]

I think the answer is YES.

But extents of 40KB are good, because there are also a lot of tables that only have 1 or 2 rows. So, rebuilding those tables to extent size 256KB would only waste hard disk space.

Now I understand the TS_SMALL, TS_MEDIUM, TS_LARGE story better.

Thanks everybody!

[slimdave]

Originally posted by efrijters
I think the answer is YES.

But extents of 40KB are good, because there are also a lot of tables that only have 1 or 2 rows. So, rebuilding those tables to extent size 256KB would only waste hard disk space.

Now I understand the TS_SMALL, TS_MEDIUM, TS_LARGE story better.

Thanks everybody!

Yes, this seems fair, but i should add that this only affects large i/o requests, like full table or fast full index scans. Accessing tables by rowid through the index, or index operations other than FF scan, get no benefit from this extent sizing issue.

[efrijters]

Thanks SlimDave! Have a good weekend!