有关temporary segment的分配、使用以及回收！

以前对临时段的认识到底是出现了错误还是oracle 10g有变化，发现临时段在sort之后查询结束释放了。[@more@]

下面这些doc都是来着oracle官方文档，都是介绍临时段的，可以参考。

Introduction to Temporary Segments
When processing queries, Oracle often requires temporary workspace for intermediate stages of SQL statement parsing and execution. Oracle automatically allocates this disk space called a temporary segment. Typically, Oracle requires a temporary segment as a database area for sorting. Oracle does not create a segment if the sorting operation can be done in memory or if Oracle finds some other way to perform the operation using indexes.

Operations that Require Temporary Segments
The following statements sometimes require the use of a temporary segment:

CREATE INDEX

SELECT ... ORDER BY

SELECT DISTINCT ...

SELECT ... GROUP BY

SELECT . . . UNION

SELECT ... INTERSECT

SELECT ... MINUS

Some unindexed joins and correlated subqueries can require use of a temporary segment. For example, if a query contains a DISTINCT clause, a GROUP BY, and an ORDER BY, Oracle can require as many as two temporary segments.

Segments in Temporary Tables and Their Indexes
Oracle can also allocate temporary segments for temporary tables and indexes created on temporary tables. Temporary tables hold data that exists only for the duration of a transaction or session.

See Also:

"Temporary Tables"
How Temporary Segments Are Allocated
Oracle allocates temporary segments differently for queries and temporary tables.

Allocation of Temporary Segments for Queries
Oracle allocates temporary segments as needed during a user session in one of the temporary tablespaces of the user issuing the statement. Specify these tablespaces with a CREATE USER or an ALTER USER statement using the TEMPORARY TABLESPACE clause.

Note:

You cannot assign a permanent tablespace as a user's temporary tablespace.
If no temporary tablespace is defined for the user, then the default temporary tablespace is the SYSTEM tablespace. The default storage characteristics of the containing tablespace determine those of the extents of the temporary segment. Oracle drops temporary segments when the statement completes.

Because allocation and deallocation of temporary segments occur frequently, create at least one special tablespace for temporary segments. By doing so, you can distribute I/O across disk devices, and you can avoid fragmentation of the SYSTEM and other tablespaces that otherwise hold temporary segments.

Note:

When the SYSTEM tablespace is locally managed, you must define a default temporary tablespace when creating a database. A locally managed SYSTEM tablespace cannot be used for default temporary storage.
Entries for changes to temporary segments used for sort operations are not stored in the redo log, except for space management operations on the temporary segment.

See Also:

"Bigfile Tablespaces"

Chapter 20, "Database Security" for more information about assigning a user's temporary segment tablespace

Allocation of Temporary Segments for Temporary Tables and Indexes
Oracle allocates segments for a temporary table when the first INSERT into that table is issued. (This can be an internal insert operation issued by CREATE TABLE AS SELECT.) The first INSERT into a temporary table allocates the segments for the table and its indexes, creates the root page for the indexes, and allocates any LOB segments.

Segments for a temporary table are allocated in a temporary tablespace of the user who created the temporary table.

Oracle drops segments for a transaction-specific temporary table at the end of the transaction and drops segments for a session-specific temporary table at the end of the session. If other transactions or sessions share the use of that temporary table, the segments containing their data remain in the table.
--=====================================
Extents in Temporary Segments
When Oracle completes the execution of a statement requiring a temporary segment, Oracle automatically drops the temporary segment and returns the extents allocated for that segment to the associated tablespace. A single sort allocates its own temporary segment in a temporary tablespace of the user issuing the statement and then returns the extents to the tablespaces.

Multiple sorts, however, can use sort segments in temporary tablespaces designated exclusively for sorts. These sort segments are allocated only once for the instance, and they are not returned after the sort, but remain available for other multiple sorts.

A temporary segment in a temporary table contains data for multiple statements of a single transaction or session. Oracle drops the temporary segment at the end of the transaction or session, returning the extents allocated for that segment to the associated tablespace.
--=================================
Size of Temporary Extents
When using a locally managed temporary tablespace, extents are all the same size because this helps avoid fragmentation. As a general rule, temporary extents should be smaller than permanent extents because there are more demands for temporary space, and parallel processes or other operations running concurrently must share the temporary tablespace. Normally, temporary extents should be in the range of 1MB to 10MB. Once you allocate an extent, it is available for the duration of an operation. If you allocate a large extent but only need to use a small amount of space, the unused space in the extent is unavailable.

At the same time, temporary extents should be large enough that processes do not have to wait for space. Temporary tablespaces use less overhead than permanent tablespaces when allocating and freeing a new extent. However, obtaining a new temporary extent still requires the overhead of acquiring a latch and searching through the SGA structures, as well as SGA space consumption for the sort extent pool.

See Oracle Database Performance Tuning Guide for information regarding locally-managed temporary tablespaces.
--======================================
我们注意到了上面提到的一句话，分配临时段的extent是需要latch的：
obtaining a new temporary extent still requires the overhead of acquiring a latch and searching through the SGA structures, as well as SGA space consumption for the sort extent pool.
SQL> select * from v$latchname where name like '%sort%';

LATCH# NAME HASH
---------- ------------------------------ ----------
183 sort extent pool 986781538

SQL> select addr,latch#,name,gets,misses,sleeps from v$latch where latch#=183;

ADDR LATCH# NAME GETS MISSES SLEEPS
-------- ---------- ------------------------------ ---------- ---------- ----------
03C49E80 183 sort extent pool 305 0 0

SQL>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1048274/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1048274/