Re: Are multiple extents still a problem with LMT?

On Apr 4, 9:02 am, Brian Peasland <d..._at_nospam.peasland.net> wrote:
> Andy Kent wrote:
> >> I can't understand why 'extent fragmentation' is a problem as you do not
> >> have any odd-sized extrents which cannot be reused by other objects
>
> > You seem to be misunderstanding how extent allocation works.
>
> Extent allocation has very little to do with performance of your
> application. This has been proven time and time again.
>
> > If two
>
> > tables are grabbing extents alternately then their extents will be
> > interleaved and there is no way you can avoid having a whole load of
> > extents of the default size for the table or tablespace. Even if
> > extents are contiguous 8i doesn't automatically figure that out. You
> > can reduce the problem with COALESCE but that will make no difference
> > where interleaving has happened.
>
> I'm not sure why this is a problem. See more on that below...
>
> > Having too many extents creates two distinct problems:
> > 1. Access to the dictionary increases with the number of extents and
> > the dictionary itself may fragment if the number is large. This can be
> > a considerable overhead (especially in sub-optimal situations where
> > the system and data tablespaces are on the same disk).
>
> Are you assuming that Oracle consults the Data Dictionary to read each
> and every extent? This is not normally the case. Most access to the data
> is done by a ROWID value which contains the file and block within that
> file. When reading data from a table, Oracle does not read the Data
> Dictionary, read a block from the table, go back and read the Data
> Dictionary for the next block location, repeat.

I'm wondering whether you guys have forgotten the kinkiness of DMT's. He doesn't have to be assuming that Oracle consults the Data Dictionary to read each and every extent. What you may run into is extreme performance degradation affecting everybody when a huge dictionary table is accessed because someone has to add an extent, partly from locking and partly from spinning cpu from all the latching taking place (depending...). And this happens often because the nexts are too small.

>
> > 2. Large queries and especially full scans are compromised because the
> > data blocks do not occupy contiguous space so seek time is increased.
> > However where datafiles use filesystem space the filesystem is likely
> > to add its own fragmentation overhead so making everything [logically]
> > contiguous within a datafile may be a lost cause.
>
> This type of thinking is not correct. Let's assume that you have a 1GB
> table and you are reading all data in that table. What are the chances
> that you'll be able to actually read all 1GB of that data without
> interruption? Remember that you only have one disk unit at play here.
> Other user's I/O requests are interleaved with your I/O request. And
> even if you are the only user in the database, there are still requests
> for access to the control files, temp space, undo, etc. If the user
> could guarantee that their I/O request would be satisfied without
> interruption from start to finish, then having the data physically
> contiguous on disk could speed things up. But that is very, very rare in
> today's environments.

But even in todays environments, certain deletion patterns can swisscheese  certain objects. We're talking O8i DMT here. Who knows what strangeness lurks after a number of years of no maintenance with unknown application idiosyncracy? He might have 2 rows in blocks that should have 50. He might have chaining. His app may be the special one that actually requires indices to be rebuilt. We must be careful not to use arguments that are meant to debunk regular defragmentation or index rebuilds to refute a long-term lack of maintenance issue.

I would agree with the assertion that it might be worth Andy's while to find out what the real performance problem is before attacking it, though. On the other hand, I have seen situations like the one described where just upgrading and rebuilding everything is quicker than evaluating what is wrong, and happens to mostly work. And makes everyone think you are a genius.

>
> > Now, I know that moving from DMT to LMT sorts out 1. What I'm trying
> > to establish is whether table rebuild should be added to the equation
> > in order to mitigate 2.
>
> As has been said many times in these (and other) forums, defragmenting
> for the sole purpose of performance gains unncessary. Oracle performs
> quite well with many, many extents.
>
> > Rest assured I am also tuning SQL (ANALYZE had never been run!),
> > indexes, SGA and undo/ redo.
>
> I sure hope you aren't running ANALYZE to gather your statistics! If you
> are on a version of Oracle that supports LMT's, then you should be using
> DBMS_STATS for your statistics gathering.

I'm not sure I see any evidence about which optimizer is being used.

And I've become a big fan of auto-allocate because I tend to work on packaged apps with many tables, most of which are small and some I can't predict what will happen (as they suddenly get used when a flag is flipped). Oracle figures out the big ones fast enough.

jg

--
@home.com is bogus.
This too shall pass.
http://www.sciam.com/article.cfm?articleId=5B76E630-E7F2-99DF-3958811DF98CBC37&chanId=sa013&modsrc=most_popular