Re: Separating data, index objects

From: DA Morgan <damorgan_at_psoug.org>
Date: Tue, 05 Jul 2005 20:06:36 -0700
Message-ID: <1120619218.812123@yasure>

Mark A wrote:
> "DA Morgan" <damorgan_at_psoug.org> wrote in message
> news:1120595124.275467_at_yasure...
>

>>** And my comment was that bufferpool hit ratio, etc. are irrlevevant.
>>

>
> Buffer pool hit ratio is relevant because if the data is already in the
> buffer pool, then there is no disk access, hence there can be no disk
> contention.

Which has absolutely nothing to do with using one tablespace, a thousand tablespaces, separating tables from indexes, or putting tables whose names begin with the letter "R" into a tablespace of their own. It may be correct but much like support help from Microsoft ... meaningless.

>>In theory, if the table data and index data are in the same tablespace, 
>>the disk contention could occur if there was a significant amount of I/O
>>against the table and index at the same time.
>>
>>** This is not true. Just not true. And the words above are yours and
>>** unedited.
>>

> In theory it is true.

Rubbish. Demonstrate to me how, in Oracle, it is even theoretically true given the reality of how Oracle works, how operating systems work, and assuming that the Oracle application (because we wish to be realistic here) has multiple simultaneous transactions taking place.

>>But in an OLTP environment like you described, there could be a high
>>transaction rate on the same tables and indexes, where it could make a 
>>difference.
>>
>>** Once again ... this is not true in Oracle.
>>

> What is not true? If you have thousands of users running the same
> application at once, some of them would be accessing the table at the same
> time others are accessing the index. Are you saying this is not
> theoretically possible in Oracle?

They could all be simultaneously accessing the same block at the same time. What is not true is that separating tables and indexes into separate tablespaces would make any difference.

>>** Reread your previous posts above this one. That is not what you said.
>>** "Where it could make a difference" is NOT theoretical. Nowhere do you
>>** mention small to medium ... and while you do mention bufferpool
>>** configuration I've already stated that this is irrelevant to the
>>** topic and issues being discussed in this thread. If you disagre then
>>** post FACT not OPINION ... cite references. I'll acknowledge I am
>>** wrong if you can show me specifically where that is demontrated by
>>** testing.

>
> The reference you cite are based on practical considerations, not
> theoretical. I completely agree that from a practical standpoint using
> modern disk subsystems and even moderate bufferpools, there is no difference
> in performance. And I was the first in this thread to state that.

Who stated it first is irrelevant ... it is incorrect. The issue has nothing to do with modern disk subsystems and nothing to do with moderate (whatever that means) bufferpools.

Speaking of which go to http://tahiti.oracle.com search for "Bufferpools"

>>You are basically repeating what I said about bufferpools and that if the 
>>data was already in SGA, there is no physical I/O.
>>
>>** Definitely NOT repeating what you said about bufferpools as I started
>>** with the position that you are wrong and they are irrelevant to the
>>** thread.

>
> Funny that you did not pick on the other poster in this thread who said the
> exact same thing I said, i.e., that because of bufferpools (and other
> cache), there is no practical or measurable difference.

Return to the top of this post. Where, and I quote you, you said: "Buffer pool hit ratio is relevant because ...."

I'm not picking on you. And I wouldn't pick on them either. I am trying to make a very simple declarative statement. Separating tables and indexes does not improve performance: EVER!

If you now agree then just say so and lets end this miserable thread.

>>If your SGA is 2 GB and your table is 4 terabytes and you are doing a mass 
>>insert on a table, where table rows and indexes are updated at the
>>same time. I am not going to say "measurable" difference (although it
>>could be), I just said theoretical. Some disk subsystems may be fast
>>enough to handle it without a difference (especially those with
>>asynchronous I/O).
>>
>>** Once again I disagree. If you believe otherwise provide a
>>** demonstration that proves you are correct. Work published by
>>** Howard Rogers and others AFAIK say otherwise.

>
> You can disagree all you want, but it is usually true that bufferpool hit
> ratio is highly correlated with better performance (all other things being
> equal). The larger the bufferpool, the less sensitive the performance is the
> speed (or slowness) of the disk subsytem.

Hits being correlated to performance is self-evident. That has not once been the issue. The issue, please refer to the subject of the thread, is "Separating data, index objects." If you wish to wax poetic on some other subject then please start another thread. But this one is about separating tables and indexes and whether that separation has a positive affect on performance.

>>As is the usually case, you quote me out of context ....
>>
>>Above you are quoted in-context and in your entirety. If you have any
>>evidence obtained from benchmarking, StatsPack, AWR Report, or other
>>please show me where I am incorrect. And I will state my opinion again
>>for the record:
>>

>
> At least this time you quoted me in total, but you did not quote me in
> context,

I am going to let this stand as it pretty much says it all.

>>Separation of tables and indexes serves no useful purpose with respect
>>to scalability and performance.

>
> I agree and that is what I said.

<RHETORICAL QUESTION>
Where?
</RHETORICAL QUESTION>

And with that I declare my participation in this over as I have no wish to subject others at c.d.o.server to this any longer.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)

Received on Tue Jul 05 2005 - 22:06:36 CDT