Single TS vs Multiple

From: Storey, Robert (DCSO) <"Storey,>
Date: Tue, 22 Dec 2015 17:03:53 +0000
Message-ID: <FE4C2B093843BB4B873D754E5E0BE4DB01998C26A1_at_DCSOSVMS02.dcso.org>

I'm working on a redesign of the structure for my database, which has been active since 2000. I have three main users/schemas that hold the data for the app (the only app running in the database).

User A has 341 tables spread across 9 tablespaces. The tablespaces have basically grouped like objects that support a specific area of the app. Also, each "data" tablespace has a corresponding "index" tablespace. So, 18 tablespaces to support those 341 objects

User B has two tablespaces. One TS has 44 objects, the other has 1 (an audtiting table and largest table in system). Each TS has it's own index tablespace.

User C owns all the PL/SQL. No physical objects.

User B is my schema that has about a 100 objects. One data TS and one index TS.

Then the assorted system TS, temp, undo, tec.

All tablespaces are supported by a single datafile. Again, same design since 2000 with no driving need to change. My Data TS total just about 40gig, indexes about 27gig

Major OLTP applccation. Tons of reads, fair amount of small writes.

Going to build a 12 disk RAID10 to support all the dat, so 6 mirrored groups, then striped. Two separate disks to handle the multiplexed redo.

So, other than organization purposes, is there any benefit to having multiple TS with their own datafiles, vs, fewer TS, each which might have a couple of datafiles? Or even just one datafile using the BIGFILE option within 11g? I can't take any one TS offline without taking down the app.

Granted, if a datafile for one of the 13 or so current TS goes bad, I only have to restore that file, instead of one really large one.

Thoughts?

--
http://www.freelists.org/webpage/oracle-l

Received on Tue Dec 22 2015 - 18:03:53 CET