Q:Table Fragmentation. How do I reduce it? 
Author Message
 Q:Table Fragmentation. How do I reduce it?


  Jan quoted:
From "Advanced Oracle Tuning and Administration" :
"Extents are often blamed for performance problems; however, their impact on
performance is minimal and can be completely avoided. In fact, careful use of
extents can improve your response time by distributing your I/O operations
across multiple devices...".
 <<

I just want to make sure that newer DBA's understand that there is a huge
difference between a table in 10 extents on one disk and a table in 10 extents
accross 10 disks.  An out of context quote is a dangerous thing when
distinctions like this are not understood.  Also as previously explained well
on this thread, extents size matters in determining  if multiple extents
adverserly affect performance.  Size them correctly and they will ususally not
be a performance problem so 10 extents on one disk may or may not be a problem
depending on how and why the table got to be in 10 extents.  (Ten is actually a
fairly small number, but it works well for the point I am trying to make)

Mark Powell  -- Oracle 7 Certified DBA
- The only advice that counts is the advice that you follow so follow your own
advice -



Mon, 04 Sep 2000 03:00:00 GMT
 Q:Table Fragmentation. How do I reduce it?


Quote:



>>>Hi Connor,
>>>why fragmentation does not affect performance? Everybody, every book claims that fragmantation is a performance penalty.

>>Not the good DBA's and not the good books.
>Are Oracle Press' books the bad ones?

>>From "Advanced Oracle Tuning and Administration" :

This one IS from Oracle Press!

Jan



Mon, 04 Sep 2000 03:00:00 GMT
 Q:Table Fragmentation. How do I reduce it?


Quote:

>  Jan quoted:
>From "Advanced Oracle Tuning and Administration" :
>"Extents are often blamed for performance problems; however, their impact on
>performance is minimal and can be completely avoided. In fact, careful use of
>extents can improve your response time by distributing your I/O operations
>across multiple devices...".
> <<

>I just want to make sure that newer DBA's understand that there is a huge
>difference between a table in 10 extents on one disk and a table in 10 extents
>accross 10 disks.  An out of context quote is a dangerous thing when
>distinctions like this are not understood.  Also as previously explained well

It is difficult to quote the whole book. Besides, it is NOT an out of
context quote.
You want something else?

OK

From "Avoiding a Database Reorganization" by Craig A. Shallahamer
(Oracle System Performance Group) :

... (yes, I'm only quoting part of it)
Segment Fragmentation

- In a production system, the net effect of possible (yet improbable)
additional I/O's is negligible.

From "Oracle Server Space Management" by Cary Millsap

Segment Fragmentation :
... "Presumed performance penalties of having more than one extent per
segment has generated considerable debate among Oracle administrators.
Many people believe that Oracle Server performance degrades
appreciably as the number of extents associated with a segment
increases. Many authors speculate that having more than one extent
bears a heavy recursive SQL cost. Others cite that costly mechanical
disk drive motion is required to scan a table that is not stored in
one contiguous hunk. Others cite an alleged devastating impact that
multiple extents have on the efficient Oracle Server multi-block read
capability"..... Of course, Cary goes on to prove these people wrong.

Now if you still want to stay with the old myth that it is not good
having multiple extents (you were probably a DBA in Oracle 6?) fine by
me.

Jan
(Ex Oracle Advanced and New Technologies)



Mon, 04 Sep 2000 03:00:00 GMT
 Q:Table Fragmentation. How do I reduce it?

Maybe I shouldn't play around with the beginners books 'DBA Handbook' and 'Tuning Oracle'.

Quote:




>>>>Hi Connor,
>>>>why fragmentation does not affect performance? Everybody, every book claims that fragmantation is a performance penalty.

>>>Not the good DBA's and not the good books.
>>Are Oracle Press' books the bad ones?

>>>From "Advanced Oracle Tuning and Administration" :

>This one IS from Oracle Press!

>Jan

--

Regards

Matthias Gresz    :-)




Tue, 05 Sep 2000 03:00:00 GMT
 Q:Table Fragmentation. How do I reduce it?

Continuing the general discussion on table and index extents and do they
matter:
For those DBA's who believe that table extents do not matter then I suggest
you see the following two books and duplicate the test described in the
second reference:

1) Oracle & Unix Performance Tuning by Ahmed Alomari, Prentice Hall, 1997.
Mr. Alomari has the title of Sr. Performance Engineer with Oracle Corp.

2) Advanced Oracle Tuning and Administration, Oracle Press.  I do not have
the book handy to list the authors, but Loney is one of them, and this book
gives a clear repeatable example of how extents and their sizing can and
does affect performance.

On a small table it is difficult to notice the effect of multiple extents,
but as tables grow toward the gigabyte range the effect becomes much more
pronounced.  Poor extent sizing can really hurt.  None of these books
mentioned explain the use of the extent list contained in the table header
block and sequential scans.  I would like to know if it has to be accessed
once for each and every extent or just once for the entire list.  This
would require latching the block.  A small price, but since this block
would be in the buffer pool I imagine it would affect the LRU.   Ver 7.3+
allows multiple LRU latches but prior versions have only one and our system
is constrained during peak periods.



Sat, 09 Sep 2000 03:00:00 GMT
 Q:Table Fragmentation. How do I reduce it?

