RE: Single TS vs Multiple

From: Mark W. Farnham <mwf_at_rsiz.com>
Date: Wed, 30 Dec 2015 12:05:49 -0500
Message-ID: <00e701d14324$5678b550$036a1ff0$_at_rsiz.com>

I am a member of BAARF as well. I do not consider this a religious oath. Likewise the non-guessing group. I will violate either for good company and/or appropriate liquids.

Now, about useful considerations, and forgive me if any of this has been discussed already in the thread.

Premises:

p1) Most data in the world has a decaying pattern of frequency of use based modally on its "born on" date.
p2) Moving the under-laying file at the Operating system, volume manager, or data virtualization layer is much, much less work and is much faster than other methods.
p3) A tablespace is the organizational unit that binds a file to particular objects (tables, indexes, out of line lobs, etc.) to a known set of files.
p4) Partitions (whether you have them yet or not) allow storing different tuples of data in different tablespaces.
p5) Storage media exists with varying performance and capacity profiles versus cost. 

Conclusions:
c1) It is well worth considering at the initial creation of a database AT LEAST the separation of data that has an aging characteristic that may in future be used as part of a partition key by tablespace for the purpose of organization of data by age so that older data may be moved to storage of lesser price (or newer data created on storage of higher price) with a minimum of overhead.

c2) It is well worth considering isolating data in service of an application that may in future be separated to a different database by tablespace (so that it may be moved either by transportable tablespace or cloning the entire database and dropping the tablespaces and files you don't need.)

This is quite apart from whether or not your database will perform faster or more cheaply on day 1 with more than the minimum number of tablespaces absolutely required to load it up. That is mostly a discussion of whether batch jobs that must run concurrently with anything else and/or certainly at a disjoint time for some jobs can be accelerated by tablespace and file separation of the bits required for a particular batch job. (Not the [pejorative redacted] suggestion made from time to time that an indexed read of a table would benefit from separation of its indexes into a different place.) The topic of separation of objects for performance is most likely a case by case consulting project.

A clue by example about what to look for regarding aging data: Transactional data tends to have a very steep aging characteristic, reaching near mothball status for almost everything after about 27 months. (Two full years plus a quarter). Lookup tables tend to age slowly in total an unpredictably by row. Quantitative analysis of future data aging requires a time machine, but usually a very solid qualitative prognosis can be made for almost everything in a database. For the hard to guess bits: 1) if it is small, throw it in the mix master 2) if it is or will become big, consider tablespace isolation.

All of this is a classic slippery slope: Ideally you want as few tablespaces as possible without causing yourself an annoyance later.

Quite apart from anything else, the exercise of making these considerations and having the relevant discussions and documentation with development staff and the folks who defined the need for the system in this context have a high potential to deploy the system more effectively than otherwise. Ideally such discussions take place before the first sprint of atop the first waterfall so a better system may be built and clues about what hardware and how to deploy it may be discovered.

Regards and good luck,

mwf

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Hans Forbrich Sent: Wednesday, December 30, 2015 9:51 AM To: oracle-l_at_freelists.org
Subject: Re: Single TS vs Multiple

Many of us have decided to keep it as a silent boycott. Note on that page, the final comment is "But no more discussions."

/Hans

On 30/12/2015 5:38 AM, Stefan Knecht wrote:
> Surprised this hasn't come up yet:
>
> http://www.baarf.com/
>

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


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

Received on Wed Dec 30 2015 - 18:05:49 CET