SEGMENTS PART VII TEMPORARY SEGMENTS

Oracle has tons of Info on Metalink read and use that as you evolve

Oracle over a period evolved the temporary segments and their usage. Evolution is from permanent tablespace with temporary contents to temporary tablespace with tempfile to temporary tabelspace groups. Most importantly all these evolutionary phases are still available depending upon choices.

TEMPORARY Tablespace

Tablespace TEMPORARY

Locally Managed

Dictionary Managed

Impossible

Yes

Datafile

Yes

Impossible

Tempfile

The evolution of the tablespace TEMPORARY started with 7.3 and this tablespace TEMPORARY is created using datafile/s.

The evolution of the TEMPORARY Tablespace started with 8i and this TEMPORARY Tablespace is created with tempfile/s.

You identify a tablespace TEMPORARY/TEMPORARY tablespace in DBA_TABLESPACES for its CONTENTS of TEMPORARY type

Purpose of these Temporary segments

A tablespace TEMPORARY/TEMPORARY tablespace provides a single temporary segment shared by all users requiring
01. Sort operations and therefore sorts extents
02. Temporary extents for GLOBAL TEMPORARY Tables

This unique temporary segment improves the concurrence of multiple sort operations, multiple transactions on the same temporary table using the same temporary segment, reduces their overhead, and avoids Oracle space management operations altogether. These temporary segments are created after the instance is started (Oracle sits idle before the instance is started when you connect) and as soon as user/s starts a sort operation or when a temporary extent is required for the GLOBAL TEMPORARY table. This temporary segment is dropped when instance is shutdown. The following synonyms can be accessed to view the allocation and de-allocation activity of these temporary segments

01. V$SORT_SEGMENT
02. V$SORT_USAGE
V$SORT_USAGE helps you identify the transaction that is currently using the temp segments.

Please read Metalink Doc Id 102339.1 to understand more about the temporary segments usage and the benefits of using the TEMPORARY tablespace with tempfile/s

The following activities require the temporary segments:

01. Creation index
02. ORDER BY or GROUP BY clauses of SELECT statements
03. DISTINCT values of SELECT statements
04. UNION, INTERSECT or MINUS operations
05. Sort-Merge joins
06. Analyze command execution
07. Create primary key constraint, enable constraint, and create table
08. The creation of a new table can start as a temporary segment if MINEXTENTS is larger than 1 or when using the statement CREATE TABLE AS SELECT.
09. May be some other activities I am missing

Memory Usage is dependent on the server configurations.

Sorting requires space in memory. These memory areas are called sort areas. Every user session has its own sort area size. The "init.ora" SORT_AREA_SIZE parameter defines the maximum size of real memory (in bytes) that will be used by a single sort. Common settings of the SORT_AREA_SIZE parameter range between 64K and 256K for larger systems. The location in memory where this sort area is allocated depends on the configuration of the Server.

In the DEDICATED SERVER CONFIGURATION the sort area is allocated in the Program Global Area (PGA). Besides this sort area, the PGA also contains session- information (e.g. user-privileges), cursor-status (of that session) and stack-space (session-variables).

In the Multi-threaded Server Configuration the sort area is allocated in the User Global Area (UGA). This UGA lies in the Shared Pool of the System Global Area (SGA).

Space Management is dependent on the way the tablespace is configured.

Dictionary-Managed Tablespace (DMT)
(Extent Management by the data dictionary).

For a tablespace that uses the data dictionary to manage its extents Oracle updates the appropriate tables in the data dictionary whenever an extent is allocated or freed for reuse. A tablespace that uses the data dictionary to manage its extents has by default incremental extent sizes, which are determined by the storage parameters INITIAL, NEXT, and PCTINCREASE. When an object is created in the tablespace, its first extent is allocated with the INITIAL size. When additional space is needed, the NEXT and PCTINCREASE parameters determine the sizes of new extents. Because dictionary tables are part of the database, the space that they occupy is subject to the same space management operations as all other data.

This is the default method of space management in a tablespace. It was the only method available in Oracle releases 8.0 and earlier.

Locally Managed Tablespace (LMT)
(Extent Management by Itself from Oracle 8.1 onwards).

