| |
 |
|
Donald K. Burleson
Oracle Tips |
Oracle tablespaces
* 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.
This is an
excerpt by Mike Ault’s book “Oracle
Administration & Management”. If you want more current Oracle tips
by Mike Ault, check out his new book “Mike
Ault’s Oracle Internals Monitoring & Tuning Scripts” or
Ault’s Oracle Scripts Download.
|
