Subject:            CONDUCTING THE TEMPORARY TABLESPACE SHUFFLE
Author:             ETAFEEN
Last Revision Date: 13 June      1995



               CONDUCTING THE TEMPORARY TABLESPACE SHUFFLE

Oracle often requires temporary work space for intermediate stages
of database processing. These areas are referred to as temporary
segments and are allocated as needed during a user operation.
A DBA may occasionally need to find additional database space on disk
for temporary segments that are larger than normally anticipated.
This article describes ways in which disk space currently allocated
to the database can be managed or even 'borrowed' to accommodate
the creation of large temporary segments.

The following SQL operations may require the use of a temporary
segment:

       * CREATE INDEX

       * SELECT .... DISTINCT, ORDER BY, GROUP BY, UNION,
                     INTERSECT,MINUS

       * NONINDEXED JOINS

       * CERTAIN CORRELATED SUBQUERIES

If the table/index can be sorted in memory, then the sorting method is
called internal. If the table is very large the sorting process is
external to main memory and requires disk storage. The INIT.ORA parameter
SORT_AREA_SIZE influences whether the sort is performed in memory
or on disk.  If the amount of data to be sorted is greater than
the allocated sort area, the data is divided into smaller pieces.Each
sort piece is then sorted individually and stored on disk in the form
of a temporary segment. These temporary segments are merged amongst
numerous sort passes and eventually merged into a final sorted result.

As discussed, two work areas influence the sort/merge routine:

     INTERNAL    amount of main memory as specified by the
                 INIT.ORA parameter SORT_AREA_SIZE.

     EXTERNAL    temporary segments used for storing
                 sorted runs before and during the merge passes.

Please note an increased SORT_AREA_SIZE will minimize the creation of
temporary segments and the necessity of available disk storage.
Some operating systems impose limits on the allocation of main memory.
In these situations, the creation of large temporary segments is
inevitable.

For example, creating a large index requires temporary segments.
To satisfy the 'CREATE INDEX ....' sql statement,the RDBMS
performs a sort operation to populate the index in the desired order.
If resource constraints on main memory exist, the sort operation will
result in the creation of one or more temporary segments on disk.
Temporary segments can consist of multiple extents. If the sort
requirements are great, temporary segments will grow by allocating
additional extents. Each extent is comprised of a contiguous set of
Oracle blocks.

If there's insufficient contiguous space on the database to allocate
the next extent, the following error will occur:

   ORA-1547 'Failed to allocate extent of size ....
             in tablespace <tablespace name>'

Where size is the number of contiguous Oracle blocks the RDBMS attempted
to allocate and <tablespace name> is the target tablespace to
contain the required temporary segments.

There are a number of ways to provide adequate temporary
storage. One approach is to allocate another data
file to the tablespace, thereby increasing the amount
of contiguous free space in the tablespace. This space is now
permanently allocated to the tablespace whether or not the space is
normally needed. Another option is to use the ALTER USER command to
point the given user's temporary segments to another tablespace that
contains more contiguous free space. However, space may not be adequate
in the other tablespaces. Perhaps disk space outside the database can
be temporarily borrowed by creating a new tablespace, altering the
user's definition to point to this tablespace for the creation of
temporary segments, and then subsequently dropping the tablespace after
the operation completes. If the additional disk space is not available,
a more complex form of the 'tablespace shuffle' is required.

The 'tablespace shuffle' is useful for creating indexes on large tables
especially in database environments that haven't been sized for
exceptionally large temporary segments. In the adjoining side bar you'll
find specific steps that demonstrate how to alleviate this problem.
Below I'll address the procedure in detail.

Initially identify a user tablespace that doesn't contain database objects
relevant to the creation of a specific index. Namely, the tablespace
should not be SYSTEM, house the target table, nor contain any in-use
rollback segments. In addition the DBA should approximate whether the
elimination of this tablespace will free up enough storage. The tablespace
is then taken offline. If the keyword 'IMMEDIATE' is used, the database
must be in archivelog mode. When the tablespace is brought online media
recovery will be required.

