| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Reorg Oracle 8 Partition tables
Thanks Thomas for all the hard work on figuring out the maths. As you say, there
are always exceptions. My point was that in the vast majority of cases, with
multiple users doing different reads on different rows (as opposed to full table
scans) the disk head will move all over the place anyway. Having an object in one
extent helps not at all in this scenario.
Pete
Thomas Kyte wrote:
> A copy of this was sent to Steve Phelan <stevep_at_toneline.demon.co.uk>
> (if that email address didn't require changing)
> On Fri, 19 Feb 1999 20:58:15 +0000, you wrote:
>
> >So 10,000 extents, all just a bit under the multi-block I/O count * block
> >size, on a partition that is predominantly full scanned is not a problem,
> >then?
> >
>
> well, they never said "10,000"....Just said "increasing in extents" I guess the
> followup should have said:
>
> as long as the extents are even multiples of your multi block read, don't worry
> about having lots of extents. Lots of extents affect the performance of certain
> DDL operations in 8.0 (like drop but how many times do you drop a table?) and
> below (but not so in 8i with locally managed extents, a new feature) but they
> don't affect:
>
> - indexed reads (regardless of the multi block read setting)
> - reads of indexes (regardless of the multi block...)
> - any other scattered io operation (regardless...)
> - full scans (given that your extents are multiples...)
>
> I guess if I have a table in 10,000 extents and lets say the extents are small,
> say 1meg+2k, and my multi block read is 64k (max on most OS's), it'll take 16
> IO's (16 ios * 64k = 1024k = 1m) to read an extent PLUS 1 extra IO to pick up
> that nasty 2k 'extra' piece.
>
> My object is 10,000 meg + 10,000*2k in size or 10,020 meg apparently.
>
> So, it would take 10,000*16 + 10,000 = 170,000 ios to full scan it.
>
> If I had taken the time to right size, and made the object with perfect 1meg
> extents, it would take 160,320 ios to read 10,020 meg of data.
>
> So in a worst case scenario, it might add 9,680 ios which sounds like alot but
> its only a 6% difference (160,320 is 94% of 170, 170,000 is 106% of 160,320) so,
> maybe its not that horrible after all (i mean, taking the time to fix this
> problem would only ever increase full scan performance by 6%)?
>
> As the size of the extents go up, the percentage overhead this "slip up" adds
> gets smaller...
>
> Yes, if you exagerate the example and make the extent size really small -- the
> percentage goes up. Just trying to point out that on a table in 10,000
> extents, 10,000 extra ios just might be noise....
>
> >Or is that just 'predominant thinking'?
> >
>
> the predominant thinking, and I see it lots, is that you have to constantly be
> striving to have objects in a single extent for some reason. thats wrong. Its
> not true. Its a myth. Ignore it. Let them grow.
>
> I agree with Peter. The original poster should really ask themselves "why am i
> doing this" to see if it *really* needs to be done.
>
> >Steve Phelan.
> >
> >(Oracle 7 & 8 OCP)
> >
> >
> >Peter Sharman wrote:
> >
> >> Kevin
> >>
> >> Before re-orging I would have to ask why are you doing this. Having an
> >> object in multiple extents is NOT a problem, regardless of the predominant
> >> thinking that it is.
> >>
> >> HTH.
> >>
> >> Pete
> >>
> >> Lo wrote:
> >>
> >> > Help,
> >> >
> >> > My partitions are increasing in extents, and I will need to re-organise
> >> > them. I need to know which is the easiest way to do it.
> >> >
>
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Service Industries
> Reston, VA USA
>
> --
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
--
Regards
Pete
Peter Sharman Email: psharman_at_us.oracle.com WISE Course Development Manager Phone: +1.650.607.0109 (int'l) Worldwide Internal Services Education (650)607 0109 (local)San Francisco
"Controlling application developers is like herding cats."
Kevin Loney, ORACLE DBA Handbook
"Oh no it's not! It's much harder than that!"
Bruce Pihlamae, long term ORACLE DBA
 |
 |