Re: Tablespace layout

I mean all extents in any given tablespace except SYSTEM are the same size. Having a few different extent sizes, with each being a multiple of the next smaller (e.g. 5 MB, 20 MB, 100 MB), is a distant second choice - in my opinion. It is better than "random" extent sizes in a tablespace, but not nearly as good as one extent size per tablespace. In my opinion, there are only really two exceptions

One is if the data exhibits exponential growth. This is very rare. The one place I have seen it is in some scientific applications (e.g. a specific molecular modeling project), where the data generated during each iteration feeds the next. (e.g. Let N[m] = quantity of data generated during iteration m. N[m+1] = N[m]**X., for X > 1.)

The second is (perhaps) 3rd party applications that insist on putting many objects of wildly varying size in the same tablespace - and are not amenable to having the DBA relocate them. I've worked most of my career in software or systems development shops where we had complete control of object sizing and placement - not at all with CRM/ERP and only a little with other 3rd party software. In those few 3rd party systems that I have had to deal with where the DBA could not freely tinker with sizing/placement, I usually adopted the "second best" approach outlined above.

The thought of having dozens or even 100+ tablespaces and perhaps even a few hundred datafiles in, for example, a critical OLTP system of 100-400 GB doesn't bother me. As mentioned earlier by another poster, if the design and design standards are well-considered and well-documented, and maintenance activities are mostly automated and dynamic (e.g. no hardcoded datafile names in a backup script!), the administrative overhead can be very low - with the possible exception of designing and setting up the initial layout. Typical, for me, might be a very critical (basically) OLTP database of 100-400 GB with a hundred(+) tables, a few hundred indexes, 30-100 tablespaces and perhaps 50-300 datafiles - often on raw devices. I have worked as the only DBA at places where I had to support dozens of distinct systems and often a hundred or more databases when remotely administered client sites are included. Once the standards are set and everyone (especially the sys admins) buys off on them, it isn't that difficult. More time is required for the initial layout, but most of that time is in analysis and planning - time well spent anyway. (An ounce of prevention is worth a metric ton of cure!)

Often, the hardest task is in convincing the sys admins - since they often have to do a lot of the layout grunge work (e.g creating multiple stripe sets and a hundred or more raw devices - of the right size, in the right physical location, and named according to standards). My approach to this is to volunteer to do this myself - until the SAs begin to see the benefits and jump onboard. For example, in the last two shops where I spent the last six years, we used Veritos Volume Manager for almost everything Oracle and many or most production systems were on raw devices. I would create (vxassist, etc.) scripts for everything rather than spend hours wading through the GUI (vxva). In both shops, the sys admins soon became converts and took this over themselves. Then the most difficult task became in convincing some of the newer DBAs to discard their dogma - about multiple extents per object being inherently bad, about hardcoding backup scripts, and other such things.

