PURPOSE
-------
This bulletin describes Oracle's use of different kinds of temporary segments,
under which circumstances they are created and various issues regarding them.
SCOPE & APPLICATION
-------------------
This document is intended for database administrators who want to review
general information about temporary segments in the database.
CONTENTS
--------
Introduction
1. Temporary Tables
2. Temporary LOBs
3. Temporary Segments as work area for sorting
4. Temporary Segments for permanent segments creation
5. Temporary Tablespaces
Introduction
------------
Oracle often requires temporary work space for intermediate stages of database
processing. There are different kinds of temporary segments in the database.
Some of them are created explicitly by the users. The others are created and
accessed for the user by the system.
There are SQL operations containing a sorting step which require temporary
segments. However, segments used for sorting are not the only segments having
SEGMENT_TYPE=TEMPORARY. Temporary segments can also exist for permanent segments
creation.
--> Temporary segments for sorting are created in the default temporary
tablespace of the user. This tablespace may be of type TEMPORARY or
PERMANENT. A TEMPORARY tablespace (Locally Managed Tablespace) is
recommended for sort operations.
--> Temporary segments for permanent segments creation are created in the
tablespace specified in the create statement or in the user’s default
tablespace.
1. Temporary Tables
--------------------
In addition to permanent tables, you can create temporary tables to hold
session-private data that only exists during a transaction or session. Oracle
can also allocate temporary segments for temporary indexes created on temporary
tables.
The segment for a temporary table is allocated when the first INSERT into that
table is issued. The SEGMENT_TYPE of this segment is TEMPORARY.
This segment is allocated in the default temporary tablespace of the user
creating the temporary table. Note that this is the default temporary tablespace
defined in the create/alter user statement that may be of type PERMANENT.
Oracle drops the segment for a transaction-specific temporary table at the end
of the transaction and drops the segment for a session-specific temporary table
at the end of the session.
DML statements on temporary tables do not generate redo logs for the data changes.
However, undo logs for the data and redo logs for the undo logs are generated.
Refer to:
Note: 68098.1 Oracle8i Overview of Temporary Tables
Note: 93756.1 Oracle8i What are Temporary Tables and some examples
Note: 94402.1 ORACLE8i Temporary Tables and Redo Generated
Oracle8i Database Concepts / Chapter 10
Oracle9i Database Concepts / Chapter 11
2. Temporary LOBs
------------------
The goal of temporary LOBs is to develop an interface to support the creation
and deletion of lobs that act like local variables.
--> Temporary LOBs are not associated with any table
--> are only accessible by their creator
--> have locators (means to access a LOB)
--> are deleted when a session ends
Use the view V$TEMPORARY_LOBS in conjunction with DBA_SEGMENTS or V$SORT_SEGMENT
to determine how much space is being used by temporary lobs.
A user can create a temporary BLOB or CLOB and its corresponding index in the
user’s default tablespace calling DBMS_LOB.CREATETEMPORARY procedure.
Refer to:
Note: 74155.1 Oracle8i Temporary Large Objects
3. Temporary Segments as Work Area for Sorting
-----------------------------------------------
When processing queries, Oracle often requires temporary workspace for
intermediate stages of SQL statement execution. The sort area is allocated
in memory. If the sort operation needs additional memory (above the value
specified by the SORT_AREA_SIZE parameter), the sorted rows are written to disk
to free up the sort area so that it can be re-used for the remaining sort.
Oracle automatically allocates this disk space called a temporary segment.
The following statements may require the use of a temporary segment for sorting:
--> CREATE INDEX
--> SELECT ... ORDER BY
--> SELECT DISTINCT
--> SELECT ... GROUP BY
--> SELECT ... UNION
--> SELECT ... INTERSECT
--> SELECT ... MINUS
--> ANALYZE TABLE
--> Unindexed joins
--> Correlated subqueries (the execution plan may show sorting as a Sort Merge
Join).
Temporary segments for sorting are created in the default temporary tablespace
of the user issuing the statement. This tablespace may be of type TEMPORARY or
type PERMANENT. A type TEMPORARY tablespace (preferably Locally Managed
Tablespace) is recommended for sort operations.
Refer to:
Note:177334.1 Overview of temporary segments
Note:102339.1 Temporary Segments: What Happens When a Sort Occurs
Note:61997.1 SMON - Temporary Segment Cleanup and Free Space Coalescing
Oracle8i Database Concepts / Chapter 4
Oracle9i Database Concepts / Chapter 3
4. Temporary Segments for Permanent Segments Creation
-----------------------------------------------------
Besides sort operations, there are other SQL operations, which also require
temporary segments:
--> CREATE PRIMARY/UNIQUE KEY CONSTRAINT
--> ALTER TABLE ... ENABLE PRIMARY/UNIQUE CONSTRAINT
--> CREATE TABLE STORAGE (MINEXTENTS>1)
--> CREATE TABLE AS SELECT
--> The CTAS creates a data segment in the target tablespace and marks this
segment as temporary in dictionary. On completion, the dictionary type
is changed from temporary to table. In addition, if the SELECT performs
a SORT operation, temporary space may be used as for a standard select.
--> For a Parallel CTAS statement, each slave builds its own data segment
(marked as temporary in the dictionary) from the row source which feeds
it.
Similarly, for Parallel Direct Load or Parallel Insert, each slave
process creates its own single temporary segment to load data into.
--> CREATE PARTITION TABLE
--> ALTER TABLE ... SPLIT PARTITION
--> CREATE SNAPSHOT
--> CREATE INDEX
The CREATE INDEX statement, after sorting the index values, builds a
temporary segment in the INDEX tablespace; once the index is completely
built, the segment type is changed to INDEX.
--> ALTER INDEX REBUILD
During an index rebuild, besides the temporary segments used to store
partial sort (segments built in the user's default TEMPORARY tablespace),
Oracle uses a segment which is defined as a temporary segment until the
rebuild is complete.
Once this segment is fully populated, the old index can be dropped and the
temporary segment is redefined as a permanent segment with the index name.
The new version of the index, currently a temporary segment, resides in the
tablespace where the index is required. Note that the old index segment that
is to be dropped is itself converted to a temporary segment first (like drop
ping a table). Therefore, an index rebuild involves three temporary segments,
one of which is a sort segment, that all may be located in different tablespaces.
--> DROP TABLE
Oracle first converts the segment to a temporary segment, and starts
cleaning up the now temporary segments extents. If the drop is interrupted,
the temporary segment is cleaned up by SMON. If the SMON is interrupted by a
shutdown abort, this may cause serious problem, and the total time to
cleanup is increased.
Note:50592.1 Extent Sizes for Sort, Direct Load and Parallel Operations (PCTAS & PDML)
Note:68836.1 How To Efficiently Drop A Table With Many Extents
5. Temporary Tablespaces
-------------------------
The space for sort operations is managed more efficiently by designating
TEMPORARY tablespaces exclusively for sorts (of type TEMPORARY).
--> No permanent schema objects can reside in a TEMPORARY tablespace
--> Within a TEMPORARY tablespace, all sort operations for a given instance and
tablespace share a single sort segment
--> After startup, the sort segment is created by the first statement that needs
a temporary tablespace for sorting and it is not released until shutdown.
--> This eliminates serialization of space management operations involved in the
allocation and deallocation of sort space, not requiring standard ST enqueue.
There is no storage space deallocation after a sort operation, but only a
change for status of the extents in the sort segments.
--> The tempfile is introduced in 8i. The following command creates a TEMPORARY
tablespace and uses tempfiles:
CREATE TEMPORARY TABLESPACE .. TEMPFILE
--> Locally managed TEMPORARY tablespaces have temporary datafiles (tempfiles).
--> Tempfiles are always set to NOLOGGING mode. Oracle does not record
checkpoint information in tempfiles. It is possible to remove tempfiles from
TEMPORARY tablespaces and keep the logical structure empty. This could be
useful in the case of sort segments cleanup with many extents. This issue
is solved by removing and recreating the tempfiles when necessary.
Refer to:
Note:73439.1 Temporary Segment Handling in Temporary Tablespace
Note:65973.1 Temporary Tablespace, the Sort Extent Pool, and OPS
Note:160426.1TEMPORARY Tablespaces : Tempfiles or Datafiles ?
Oracle8i Database Concepts / Chapter 3
Oracle9i Database Concepts / Chapter 4
|
No comments:
Post a Comment