NyQuest: The OneStop for Banner® Technical Support

Oracle Locally Managed and UNDO Tablespaces

LMTs

Prior to Oracle 9i, all tablespace extent information was stored in the data dictionary of the SYSTEM tablespace. This type of Dictionary Managed Tablespace used a lot of reads and writes to this all-important tablespace, which can increase chances of something going wrong.

Starting with Oracle 9i, the extent information for each LOCALLY MANAGED TABLESPACE (LMT) is removed from SYSTEM and stored within each LMT individually. This improves performance by allowing each tablespace to handle extent allocation separately and taking a lot of work away from the SYSTEM tablespace.

There are three different types of LMTs, all of which are supported under Banner® 6.x

USER - The LMT acts like a standard Dictionary Managed Tablespace, except that extent allocation is done at the datafile level.

SYSTEM - Oracle allocates the extents based on internal algorithms.

UNIFORM - All extents are fixed and of the same size. The size is specified during at the tablespace creation.

Of these three types, it is the latter, Uniform LMT that offers the most performance gains. Also note that storage clauses should not be specified for each individual object.

To create a LMT of uniform extent, add the clause 'EXTENT MANAGEMENT LOCAL UNIFORM SIZE [SIZE] to the CREATE TABLESPACE SQL as shown below:

create tablespace FGBTRND_DATA
datafile '/u01/oradata/PROD/fgbtrnd_data_01.dbf' size 2048M
extent management local uniform size 1024K;

When creating tablespaces, also try to limit each datafile size to 2GB, as anything larger may be problematic when you try to move these files around.

UNDO Tablespaces

Remember running out of rollback segments, or hitting MAXEXTENTS? Well in Oracle 9i they made a very good effort to eliminate these problems. Instead of having the DBA create segments manually, in 9i the DBA specifies a tablespaces to be used exclusively for rollback & read consistency purposes, and Oracle will automatically create segments in there as needed. These tablespaces are known as UNDO Tablespaces. If some user executes a long running query, for example, Oracle will automatically create a more segments in the UNDO tablespaces, and get rid of them once the query finishes. UNDO tablespaces are also supported in Banner® 6.x.

To utilize UNDO tablespaces, perform the following steps:

1. Create the UNDO Tablespace using CREATE UNDO TABLESPACE SQL command as shown below. You can also specify this tablespace as an LMT.

create undo tablespace UNDO1
datafile '/u01/oradata/PROD/undo1_PROD_01.dbf' size 2048M
extent management local;

2. Add the following lines to the initSID.ora file:


UNDO_MANAGEMENT=AUTO

UNDO_TABLESPACE=UNDO1 (or whatever you called it above)

UNDO_RETENTION=3000

3. Restart the instance.

If you find that you are getting Snapshot Too Old on some longer running queries, increase the value of UNDO_RETENTION. This is the amount of time in secords that Oracle will keep information in the virtual rollback segments.

You can also query the status of these segments from by doing a SELECT on DBA_ROLLBACK_SEGS.

The contents of www.nyquest.com are Copyright © 2007 by Nyquest Consulting.
Sungard® and Banner® are registered trademarks of Sungard® Corporation.
Nyquest is not affiliated in any way with Sungard®.
All Rights Reserved.