Re: further issues

"yls177" <yls177_at_hotmail.com> wrote in message news:c06e4d68.0405301709.61043d5a_at_posting.google.com...

>
> thanks for the clarification... but say now , there are some DML
> statements which reduce the segment size from 23K to 20K, then there
> will be "holes" in this extent which are known as fragmentation?

You need to be a bit more precise when discussing this sort of thing. Say you do some deletes on a table, so that instead of 23K of row data, there is now 20K of row data. There will definitely be 3K more free space *within* an extent when that happens. The extent won't be fragmented, you'll simply have some more free space for future inserts to use. If you don't set PCTUSED correctly, or if your deletes have been scattered across a table instead of concentrated in one bit of it, it's true that no future insert might be able to make use of that 3K of space (if you've removed one row from dozens of blocks, for example, instead of removing dozens of rows from one block, then chances of re-using that free space by performing new inserts are probably slim). But even then, free space in a block, however small, can be used by other rows in that block when they are updated and have to grow. When you change someone's name from 'TIM' to 'TIMOTHY PONSONBY-SMYTHE' for example, his row will need to grow. The extra space your blocks have as the result of earlier scattered deletions might allow such row growth without having to induce row migration.

So: dirty great holes in the middle of an extent are not an issue provided future inserts will take place, because those future inserts will make use of those large holes in preference to acquiring new blocks or new extents. Scattered little bits of free space might not be used by future inserts, but might still be used for future updates.

Where a table has been subject to lots of scattered deletes, and no future inserts or updates are intended, it might be time to alter table X move, because that will re-pack things tightly into fewer blocks.

In general, the word "fragmentation" needs to be qualified carefully. It is usually used in an Oracle context to mean "tablespace fragmentation", and that is what happens when you have chunks of free space of odd sizes that none of the other segments can make use of. That can only happen if (a) you allow segments to acquire differently-sized extents from each other, and (b) if you drop or truncate a table. If you do (a) but never do (b), tablespace fragmentation, even in DMT, cannot happen. In any event, tablespace fragmentation is a waste of space issue. It is never a performance issue.

"Little bits of free space inside an extent" I suppose could be called fragmentation, but rarely is (because the word is invariably taken to mean tablespace fragmentation). It can be a performance issue, because it means a table is spread out over more blocks than it need be, and therefore full table scans can take longer than they should. That's where you have to start computing your average row length multiplied by the number of rows, seeing how many blocks that ideally should be stored in and comparing that with how many blocks it's actually stored in. When there is a huge discrepancy, some sort of table re-organisation may be in order (MOVE being the simplest). I say 'may be' not 'is' because of the issue of updates: a tightly-repacked table that then experiences updates is likely to suffer row migration issues. You have to weigh things up a bit before plunging in.

I tend to call this sort of thing 'High Water Mark Inflation Syndrome', but that's just my name for it.

> also, what happens now if a segment is asking for 700K? 1MB extent
> will be given as discussed earlier. but is this 1MB extent a single
> 1MB extent or 2 500K extents?

In dictionary managed tablespace with a MINIMUM EXTENT 500K clause, it will be a single 1MB extent. In locally managed tablespace, with a UNIFORM SIZE of 500K, the same request for an initial extent would give 2 500K extents. If the segment were asking for 700K as a NEXT extent, then in DMT, you'll get another 1MB single extent. In locally managed tablespace, you'd get a single 500K extent (size attributes for NEXT extent requests are totally ignored in LMT).

But with your DMT, it will always be single extent allocations.

> finally, u guys are right.. its 1.5TB, i made a typo error, 1.5GB is
> not huge at all.. since our personal computer hardisk can easily hit
> over that size.

Thought so!

Regards
HJR
>
> cheers
>
>
>
> > > also, a 1MB extent is given because the new segment is asking for a
> > > 612K, whereas the minimum extent size is 500K, therefore it is not
> > > sufficient and hence 2 is needed or 1 extent with a size of 1MB? .
> > > which means to say that the extent size are not uniform which is
> > > fragmentation
> > > >
> > > > You can retrofit a DMT tablespace with a MINIIMUM EXTENT clause
(alter
> > > > tablespace X minimum extent Y). But that doesn't do anything for the
> > extents
> > > > within the tablespace that have already been allocated, of course.
> > > >
> > >
> > > here, u were saying that the above sql command is not valid for those
> > > extents that have already been allocated?
> >
> > It's not "not valid". It's a perfectly legitimate bit of SQL, that will
be
> > accepted at any time. But it won't cause the tablespace to re-size those
> > extents which have already been allocated. They will still have their
> > original sizes, even if they are extremely weird and have not the
slightest
> > resemblance to a new MINIMUM EXTENT clause.
> >
> > Regards
> > HJR
> >
> >
> >
> > > > Regards
> > > > HJR
Received on Mon May 31 2004 - 00:19:31 CDT