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/