There are several ways to do this.  I have researched this.

Best way to achieve COALESCE of the contiguous fragmented space is to set
pctincrease =1 for tablespace where the indexes or tables are maintained.
If you do
this  Oracle SMON process will automatically defragment the space after
deletions/inserts as it wakes up.

Only problem is you will have to use Alter tablespace command and it will
COALESCE only the newer segments.

To achieve COALESCE for all disk space you will have to drop and recreate
tables and indexes.  It took SMON several minutes,
as much as 15 to 20 minutes to COALESCE approximately 100 MEG of disk
space.

Good Luck !!!



Quote:

> > Hi all

> > Setup: Oracle 7.3.2.3 on HP-UX 10.20

> > I have a table(s) with lot of extents allocated to it. These extents
are
> > small extents. The extents allocated are not all next to each other.
> > Some are but not all. If I drop the table and recreate it, do I get a
> > lot of "free space fragmentation"?  How do I reduce the fragmentation
of
> > the table? Do I have to export and import the whole tablespace?? ( I
have
> > many of these tables with lot of extents allocated to them in the
tablespace)

> > Thanx in ADvance

> > Rao Uppuluri

> Also worthy of note that in a normal (ie multi-user) environment then as
> a general rule, lots of extents will NOT affect performance.  The
> popular belief that the performance of one extent is always better than
> many is a myth...

> This is not to say the exp/imp will not assist.  Unloading and reloading
> the data has many benefits in terms of better packing the rows etc
> etc...But exp compress=y OR exp compress=n will give you the same
> benefits...
> --
> ==========================================
> Connor McDonald
> BHP Information Technology
> Perth, Western Australia
> "These views mine not BHP..etc etc"

> "The only difference between me and a madman is that I am not mad."



Sat, 09 Sep 2000 03:00:00 GMT
 Q:Table Fragmentation. How do I reduce it?



Quote:
>Continuing the general discussion on table and index extents and do they
>matter:
>For those DBA's who believe that table extents do not matter then I suggest
>you see the following two books and duplicate the test described in the
>second reference:

>1) Oracle & Unix Performance Tuning by Ahmed Alomari, Prentice Hall, 1997.
>Mr. Alomari has the title of Sr. Performance Engineer with Oracle Corp.

>2) Advanced Oracle Tuning and Administration, Oracle Press.  I do not have
>the book handy to list the authors, but Loney is one of them, and this book
>gives a clear repeatable example of how extents and their sizing can and
>does affect performance.

>On a small table it is difficult to notice the effect of multiple extents,
>but as tables grow toward the gigabyte range the effect becomes much more
>pronounced.  Poor extent sizing can really hurt.  None of these books

....

Don't mix the # of extents and their sizing. The (excellent) reference
you give proves this. The example you mention can be found on page 89.

This were the conclusions :

Quote
1. If the extents are properly sized, the number of extents has no
impact on the number of reads required by table scans.
2. If the extents are not properly sized, the number and size of the
extents can greatly increase the amount of work performed by the
database during a full table scan.
Unquote

So, once again : having several well-sized extents does NOT harm
performance significantly. But the size does indeed matter a lot.

Jan



Sun, 10 Sep 2000 03:00:00 GMT
 Q:Table Fragmentation. How do I reduce it?


Quote:



>>Continuing the general discussion on table and index extents and do they
>>matter:
>>For those DBA's who believe that table extents do not matter then I suggest
>>you see the following two books and duplicate the test described in the
>>second reference:

>>1) Oracle & Unix Performance Tuning by Ahmed Alomari, Prentice Hall, 1997.
>>Mr. Alomari has the title of Sr. Performance Engineer with Oracle Corp.

>>2) Advanced Oracle Tuning and Administration, Oracle Press.  I do not have
>>the book handy to list the authors, but Loney is one of them, and this book
>>gives a clear repeatable example of how extents and their sizing can and
>>does affect performance.

>>On a small table it is difficult to notice the effect of multiple extents,
>>but as tables grow toward the gigabyte range the effect becomes much more
>>pronounced.  Poor extent sizing can really hurt.  None of these books

>....

>Don't mix the # of extents and their sizing. The (excellent) reference
>you give proves this. The example you mention can be found on page 89.

>This were the conclusions :

>Quote
>1. If the extents are properly sized, the number of extents has no
>impact on the number of reads required by table scans.
>2. If the extents are not properly sized, the number and size of the
>extents can greatly increase the amount of work performed by the
>database during a full table scan.
>Unquote

>So, once again : having several well-sized extents does NOT harm
>performance significantly. But the size does indeed matter a lot.

And of course, page 90:  "...forcing each object in your database to have just
one extent will yield little in the way of performance improvements, and may
actually hurt your performance."  Then mentions you can't stripe, can't
effectively use Parallel Query, and can't determine growth rate.

If the extent size is a multiple of the multiblock read, "...there is no
performance penalty."

Quote:

>Jan

--
These opinions are my own and not necessarily those of Information Quest

http://ourworld.compuserve.com/homepages/joel_garry



Tue, 12 Sep 2000 03:00:00 GMT
 
 [ 23 post ]  Go to page: [1] [2]

 Relevant Pages 

 
Powered by phpBB® Forum Software