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

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<A12W9.26822$jM5.69937_at_newsfeeds.bigpond.com>...
> "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.

Whoa, all of a sudden sounds like you need to rationalize rules-of-thumb, incorrect over-generalizations, and all that other stuff you've been railing against. I wouldn't want to teach, I have no patience for it. I have nothing but respect and admiration for those who do what I won't or can't or don't want to.

But you must remember, this is not strictly a teaching environment, this is usenet. If you are going to advise people in production environments, that is fine, be prepared to answer for it. Accept that sometimes your view is bound to be ivory-tower.

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

Well, intrinsically and generally are two very different things. People don't generally run red lights, that doesn't mean you should not scan your eyes around when you have a green light. That special case is catastrophic, therefore needs to be dealt with as a mode case.

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

It was the next one in that posting, including quoting.

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

Yes, that is true, but it does not mean that the specific contention of two segments that _will be accessed serially within a transaction as well as contemporaneously between users_ is the same as two segments that might sometimes be accessed contemporaneously.

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

Back attcha.

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

I don't admit the problem doesn't exist. Here's an example:

You've got 100 people pounding in orders to an order table, another person or 3 scanning and updating the table to dispatch product, and another person or 6 scanning the table to to invoice the products, and another person or 10 scanning the table to generate some reports that must be up-to-the-minute, and another person accessing EMP as SCOTT, and 400 other people doing other random things on the server that may or may not access the order table. You do not have resources to do a whole lot of modification on the packaged order system.

OK, maybe dbw is magic and the disks are just sitting there not doing a whole lot. Riiiiight.

If you don't see that it is predictable that the order table needs indices as defined by the vendor and that there will be some contention there, I can't help you.

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

But if you know your application pounds on a table and its' associated indices, that's not just as likely as that table and SCOTT.EMP. It's intrinsic because the application makes it so.

>
> > 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?

A (write to the table followed by a write to the index)* 100 people at the same time.

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

I'm pretty convinced raid simply makes the issue a little murkier.

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

I totally agree with this. The big problem is how to convince people who depend on exp/imp for backups that there is a management convenience :-O

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

So you would be ok with someone not believing they've dealt with it, but rather simply have dealt with a known likely point of contention understanding they may have to look more closely in the future for other actual contention? I could live quite happily with that, that's how I generally do it. Of course, I take all vendor advice about what will contend with a grain of salt anyways, and am highly entertained when they are demonstrably wrong.

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

Let's all have a beer and toast HJR!

>
> Regards
> HJR
jg

--
@home is bogus.
The best things in life are free, except skiing and everything else.