FAQ
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 stellr_at_vt.edu (540) 231-4109 KE4TJC 28^D
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ray Stell
INET: stellr_at_cns.vt.edu

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

Search Discussions

  • Tim Gorman at Oct 21, 2002 at 10:39 pm
    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...

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

    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. 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:
    a.. different recoverability requirements
    a.. indexes can be rebuilt instead of restored
    b.. data (tables and clusters) must be restored -- cannot be "rebuilt" from anything
    b.. different types of I/O requests
    a.. indexes are predominantly accessed using single-block, random read I/O (i.e. UNIQUE scans, RANGE scans, FULL scans)
    a.. relatively seldom are accessed with multi-block sequentially-accessed read I/O (i.e. FAST FULL scans)
    b.. while tables are often accessed with a mix of the two types of I/O, depending on the application
    a.. OLTP usually has heavier single-block, random read I/O due to heavy use of indexes
    b.. DW usually has heavier multi-block, sequentially-accessed read I/O due to heavy use of FULL table scans
    c.. 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"
    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 stellr_at_vt.edu (540) 231-4109 KE4TJC 28^D
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ray Stell
    INET: stellr_at_cns.vt.edu

    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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Tim Gorman
    INET: Tim_at_SageLogix.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).
  • Jared.Still_at_radisys.com at Oct 21, 2002 at 11:15 pm
    Ray,

    In addition, there are apps that expect to find indexes and data in
    separate locations. SAP is one of those.

    Jared

    Ray Stell
    Sent by: root_at_fatcity.com
    10/21/2002 01:43 PM
    Please respond to ORACLE-L



    To: Multiple recipients of list ORACLE-L
    cc:
    Subject: oraperf comment

    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 stellr_at_vt.edu (540) 231-4109 KE4TJC 28^D
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ray Stell
    INET: stellr_at_cns.vt.edu

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

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Jared.Still_at_radisys.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).
  • Yechiel Adar at Oct 22, 2002 at 9:43 am
    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 -----
    From: Tim Gorman
    To: Multiple recipients of list ORACLE-L
    Sent: Tuesday, October 22, 2002 12:39 AM
    Subject: Re: oraperf comment

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



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



    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. 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:
    a.. different recoverability requirements

    a.. indexes can be rebuilt instead of restored
    b.. data (tables and clusters) must be restored -- cannot be "rebuilt" from anything
    b.. different types of I/O requests
    a.. indexes are predominantly accessed using single-block, random read I/O (i.e. UNIQUE scans, RANGE scans, FULL scans)
    a.. relatively seldom are accessed with multi-block sequentially-accessed read I/O (i.e. FAST FULL scans)
    b.. while tables are often accessed with a mix of the two types of I/O, depending on the application
    a.. OLTP usually has heavier single-block, random read I/O due to heavy use of indexes
    b.. DW usually has heavier multi-block, sequentially-accessed read I/O due to heavy use of FULL table scans
    c.. 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 -----
    From: "Ray Stell"
    To: "Multiple recipients of list ORACLE-L"
    Sent: Monday, October 21, 2002 2:43 PM
    Subject: oraperf comment

    >
    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 stellr_at_vt.edu (540) 231-4109 KE4TJC 28^D
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ray Stell
    INET: stellr_at_cns.vt.edu >
    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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Yechiel Adar
    INET: adar76_at_inter.net.il

    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).
  • Markham, Richard at Oct 22, 2002 at 3:03 pm
    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
    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...

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

    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" <
    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 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: 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: 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).
  • Tim Gorman at Oct 22, 2002 at 3:14 pm
    ...resending, as the original send encountered some kind of "locking problem" at fatcity...

    Original Message -----
    To: ORACLE-L_at_fatcity.com
    Sent: Tuesday, October 22, 2002 6:35 AM

    Why? What are the chances of precisely that scenario happening, as opposed to Oracle doing concurrent I/O to tables for both users A and B? Or to indexes for both users A and B simultaneously?

    Splitting tables and indexes into separate tablespaces makes sense, but mainly for recovery purposes. This has little to do with the placement of the datafiles of those tablespaces on devices (non-RAID or RAID).

    Generally, indexes tend to cache extremely well in Oracle (because they are more compact and because of the nature of the I/O), so they usually don't get as much physical I/O as tables. Check V$FILESTAT on a busy application to prove it for yourself...

    After seeing this performance data, why would you place a datafile/tablespace which only gets a small amount of I/O on one device while placing a much busier datafile/tablespace onto another device, just because one contains indexes and the other tables?

    Please think in terms of I/O counts, not poorly-conceived but oft-repeated "conventional wisdom". Keep indexes and tables segregated to different tablespaces, but for decisions on placement of datafiles upon devices, use empirical performance data only.

    Original Message -----
    From: Yechiel Adar
    To: Multiple recipients of list ORACLE-L
    Sent: Tuesday, October 22, 2002 3:43 AM
    Subject: Re: oraperf comment

    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 -----
    From: Tim Gorman
    To: Multiple recipients of list ORACLE-L
    Sent: Tuesday, October 22, 2002 12:39 AM
    Subject: Re: oraperf comment

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


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


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

    a.. different recoverability requirements
    a.. indexes can be rebuilt instead of restored
    b.. data (tables and clusters) must be restored -- cannot be "rebuilt" from anything
    b.. different types of I/O requests
    a.. indexes are predominantly accessed using single-block, random read I/O (i.e. UNIQUE scans, RANGE scans, FULL scans)
    a.. relatively seldom are accessed with multi-block sequentially-accessed read I/O (i.e. FAST FULL scans)
    b.. while tables are often accessed with a mix of the two types of I/O, depending on the application
    a.. OLTP usually has heavier single-block, random read I/O due to heavy use of indexes
    b.. DW usually has heavier multi-block, sequentially-accessed read I/O due to heavy use of FULL table scans
    c.. 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 -----
    From: "Ray Stell"
    To: "Multiple recipients of list ORACLE-L"
    Sent: Monday, October 21, 2002 2:43 PM
    Subject: oraperf comment

    >
    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 stellr_at_vt.edu (540) 231-4109 KE4TJC 28^D
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ray Stell
    INET: stellr_at_cns.vt.edu >
    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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Tim Gorman
    INET: Tim_at_SageLogix.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).
  • Markham, Richard at Oct 22, 2002 at 3:49 pm
    Tim, point well said. Thank you.

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

    ...resending, as the original send encountered some kind of "locking
    problem" at fatcity...


    Original Message -----
    To: ORACLE-L_at_fatcity.com
    Sent: Tuesday, October 22, 2002 6:35 AM

    Why? What are the chances of precisely that scenario happening, as opposed
    to Oracle doing concurrent I/O to tables for both users A and B? Or to
    indexes for both users A and B simultaneously?


    Splitting tables and indexes into separate tablespaces makes sense, but
    mainly for recovery purposes. This has little to do with the placement of
    the datafiles of those tablespaces on devices (non-RAID or RAID).


    Generally, indexes tend to cache extremely well in Oracle (because they are
    more compact and because of the nature of the I/O), so they usually don't
    get as much physical I/O as tables. Check V$FILESTAT on a busy application
    to prove it for yourself...


    After seeing this performance data, why would you place a
    datafile/tablespace which only gets a small amount of I/O on one device
    while placing a much busier datafile/tablespace onto another device, just
    because one contains indexes and the other tables?


    Please think in terms of I/O counts, not poorly-conceived but oft-repeated
    "conventional wisdom". Keep indexes and tables segregated to different
    tablespaces, but for decisions on placement of datafiles upon devices, use
    empirical performance data only.

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

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

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

    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" <
    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 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: 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: 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).
  • Yechiel Adar at Oct 22, 2002 at 3:58 pm
    Sorry if I caused confusion.
    I meant disks that have different controllers because Ray is talking about a system WITHOUT raid so striping is not an option.

    Tim said: 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 just wanted to point that the parallelism helps because Oracle serves more then one user at a time and can benefit from parallel I/O.

    Yechiel Adar
    Mehish

    Original Message -----
    From: Markham, Richard
    To: Multiple recipients of list ORACLE-L
    Sent: Tuesday, October 22, 2002 5:03 PM
    Subject: 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-----
    From: Yechiel Adar
    Sent: Tuesday, October 22, 2002 5:44 AM
    To: Multiple recipients of list ORACLE-L
    Subject: Re: oraperf comment

    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 -----
    From: Tim Gorman
    To: Multiple recipients of list ORACLE-L
    Sent: Tuesday, October 22, 2002 12:39 AM
    Subject: Re: oraperf comment

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

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

    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. 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:
    a.. different recoverability requirements
    a.. indexes can be rebuilt instead of restored
    b.. data (tables and clusters) must be restored -- cannot be "rebuilt" from anything
    b.. different types of I/O requests
    a.. indexes are predominantly accessed using single-block, random read I/O (i.e. UNIQUE scans, RANGE scans, FULL scans)
    a.. relatively seldom are accessed with multi-block sequentially-accessed read I/O (i.e. FAST FULL scans)
    b.. while tables are often accessed with a mix of the two types of I/O, depending on the application
    a.. OLTP usually has heavier single-block, random read I/O due to heavy use of indexes
    b.. DW usually has heavier multi-block, sequentially-accessed read I/O due to heavy use of FULL table scans
    c.. 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 -----
    From: "Ray Stell"
    To: "Multiple recipients of list ORACLE-L"
    Sent: Monday, October 21, 2002 2:43 PM
    Subject: oraperf comment

    >
    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 stellr_at_vt.edu (540) 231-4109 KE4TJC 28^D
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ray Stell
    INET: stellr_at_cns.vt.edu >
    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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Yechiel Adar
    INET: adar76_at_inter.net.il

    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).
  • Yechiel Adar at Oct 22, 2002 at 4:09 pm
    I do not understand the WHY in the beginning.

    I said that it is better to split according to the I/O load, but without more data, split between indexes and tables as a typical sql select will use both.

    Yechiel Adar
    Mehish

    Original Message -----
    From: Tim Gorman
    To: Multiple recipients of list ORACLE-L
    Sent: Tuesday, October 22, 2002 5:14 PM
    Subject: Fw: oraperf comment

    ...resending, as the original send encountered some kind of "locking problem" at fatcity...


    Original Message -----
    From: Tim Gorman
    To: ORACLE-L_at_fatcity.com
    Sent: Tuesday, October 22, 2002 6:35 AM
    Subject: Re: oraperf comment

    Why? What are the chances of precisely that scenario happening, as opposed to Oracle doing concurrent I/O to tables for both users A and B? Or to indexes for both users A and B simultaneously?


    Splitting tables and indexes into separate tablespaces makes sense, but mainly for recovery purposes. This has little to do with the placement of the datafiles of those tablespaces on devices (non-RAID or RAID).


    Generally, indexes tend to cache extremely well in Oracle (because they are more compact and because of the nature of the I/O), so they usually don't get as much physical I/O as tables. Check V$FILESTAT on a busy application to prove it for yourself...


    After seeing this performance data, why would you place a datafile/tablespace which only gets a small amount of I/O on one device while placing a much busier datafile/tablespace onto another device, just because one contains indexes and the other tables?


    Please think in terms of I/O counts, not poorly-conceived but oft-repeated "conventional wisdom". Keep indexes and tables segregated to different tablespaces, but for decisions on placement of datafiles upon devices, use empirical performance data only.

    Original Message -----
    From: Yechiel Adar
    To: Multiple recipients of list ORACLE-L
    Sent: Tuesday, October 22, 2002 3:43 AM
    Subject: Re: oraperf comment

    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 -----
    From: Tim Gorman
    To: Multiple recipients of list ORACLE-L
    Sent: Tuesday, October 22, 2002 12:39 AM
    Subject: Re: oraperf comment

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

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

    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. 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:
    a.. different recoverability requirements
    a.. indexes can be rebuilt instead of restored
    b.. data (tables and clusters) must be restored -- cannot be "rebuilt" from anything
    b.. different types of I/O requests
    a.. indexes are predominantly accessed using single-block, random read I/O (i.e. UNIQUE scans, RANGE scans, FULL scans)
    a.. relatively seldom are accessed with multi-block sequentially-accessed read I/O (i.e. FAST FULL scans)
    b.. while tables are often accessed with a mix of the two types of I/O, depending on the application
    a.. OLTP usually has heavier single-block, random read I/O due to heavy use of indexes
    b.. DW usually has heavier multi-block, sequentially-accessed read I/O due to heavy use of FULL table scans
    c.. 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 -----
    From: "Ray Stell"
    To: "Multiple recipients of list ORACLE-L"
    Sent: Monday, October 21, 2002 2:43 PM
    Subject: oraperf comment

    >
    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 stellr_at_vt.edu (540) 231-4109 KE4TJC 28^D
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ray Stell
    INET: stellr_at_cns.vt.edu >
    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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Yechiel Adar
    INET: adar76_at_inter.net.il

    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).
  • Rajesh.Rao_at_jpmchase.com at Oct 22, 2002 at 4:34 pm
    To summarise, the goal should be to spead I/O evenly across the devices.
    Right?

    Raj

    "Markham, Richard"

    ricas.com> cc:
    Sent by: Subject: RE: oraperf comment
    root_at_fatcity.com

    October 22, 2002
    11:49 AM
    Please respond to
    ORACLE-L

    Tim, point well said. Thank you.
    -----Original Message-----
    From: Tim Gorman
    Sent: Tuesday, October 22, 2002 11:15 AM
    To: Multiple recipients of list ORACLE-L
    Subject: Fw: oraperf comment

    ...resending, as the original send encountered some kind of "locking
    problem" at fatcity...

    ----- Original Message -----
    From: Tim Gorman
    To: ORACLE-L_at_fatcity.com
    Sent: Tuesday, October 22, 2002 6:35 AM
    Subject: Re: oraperf comment

    Why? What are the chances of precisely that scenario happening, as
    opposed to Oracle doing concurrent I/O to tables for both users A and
    B? Or to indexes for both users A and B simultaneously?

    Splitting tables and indexes into separate tablespaces makes sense,
    but mainly for recovery purposes. This has little to do with the
    placement of the datafiles of those tablespaces on devices (non-RAID
    or RAID).

    Generally, indexes tend to cache extremely well in Oracle (because
    they are more compact and because of the nature of the I/O), so they
    usually don't get as much physical I/O as tables. Check V$FILESTAT on
    a busy application to prove it for yourself...

    After seeing this performance data, why would you place a
    datafile/tablespace which only gets a small amount of I/O on one
    device while placing a much busier datafile/tablespace onto another
    device, just because one contains indexes and the other tables?

    Please think in terms of I/O counts, not poorly-conceived but
    oft-repeated "conventional wisdom". Keep indexes and tables
    segregated to different tablespaces, but for decisions on placement of
    datafiles upon devices, use empirical performance data only.
    ----- Original Message -----
    From: Yechiel Adar
    To: Multiple recipients of list ORACLE-L
    Sent: Tuesday, October 22, 2002 3:43 AM
    Subject: Re: oraperf comment

    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 -----
    From: Tim Gorman
    To: Multiple recipients of list ORACLE-L
    Sent: Tuesday, October 22, 2002 12:39 AM
    Subject: Re: oraperf comment

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

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

    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. 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 -----
    From: "Ray Stell"
    To: "Multiple recipients of list ORACLE-L"
    Sent: Monday, October 21, 2002 2:43 PM
    Subject: oraperf comment

    >
    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 stellr_at_vt.edu (540) 231-4109 KE4TJC 28^D
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ray Stell
    INET: stellr_at_cns.vt.edu >
    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).

    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author:
    INET: Rajesh.Rao_at_jpmchase.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).
  • Ray Stell at Oct 22, 2002 at 4:48 pm
    Thanks for your comments Tim. The reason I brought this up is that the
    "conventional wisdom" is, well, conventional. I like your logic, but
    have always lived on the other side of the tracks on this. I'll say
    one thing for oraperf, they got my attention. The oraperf writer used
    the exclusive:

    "Never split index and data files to different sets of disks."

    I wonder if they were making an overstatement in an effort to dispell
    "conventional wisdom". A statement about i/o balancing may be more
    appropriate.

    BTW, some of my ts actually tend to operate in the opposite fashion
    from your generalized argument, but perhaps your argument holds true on
    the opposite side of the coin, but I still find "Never" tough to
    swallow:

    Av Av Av Av Buffer Av Buf
    Reads Reads/s Rd(ms) Blks/Rd Writes Writes/s Waits Wt(ms)

    -------------- ------- --------- ------- ------------ -------- ---------- ------
    TACPLUS_BINDEX

    65,711 1 2.7 1.0 59,353 1 0 0.0

    TACPLUS_BDATA

    4,342 0 12.4 1.0 1,615 0 0 0.0

    Never say never!
    On Tue, Oct 22, 2002 at 07:14:46AM -0800, Tim Gorman wrote:
    ...resending, as the original send encountered some kind of "locking problem" at fatcity...

    ----- Original Message -----
    To: ORACLE-L_at_fatcity.com
    Sent: Tuesday, October 22, 2002 6:35 AM


    Why? What are the chances of precisely that scenario happening, as opposed to Oracle doing concurrent I/O to tables for both users A and B? Or to indexes for both users A and B simultaneously?

    Splitting tables and indexes into separate tablespaces makes sense, but mainly for recovery purposes. This has little to do with the placement of the datafiles of those tablespaces on devices (non-RAID or RAID).

    Generally, indexes tend to cache extremely well in Oracle (because they are more compact and because of the nature of the I/O), so they usually don't get as much physical I/O as tables. Check V$FILESTAT on a busy application to prove it for yourself...

    After seeing this performance data, why would you place a datafile/tablespace which only gets a small amount of I/O on one device while placing a much busier datafile/tablespace onto another device, just because one contains indexes and the other tables?

    Please think in terms of I/O counts, not poorly-conceived but oft-repeated "conventional wisdom". Keep indexes and tables segregated to different tablespaces, but for decisions on placement of datafiles upon devices, use empirical performance data only.
    ----- Original Message -----
    From: Yechiel Adar
    To: Multiple recipients of list ORACLE-L
    Sent: Tuesday, October 22, 2002 3:43 AM
    Subject: Re: oraperf comment


    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 -----
    From: Tim Gorman
    To: Multiple recipients of list ORACLE-L
    Sent: Tuesday, October 22, 2002 12:39 AM
    Subject: Re: oraperf comment


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


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


    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. 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:
    a.. different recoverability requirements
    a.. indexes can be rebuilt instead of restored
    b.. data (tables and clusters) must be restored -- cannot be "rebuilt" from anything
    b.. different types of I/O requests
    a.. indexes are predominantly accessed using single-block, random read I/O (i.e. UNIQUE scans, RANGE scans, FULL scans)
    a.. relatively seldom are accessed with multi-block sequentially-accessed read I/O (i.e. FAST FULL scans)
    b.. while tables are often accessed with a mix of the two types of I/O, depending on the application
    a.. OLTP usually has heavier single-block, random read I/O due to heavy use of indexes
    b.. DW usually has heavier multi-block, sequentially-accessed read I/O due to heavy use of FULL table scans
    c.. 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 -----
    From: "Ray Stell"
    To: "Multiple recipients of list ORACLE-L"
    Sent: Monday, October 21, 2002 2:43 PM
    Subject: oraperf comment

    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 stellr_at_vt.edu (540) 231-4109 KE4TJC 28^D
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ray Stell
    INET: stellr_at_cns.vt.edu

    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).
    --
    ===============================================================
    Ray Stell stellr_at_vt.edu (540) 231-4109 KE4TJC 28^D
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ray Stell
    INET: stellr_at_cns.vt.edu

    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).
  • Tim Gorman at Oct 22, 2002 at 8:00 pm
    Yechiel,

    You had mentioned only one possible scenario (i.e. "user A accesses table while user B simultaneously accesses index") where there are several other possible, equally-likely scenarios (i.e. "user A accesses table while user B simultaneously accesses table", "user A accesses index while user B simultaneously accesses index", etc). Separating tables and indexes to separate devices does nothing for those other, equally-likely scenarios, does it? That's the reason for the question "why?" in the beginning of my last reply...

    At issue here is not the concept of parallelism in I/O. At issue (at least for me) is the "conventional wisdom" that states/implies that there is some performance benefit of separating tables and indexes to separate devices. My assertion is that this is irrelevant for two reasons: a) within a single process the accessing of table blocks and index blocks are purely sequential and b) tables and indexes have different I/O characteristics which make it less likely that they will conflict with each other. In fact, in most situations datafiles/tablespaces containing indexes generate far fewer physical I/Os than datafiles/tablespaces containing tables. From an I/O perspective, the key is not to focus on whether the datafile/tablespace contains tables or indexes but rather to focus on the volume and type of physical I/O they generate.

    By focusing on the I/O statistics rather than whether they are tables or indexes, one can make better determinations on how to distribute I/O across non-RAID devices.

    Hope this helps...

    -Tim

    Original Message -----
    From: Yechiel Adar
    To: Multiple recipients of list ORACLE-L
    Sent: Tuesday, October 22, 2002 10:09 AM
    Subject: Re: oraperf comment

    I do not understand the WHY in the beginning.

    I said that it is better to split according to the I/O load, but without more data, split between indexes and tables as a typical sql select will use both.

    Yechiel Adar
    Mehish

    Original Message -----
    From: Tim Gorman
    To: Multiple recipients of list ORACLE-L
    Sent: Tuesday, October 22, 2002 5:14 PM
    Subject: Fw: oraperf comment

    ...resending, as the original send encountered some kind of "locking problem" at fatcity...

    Original Message -----
    From: Tim Gorman
    To: ORACLE-L_at_fatcity.com
    Sent: Tuesday, October 22, 2002 6:35 AM
    Subject: Re: oraperf comment

    Why? What are the chances of precisely that scenario happening, as opposed to Oracle doing concurrent I/O to tables for both users A and B? Or to indexes for both users A and B simultaneously?

    Splitting tables and indexes into separate tablespaces makes sense, but mainly for recovery purposes. This has little to do with the placement of the datafiles of those tablespaces on devices (non-RAID or RAID).

    Generally, indexes tend to cache extremely well in Oracle (because they are more compact and because of the nature of the I/O), so they usually don't get as much physical I/O as tables. Check V$FILESTAT on a busy application to prove it for yourself...

    After seeing this performance data, why would you place a datafile/tablespace which only gets a small amount of I/O on one device while placing a much busier datafile/tablespace onto another device, just because one contains indexes and the other tables?

    Please think in terms of I/O counts, not poorly-conceived but oft-repeated "conventional wisdom". Keep indexes and tables segregated to different tablespaces, but for decisions on placement of datafiles upon devices, use empirical performance data only.

    Original Message -----
    From: Yechiel Adar
    To: Multiple recipients of list ORACLE-L
    Sent: Tuesday, October 22, 2002 3:43 AM
    Subject: Re: oraperf comment

    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 -----
    From: Tim Gorman
    To: Multiple recipients of list ORACLE-L
    Sent: Tuesday, October 22, 2002 12:39 AM
    Subject: Re: oraperf comment

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

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

    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. 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:
    a.. different recoverability requirements
    a.. indexes can be rebuilt instead of restored
    b.. data (tables and clusters) must be restored -- cannot be "rebuilt" from anything
    b.. different types of I/O requests
    a.. indexes are predominantly accessed using single-block, random read I/O (i.e. UNIQUE scans, RANGE scans, FULL scans)
    a.. relatively seldom are accessed with multi-block sequentially-accessed read I/O (i.e. FAST FULL scans)
    b.. while tables are often accessed with a mix of the two types of I/O, depending on the application
    a.. OLTP usually has heavier single-block, random read I/O due to heavy use of indexes
    b.. DW usually has heavier multi-block, sequentially-accessed read I/O due to heavy use of FULL table scans
    c.. 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 -----
    From: "Ray Stell"
    To: "Multiple recipients of list ORACLE-L"
    Sent: Monday, October 21, 2002 2:43 PM
    Subject: oraperf comment

    >
    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 stellr_at_vt.edu (540) 231-4109 KE4TJC 28^D
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ray Stell
    INET: stellr_at_cns.vt.edu >
    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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Tim Gorman
    INET: Tim_at_SageLogix.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).
  • Yechiel Adar at Oct 23, 2002 at 9:58 am
    Hello Tim

    Maybe I did not express myself as I should have.
    I am in complete agreement with you on this point.

    Yechiel Adar
    Mehish

    Original Message -----
    From: Tim Gorman
    To: Multiple recipients of list ORACLE-L
    Sent: Tuesday, October 22, 2002 10:00 PM
    Subject: Re: oraperf comment

    Yechiel,


    You had mentioned only one possible scenario (i.e. "user A accesses table while user B simultaneously accesses index") where there are several other possible, equally-likely scenarios (i.e. "user A accesses table while user B simultaneously accesses table", "user A accesses index while user B simultaneously accesses index", etc). Separating tables and indexes to separate devices does nothing for those other, equally-likely scenarios, does it? That's the reason for the question "why?" in the beginning of my last reply...


    At issue here is not the concept of parallelism in I/O. At issue (at least for me) is the "conventional wisdom" that states/implies that there is some performance benefit of separating tables and indexes to separate devices. My assertion is that this is irrelevant for two reasons: a) within a single process the accessing of table blocks and index blocks are purely sequential and b) tables and indexes have different I/O characteristics which make it less likely that they will conflict with each other. In fact, in most situations datafiles/tablespaces containing indexes generate far fewer physical I/Os than datafiles/tablespaces containing tables. From an I/O perspective, the key is not to focus on whether the datafile/tablespace contains tables or indexes but rather to focus on the volume and type of physical I/O they generate.


    By focusing on the I/O statistics rather than whether they are tables or indexes, one can make better determinations on how to distribute I/O across non-RAID devices.


    Hope this helps...


    -Tim

    Original Message -----
    From: Yechiel Adar
    To: Multiple recipients of list ORACLE-L
    Sent: Tuesday, October 22, 2002 10:09 AM
    Subject: Re: oraperf comment

    I do not understand the WHY in the beginning.


    I said that it is better to split according to the I/O load, but without more data, split between indexes and tables as a typical sql select will use both.

    Yechiel Adar
    Mehish

    Original Message -----
    From: Tim Gorman
    To: Multiple recipients of list ORACLE-L
    Sent: Tuesday, October 22, 2002 5:14 PM
    Subject: Fw: oraperf comment

    ...resending, as the original send encountered some kind of "locking problem" at fatcity...

    ----- Original Message -----
    From: Tim Gorman
    To: ORACLE-L_at_fatcity.com
    Sent: Tuesday, October 22, 2002 6:35 AM
    Subject: Re: oraperf comment

    Why? What are the chances of precisely that scenario happening, as opposed to Oracle doing concurrent I/O to tables for both users A and B? Or to indexes for both users A and B simultaneously?

    Splitting tables and indexes into separate tablespaces makes sense, but mainly for recovery purposes. This has little to do with the placement of the datafiles of those tablespaces on devices (non-RAID or RAID).

    Generally, indexes tend to cache extremely well in Oracle (because they are more compact and because of the nature of the I/O), so they usually don't get as much physical I/O as tables. Check V$FILESTAT on a busy application to prove it for yourself...

    After seeing this performance data, why would you place a datafile/tablespace which only gets a small amount of I/O on one device while placing a much busier datafile/tablespace onto another device, just because one contains indexes and the other tables?

    Please think in terms of I/O counts, not poorly-conceived but oft-repeated "conventional wisdom". Keep indexes and tables segregated to different tablespaces, but for decisions on placement of datafiles upon devices, use empirical performance data only.
    ----- Original Message -----
    From: Yechiel Adar
    To: Multiple recipients of list ORACLE-L
    Sent: Tuesday, October 22, 2002 3:43 AM
    Subject: Re: oraperf comment

    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 -----
    From: Tim Gorman
    To: Multiple recipients of list ORACLE-L
    Sent: Tuesday, October 22, 2002 12:39 AM
    Subject: Re: oraperf comment

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

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

    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. 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:
    a.. different recoverability requirements
    a.. indexes can be rebuilt instead of restored
    b.. data (tables and clusters) must be restored -- cannot be "rebuilt" from anything
    b.. different types of I/O requests
    a.. indexes are predominantly accessed using single-block, random read I/O (i.e. UNIQUE scans, RANGE scans, FULL scans)
    a.. relatively seldom are accessed with multi-block sequentially-accessed read I/O (i.e. FAST FULL scans)
    b.. while tables are often accessed with a mix of the two types of I/O, depending on the application
    a.. OLTP usually has heavier single-block, random read I/O due to heavy use of indexes
    b.. DW usually has heavier multi-block, sequentially-accessed read I/O due to heavy use of FULL table scans
    c.. 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 -----
    From: "Ray Stell"
    To: "Multiple recipients of list ORACLE-L"
    Sent: Monday, October 21, 2002 2:43 PM
    Subject: oraperf comment

    >
    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 stellr_at_vt.edu (540) 231-4109 KE4TJC 28^D
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Ray Stell
    INET: stellr_at_cns.vt.edu
    >
    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).
    --
    Please see the official ORACLE-L FAQ: http://www.orafaq.com
    --
    Author: Yechiel Adar
    INET: adar76_at_inter.net.il

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedOct 21, '02 at 8:43p
activeOct 23, '02 at 9:58a
posts13
users6
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase