ss

Oracle Tips by Burleson

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.

See Code Depot