Re: Unlimited extents?

On Fri, 7 Dec 2001 19:21:24 -0500, "news1.trim.net" <chumly_at_jg2.org> wrote:

>What is the general feeling towards setting table and tablespace storage
>settings to unlimited extents. My tendency is to want to set the extents as
>close as possible to what I have calculated as being enough. Am I being
>foolish? Should I just let it go?
>
>What are the pros/cons?
>
>Thanks
>
>Barry
>

Your policy is what everyone was doing in the past (before 1998). Everyone was doing it because of the upper limit in the number of extents related to block size.
The existence of an upper limit was one of the main reasons why everyone tried to keep the number of extents as low as possible. This means everyone was constantly reorganizing tables as calculations are at it's best educated guesses.

Nowadays, without any upper limit, and cheap disks, it has been established the performance hit because of a high number of extents is a vigorous well-established *myth*. Oracle won't read more than db_block_size * db_file_multi_block_read_count in one go anyway.

So, IMO, you should let it go. You still have constraints and upper limits, established by the size of your disks. Your policy would result in a kind of implicit very granular quota scheme, quota per table. I would try to limit those quota to an user, and start to worry when users constantly exceed their quota. Doing so you also avoid users seeing the dratted 'maxextents exceeded' error message.

Just my 2 eurocents

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Sat Dec 08 2001 - 01:16:27 CST