RE: Performance of multi datafile tablespaces

From: Kimberly Smith <kimberly.smith_at_gmd.fujitsu.com>
Date: Thu, 03 May 2001 05:53:05 -0700
Message-ID: <F001.002F82B8.20010503054022@fatcity.com>

Depending on your application you can actually create your own partitioning. For example, we have a database here where we create a new tablespace and tables every month to deal with that months data. We have some set views that combine certain months together. I would say that if you can partition using Oracles method then you could probably do it manually. We also have a database where we manually stripped some tables and to me its more then a pain in the butt for what benefit we got out of it. I did not do the stripping but basically its just 1 extent can only fit in one datafile and as you already found out you have the extents sized to something smaller then what would fit in one extent.

-----Original Message-----
[mailto:Bruce.Reardon_at_comalco.riotinto.com.au] Sent: Wednesday, May 02, 2001 10:55 PM
To: Multiple recipients of list ORACLE-L

Hi,

We have a number of large tables that we want to increase the performance of queries against.

This will be using 8171 on NT utilising the Parallel query option (PQO) on a server with 4 CPUs.

Ideally we would like to partition the tables but due to the cost of the partitioning option (hundreds of thousands of dollars across our 3 sites) we have all but ruled this out for the moment.

So, what is being considered is manual striping of datafiles within tablespaces.

That is, each tablespace will consist of multiple datafiles (4 is being considered) with each datafile on separate hardware mirror sets. Locally Managed Tablespaces (LMTs) will also be used.

I am interested in feedback on this option - eg past experiences, comments, other alternatives etc.

Also, do you know of any references / whitepapers on this topic?

So far I have found the following:
http://www.revealnet.com/newsletter/disk_io_distribution.doc which suggests "Physically stripe the temporary tablespace across multiple disks by allocating multiple datafiles with sizes mapped to the tablespace's extent size (so each datafile contains exactly one extent)."

Metalink articles suggesting using minextent clause on tables / indexes and the need to ensure that the objects don't fit into just 1 extent - or in our case that the objects will require at least 4 extents.

Chapter 20 of the Oracle8i Designing and Tuning for Performance manual (8.1.7) under Striping Disks Manually.
and such commands as "ALTER TABLE ... ALLOCATE EXTENT DATAFILE '...' " but the manual reference only refers to when the datafiles are filled by 1 object.

Thanks,
Bruce Reardon
mailto:bruce.reardon_at_comalco.riotinto.com.au

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Reardon, Bruce (CALBBAY)
INET: Bruce.Reardon_at_comalco.riotinto.com.au

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

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: Kimberly Smith
INET: kimberly.smith_at_gmd.fujitsu.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------