Re: Database reorg in Oracle 8?

1. You have just put most of the index into cache.(thus improving retrieval from the index since most of it is in ram)
2. You have just packed the index which will become less packed as data is changed .(try the rebuild with the extents the same number)

Generate a test case where it clearly shows that the number of extents matters. Test it.

I will grant you that if you choose extent sizes that are not a good fit for your multiblock read count and not a good fit for how your OS handles reading data off the disk, then you can make it look bad. However, you cannot guarrentee - without getting a freshly formatted disk - that the actual block order is the same as the physical block order on the disk. 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.
"Sheilah Scheurich" <mommydba_at_hotmail.com> wrote in message
news:3EE5F736.3050505_at_hotmail.com...


> 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.

> >>| >

> >>| >

> >>|

> >>|

> >>

> >>

> >

> >

>