RE: oraperf comment

I'm a little confused when one is talking about putting indexes and tables into seperate TABLESPACES and the other is talking about seperate DISKS. To any extent, I cant imagine how seperating IO types across physical controllers could be anything but rewarding. Yet, splitting across "DISKS" and splitting across "SPINDLES" are two different concepts. You have striping so you can benefit from more heads to do more IO and you'll only benefit more with having more spindles, ~again~, to handle more IO. Splitting these across multiple spindles has proven performance gains for me and I think the "Never split index and data files to different sets of disks." has a bit of ~a CACHE will solve everything mentality~ (no pun intended). ORACLE will feast on a disk cache especially with 11i applications, but thats not to say it doesn't help.  

Please correct me as i'm looking for guidance. =)

-----Original Message-----
Sent: Tuesday, October 22, 2002 5:44 AM
To: Multiple recipients of list ORACLE-L

Hello Tim  

I beg to differ. Without raid it is better to put indexes and tables on different disks and controllers.
This way Oracle can do I/O to a table for user A while doing I/O to the index for user B.  

It is better if you can find the high I/O areas of the database and split them across disks, but as a rule of thumb splitting indexes and tables make sense (again - when you work without raid).  

Yechiel Adar
Mehish

• Original Message ----- To: Multiple recipients of list ORACLE-L <mailto:ORACLE-L_at_fatcity.com> Sent: Tuesday, October 22, 2002 12:39 AM

Ray,  

I don't know exactly what was intended with the comment, but I agree with your interpretation.  

---
 
As far as any other reasons for the comment...
 
<RANT>
In terms of myths that have persisted with Oracle over the years, the idea
that some performance benefit exists from I/O parallelism due to separating
tables and indexes to different devices has been especially persistent.
I've even heard it described as "conventional wisdom".  As a matter of fact,
there is no possibility for "parallelism" benefits on indexed I/O
operations.  Never has been;  might never be (though "never" is a long
time)...
</RANT>
 
The reason is that navigating a B*Tree index structure is inherently
sequential.  Think about it -- first you have to access the "root" block.
Looking inside the contents of the "root" directs you to the next "branch"
or "leaf" block in the index B*Tree structure.  You cannot seek for the next
block in parallel;  you've got to look inside one block in order to know
what block to access next.  Then, once you've accessed down to the final
"leaf" block, reading its contents tells you which row in the table to
access.  If you are doing a "range scan" operation, then you have to go back
to the index "leaf" block in order to find the next table row to access.
 
The name of the wait-event for this type of I/O (a.k.a. "db file sequential
read", a.k.a. single-block random-access read) also suggests this
"sequentialiality" (is that a word?).  Jeff Holt wrote a great paper on the
reasons for the apparent mis-naming of the wait-events "db file sequential
read" and "db file scattered read" -- I'm sure that it is downloadable from
<http://www.hotsos.com> http://www.hotsos.com.  Even when "asynchronous I/O"
is available and configured, indexed I/O operations are still essentially
synchronous (and non-parallel)...
 
There is a possibility of some form of "parallelization" in "range-scan"
operations, but there is no evidence that this is happening.  For example,
while performing an indexed range-scan, if we wanted to read a batch of
index entries from the index "leaf blocks" and submit a list of I/O requests
for data blocks on the corresponding table, we could do so.  However, when
I've performed "truss" operations on an Oracle server process performing
such a range-scan operation (at least through Oracle8i), I've not seen this
happening.  Purely generic "read()" operations, one at a time,
sequentially...
 
---
 
The only real advantages of separating tables from indexes into different
tablespaces are:



*	different recoverability requirements 





*	indexes can be rebuilt instead of restored 




*	data (tables and clusters) must be restored -- cannot be "rebuilt"

from anything



*	different types of I/O requests 





*	indexes are predominantly accessed using single-block, random read

I/O (i.e. UNIQUE scans, RANGE scans, FULL scans) 




*	relatively seldom are accessed with multi-block

sequentially-accessed read I/O (i.e. FAST FULL scans)



*	while tables are often accessed with a mix of the two types of I/O,

depending on the application 




*	OLTP usually has heavier single-block, random read I/O due to heavy

use of indexes 



*	DW usually has heavier multi-block, sequentially-accessed read I/O

due to heavy use of FULL table scans



*	may be advantages from this in Oracle9i where different blocksizes

are possible for different tablespaces

These last points are related to performance, but not in the sense that the
mythical "conventional wisdom" dictates...
 
Hope this helps...
 
-Tim
 
----- Original Message ----- 
To: "Multiple recipients of list ORACLE-L" <  <mailto:ORACLE-L_at_fatcity.com>
ORACLE-L_at_fatcity.com>
Sent: Monday, October 21, 2002 2:43 PM


> 
> An recent oraperf report included the comment:  "Never split index
> and data files to different sets of disks."  It goes on to state that
> striping is better.  If the system in question does not have
> raid support, wouldn't it be better to split the index and data across
> spindles?  That would make the word "Never" inappropriate here?  Maybe
> this is their way of saying don't use old technology.  Is there some 
> other reason I am missing?  
> ===============================================================
> Ray Stell    <mailto:stellr_at_vt.edu> stellr_at_vt.edu     (540) 231-4109
KE4TJC    28^D
> -- 
> Please see the official ORACLE-L FAQ:  <http://www.orafaq.com>
http://www.orafaq.com
> -- 
> Author: Ray Stell
>   INET:  <mailto:stellr_at_cns.vt.edu> stellr_at_cns.vt.edu
> 
> Fat City Network Services    -- 858-538-5051  <http://www.fatcity.com>
http://www.fatcity.com
> San Diego, California        -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to:  <mailto:ListGuru_at_fatcity.com> ListGuru_at_fatcity.com (note EXACT
spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from).  You may
> also send the HELP command for other information (like subscribing). 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Markham, Richard
  INET: RMarkham_at_hafeleamericas.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).