Re: Tablespaces and indexes

SOrry for the delay in responding. I've been doing some RAC internals stuff and didn't have time to read the mail again last week. As a total aside, the more I see of the way that RAC works the more impressed I am - it's really cool!

Anyway back to Pete's original response to mine. Connor's already responded with pretty much what I would have responded to it. I suppose the point I should have made more clear in my original email (and this ties in to what Nuno had to say as well) is that while there is no performance gain for separating data and indexes, it's still something that is beneficial from other viewpoints (manageability, availability and so on).

As yet another complete aside, does anyone know why some responses to postings arrive before the postings themselves, or is it just the way our news server works?

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

"Connor McDonald" <connor_mcdonald_at_yahoo.com> wrote in message
news:3BABBEBE.2254_at_yahoo.com...


> Pete Finnigan wrote:

> >

> > Hi Conner

> >

> > Fair point, most of the index should reside in block buffers in an ideal

> > situation. What about fragmentation of the tablespace if both the table

> > and index are in the same tablespace?. And again what about parallel

> > operations would there be a benefit there by splitting the table and

> > index up.

> >

> > I saw a database in Vienna about four years ago that i was involved in a

> > team working on, and we saw that tables and their indexes were on the

> > same physical disks and in the same tablespaces. By seperating them out

> > and placing the tables and indexes based on their function and use we

> > were able to stop erratic performance and improve batch throughput by

> > over 100%. we ensured that tables and their indexes used in the same

> > query ended up on different disks where possible and placed all tables

> > with the same extent sizes where appropriate in the same tablespaces, ie

> > objects in a tablespace always had the same extent sizes. This meant a

> > lot of tablespaces but it allowed us to group objects based on size and

> > use and then place them on disk based on use / hot / cold and so on.

> > This improved performance and made it less erratic.

> >

> > OK, i am probably not going to win this discussion, but splitting tables

> > and indexes has on a number of occasions helped me to be able to layout

> > the physical database on disk in a defined and consistent manner.

> >

> > regards

> >

> > Pete Finnigan

> > www.pentest-limited.com

> >

> > In article <3BAA66A9.1FD9_at_yahoo.com>, Connor McDonald

> > <connor_mcdonald_at_yahoo.com> writes

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

> >

> > --

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

>


> I'd say we're both using the different word to describe the same thing.

> Being able to balance and separate the different types and behaviours of

> IO is the important thing - if in some/many cases this is separating

> data and indexes then so be it, etc

>


> CHeers

> Connor

> --

> ==============================

> Connor McDonald

>


> http://www.oracledba.co.uk

>


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