Re: Tablespaces and indexes

Pete Finnigan wrote:
>
> Hi Pete
>
> If there is only one user yes, but if we have tens, hundreds or
> thousands of users doing the same basic things accessing the same tables
> and indexes then surely splitting the index and table will reduce
> contention. If two users do the same thing and the index and table are
> in the same tablespace ( and could be the same datafile) then unless
> there is aysincronous access there would be contention. If the index and
> table are split then user A accesses the index user B waits to access
> the index, user A then accesses the data and User A can access the index
> while user A reads the table data. In your example user B would have to
> wait for user A to access the index and data. What about parallel
> queries ??
>
> just my thoughts on it
>
> cheers
>
> Pete Finnigan
> www.pentest-limited.com
>
> In article <84oq7.30$vj2.7045_at_inet16.us.oracle.com>, Pete Sharman
> <peter.sharman_at_oracle.com> writes
> >I will don my cape as 'Slayer of Oracle Myths' and climb on to my soapbox.
> >There is NO performance benefit from isolating data and index. The operation
> >of an indexed access will serially and synchronously perform any IOs
> >required to traverse the index to get a row id and then, synchronously,
> >perform any IO required to read a data block. Note each of these operations
> >are done serially, there is no overlap between the index IO and the data IO.
> >For this reason there is no performance benefit.
> >
> >
> >
> >The purpose of IO tuning is to get consistent response times from all of the
> >disk operations. If the number of operations on data and indexes are
> >comparable then splitting the data and indexes is one method of doing that.
> >However I have seem many cases where customers have had IO problems
> >precisely because they have believed this myth. A data warehouse where most
> >of the operations are scans rarely access the index disks. A high volume
> >OLTP system where many queries are satisfied in the index only and there is
> >very good locality of access on the data would rarely access the data disks.
> >There are reasons for splitting data and indexes, mainly for management
> >purposes, but performance is not one of them.
> >
> >
> >
> >--
> >HTH. Additions and corrections welcome.
> >
> >Pete
> >Author of "Oracle8i: Architecture and Administration Exam Cram"
> >Now got a life back again that the book is released!
> >
> >"Controlling developers is like herding cats."
> >Kevin Loney, Oracle DBA Handbook
> >
> >"Oh no, it's not. It's much harder than that!"
> >Bruce Pihlamae, long-term Oracle DBA
> >
> >"Dale Edgar" <dale_at_databee.com> wrote in message
> >news:3ba9dcbe.12372123_at_news.btclick.com...
> >> Hi Michiel
> >>
> >> On Thu, 20 Sep 2001 13:32:35 +0200, "Michiel Brunt" <mbrunt_at_inergy.nl>
> >> wrote:
> >> >However, I have heard others say it is better to store indexes in a
> >seperate
> >> >index tablespace.
> >>
> >> The basic idea in tablespace layouts is to reduce the "contention" (ie
> >> waiting) of the disks as the heads move back and forth. Many operating
> >> systems can also do simultaneous reads from from different disks.
> >>
> >> If your tablespaces are NOT located on disks that are striped then it
> >> is better to have indexes in their own tablespace and on a different
> >> physical disk. If the disks are striped together into a logical volume
> >> it is less important to separate the data and indexes since you really
> >> don't know which physical disk is being hit anyways. In that case, if
> >> one is trying to get the ultimate in disk efficiency one would put the
> >> index tablespace on a different stripe set and make sure each stripe
> >> set uses a different group of disks.
> >>
> >> Regards
> >> Dale
> >> ----------
> >> DataBee - Oracle DataBase Subsets
> >> The simple and easy way to create small versions of large Oracle
> >databases. http://www.databee.com
> >
> >
>
> --
> Pete Finnigan
> IT Security Consultant
> PenTest Limited
>
> Office 01565 830 990
> Fax 01565 830 889
> Mobile 07974 087 885
>
> pete.finnigan_at_pentest-limited.com
>
> www.pentest-limited.com

If you've got 1000's of concurrent users, you'd be hoping that most of that index is in the cache. If its not, then they are accessing different parts of the index - in which case, it won't matter if your data and indexes coexist - your heads are already jumping

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."