The number of extents on tables may or may not have negative impact,
however the number of extents on an index definatly will have an impact.
Simply run statistics (with or without timing) on an index with a
large number of indexes (100 or more), rebuild the index without the
extents and run the same process. You will see that the number of reads
as well as the overall performance of the query is dramatically improved.
-Sheilah Scheurich
Jim Kennedy wrote:
> Prove the number of extents has a negative impact.
> Jim
> "Anton Buijs" <remove_aammbuijs_at_xs4all.nl> wrote in message
> news:3ee3b911$0$49114$e4fe514c_at_news.xs4all.nl...
>
>>That's what I try to say to: what's the cause.
>>And number of extents is irrelevant to performance... to a certain extent.
>>
> I
>
>>don't want to perpetuate that myth, please, I will fight it with you. But
>>
> in
>
>>a DMT (since he is on 8.0.5 LMT is not available) lets say >1000 (you can
>>make it 5000 too if you like) CAN begin to have impact. Therefore if you
>>
> are
>
>>going to reorganize its not a bad idea to apply proper storage clauses
>>
> too.
>
>>Won't hurt performance either, does it?
>>
>>In addition: Heiko, what is the blocksize of the db? If 2Kb it is a
>>
> possible
>
>>candidate for performance degradation.Changing it to 8Kb for instance can
>>improve it but requires full export, create the db again and do full
>>import.Unfortunately only tests could show what is the best for your
>>situation.
>>
>>Jim Kennedy <kennedy-down_with_spammers_at_attbi.com> schreef in
>>
> berichtnieuws
>
>>0nOEa.883579$OV.827446_at_rwcrnsc54...
>>| The number of extents is irrelevant to performance. Stop perpetuating a
>>| myth. Heiko needs to determine why his performance is terrible.
>>Currently,
>>| Heiko is operating on pure guesswork and causing himself a lot of extra
>>| work. Determine the cause of the performance problem and focus on
>>
> fixing
>
>>| that.
>>| Jim
>>|
>>| --
>>| Replace part of the email address: kennedy-down_with_spammers_at_attbi.com
>>| with family. Remove the negative part, keep the minus sign. You can
>>figure
>>| it out.
>>| "Anton Buijs" <remove_aammbuijs_at_xs4all.nl> wrote in message
>>| news:3ee387fd$0$49101$e4fe514c_at_news.xs4all.nl...
>>| >
>>| > Heiko <ht_at_bigfoot.de> schreef in berichtnieuws
>>| > c7307$3ee3802a$d4cac0e3$771_at_nf1.news-service.com...
>>| > | > | Is there a tool to reorg a Oracle 8 database? Size is 10 GB with
>>
>>| 100
>>| > | > | tables.
>>| > | > |
>>| > | > | Thanks!
>>| > | > | Heiko
>>| > | >
>>| > | > Oracle export/import?
>>| > | > But it all depends on WHAT you want to reorganise. What is your
>>| purpose?
>>| > | > Why? What is it you need to change?
>>| > | > (please specify exact version and platform too).
>>| > |
>>| > | First I want to rebuild the index-files, in a second step - I hope I
>>can
>>| > | reorganize
>>| > | the data-tables.
>>| > | We work on Windows 2000 Server with Oracle 8.0.5.0.0
>>| > |
>>| > | Thanks!
>>| > | Heiko
>>| > |
>>| >
>>| > In that case: reorg the tables first (alter table <tablename> move
>>....;)
>>| > Moving a table makes all its indexes invalid and they must be rebuild.
>>| > I'am still curious why you think reorganizing is good. Do you see many
>>| > chained rows? Do tables have too many extents (many is > 500 or > 1000
>>or
>>| > so, then change the storage clause too in the alter table move).
>>| > You probably know it but you should consider an upgrade to 8.1.7.3 (or
>>| > 8.1.7.4) because 8.0.5 is no longer supported.
>>| >
>>| >
>>|
>>|
>>
>>
>
>
Received on Tue Jun 10 2003 - 10:20:22 CDT
