Re: The cardinality of tablespace and datafile

Hi,

Let me run this by you; I'm about to take my first stab at an oracle setup (strictly OLTP) and I don't want to have my experience with other databases get in the way of what's good for oracle. Please advise.

I have 8 (9GB) drives on one controller and another set of 8 (9GB) drives on a different controller.
I'm planning to make 3 disk_groups (dg)'s using Veritas LVM as follows: dg1(27GB): made of 3 drives in raid-10 against another 3 drives. (system/rollback/data/index tablespaces/datafiles go here)

dg2(27GB): made of 3 drives in raid-10 against 3 drives. (system/rollback/data/index tablespaces/datafiles go here)

dg3(18GB): made of 2 drives in raid-1 "no striping" against 2 drives. (Redo logs go here)

Points to consider:
1. The typical user/transaction_load is around 100 concurrent users firing off 300KByte transactions randomly.

2. dg1 and dg2 disk_groups will be sliced down to 500MB datafiles. i.e each tablespace will be made of a many small datafiles as opposed to few larger ones.

3. dg3 for online RedoLogs, I only need about total of 500MB for redo logs, what about the tons of leftover space here!

Questions:
1. Would it be better if I stripe and mirror against all 8 drives and have each data file come out of ONE giant disk_group? (redo, rollback, system, the whole bit go here)

2. Is it true that the ONLY objection to using raw devices by the oracle dba folks is mainly because dd is hard to use??? Why does 'dd' count against ease of administration?

Agamia
agamia_at_yahoo.com

In article <3940309e.306040042_at_news.eagles.bbs.net.au>,   steve.adams_at_ixora.com.au (Steve Adams) wrote:
> Hi all,
>
> I think that Anjo is just playing the role of "the devil's advocate"
 here.
>
> Broad striping is indeed the simplest and may be adequate in a good
 many cases,
> particularly OLTP systems, but it is NOT the best. If you have enough
 disks to
> service the I/O workload without any queuing in the disk subsystem,
 which you
> should, then careful I/O separation can give better performance than
 mindless
> broad striping, particularly for non-OLTP systems.
>
> I/O separation can be used to preserve the sequential nature of
 logically
> sequential I/O. There is much to gain in the disk service times,
 assuming no
> waiting time either with or without the I/O separation.
>
> Again, there can be much to gain in service times by separating
 related
> segments. Here is a quote from my web tip on the matter ...
>
> "In general, it is safest to separate related segments into separate
> tablespaces. However, not all query execution plans that access two
 related
> segments cause repeated alternating access between them. For example,
 two
> related tables may reside on the same disks if they are only ever
 joined using a
> sort-merge join or hash join, because these query execution plans
 cause the
> tables to be accessed in series. The same applies to index merges.
 However, a
> nested loops join normally involves repeated access to first the outer
 table,
> then the index on the inner table, and then the inner table itself. If
 any 2, or
> all 3, of these segments reside on the same disks, head thrashing will
 occur and
> will result in poor I/O service times.
>
> Similarly, an index range scan normally involves repeated alternating
 access to
> the index and the table, and partition parallel execution plans
 normally involve
> concurrent access to multiple partitions of the same index. These
 execution
> plans can also cause disk head thrashing if related segments have not
 been duly
> separated. Because of these factors, it is safest to always separate
 related
> segments into distinct tablespaces, unless you are confident that the
 optimizer
> will always choose an execution plan that does not involve repeated
 alternating
> access between the segments."
> http://www.ixora.com.au/tips/creation/tablespaces.htm
>
> Regards,
> Steve Adams
> http://www.ixora.com.au/
> http://www.oreilly.com/catalog/orinternals/
> http://www.christianity.net.au/
>
> On Thu, 08 Jun 2000 14:16:54 GMT, "akolk - gelrevision.nl"
> <akolk_at_gelrevision.nl> wrote:
>
> >It is not wise to dedicate disks for certain functions. The best and
 simplest thing
> >to do is stripe over as many disks as you can .....
> >
> >Point 2 is old and has never been true and can really hurt
 performance/throughput
> >of your database
> >
> >Also remember Databases is about disks/IOs not number of files ! (1
 disk with 10
> >files or 1 file will perform as bad).
> >
> >Waiting for replies ;-)
> >
> >Anjo.
> >
> >
> >Andreas Stephan wrote:
> >
> >> Hi Chuan,
> >>
> >> here are some rules I use when I install a database:
> >>
> >> 1) never put rollback segment tablespace on a drive containig data
 (table) or
> >> index tablespaces
> >> 2) never put data and index tablespaces on the same drive if they
 interact
> >> 3) use a max of 2Gig Datafiles to keep maintenance simpler and to
 avoid
> >> unnecessary recovery time
> >> (it is easier and quicker to recover one 2 Gig Datafile than
 one of 20 Gig).
> >> You can transport a
> >> 2 Gig Datafile on an old dat tape but with 20 Gigs?? And
 copying from tape
> >> to disk or over the lan
> >> will not cause any problems if you have only 2 Gig Datafiles.
> >> 4) try to use partitioned tablespaces if possible. Split the data
 onto the
> >> partitions. Each partition should
> >> reside on a separate drive to maximize performance
> >>
> >> putting datafile on different drives will only help if you have
 more than one
> >> tablespace and your objects are
> >> well separated onto these tablespaces (index objects in index
 tablespaces,
> >> tables in data tablespaces, tables that
> >> are often joined moved into separate tablespaces on separate disks
 or
> >> implemented using clusters etc..)
> >>
> >> hth
> >> Andy
> >>
> >> chuan zhang schrieb:
> >>
> >> > Hi, all,
> >> >
> >> > Could anyone tell me that which the following option is better
 in terms of
> >> > performance:
> >> >
> >> > 1) One tablespace with one big datafile, this datafile might
 increase more
> >> > if there are more storage.
> >> >
> >> > 2) One tablespace with many small or medium datafiles
> >> > or
> >> > 3) Splitting many datafiles into many tablespaces.
> >> >
> >> > Note, I have considered to put the datafiles into different
 drives in order
> >> > to increase the performance.
> >> >
> >> > Thanks
> >> >
> >> > Chuan Zhang
> >> >
> >> > Asiaonline Ltd Co.
> >
> >
> >
> >
> >
> >
> >
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Jun 16 2000 - 00:00:00 CDT