"Howard J. Rogers" wrote:
> "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
Any chance you, or Jonathan, or someone else would care to put together a quick
demo or tutorial on how to identify contention. What I mean by this is put
together a demo that forcibly creates contention and then show a correction
eliminating it?
Daniel Morgan
Received on Sat Jan 18 2003 - 11:31:17 CST
