Re: Table-Extents / Performance

So,

what is the reason, that a simple "select * " on an empty(!) table with many extents needs about 20 seconds and less then 2 seconds after a truncation of that table ??

Thank You,
Marcus

"Steve Long" <steve-long_at_mediaone.net> wrote in message news:<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 Wed Sep 12 2001 - 13:47:30 CEST