Re: Database reorg in Oracle 8?

I have indexes with close to a 1,000 extents in a LMT without any noticable affect on performance. Do you have any further information to back up your claims? I suspect that you noticed a difference because you rebuilt an index which could use rebuilding. After rebuilding, you made the index smaller, thus the statistics were calculated faster. But what does this have to do with the overall number of extents? Have you done studies on an index with 1,000 1MB extents and done the same test on the same index, rebuilt as 10 100MB extents? Or something like that to show that the number of extents is what is causing the performance problem and not something else.....

Cheers,
Brian

Sheilah Scheurich wrote:
>
> 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.
> >>| >
> >>| >
> >>|
> >>|
> >>
> >>
> >
> >

-- 
===================================================================

Brian Peasland
oracle_dba_at_remove_spam.peasland.com

Remove the "remove_spam." from the email address to email me.


"I can give it to you cheap, quick, and good. Now pick two out of
 the three"