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

"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 don't have a problem with rules of thumb, if they work more than they, um, dis-work. The index/table ts separation rule generally doesn't do anything under modern configurations, but there's usually a few, critical, situations where it does, given a transactional multiuser system. And those justify the rule. I don't have a problem with throwing out rules that are demonstrably false.

>
> > Aside from the special cases of full-table scans and index-only scans,
> > don't you think that there is some contemporaneity between indexes and
> > their tables?
>
> No. None whatsoever in fact. No special degree of contemporaneity that
> arises because one's an index and one's a table, anyway. Of course, if you
> modify the contents of the table, you have to modify the contents of the
> index. And that is an issue: that's why my favourite phrase regarding
> indexes (which they wouldn't let me put in the book because it sounded too
> gloomy!) is: 'if you can manage without, do'. Because every index slows down
> DML. But that's because index maintenance costs something, not because there
> is any inherent contention for the table and its index.

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. This is what makes a rule of thumb that can be correctly argued to be incorrect for _most_ tables and their indexes, useful.

>
> >And in fact, isn't that going to be expected in an
> > online system with multiple users - the serialization of each users
> > table and index on an insert, for example, would be mooted by all the
> > other users inserting at random times? How can you not expect that
> > ahead of time?
> >
>
> True, but does moving the index into its own tablespace help matters?
> Intrinsically, not. Oh, it might because presumably this is a new file on
> yet another hard disk. More spindles must (ordinarily) mean less I/O
> contention. But you could achieve the same reduction by raiding the single
> tablespace. Or by creating the single tablespace out of multiple data files.
>

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

> That's why I keep stressing the word 'inherently' or 'intrinsically'. If the
> physics of table/index access dictate a serialized approach, then separation
> can be no cure, since there's no problem. And that basic rule holds whether
> you've got one insert, or 1000,000. Now, when you have 1,000,000 concurrent
> inserts, there's a *different* problem of simultaneous access to segments.
> But that's a different problem, requiring a different cure. Mere seapration
> of indexes and tables isn't it.

You seem to be saying the separation cannot help, and I disagree. Just because there is serialization doesn't mean everything is serialized in a manner that completely rules out separation helping.

>
> > >
> > > At the end of the day, an index is just a segment like a table is. If
> you
> > > are going to propose that we need to separate one segment away from
> another
> > > because of multi-user contention, then you might as well argue that
> every
> > > table needs to be in a separate tablespace, too. If you update EMP
> whilst I
> >
> > Believe it or not, I saw a system with 1000 tables with an option to
> > do exactly that. In fact there were 3 options: put everything in one
> > ts, put tables in one and indexes in another, or a ts for everything.
> > I thought it was hilarious at the time. I spent some time analysing
> > what was really needed and wrote scripts for it.
>
> 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? :-)

>
> > > update DEPT, and we contend, that's two tables contending for I/O. It's
> > > exactly the same if I'm updating EMP whilst you update EMP_PK_IDX.
> Either
> > > they all need to be separated, or none do. It's a question of analysing
> I/O
> > > contention issues, not "These are indexes so they go there; and these
> are
> > > tables so they go here".
> >
> > What if 500 of us are randomly updating emp and emp_pk_idx? Whether
> > you like it or not, there is a relationship between tables and their
> > indices. Fairly predictable, in fact. Once upon a time, we had to
> > quantify it.
>
> There's no questioning there's a relationship from this end. It's rather the
> *nature* of that relationship that's at issue here. The common misperception
> is that they intrinsically contend for I/O. But they don't.
>
>
> > >
> > > Separation of an index from its table doesn't intrinsically help
> contention
> > > issues at all.
> >
> > It may or may not, but certainly more likely than two unrelated
> > tables.
>
> 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. Unless you just throw everything in one TS with init.ora parameters barely modified to make the thing startup and let the next guy worry about it. Would that be very good administration? I've certainly been the next guy often enough...

>
> I don't even say that a table and its indexes will never contend, either,
> because you might be modifying the index whilst I'm starting a fresh update
> on the table. But that's got nothing to do with the 'realtionship' between
> that index and table, but arises purely out of the fact that in a multi-user
> system you may get simultaneous I/O on different *segments*, whatever the
> nature of those segments.
>
> 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.

>
> Regards
> HJR
jg

--
@home is bogus.
http://www.signonsandiego.com/news/uniontrib/fri/news/news_1n17oracle.html