Re: Table-Extents / Performance

the following excerpt from the document you refer to should provide insight to the questions you raise:

Oracle supports an unlimited number of extents in a segment. The performance for DML operations is largely independent of the number of extents in the segment. However, certain DDL operations such as dropping and truncating of segments are sensitive to the number of extents. Performance measures for these operations have shown that a few thousand extents can be supported by Oracle without a significant impact on performance. A reasonable maximum has been determined to be 4096.

The goal of our recommended algorithm is to keep the number of extents below 1024 which is well within the range that Oracle can efficiently handle. When a segment reaches 1024 extents it is a candidate to be moved to the next larger extent size tablespace.

"waltervdv" <marcus.kuehl_at_t-online.de> wrote in message news:b2677297.0109110152.64cd8786_at_posting.google.com...
> Hallo !
>
> Recently I read in a paper that it's no problem to have tables with
> many extents in a database
> (http://technet.oracle.com/deploy/availability/pdf/defrag.pdf). My
> DBA-Handbook says, it is the best to put the whole table into one
> extent.
>
> I'm working on a database with no online-transactions - only
> batch-jobs (inserts and deletes, very few updates). Last week I had to
> remove the data from some tables. I did that by "delete from table
> xxxx". It was a hard job, because it took very very long.
> Afterwords I looked at some tables with the schema-browser of toad.
> Although the tables where empty it took very long (maybe 20 seconds)
> for toad to show the table-data-view. The tables had some extents
> (maybe 10 - 30).
> After I had done a "truncate table xxx drop storage" the access-speed
> to these tables was "normal" again (1 - 2 seconds).
> I think this is because the tables had only one extent left after the
> truncate statement.
> So why can anybody say that the number of extents is not important for
> performance ?
>
> Any Ideas ????
>
> Marcus
Received on Tue Sep 11 2001 - 12:38:58 CEST