A tablespace that manages its own extents maintains a bitmap in each datafile to keep track of the free or used status of blocks in that datafile. Each bit in the bitmap corresponds to a block or a group of blocks. When an extent is allocated or freed for reuse, Oracle changes the bitmap values to show the new status of the blocks. A tablespace that manages its extents locally can have either uniform extent sizes or variable extent sizes that are determined automatically by the system. When you create the tablespace, the UNIFORM or AUTOALLOCATE (system-managed) option specifies the type of allocation. The storage parameters NEXT, PCTINCREASE, MINEXTENTS, MAXEXTENTS, and DEFAULT STORAGE are not valid parameters for extents that are managed locally.

When a TEMPORARY tablespace is created the formatting is different to datafile. The tempfile created is called sparse file. A sparse file is a file where any 'empty' O/S blocks do not actually take up a full block but have a marker to show the block is empty. The operating system then finds a free block for use when the block is populated with data.

An Oracle8 data file is filled with formatted blocks when it is created, but the middle of these blocks contains all ‘0’s, which may span one or more O/S blocks.

An Oracle8i TEMPFILE is NOT explicitly filled with '0's and can be created as a sparse file on some platforms.

This is correct behavior for this type of TEMPFILE

Advantages of LMT

01. Reduces recursive calls to the Metadata
02. Tracks adjacent free space, eliminating the need to coalesce free extents

Note:
When you create a tablespace, you choose one of these methods of space management. It is not possible to alter the method of space management later.

Disadvantages of DMT

01. Tablespace can have more than one temp segment and the creation of these segments is based on the transactions.
02. Repetitive allocations and de-allocations can fragment the tablespace and the tablespace needs to be coalesced often
03. SMON automatically coalesces when the PCTINCREASE is greater than one. The wake up of SMON takes some time and this degrades the performance

Who takes care de-allocation in LMT?

The background process SMON actually de-allocates the sort segment after the instance has been started and the database has been opened. Thus, after the database has been opened, SMON may be seen to consume large amounts of CPU as it first de-allocates the (extents from the) temporary segment, and after that performs free space coalescing of the free extents created by the temporary segment cleanup. This behavior will be exaggerated if the temporary tablespace, in which the sort segment resides, has inappropriate (small) default NEXT storage parameters.

Down side of it?

This means that Oracle 9i onwards if you are using LM TEPORARY tablespace in your database you are to plan the reboot schedule basing on the usage of the temporary tablespace and the max space allocated for the temporary tablespaces.

Note

If your SYSTEM tablespace is created as LMT you cannot have any dictionary-managed tablespace of any nature.

Guidelines as Prescribed By Oracle in Creating and Managing Temporary Segments:

01. Use LMT temporary tablespaces to avoid frequent allocation, de-allocation segments and thus fragmentation of tablespace/s
02. To avoid IO conflicts and contentions create multiple Temporary Tabelspaces
03. Stripe these tempfiles across physical disks
04. Create tablespaces with different storage clauses and assign to the users as required.
05. Always set initial = next when ever a temporary tabelspace is created as part the default storage parameters
06. To arrive at the NEXT value use this formula n*s+b where N is a positive integer S is the value for sort_area_size parameter value in the init.ora file
B is the value for db_block_size parameter value in the init.ora file.
07. In case you have Dictionary Managed SYSTEM tablespace and are ready to have permanent tablespace with TEMPORARY contents, then set the PCTIncrease to ZERO value (and set the MAXEXTENTS to unlimited while enabling the AUTOEXTEND on switch depending upon the file system. If you are using raw devices (?) for this you cannot have this privilege of switching on the AUTO EXTENSION so you are estimate the usage of temp tablespace by different users for different purposes and determine the max size that a temporary tablespace can grow and then create those many pieces and allocate them to the TEMPORARY tablespace. This estimation is to be made to meet the space between startup and shutdown of instance cycles).
08. If the INITIAL and NEXT extents are set to smaller pieces and the tablespace has multiple larger number of extents, SMON needs lot of time to startup the database when you have shutdown the database with ABORT option
09. Avoid sorts in indexes creation by trying to load the data in an order while loading the indexes column.
10. Create Indexes with NOSORT option
11. When parallelizing a query remember that you need to have lot of memory as each parallelized query shall have its own sort_area_size allocated and the memory and this leads to memory shortage in other areas of database.