Oracle Tips by Burleson

Oracle tablespaces

These are disk space and create-script-related issues. The number of potential applications will drive the number and size of database tablespaces above and beyond the eight base tablespaces. You will see that these are:

• SYSTEM. Contains files owned by the SYS and SYSTEM user.
   
• TOOLS. Contains files usually owned by SYSTEM but that apply to the Oracle developer’s toolset; these files contain base information and details of forms, reports, and menus.
   
• ROLLBACK. Contains the private rollback segments; its size will depend on number of rollback segments and expected transaction size. May also be an Oracle-configured UNDO tablespace, which means you will also have to plan for how long UNDO data should be retained to allow for flashback query.
   
• DEFAULT USER. Tablespace in which users can create and destroy temporary, nonapplication-related tables such as those used in SQL*REPORT for intermediate queries.
   
• TEMPORARY USER. Tablespace for sorts, joins, and other operations that require temporary disk space for intermediate operations. If this tablespace is not available, and default tablespace is not set for each user, these tables will be created and dropped in the SYSTEM tablespace, resulting in fragmentation. Additionally, a poorly designed join or overly ambitious SELECT statement could result in filling the SYSTEM area and halting the database.
   
• UNDOTBS. IN Oracle9i databases created from default templates in the Database Creation Assistant (DBCA) rollback segments are placed in the UNDO tablespace and are now referred to as UNDO segments. Oracle uses automated management of these UNDO segments by default. This UNDOTBS is used by Oracle9i to hold UNDO segments.
   
• CWMLITE. This Oracle9i tablespace is used to store OLAPSYS schema objects for the new Online Analytical Processing (OLAP) utilities in Oracle9i and is created when the default templates in the DBCA are used to create a database.
   
• DRSYS. Used to store CTXUSER and WKSYS schema objects used in advanced indexing options and workspace management utilities and is created when the default templates in the DBCA are used to create a database.

Each application should have its own set of data and index tablespaces. If there are several small applications, you might want to put them in a single large tablespace; but if you can avoid this, it makes application management easier. Each application should also have its own index tablespace. This results in a simple formula for determining the number of tablespaces:
5 + 2 times the number of applications expected

Some applications may require multiple tablespaces, for example where, for performance, you want to separate out large tables from the rest of the application. In one case, a single application generated 13 tablespaces. Most applications aren’t as complicated as this and will only require two tablespaces. Of course, the purists will claim each table should be in its own tablespace, but this often is overkill. If you are looking at Oracle Applications installs, there may be over 100 tablespaces configured.

How to size tablespaces is a difficult question to answer because each tablespace will have unique requirements. Here are some general guidelines:

• The SYSTEM tablespace, if you split out the tool tables, should only require 300 to 400 MB of disk space, this has increased dramatically due to increased Java and other stored procedures in the Oracle9i system.
   
• The TOOLS tablespace will depend entirely on the amount of development you expect. At one site with 16 applications being developed, nearly 90 MB were required for the TOOLS tables.
   
• The ROLLBACK tablespace will again be driven by the number and size of rollback segments you require. The number and size of rollback segments is driven by the number of transactions per rollback segment, the number of users, and the maximum size of nonbatch transactions. With Oracle8i and Oracle9i, you can create a large rollback segment and leave it offline until it is needed for a large transaction, and then use the SET TRANSACTION USE ROLLBACK SEGMENT command to utilize it after bringing it online. The number of rollback segments is driven by the number of expected transactions and can be estimated by the equation:
  
  NUMBER OF DML TRANSACTIONS / TRANSACTIONS PER ROLLBACK SEGMENT
   
• The number of transactions will be driven by the number of users and types of database operations they will be doing. In fact, if the Oracle kernel sees a violation of the above formula, it will bring online any available public rollback segments. In Oracle9i you should also consider if you wish to use the UNDO tablespace, which takes the place of the rollback segments if it is configured. By default Oracle9i sizes the UNDOTBS at 200 megabytes.
   
• The DEFAULT USER tablespace size will depend upon the number of users you want to assign to it and the estimated size of tables they will be using. In most cases, 10 to 20 MB is sufficient. If you expect heavy usage, assign quotas to each user.
   
• The TEMPORARY USER tablespace should be up to twice the size of your largest table, if you use RULE-based optimization and up to four times the size of your largest table for COST-based; it is also dependent on the number of users and the size of sorts or joins they perform. An improperly designed join between large tables can quickly fill a temporary area. For example, an unrestricted outside join of 2,000 row tables will result in a 1-million-row temporary sort table. If those rows are each several hundred bytes long, there goes your temporary space. Unfortunately, there isn’t much that can be done other than to train developers or ad hoc query generators not to do unrestricted joins of large tables. If a temporary tablespace gets filled, the users who are assigned to it cannot perform operations requiring temporary space; or, worse, the temporary space may be taken from the SYSTEM area. There is a valid argument for having several temporary areas if you have a large number of users. In one instance, a 100-MB temporary tablespace was completely filled by a single multitable outside join using DECODE statements.
   
• The CWMLITE and DRSYS tablespaces are usually sized at around 20 megabytes by default.

If you have the disk space, placing the TEMPORARY USER tablespaces on disk assets of their own will improve query and report performance due to reduction of disk contention, especially for large reports or queries using disk sorts.

Expert Remote DBA BC is America's oldest and largest Remote DBA Oracle support provider.  Get real Remote DBA experts, call BC Remote DBA today.