Re: How to "Defragment" Tablespaces ?

Ricky,

> Yes, it is true that placing high IO objects on the same drive will increase
> contention. We certainly agree on that. I think Osvaldo knew that too, which is why
> he split up his vehicles over two highways instead of one. And yes, the vehicles were
> different. One group was yellow, the other blue. Rosa's idea. Sort of like tables and
> indexes. Some are called tables, the rest are called indexes. And yes, they each have
> variations in how they are accessed. Sometimes as individual, random (sort of) block
> accesses, other times as multiblock accesses. But, from the standpoint of
> performance, they both incur IO. And since IO is the actual bottleneck, IO is what we
> need to measure and address. Load balancing, not feathers and crystals.

True, they both incur I/O, but as I already pointed out, not all I/O is equal.... Surely you'll agree that there are multiple components to I/O. Three major factors contribute to the amount of time it takes to read data from disk. They are "seek time", "rotational delay", and "read time". Of those three components, "seek time" takes by far the longest amount of time (more than rotational delay and read time combined.

And I should also point out that I agree with you on load balancing the I/O.  

> Let's imagine that it is a single fat drive, just to simplify things. Further, the
> indexes and tables all have the same extent sizes, which is how they got in that
> tablespace to begin with.

What does having the same extent sizes have to do with the reason why indexes and tables got in the same tablespace to begin with? Their extent sizes have nothing to do with *why* they are in that tablespace.

> "Yes", I would reply, "but it simply does not matter which you move. The correct
> method is to base your decision on actual IO incurred, since IO is what you are
> balancing." And that is the crux of the matter.

So you have your points on load balancing, but there is still something missing. For this next example, I'm going to assume that accessing the data from the table is done with an index. No full table scans and no Fast Full Scans on the index. I'm also going to assume that the blocks on disk are not yet in the buffer cache. They have been aged out. Otherwise, this whole exercise is rather moot.

In order to satisfy the query, I have to read the index, read the table, read the index, read the table, etc. By going back and forth, this involves the dreaded seek time. It is compounded even more when I have multiple users accessing the same objects (but not necessarily the same blocks). If I separate the index from the table, then User1 can read the index. While User1 is reading the table, then User2 can be reading the index. When User2 goes to read the table, then User1 can be reading the index again. If both the table and index are on the same disk, then they will both contend with one another. If you separate the index from the table, then their contention for the same resource (access to the data) is lessened.

In your case about load balancing, you never take this into account. This is because you only look about how often an object is incurring I/O, not how often two object incur I/O *at the same time*! In your case, you are only separating on the basis of the total I/O operations for the object. So let's say that I have table_a, table_b (neither of which have indexes) and table_c with index_c. Now, table_a incurs 100,000 read/write operations per day. And table_b incurs 100,000 read/write operations per day (or whatever time unit you want. it doesn't matter). Table_c incurs only 50,000 read/write operations per day and index_c incurs ony 50,000 read/write operations per day. If I spread these objects over 3 disk volumes (according to your method), then I will have table_a on one spindle (a total of 100k r/w), table_b on another spindle (a total of 100k r/w) and table_c with index_c on the last spindle (a total of 100k r/w). All is balanced by I/O, is it not? Don't you see the bottleneck here? I will get better throughput by placing the index on a separate drive from the table even if that "overloads" one of the spindles a little. This case can easily occur because table_a and table_b are summary tables where I look up all the information on an occasional basis. But table_c (with index_c) is a lookup table which I access often. This case does occur quite frequently.

Your method of balancing solely on I/O also is greatly predicated on the fact that each object is accessed independantly of each other. While tables can be independent of each other, there is a great dependence between the index and it's associated table. Unless you are doing a full table scan or fast full scans on the index, then reading the table will also read the index. They are accessed *together*. Since they are accessed together, it makes sense to separate them from each other.

And I have to cast your mind back... You originally said "Never worry about indexes and tables having to be on separate drives." I went back on Deja to get this quote from your original post into this thread. This is what started the whole thing in the first place. "NEVER"? That's a pretty strong statement to make.

It seems to me that you had a good idea some time ago about I/O being the driving factor between load balancing, not object type driving load balancing. But this in and of itself is not set up correctly to debunk a "myth". I'm not saying that indexes and tables cannot peacefully coexist on the same drive. In fact, I do it on almost every one of my systems. I have indexes and tables on the same spindle all the time! But what I don't do is put a table's index on the same spindle (unless I have no other choice). For instance, I put table_a on disk1. I put table_b on disk2. I put index_a (for table_a) on disk2 and index_b on disk1.

So there is my case. Lump it in with the rest! Brian  

-- 
========================================
Brian Peasland
Raytheons Systems at
  USGS EROS Data Center
These opinions are my own and do not
necessarily reflect the opinions of my 
company!
========================================