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

"Joel Garry" <joel-garry_at_home.com> wrote in message
> > > 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.

You didn't read the rest of the post at this point, did you? Teaching means 'come up with something which is correct 90% of the time with 10% of the words'. It doesn't mean I generalise to the point of abstraction, or to the point of woeful inadequacy.

The point of a generalization is that it true more times than not, and for reasons which apply more times than not. The gurus will add their provisos, so that you can work out whether or not you fall into the "90%" catageory (or whatever percentage you will allow). But, under no circumstances, does offering a generalisation mean "I'll tell you porkie pies, so you can switch off the brain and buy into instant solutions".

And I have to say that this is the worst possible secret passage and 'get out of jail free card' I've seen, and I've seen it repeatedly: if you can't win you argument on physics, principle, or any other grounds, just accuse Howard of being a theorist with no practical application. Never mind that there's a half-dozen Oracle databases in Sydney that work because I make them work. Never mind that I've DBA'd for three years for one of Australia's largest corporations. Or spent 7 years DBAing for a fairly significant company in the UK. Never mind that anything I say will be jumped on by the likes of Steve, Jonathan, Thomas, Anjo, Cary or whoever and tested to destruction, in private, before I post it here. No, I'm just a theorist, so I can be dismissed.

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

And I notice you don't argue with my assertion that my generalization is correct.

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

Er, actually, they're not. If something is intrinsic, then it means that it is generally true and you'll have to put up a special case as to why frogs don't croak, insects don't have 6 legs, or gravity doesn't always pull to the centre of the body of mass. Or why indexes suddenly start to contend with their tables.

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

Uh huh. I honestly can't work out what you're saying here. Are you suggestng that index/table contention is equivalent to some loony running a red light? I absolutely, 100%, agree. It's pretty rare, but it does happen.

Should we all be carefull to minotr contention to discover whether its a table and its index? Absolutely, 100%, couldn't agree more.

So what's your point?

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

Obviously, therefore, I'm an idiot. Because I couldn't work out to what you were referring. But don't let that stop you making your point.

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

I agree.

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

No, you're wrong. This is not a case of me just re-asserting dogma. This is me advancing an argument based on principle, physics, testable results, and the thoughts of a dozen gurus who are a darn'd sight better DBAs than me. There are previous posts out there which demonstrate the matter, for example.

Plus, I believe the burden of proof to be on the other foot: You have to come up with a practical explanation as to why indexes and tables especially contend. I don't: to me, they're just segments.

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

Presumably, that's 'dbwR'. And I have no idea what the above is meant to prove or demonstrate. I think you're saying that you have a system with a few tables, but lots of users. I could have said it in 10 words, but you chose to say it in 96. I can't see what specific point you're making that suggest inherent index/table contention.

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

Come off it. You are saying that a table has an index, and that magically this should mean I just keel over and declare that, obviously, there is some special relationship between the table and the index that inherently means there will be contention. That argument is total bollocks, as you well know... At least, if you believe that some special contention exists, you need to do more than just *assert* that exists.

It's an a priori argument, and as such falls flat on its big, fat, arse.

The real argument is: you have two segments. Both are read independently. Both are written independently. Why should I suddenly believe that an I/O on one presupposes an I/O on the other?

You wouldn't claim that if you were talking about EMP and DEPT, depsite the close relationship between those two table. Yet, magically, and without supporting evidence, I am supposed to accept that there is a mystical relationship between the two segments. So: easy. Prove it.

In theory would be nice to start with. And then in SQL for practical purposes. Anything you knock up, I'll be happy to come up with a counter-suggestion.

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

In the great scheme of things, it is more likely that you will update EMP and PK_EMP than EMP and SALES. Undoubtedly. So, every time you update a couple of segments, there is a chance of I/O contention. Yes? Fine. Now explain to me why the table and its index should be *inherently* more prone to such contention than TABLEA and TABLEB.

Bear in mind that in a lot of cases, the only index on a table will be its primary key. Now tell me how often you update the primary key of a table, necessitating an update of both the index and its table. And then explain to me how realistic that is if the thing's been properly denormalized and conforms to relational theory in the first place. And then, even if you agree that you will be updating the primary key, explain how something which is serialised for the read suddenly induces a special amount of contention on the write.

Suddenly, I guarantee, you will be saying that "well, a table update implies an index update, whereas an EMP update does NOT imply a SALES update". And then I'll show you an application that *does* imply that if table A is updated, so must table B be. And at that point, you have no more inherent contention for a table and an index than for a table and a related table.

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

Doesn't pass the 'I hadn't though of that' test. You're just talking about writes to segments. Serialized, and then compounded by multiple users and high concurrency. Nope. Doesn't pass the test: what's the *particular* mechanism you are proposing which explains why a table specifically and inherently contends more with its index in such circumstances than with any other table. Or cluster. Or rollback segment. Or, to put it bluntly, with any other segment.

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

Uh huh. Doesn't sound like you agree, shall we say. Which means you've missed my point.

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

Nope. I would rather someone realised that table/index contention is NOT a "likely point of contention". So then they could concentrate on something else which is more a problem. You seem to suggest that I'd be happy with someone dealing with a "known likely point of contention" rather than understanding the nuts and bolts of the matter. Absolutely not. Not, I should say, if you mean "dealing with myths and fairy tales as if they were possible or likely sources of contention".

I trust that every one of my students knows (1) not to rebuild indexes very often and (2) doesn't feel a compelling need for performance reasons to house indexes in their own tablespace until (3) they've proved that its the table and the index that are contending for I/O. Beyond that, it's down to understanding of the RDBMS, and subtle fine-tuning. Nice if they can get that far. But not compulsory.

> I could live quite happily with that,

I'm sure you could. Whilst you're out ticking off "index/table contention ... tick", I'd be getting on dealing with the real contention issues. That's not what I'd like from my DBAs. I'd like an intelligent approach that says "where is contention going to arise. And what do we do to resolve it". And I don't want them thinking at the back of their brains "check for indexes and table contention to get a quick fix. Because it just isn't an issue.

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

Uh huh. Sarcasm wins no particular brownie points, and doesn't in the least dissuade me from posting, and re-posting, the facts of the matter.

Ultimately, Joel, as I've said: I couldn't give a monkey's. You want to go round asserting X is Y when actually it's Z: I don't care. I know the facts of the matter, and I don't expect you to hop aboard. So I don't mind. But please try and hold off the personal abuse, the 'he's only a trainer" crap (which isn't true in any case), and the "if I rephrase things often enough, people will miss the point" stuff.

Just post WHY indexes should *especially* contend with their tables, as opposed to one segment contending with another segment. Then I'll shut up.

HJR Received on Tue Jan 21 2003 - 01:08:17 CST