Re: Information of Tablespaces

"Ethan Post" <epost1NOepSPAM_at_yahoo.com.invalid> wrote in message news:21c698a0.5868ab4b_at_usw-ex0104-031.remarq.com...
> Jay,
>
> Check out the Oracle Conepts Manual. It is likely you will end
> up doing the following.
>
> Here is the minimum.
>
> 1. Separate all objects on to 2 tablespaces, one for indexes and
> one for data, make sure they are on different disk
> controllers/volumes.
>
> It tends to get more complex from there and everyone will have
> their own opinion of what is best. You could go to 4
> tablespaces, 2 for data and indexes associated with low activity
> tables and 2 for the data and indexes associated with high
> growth tables. There is somthing called "Locally Managed
> Tablespace" (I think) that you could look at using, this will
> help in preventing fragmentation with your fast growing tables.
> Also you are going to want to make sure you fast growing tables
> are grabbing big extents. I believe it is best to keep the same
> extent size for tables within a tablespace, thus one of the
> reasons you will want to put your larger tables on a different
> tablespace.
>
> This is just my opinion, I'm still a bit of a newbie, only 2
> years experience but this is the gist of what I have been seeing
> and reading in regards to this. Ideally you want to pick up a
> few Oracle books that go into more detail about design and
> optimization.

Don't disagree with this at all.

Most Oracle material will recommend a minimum of six tablespaces: SYSTEM (obviously!), DATA, INDEX, RBS (purely for rollback segments), TEMP (purely for Users to swap their big sorts down to) and one called TOOLS (which is a sort of 'misc' tablespace, used for storing tables and such like which allow shrinkwrapped applications to do their stuff, but where those tables don't contain any data which is actually of interest to you).

The key thing in such a design is to keep data separate from indexes, and rollback segments and temp sorting space separate from everything else and each other, otherwise you'll be facing i/o contention issues.

However, this is just a starting point. As Ethan correctly notes, you also have to worry about different access patterns to your data... you don't want *all* your tables in a single DATA tablespace, because some get thrashed day in day out, and some don't change for weeks on end. So, create DATA1 for thrashed tables, and DATA2 for static ones. And if you split your DATA tablespaces, you'll also want to make a corresponding split to your INDEX tablespaces.

You will then notice that whilst all your 'thrashed' tables are constantly being updated, some grow quickly, others not so quickly and so on -so some tables will need larger extents than others. Again, Ethan is correct -odd-sized extents within a tablespace will render you liable to future tablespace fragmentation if you ever start doing truncates or drops. So, you'll need a further sub-division of DATA tablespaces (and hence also of INDEX tablespaces) into 'thrashed-big extents', 'thrashed-medium extents', 'thrashed-small extents', 'static-big', 'static-medium' and 'static-small'. You should be able to assess your own data and determine whether any or all of these subdivisions are actually necessary for your own situation.

Depending on the number of concurrent Users, you may also need two or more TEMP tablespaces -contention issues again arise if 1000 Users all run giant reports simultaneously, and all therefore try to swap down to the one TEMP tablespace. Instead, have different groups of Users writing to different TEMPS. You may also need to subdivide your RBS tablespace. If you do batch processing, you'll need relatively few, but relatively huge, rollback segments; if you do OLTP, you'll need lots of relatively small rollback segments. And if you live in the real world, you'll probably need to cater for both situations in the one database -and on no account should the two sorts of segment reside in the one RBS tablespace.

Finally, you may have tables which are so static they never get written to at all. If they reside in the same tablespace as all your other tables, they'll be included in the usual backup process, which is really a waste of time and effort. So you may wish to rescue them out into their own tablespace (say, DATA_RO), which you can backup once, then turn into proper readonly tablespace, and never backup again. And again, you may need to consider subdividing this tablespace into RO_BIG, RO_MEDIUM and RO_SMALL etc. Indexes on such tables should also be moved into their own separate INDEX_RO tablespace.

Also (and really finally this time!), you may need to consider i/o issues for load balancing. A tablespace can be made up of multiple datafiles, and it is possible to force extents for a table to be created on specific datafiles within the tablespace. In this way, a single tablespace can have its i/o spread across multiple disks. However, it is possible to get much the same sort of effect by having multiple tablespaces. So you might sub-divide your THRASHED-BIG tablespace into T-B1, T-B2, T-B3 and so forth, simply to ensure that all your most popular tables don't happen inadvertently to end up on just one or two heavily over-subscribed disks.

In short, Oracle material will recommend to you that you have lots of tablespaces, and you shouldn't be afraid of subdividing where i/o issues, performance issues, load-balancing concerns and management (ie, backup and recovery) issues warrant it.

Regards
HJR
>
> -Ethan
> http://www.freetechnicaltraining.com
> http://www.gnumetrics.com
>
>
>
>
> -----------------------------------------------------------
>
> Got questions? Get answers over the phone at Keen.com.
> Up to 100 minutes free!
> http://www.keen.com
>
Received on Sun Aug 06 2000 - 00:00:00 CDT