Re: Table-Extents / Performance
Date: Tue, 11 Sep 2001 10:38:58 GMT
Message-ID: <6Rln7.43$vr1.6352_at_typhoon.jacksonville.mediaone.net>
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