Re: How to "Defragment" Tablespaces ?

Brian -

The issue is not about placing indexes and tables together, but whether you ought to necessarily separate them - as tables and indexes - to improve performance. The DBA myth that I am refuting is that the concern is about tables and their indexes rather than objects and their IO.

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.

So, to put it another way, let's suppose you have both indexes and tables in a single tablespace and you know you have a contention problem with that tablespace. 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. To make it a clean hypothesis, we further suppose that there are just as many indexes and tables and they happen to be the same size. What to do?

Well, you followed the mysterious thinking of the modern DBA and placed the indexes in another tablespace, leaving the tables behind. Moreover, you checked to make sure that the indexes belonged to those particular tables, since that is part of the mojo.

Did your performance necessarily get better? Let's suppose that you had measured the IO by object and found that 80 percent of the IO was on the tables. You can easily do that if the objects each have their own files. With uniform extents this is sensible and trivial. You moved all of the indexes, but only moved 20 percent of the IO. Maybe that is good enough and you don't care. But, had you moved objects based on actual IO you might have optimized performance.

"Well", you say, "fine and well, but what if the IO was exactly the same for each file, tables and indexes alike? If you move just the indexes then you will have optimized IO for those (now two) tablespaces."

"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.

This is about methodology and the method is not witchcraft but logic. Pay attention to the IO, not the name of the object. If you want to place large objects on their own files, that might make sense. Now you can measure IO for a particular narrow window of time and compare bottlenecks and respond accordingly. Whatever that file contains.

• ricky

Brian Peasland wrote:

>
> Thanks for proving my point and I didn't need any old uncle Olaf stories
> to help me out...
>
Received on Tue Apr 10 2001 - 12:26:39 CDT