Re: Locally Managed Tablespaces ... again!!!

"Joel Garry" <joel-garry_at_home.com> wrote in message news:91884734.0301171701.30b0aeda_at_posting.google.com...
> "Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message
news:<ZfKV9.26114$jM5.68590_at_newsfeeds.bigpond.com>...
> > "Joel Garry" <joel-garry_at_home.com> wrote in message
> > > This is where I think you must have been driven nuts by this debate.
> >
> > I certainly have been. I wouldn't mind if it was just me saying this.
Being
> > a nonentity from Sydney, I'd expect a vigorous debate. But Tom Kyte has
said
> > it. Jonathan Lewis has. Steve Adams has. Not in quite so black-and-white
a
> > way as me, perhaps. But they've agreed with the general thrust of the
> > proposition. Splitting tables and indexes into different tablespaces is
one
> > of those 'rules of thumb' that tom hates so viscerally, and has about as
> > much validity as several other 'rules of thumb' that have done the
rounds in
> > the past. Which is about zero.
>
> This is probably where I think you are losing out to those other guys.
> They invariably qualify the statement so it doesn't fall apart under
> times when the rule of thumb comes up with a correct answer, even if
> by incorrect means.

I teach. They make a living. I have to get a message across to students in the fewest number of words before they all fall asleep.

In this case, my generalisation is correct, however. Intrinsically, tables and indexes don't contend any more than a table and another table. Therefore, generally, there will not be contention between them. And therefore, generally, there can be no performance benefits from splitting them into separate tablespaces.

Have I said contention between tables and indexes *can* arise? Yup. Have I said that I would probably split them anyway for management convenience reasons? Yup.

So what's the general truth I'm trying to get across here? That indexes and tables don't intrinsically (ie, generally) contend. So what's the correct (ie, generally truthful) rule of thumb? Don't bother splitting them to get extra performance.

> I'll concede there is no inherent contention, but not none whatsoever
> for all situations, nor for predictable, normal situations such as the
> next paragraph.

I couldn't work out what next paragraph you meant. But if you concede there's no inherent contention, then anything else is a special case.

>This is what makes a rule of thumb that can be
> correctly argued to be incorrect for _most_ tables and their indexes,
> useful.

The point is, it isn't true for practically *any* tables and their indexes. But a specific application, or a specific pattern of user activity, could make for a special case.

Point is, in that situation, you're dealing with a specific matter of contention between two segments. The fact that one is an index and one is a table is merely incidental to the real issue: contention between segments.

> So the question becomes, controlling for the variable of raid or multi
> data files, _can_ an index ts help - I say, yes.

Well, this is just another example of 'if all else fails, keep asserting something until someone can't be bothered responding.'

Explain *why* it would help. The physics is against you. And you've just conceded there's no inherent contention, so how a separate index tablespace can help a problem you admit doesn't exist, I've no idea.

>
> You seem to be saying the separation cannot help, and I disagree.

If you choose to interpret someone's words, go easy and be careful. I am saying that separation cannot resolve a contention issue that intrinsically doesn't arise. Of course separation of segments that contend is a good thing to do. But that is just as likely to be two tables as it is a table and its index.

> Just because there is serialization doesn't mean everything is
> serialized in a manner that completely rules out separation helping.

Er, and to what are you referring then. As I say, show me *how* contention can arise between a table and its index. The reads are serialized. The writes are random, in the sense that DBWR is just as likely to flush out two table buffers as a table buffer and its related index buffer. There's no contention anywhere I can see. What's in the 'everything' pot of goodies you're thinking about?

> >
> > I believe it. However, it only makes sense to do so when each datafile
is on
> > its own hard disk. And that's a bit of a tall order for 1000 tables.
>
> Well, that would depend on how I is RAID, eh? :-)

If you're RAIDing, there's even less need to separate tables from their indexes into separate tablespaces for *performance* reasons. Hell, for performance reasons, you could probably make do with just two tablespaces -SYSTEM and EVERYTHING_ELSE. Ease-of-Management issues are another story, of course.

[snip]
> >
> > I totally disagree. If Mary in HR is hammering her payroll application
to
> > death, and I'm processing month-end client accounts at the same time,
then
> > table HR and table SALES are almost inevitably going to contend. Think
up
> > any scenario you want: you *can't* say that because two tables are
> > unrelated, they will not contend or will be intrinsically "unlikely" to
> > contend. You need to know your application(s) and user activity patterns
> > before you can make such a statement with any confidence.
>
> Ah, but when you are first installing the system and don't have any
> activity patterns, you have to make assumptions.

Hopefully, intelligent guesses, not assumptions. And the last guess I'd make is that I would get a contention issue arising on indexes and their tables.

> > I think Nuno said it best: deal with contention, not with indexes and
> > tables. I wholeheartly agree with him.
>
> I agree too. But I'm not going to spend a lot of time justifying
> separation of a critical table and index that I know are going to be
> pounded on. Unless someone pays me to.

At the end of the day, no-one is going to take a blind bit of notice of any of this stuff anyway, because Oracle itself is still peddling the 'separate for better performance' tripe. I don't mind that, and as I've repeatedly said, I still tend to separate indexes and tables because I want different backup schedules, and other management conveniences. So I don't particularly care one way or another, because the end result is going to look much the same regardless of why you do it.

But I would like to think that people can learn the *real* reason why they do something like index/table splits.

Partly for reasons of intellectual purity: people shouldn't believe things which are demonstrably untrue. But partly because the myth conceals a much bigger issue which it's important for any DBA to understand: contention, wherever it arises, and between whatever it arises, is the real thing to be watchful of. The myth would have you believe you've dealt with the issue before it arises, because somehow there's a special risk of contention between and index and its table. And that's profoundly misleading, and a recipe for complacency.

And *that's* why I'll still keep peddling the more generally valid truth on this particular issue.

Regards
HJR Received on Fri Jan 17 2003 - 19:35:10 CST