Oracle Temporary table Space

When Oracle executes some SQL, it will require some temporary space to store the intermediate data that is generated during execution. These temporary spaces are made by Orac le

Allocates to the process from the specified temporary table space. There are three main scenarios that take up temporary space: temporary table/index operations, sorting, and temporary LO B

Object operation.

A. Temporary tables/indexes:

In a session, when an INSERT (including CTAS) is made for the first time on a temporary table, Orac le will be a temporary table from the temporary table space

and its index allocates temporary space one storage data.

B. Sort:

Any action that uses a sort, including JOIN, create (Rebuild) INDEX, order BY, aggregation calculation (GROUP

by), and statistical data collection, may be used to temporary tablespace. The sort operation first selects the sort area in memory

(sort in Memory), once the sort area is insufficient, the temporary space is used for the sort operation (sort in Disk).

Look at the following example:

Sql> alter session Set sort_area_size = 10000000;

Session altered.

Sql> Select owner, object_name from T_test1 to Objec t_id;

47582 rows selected.

Exec ution Plan

Plan Hash value:1312425564

Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

| 0 | SELECT STATEMENT | | 47582 | 1486k| 155 (4) | 00:00:02 |

| 1 | SORT ORDER BY | | | 47582 | 1486k| 155 (4) | 00:00:02 |

| 2 | TABLE ACCESS full| T_test1 | 47582 | 1486k| 150 (1) | 00:00:02 |

Statistics

1 Recursive calls

0 db Block gets

658 C Onsistent gets

0 Physic Al Reads

0 Redo Size

1566184 Bytes sent via sql*net to C lient

35277 bytes received via sql*net from client

3174 sql*net roundtrips to/from c lient

1 Sorts (memory)

0 Sorts (disk)

47582 rows processed

Sql> alter session Set sort_area_size = 10000;

Session altered.

Sql> Select owner, object_name from T_test1 to Objec t_id;

47582 rows selected.

Exec ution Plan

Plan Hash value:1312425564

| Id | Operation | Name | Rows | Bytes | tempspc| Cost (%CPU) | time|

| 0 | SELECT STATEMENT | | 47582 | 1486k| | 1251 (1) | 00:00:16 |

| 1 | SORT ORDER BY | | | 47582 | 1486k| 4136k| 1251 (1) | 00:00:16 |

| 2 | TABLE ACCESS full| T_test1 | 47582 | 1486k| | 150 (1) | 00:00:02 |

Statistics

6 Recursive calls

DB Bloc K gets

658 C Onsistent gets

629 Physical Reads

0 Redo Size

1566184 Bytes sent via sql*net to C lient

35277 bytes received via sql*net from client

3174 sql*net roundtrips to/from c lient

0 Sorts (memory)

1 Sorts (disk)

47582 rows processed

C. Temporary LOB objects:

LOB objects include BLOBs, CLO B, NCLOB, and BFILE. In a PLSQL program block, if you define a LOB variable,

These LOB variables are temporary LOB objects. A temporary LOB object is created on a temporary tablespace until the LOB data is

Release, or the session ends.

This article is from the "Oracle Road" blog, please be sure to keep this source http://19880614.blog.51cto.com/4202939/791762

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/