Re: Number of extents related with performance?

On Fri, 29 Dec 2000 00:44:54 -0800, Jimmy <anonymous_at_anonymous.com> wrote:

> I've read news and documents about the effect on the performance
>when the number of extents of object is increased. Most of it (even on
>Oracle Metalink) just mentioned that the performance is not related with
>the number of extents (except dropping and truncating tables). However,
>when I query the table that has about 800000 rows (about 200M storage
>spaces) with over 100 extents (extent size is 32K), it takes about 10
>second to return the number of rows. When I increase the extent size to
>64M (number of extents is now 3), it just takes 2 seconds.
>
> Could anyone tell me is there any relationship between the performance
>and the number of extents of an object? (in my case, both tables are
>newly created, so no fragmentation) It seems that, as my case, decrease
>the number of extents can increase the query performance.
>

Sounds awful. I have not yet been able to prove conclusively that number of extents is significant for performance since V7. Wasn't really on V6 either, but few people knew what to do to init.ora to stop the problem.

Which version and what OS you running this on?

I'm curious, because it is against my experience so far in later versions of ORACLE. In fact, since V7 I have taken take the "easy space management" approach explained in a paper by Himatzinga and Loaitza a while ago and have never looked back.

Cheers
Nuno Souto
nsouto_at_bigpond.net.au.nospam
http://www.users.bigpond.net.au/the_Den/index.html Received on Fri Dec 29 2000 - 08:12:14 CST