After the tablespace is taken offline, an operating system backup is per-
formed on all associated data files. Ater verification, delete those
data files.

Create a new tablespace that will be used exclusively to build temporary
segments for your 'CREATE INDEX' statement. The data file(s)
specified should point to the same disk that contained the recently
deleted file(s). You may also want to access space from additional disks.

Once the tablespace is created, alter the user's definition to point to
this tablespace for the default creation of his temporary segments.
During index creation, monitor temporary segment space requirements by
issuing:

      SELECT SEGMENT_NAME, BYTES, EXTENTS FROM SYS.DBA_SEGMENTS
      WHERE SEGMENT_TYPE='TEMPORARY'

Note that temporary segment names are in the form of 'file#.start block#'.
Determine how much free space remains in the tablespace with:

      SELECT MAX(BYTES) FROM SYS.DBA_FREE_SPACE
      WHERE TABLESPACE_NAME='...........'

After the index is successfully created, modify the user definition to point
back to his original temporary tablespace default and drop the recently
created tablespace. Delete these data file(s) and restore the above backups.
Bring the offline tablespace online and if necessary perform media recovery.

*  ALTER TABLESPACE ts1 OFFLINE;

*  CREATE TABLESPACE temp DATAFILE '...............' SIZE .....
   DEFAULT STORAGE (INITIAL 50K NEXT 50K PCTINCREASE 0);

*  ALTER USER abc TEMPORARY TABLESPACE temp;

*  CREATE INDEX .............;

*  ALTER USER abc TEMPORARY TABLESPACE ......;

*  DROP TABLESPACE temp;

*  ALTER TABLESPACE ts1 ONLINE;

COMMON QUESTIONS:

Q1. Are there any rules to estimate TEMPORARY extent needs?

A1. You calculate the estimated space for TEMPOARY segments as you would for
any other tables.  The worse case will to the same size as table A (the 4
million row table).  If the GROUP BY of 26 columns is a subset of table A, then
it will be smaller.  To calculate do the following:

- block overhead = 24 (cache header) + 19 (fixed itl header) + 14 (data hdr)
                 = 57 bytes

  Note: the 4 bytes at the end of the block have already been taken account
        of in the 24 bytes for the cache header.
        add 4 bytes per table directory entry. (4 bytes only if table not
                                                not clustered)

  block size - block overhead - table dir - block - free space

So for example, at 10% free space with 2048 block size and 57 bytes of overhead
(assuming 1 itl), non-clustered we get

bas  = 2048 - 57 - 23 - 4 - 2x - (2048 - 57 - 23 * .1)
     = 1964 - 2x - 196            ^^^^^^^^^^^^^^^^^^^
     = 1768 - 2x                  calculated on block - some overhead stuff

#r/b = x = 1768 - 2x - x * max(9, avg row size)

       assume avg row size = 3

then

           2x + 9x = 1768
            x      = 1768 / 11
                   = 160

therefore, #blocks = #rows / #r/b

So for 10000 rows, #blocks = 10000 / 160
                           = 62 blocks

For your 27 column group by you will need to come up with a row length.  This
may be larger than the 2048 byte block size.  Do the following calculation:

assume avg row size (which note includes space for column overhead and row
flags) is 4000 bytes. Then

#r/b = 1768 - 2x - 4000x
     --> 4002x = 1768
             x = 1768 / 4002
            x = .441

     -->  1 / .441 = # blocks/r

Q2. How does the sum clause operate in this situation and how much space would
    it need? (the size of the table, etc..)

A2. I doubt that the sum function will need much space, it will be a running
    total after the group by is computed off the temporary segment.

Q3. Same as #2 but with group by's. I guess what he is hoping for is a high
    level algorithm as to what operations are involved and how they are
    performed.

A3. Use the formulas outlined under Q1 above.  Anytime you use a GROUP BY,
    ORDER BY, or join tables, you will need a sort area size and if the
    sort/join cannot be computed in memory (number of rows in table will
    not all fit into the memory allocated by SORT_AREA_SIZE), a temporary
    segment will be created.