Re: The cardinality of tablespace and datafile

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.
>
>
>
>
>
>
>
Received on Fri Jun 09 2000 - 00:00:00 CDT