Also worthy of consideration is that management must adapt, not simply dictate. Planning and building such a layout precludes management blind-siding (e.g. "I need this new system built before the end of the day" - when they have known about it for weeks, but didn't bother to tell you!). The best defense in the latter case is a well-defined, well-documented, and widely disseminated set of internal standards - accompanied by repeated (preferably written) forewarnings that it takes a minimum of N days to design and layout any new database.

-Don Granaman
[certifiable OraSaurus]

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, August 29, 2001 8:16 AM

>
> Don,
>
> By uniform extents, do you mean all extents in the same tablespace being
> the same size? Or do you mean having
> a choice of say three different sizes in on tablespace but sticking to
> those sizes? That seems to be the crux of
> the disagreement amongst our team.
>
> Thanks for your comprehensive reply.
>
> Cherie
>
>
>
> "Don Granaman"
> <granaman_at_home To: Multiple recipients of
list ORACLE-L <ORACLE-L_at_fatcity.com>
> .com> cc:
> Sent by: Subject: Re: Tablespace
layout
> root_at_fatcity.c
> om
>
>
> 08/29/01 02:10
> AM
> Please respond
> to ORACLE-L
>
>
>
>
>
>
> There is no advantage at all in making extent sizes a function of
> db_block_buffers.
>
> I agree, to a certain extent (no pun intended), with the idea of using
> "dedicated" tablespaces. I prefer to use dedicated tablespaces for all
> "significantly large" tables, where the value for "significantly large" is
> subjective. For example, in a very critical 200 GB OLTP database, it
might
> mean dedicated tablespaces for tables of 200 MB or larger, a few
> tablespaces - with uniform extent sizes - for smaller tables, and
dedicated
> tablespaces - also with uniform extent sizes - for indexes on larger
> tables.
> For example, ACCT_DATA, ACCT_INDX, CUST_DATA, CUST_INDX, ORDR_DATA,
> ORDR_INDX, STAT_DATA, STAT_INDX, etc. Another consideration is in how the
> contents of the tablespace are used. STAT_DATA, for example, might be
> reserved for largely static tables. There might be another tablespace for
> similarly sized tables that are very volatile.
>
> In general, my main considerations are:
>
> Object size
> uniform extents - ironclad rule for everything except SYSTEM, in all
> but
> most rare circumstances
> only a few different extent sizes for all app data and indexes in the
> entire database
> ignore the "multiple extents kills performance" myth (but keep it
> "reasonable"!)
>
> Volatility
> read-only data?
> objects continually dropped/truncated and recreated/reloaded
> intensely transactional?
> etc...
>
> I/O distribution
> a single non-dedicated tablespace should not contain objects likely to
> be in contention
> separate indexes and data (I/O effect is debatable, but index
rebuilds,
> etc. favor)
> tablespace design so as to support reasonably even I/O distribution
> across disks/stripe sets
> ability to (relatively) easily move datafiles to rebalance I/O
>
> Recoverability
> Preference for many tablespaces over very large multi-table
tablespaces
> Preference for more smaller datafiles over very large datafiles (files
> >
> 2 GB are very rarely needed)
>
> Partitioning
> For large partitioned objects - a tablespace for each partition
> Rolling temporal partitions - as above. Age off or move data by
> partition exchange
>
> Transportable tablespaces
> What, if anything , needs to be moved? (e.g to a warehouse staging
> area)
> At what frequency / data volume?
>
> Parallel server
> Access patterns, PCM lock allocation (pre-9i at least), etc.
>
> This works best with something like only three or four different extent
> sizes for everything - except perhaps rollback, system, and temp, and
> perhaps only three or four different datafile sizes for everything except
> perhaps control files and redo logs.
>
> The above is an "off the top of the head" list. I don't pretend that it
is
> comprehensive. However, it might generate some interesting discussion.
It
> seems that space management is sometimes almost a religious topic. I
> converted to the "uniform extents" philosophy early - about 1990 - and
> confess to being an evangelist.
>
> -Don Granaman
> [certifiable OraSaurus]
>
>
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Tuesday, August 28, 2001 5:28 PM
>
>
> I agree completely with Lisa. Several years ago I had a chance to talk
> with an Oracle tester in IBM's benchmark lab. They had a one table/one
> tablespace/one datafile layout, mostly for recoverability. Once you
> document it fully (and name it logically) administration is not that
> difficult.
>
> For those of you who know Oracle internals, is there any advantage in
> making the uniform extents a multiple of the db_block_buffers area in the
> SQA? Or is that just a feeling left over from when I had to align strings
> on full-words while programming assembler?
>
>
>
>
> "Koivu, Lisa"
> <lisa.koivu_at_efair To: Multiple recipients
of
> list ORACLE-L
> field.com> <ORACLE-L_at_fatcity.com>
> Sent by: cc:
> root_at_fatcity.com Subject: RE: Tablespace
> layout
>
>
> 08/28/2001 05:07
> PM
> Please respond to
> ORACLE-L
>
>
>
>
>
>
>
>
> Well Satar you didn't even read Cherie's email.
>
> Way back before I took that db to the tablespace layout you refer to
> (straight out of HOw to Stop Defragmenting and Start Living white paper) I
> had created a separate tablespace for each partition. It was an awful pain
> back then and the number of tablespaces I had was ridiculous. I can see
> your point with mttr and keeping your tablespaces fairly small in
> comparison to what it's become. I guess it's a tradeoff with pros and cons
> either way.
>
> However, I suggest you take that paper (above) and throw it their faces. I
> disagree about different sized extents in the same tablespace vehemently.
> I think they should be uniform, period, for the same reason you state - if
> disk is so precious, then use it wisely !! Plus, when your partition
> starts looking for another 100MB extent, you are out of luck.
>
> -- Or should I just leave them all in the same tablespaces as
> non-partitioned tables?
>
> What's your feeling on partitioning? I think if you are dropping data
> monthly, use partitions wherever you can. That way you are more assured of
> reusing your disk - however, then you are looking at a different
tablespace
> per partition.
>
> I believe at one point I had 2-3 partitions in each tablespace, rotating
> them (part1 in ts1, part2 in ts2, part3 in ts1, part4 in ts2, etc.) Maybe
> that will provide the compromise in functionality, disk space management
> and recoverability you seek.
>
> I do know your pain. Good Luck.
>
> Lisa Koivu
> Certified Monkey and DBA
> Ft. Lauderdale, FL, USA
>
>
>
> -----Original Message-----
> From: Satar Naghshineh [SMTP:Satar.Naghshineh_at_irvine.mellesgriot.com]
> Sent: Tuesday, August 28, 2001 4:30 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Tablespace layout
>
> Hi Cherie,
>
> Just shove all your data on a RAID 5 (great for data Warehouses) and
> forget about it. If that is not possible, then stick with what your DBA
> team has stated about everything being ok as long as the extents are
> multiples of one another.
>
> Regards,
> Satar Naghshineh
>
> -----Original Message-----
> From: Cherie_Machler_at_gelco.com [SMTP:Cherie_Machler_at_gelco.com]
> Sent: Tuesday, August 28, 2001 7:27 PM
> To: Multiple recipients of list ORACLE-L
> Subject: Tablespace layout
>
> I've been thinking a lot about our tablespace layout for our
> data warehouse. Our warehouse is approaching 200Gig
> and is almost out of space on disk. I'm getting another 75 Gig
> this weekend and am trying to plan the best use for that 75Gig.
>
> Our current data tablespace layout is that we have two tablespaces
> for small, medium, and large data tables and the same for indexes.
> So it's something like this:
>
> SM_DATA01
> SM_DATA02
> MED_DATA01
> MED_DATA02
> LG_DATA01
> LG_DATA02
> SM_IDX01
> SM_IDX02
> MED_IDX01
> MED_IDX02
> LG_IDX01
> LG_IDX02
>
> I think that theoretically, all of the tables in each of the
tablespaces
> were supposed to have the same sized extents when they were
> originally created. However, over time, there are multiple sizes
> of extents in the large and medium tablespaces. Even though we
> show significant free space, it is fragmented and coalescing can
> not put together enough contiguous space to reuse a lot of the
available
> space.
>
> Our largest tables are partitioned. However the partitions are not
> split
> out into separate tablespaces but go into the same medium and large
> tablespaces as non-partitioned tables. Theoretically, I suppose that
> this
> is not a problem if, when partitions are dropped every month, the
> resulting
> space is reused 100%. I'm not sure if it is.
>
> Here is my question. My DBA team members feel that it's fine to have
> tables with a variety of extent sizes in the same tablespace as long as
> they are all multiples of each other (50, 100, 400, 2000, etc.). My
> concern
> is that this setup is fine when the smaller tables need to extend but
> when
> the larger tables need to extend, they can't pull together enough
> contiguous
> space and I keep having to add more. I'd prefer to have only one size
> of
> extent in each tablespace and keep it very pure that way. Then I know
> every single extent can be reused. So I am considering increasing the
> number of tablespaces so we have something more like this:
>
> 1kdata
> 10kdata
> 100kdata
> 1mdata
> 10mdata
> 100mdata
> 1000mdata
> 1kidx
> 10kidx
> 100kidx
> 1midx
> 10midx
> 100midx
> 1000midx
>
> I would also probably split them out into at least two tablespaces for
> each
> level.
> Maybe not for the smaller sizes, but for the larger sizes.
>
> I have several issues I'm trying to keep in mind. One is the ease of
> maintenance
> for initial creation and ongoing upkeep. I don't want to have too many
> tablespaces
> if I don't need to. Another issue is mean time to recover. If we
> lose a
> single tablespace,
> I'd prefer to have to recover fewer files. The maximum file size we are
> using is 2Gig.
> We need to keep our recovery time under four hours total.
>
> Probably the biggest issue I'm facing now is the sheer size of the
large
> tablespaces.
> They are so big and bulky that it's almost impossible to reorg them or
> even
> just
> clean them up. I think that if I had more smaller tablespaces, I would
> have more
> options. This database is still at 8.0.4 and it's going to be a while
> before it can be
> upgraded so that limits my options for reorging as well. All cleanup
> has
> to be done
> in a series short Sunday windows. I don't have the luxury of a tool for
> doing this
> reorg so have to do it manually.
>
> Another issue is partitions. We are dropping the old partitions on the
> main fact
> table once a month. We are not currently planning on dropping any of
> the
> other partitions.
> The tables have a variety of partition names and schemes. Some are
> partitioned yearly,
> monthly, quarterly, half-yearly. There is no consistency. I'm
> debating
> whether I should
> split each partition out into it's own tablespace. That would be
> almost a
> hundred tablespaces.
> Or just the table that we're dropping partitions on monthly. That
> would
> be about 50 tablespaces.
> Or should I just leave them all in the same tablespaces as
> non-partitioned
> tables?
>
> We are using Sun Solaris 2.6 on an E10K. We have EMC disk and Veritas
> file manager.
> Using version 8.0.4 of Oracle, as I said. Using RMAN and Veritas for
> backups.
>
> Any feedback, ideas, suggestions, things to watch out for, think about,
> etc. would be greatly
> appreciated. This is going to take a lot of time and effort to do and
> I
> don't want to get all the
> work done and find out it doesn't work as well as I hoped and have to
> redo
> everything.
>
> Thanks for your time,
>
> Cherie Machler
> Oracle DBA
> Gelco Information Network
>
> --
> Please see the official ORACLE-L FAQ: <http://www.orafaq.com>
> --
> Author:
> INET: Cherie_Machler_at_gelco.com
>
> 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:
> INET: tday6_at_csc.com
>
> 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: Don Granaman
> INET: granaman_at_home.com
>
> 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:
> INET: Cherie_Machler_at_gelco.com
>
> 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: Don Granaman
  INET: granaman_at_home.com

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).