Re: How to "Defragment" Tablespaces ?

I disagree majorly with your position It was explained to me this way. I have no proof of it, but it would be one way of implemention.

A tablespace is much like a disk drive. Think of tables as files. Like files tables have extents all through the tablespace. There has to be a diskmap sort of data structure which keeps track of the extents and tables. This is where the contention happens. I had heard that there was a limit of approximately 25 concurrent accesses allowed to this area in 7.x. I do not know about 8.x.

I have changed 8 instances, which were the idiotic dump all the data into one tablespace design. In each case I saw dramatic increases in performance when I moved the hot tables to their own tablespaces. Moving the indexes out to their own tablespace gives a moderate improvment. Before you start yelling about the disks. All of these instances had EMC Symetrics as their disks, which were raid 0 + 1.

Before I became a DBA, I was an operating system internals type. This is how a OS handles its disks. (I don't care which OS. This is true for unix, vms, and win anything.) This is the most logical method of designing Oracle.

Liz

In article <3ACCD61D.3AC87253_at_more.net>, rsanchez_at_more.net says...
> Sybrand, you are being silly. You are asking me to prove the absence of a needle
> in a haystack. The discussion originated around defragmenting tablespaces. I
> breezed off an outline of a method to avoid the need for tablespace
> defragmentation. There is more to the method, but the essentials were presented
> and are sound.
>
> The myth to which I referred is the mistaken notion that tables and their
> indexes must not reside in the same tablespace - or datafile, from the pre-raid
> days. What is missing here is any proof or even reasoning as to why that might
> be or ever have been been necessary.
>
> By the way, you can drink a Pepsi and eat a packet of Pop Rocks at the same time
> and not have your stomach explode, but I cannot really prove that. The only
> reasons DBAs continue to defrag tablespaces are 1) it has always been assumed to
> be necessary and 2) vendors like Quest and CA sell products that defrag
> tablespaces and, like the AMA, have no interest in preventive medicine or sound
> space management practices.
>
> The only plausible argument for tablespace defragging would be to avoid
> situations where odd-sized extents are "stranded" in a tablespace because all
> the objects required non-standard extents. So, you had to defrag to reclaim the
> space*. Using uniform extents eliminates that issue altogether.
>
> IO contention is another matter. I assert that tables and their corresponding
> indexes can co-reside on the same tablespace without special issues of
> contention. Of course, you would expect them to be placed according to extent
> size requirements as described in my original response. Assuming that their
> extents were the same size, and that such size for the tablespace is mapped to
> the IO multiblock capabilities of the underlying operating and storage systems,
> no particular or unique IO contention will exist for them. Certainly not in a
> scaled environment where performance is actually an issue. Consider the
> following:
>
> You are either reading randomly or you are doing a full table scan or a fast
> full scan of the index. In the latter two cases you do multiblock reads and
> accesses for the two objects are then mutually exclusive. That is, you do a full
> scan on either the index or the table, but not both at the same time. In cases
> of single block reads, such as b*tree index navigation and single block table
> (row) fetches, you have random reads on different parts of each object and there
> is no gain by placing them on different devices or tablespaces. The reads are
> random anyway and head positioning is irrelevant - ie you can neither predict
> nor control it. Factor in other users concurrently accessing the same objects
> and the reads even more random.
>
> Now someone will come along and say, "Well, Ricky, fine and well, but what about
> those nested loop joins? Or those range-selects that naive application
> developers use in a pathetic counter-productive attempt to avoid sorts?" Glad
> you asked.
>
> In the case of a nested-loop join, you read an index value and then get the
> corresponding row. The index is read randomly (logically sequential, physically
> random) and the single row is read randomly from the table. Again, no gain from
> separating the objects. You take a big hit for reading the same blocks
> repeatedly as you visit rows in logical index order, but we explode that myth in
> another thread.
>
> So, show me why it is necessary or helpful to defrag a tablespace, or to
> deliberately separate tables and their indexes.
>
> -ricky
>
> * footnote: We also had that extent limit thing before 7.3, so we did the defrag
> drill more to coalesce than to defrag per se. Now we have unlimited extents and
> naturally, fools who actually attempt to have unlimited extents and find that it
> takes three days to truncate a table. But that is solved by locally managed
> tablespaces and is a topic for yet another thread.
>
>
> Sybrand Bakker wrote:
>
> > So far the only thing I see is non-documented assumptions.
> > If you are so sure about this, and even use words like 'DBA myths', why
> > don't you just come up with *Proof*
> >
> > Regards,
> >
> > Sybrand Bakker, Oracle DBA
> >
>
>
Received on Mon Apr 09 2001 - 08:48:29 CDT