Re: Separating data, index objects

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

> 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. In practice it is not true because disks are fast, disk controllers have cache, the OS has cache (unless turned off or bypassed), and Oracle has cache for the table and indexes. What you mean to say is that the difference cannot be measured, and I agree with that. But theoretically, if the disk subsystem was slow enough and primitive enough, there could be contention. For example if the disk drive had an seek time of 5 seconds (when playing with Daniel, absurdity is the order of the day) then the difference would move from merely theoretical to the realm of practical. For someone who teaches college, you have a appalling lack of understanding of the of the concept of "theory."

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

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

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

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

> 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, since the posts I was responding to where not shown by you. I guess "context" like "theory" is not a conept you understand.

For example, another person agreed with me that part of the reason for the fact that there is no practical difference is because of bufferpools (and by implication other caching methods). My post was simply agreeing with him. For some reason, you only took exception to my comments, and not the other comments which were identical.

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

I agree and that is what I said. And I said it in this thread long before you said it. I am really wondering what took you so long to come around to my position on this. Received on Tue Jul 05 2005 - 19:12:31 CDT