Re: Large vs. Small Tablespaces?

That's exactly what tablespaces are for: separating different segment types from each other (as you've already done); separating segments of the same type from each other because they behave different (some are updated frequently, some rarely); separating segments of the same type from each other because they grow differently (some require new extents of the multi-megabytes, some of the tens of kilobytes); separating segments of the same type from each other because (and here's what you're really after) they get managed differently (some get backed up daily, some weekly, some monthly).

So I would suggest that what you propose is a good idea. The tablespace is the smallest unit of backup, effectively, so the more of them you have, the more flexibility you have in the way you conduct your backups. Also, of course, if you're doing O/S hot backups, placing a tablespace into hot backup mode causes block-level redo to be generated. If you've a few large tablespaces instead of many smaller ones, the redo subsystem is going to be that much more stressed by performing backups. It's definitely a good idea to put as small a part of the database into hot backup mode at a time as you can meaningfully manage.

In an ideal world, it would be nice to put the various datafiles on multiple hard drives, for performance reasons. But you'll get the backup management flexibility anyway, even if they are all on the one drive.

It is possible to go too far: the more data files you have (ie, the more tablespaces you have), the more work CKPT has to do at each checkpoint, and that's a potential performance problem. But you'd have to have hundreds of data files before I reckon you'd notice the degradation.

Otherwise, there is no performance penalty I can think of, and you'd be well-advised to go ahead and do what you propose.

Regards
HJR

--
----------------------------------------------
Resources for Oracle: http://www.hjrdba.com
===============================


"gdas" <gdas1_at_yahoo.com> wrote in message
news:7a4ed455.0201090717.46e16f04_at_posting.google.com...


> Hi,

>

> Right now we have 4 large tablespaces.  2 of those tablespaces are

> index tablespaces.  All of the table data is stored in the other 2

> tablespaces.  The largest 'data' tablespace contains 50 ~500 MB

> datafiles and the largest index tablespace contains 20 ~500 MB

> datafiles.

>

> I'm in the midst of implementing a hot backup script and due to the

> fact that I need to put the tablespaces into backup mode, backup the

> datafiles and then reset the state of the tablespace to normal...  I

> am wondering if it would be beneficial if I segmented my tables across

> more smaller tablespaces (even though they would be on the same

> physical disks that they are on now), in order to minimize the amount

> of time each tablespace has to be in backup mode since there would be

> fewer files to backup for each tablespace.

>

> Is this a sound idea?  Will there by any issues?  For example if

> scenario 1 has Table A and Table B are in tablespace 'USERS'  and

> scenario 2 has Table A in tablespace 'USERSA' and Table B is in

> tablespace 'USERSB'...  and both scenarios have the datafiles for all

> tablespaces in question on the same physical disk. Assuming there are

> queries that join table A to Table B, will one of the above scenarios

> perform better than the other?  Are there any other issues with using

> this approach that I might be concerned about?

>

> Thanks,

> Gavin