Re: Q:Table Fragmentation. How do I reduce it?

On 19 Mar 1998 15:23:20 GMT, markp28665_at_aol.com (MarkP28665) wrote:

>From: jan.coekelberghs_at_ping.be (Jan Coekelberghs) >>
> 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) Received on Thu Mar 19 1998 - 00:00:00 CST