Re: dedicated tablespaces

Richard,

My comments are more on extent sizes rather than purpose of the table. There have been a number of comments over the last couple of weeks and months while I have been active on the news group, and presumably before I arrived.

Object management effects the data dictionary tables. When an Object extends, up until Oracle8i, the extent is allocated by modifying two data dictionary tables sys.uet$ (Used Extents) and sys.fet$ (Free Extents). Effectively, these two tables hold the mapping of the tablespace, what is free and what is used.

NB. Under Oracle8i you can have bitmap's in the header of the data files that controls extent management.

When a table is dropped (or truncated drop storage), the Free Extents table gets a record inserted and the Used Extents table gets a row deleted (multiple if more than 1 extent is released), reflecting the space being given back to the database.

Now when a new extent is required, by an object expanding, the Free Extents table is scanned looking for an extent size equal to that being requested. If found, then the extent is allocated within the data dictionary, and the operation continues. If an exact match is not found, then Oracle looks for a near match (rounded up to 5 blocks) and uses that size if available. If however, the match is not found, then Oracle has to break up a free extent into a smaller free extent and a used extent. Finally, if still no match, then Oracle has to coalesce the free space in the tablespace, and repeat the exercise.

Hopefully, you can see that space management is expensive, so if you have one size of extent in the tablespace, then space management is kept to a minimum.

The only reason for separating the two types of objects is if the online tables populate the archived tables, and then you might want to place these two type of objects onto different RAID sets for a purpose of speed.

Rgds
Andrew

BTW - My normal recommendation is to have 4 or 5 tablespaces on a single RAID set, a Small, Medium, Large, XLarge and if required XXLarge. Then I allocate the tables to one of these tablespaces, and default the storage clause to that of the Tablespace. In this way, you keep control of extent management. I also provide a SUPPORT tablespace that developers are allowed to create objects in for diagnostic reasons.

One of my rules that I work hard to have adopted is that the owner of the database objects (an Application User) is owned by the DBA department, and under normal circumstances, the Developers do not know the password to this account, so they cannot create (or drop) objects against the application system, in a way that GL holds the General Ledger tables, AP holds the Account Payable tables, and the users are registered and are allocated the required permissions.

Richard Baines wrote:

> Is there any benefit for setting up different tablespaces to store
> tables that will perform different roles. (using RAID by the way).
>
> We have a database used for general usage, lots of rows added to several
>
> tables and and regular deletions etc. We also have a set of archiving
> tables which are populated with data from the regular tables once a
> month, data is then exported from the archive tables, the archive tables
>
> are then truncated until the following archive occurs (standard sort of
> data archiving set up).
>
> Are there any performance benefits from putting the archive tables in
> their own tablespace. Is the regular populating of these tables and
> truncating of these tables adding to disk fragmentation in my main
> tablespaces.
>
> any experience/assisstance greatly appreciated
>
> Richard
Received on Tue Apr 20 1999 - 03:54:25 CDT