Causes and solutions for high usage of temporary ORACLE tablespace

The reason for the high usage of temporary tablespace in ORACLE and the solution in the daily study of the database found that the default temporary tablespace temp usage in the company's production database reached 30 GB, and the usage reached 100%; after the usage is adjusted to 32 GB, the usage is still 100%, resulting in insufficient disk space. Sorting and

The reason for the high usage of temporary tablespace in ORACLE and the solution in the daily study of the database found that the default temporary tablespace temp usage in the company's production database reached 30 GB, and the usage reached 100%; after the usage is adjusted to 32 GB, the usage is still 100%, resulting in insufficient disk space. Sorting and

Causes and solutions for high usage of temporary ORACLE tablespace

In the daily study of the database, we found that the default temporary tablespace temp usage of the company's production database has reached 30 GB, and the usage has reached 100%. After being adjusted to 32 GB, the usage is still 100%, insufficient disk space. Based on the temporary tablespace mainly sorting temporary data and caching temporary data, temp will be automatically released after the database is restarted. So I tried to restart the database to alleviate this situation. However, after I restarted the database, I found that the temp usage of the temporary tablespace was still 100%, but it was not changed at all. Although no error is reported for the running application, there is a certain risk to some extent and this problem needs to be solved. Temporary tablespace is mainly used in the following situations:
1. order by or group by (disc sort accounts for the main part );
2. index creation and re-creation;
3. Perform the distinct operation;
4. union & intersect & minus sort-merge joins;

5. Perform Analyze operations;
6. Some exceptions may also cause the TEMP surge.

The analysis of the phenomenon of temporary tablespace increase in Oracle may be due to the following reasons:
1. Do not set the upper limit for the temporary tablespace, but allow unlimited growth. However, if you set an upper limit, you may still encounter an error due to insufficient space. The temporary tablespace setting is too small, which affects performance. The temporary tablespace setting is too large, which also affects performance, as for how many cases need to be set, you need to perform a careful test.
2. During query, too many tables are used in table join queries. We know that during a table connection query, A discar product is generated based on the queried fields and the number of tables. The size of the discar product is the size of the temporary space required for a query, if too many fields are queried and the data is too large, a large temporary tablespace is consumed.
3. Some fields in the query are not indexed. In Oracle, if the table does not have an index, all the data will be copied to the temporary tablespace. If there is an index, the data of the index is generally copied to the temporary tablespace.
Based on the above analysis, the query statements and indexes are optimized to alleviate the problem, but further tests are required.
Summary:
1. SQL statements will affect disk consumption. Improper statements will cause disk spikes.
2. You need to carefully plan the query statements. do not define a query statement, especially in software that provides custom queries.
3. carefully plan the table index. If the temporary tablespace is temporary, the space is not released, but is marked as free after the sort ends. If the temporary tablespace is permanent, SMON is responsible for releasing it after the sort ends, they do not need to be manually released. View which users and SQL leads to TEMP growth in two important views: v $ sort_usage and v $ sort_segment.

By querying relevant information, we found that there are several solutions:
I. Recreate temporary tablespace temp
Temporary tablespace cannot directly drop the default Temporary tablespace, but we can achieve it through the following methods.
View the current Temporary Tablespace
SQL> select name from v $ tempfile;

NAME
-----------------------
D: \ ORACLE \ ORADATA \ ORCL \ TEMP01.DBF

SQL> select username, temporary_tablespace from dba_users;
USERNAME TEMPORARY_TABLESPACE
------------------------------------------------------------
MGMT_VIEW TEMP
SYS TEMP
SYSTEM TEMP
DBSNMP TEMP
SYSMAN TEMP

1. create temporary tablespace for transit
Create temporary tablespace TEMP1 TEMPFILE 'd: \ ORACLE \ ORADATA \ ORCL \ temp02.DBF 'size 512 m reuse autoextend on next 1 M

Maxsize unlimited;
2. Change the default temporary tablespace to the newly created temporary tablespace temp1.
Alter database default temporary tablespace temp1;
3. Delete the original temporary tablespace
Drop tablespace temp including contents and datafiles;
4. Recreate the temporary tablespace
Create temporary tablespace temp tempfile 'd: \ ORACLE \ ORADATA \ ORCL \ temp01.DBF 'size 512 m reuse autoextend on next 1 M MAXSIZE

UNLIMITED;
5. Reset the default temporary tablespace to the new temp tablespace.
Alter database default temporary tablespace temp;
6. Delete temporary tablespace for transit
Drop tablespace temp1 including contents and datafiles;

The above method only temporarily releases the disk space occupied by the temporary tablespace. It is a temporary but not a temporary solution. The real solution is to find SQL statements that consume a large amount of resources in the database, then optimize it. The following is a query of the time-consuming SQL statements used in the sort sorting area:

Select se. username, se. sid, su. extents, su. blocks * to_number (rtrim (p. value) as Space, tablespace, segtype, SQL _text
From v $ sort_usage su, v $ parameter p, v $ session se, v $ SQL s
Where p. name = 'db _ block_size 'and su. session_addr = se. saddr and s. hash_value = su. sqlhash and s. address = su. sqladdr order by se. username, se. sid;
Or:
Select su. username, su. Extents, tablespace, segtype, SQL _text
From v $ sort_usage su, v $ SQL s
Where su. SQL _ID = s. SQL _ID;
NOTE: If no user uses the original temporary tablespace (select tablespace_name, current_users, total_blocks, used_blocks, free_blocks, free_blocks/total_blocks from v $ sort_segment ;), if it is a file system, you can check the file timestamp.
We can delete this tablespace: if the original temporary tablespace is still in use, you cannot delete this tablespace! During a temporary tablespace switchover in the production environment, there are still users in the original temporary tablespace. Even if I close the foreground program, there are still users, no user is using the new temporary tablespace. I guess the user process is dead in the original temporary tablespace. Then, only after the database is restarted can the old temporary tablespace be deleted.

Ii. Modify parameters (this scheme is applicable to versions earlier than 8i and 8i)
Modify the storage parameters of the TEMP tablespace so that the Smon process can view the temporary segments to clean up and TEMP tablespace.
SQL> alter tablespace temp increase 1;
SQL> alter tablespace temp increase 0;

Iii. Kill session
1. Run the following statement a to check who is using the temporary segment.
SELECT se. username, se. SID, se. serial #, se. SQL _address, se. machine, se. program, su. TABLESPACE, su. segtype, su. CONTENTS FROM

V $ session se, v $ sort_usage su WHERE se. saddr = su. session_addr
2. kill the process using the temporary segment
SQL> Alter system kill session 'sid, serial #';
3. shrink the TEMP tablespace.
SQL> Alter tablespace TEMP coalesce;

Note: This method can only be used to manage the Tablespace (Dictionary Managed Tablespace) in the Dictionary ). Local Management of tablespaces (LMT: Local Managed Tablespace. After 9i, you can only create local-managed tablespaces.
Create tablespace test datafile 'd: \ TEST01.dbf 'size 5 M EXTENT MANAGEMENT DICTIONARY
Create tablespace test datafile 'd: \ TEST01.dbf 'size 5 m extent management local;

4. Using Diagnostic events is also a relatively effective method.

1. query Event code
SQL> select ts #, name from sys. ts $;
TS # NAME
----------------------------------------
0 SYSTEM
1 UNDOTBS1
2 SYSAUX
3 TEMP
4 USERS
5 UNDOTBS2
2. Perform the cleanup operation.
SQL> alter session set events 'immediate trace name DROP_SEGMENTS level 4 ';
Note: The TS # Of the temp tablespace is 3, So TS # + 1 = 4.

Why oracle temporary tablespace is too large

Temporary Oracle tablespace is mainly used to query and store some cached data. One of the main reasons for disk consumption is to sort the query results, in the allocation of disk space (memory), Oracle uses the greedy algorithm. If the last disk space consumption reaches 1 GB, the temporary tablespace is 1 GB. If there is still growth, in this case, the temporary tablespace is always at the maximum. The phenomenon of temporary tablespace increase in Oracle may be caused by the following reasons.
1. Do not set the upper limit for the temporary tablespace, but allow unlimited growth. However, if you set an upper limit, you may still encounter an error due to insufficient space. The temporary tablespace setting is too small, which affects performance. The temporary tablespace setting is too large, which also affects performance, as for how many cases need to be set, you need to perform a careful test.
2. During query, too many tables are used in table join queries. We know that during a table connection query, A discar product is generated based on the queried fields and the number of tables. The size of the discar product is the size of the temporary space required for a query, if too many fields are queried and the data is too large, a large temporary tablespace is consumed.
3. Some fields in the query are not indexed. In Oracle, if the table does not have an index, all the data will be copied to the temporary tablespace. If there is an index, the data of the index is generally copied to the temporary tablespace.
Based on the above analysis, the query statements and indexes are optimized to alleviate the problem, but further tests are required.

Summary:
1. SQL statements will affect disk consumption. Improper statements will cause disk spikes.
2. You need to carefully plan the query statements. do not define a query statement, especially in software that provides custom queries.
3. carefully plan the table index.

If the temporary tablespace is temporary, the space is not released, but is marked as free after the sort ends. If the temporary tablespace is permanent, SMON is responsible for releasing it after the sort ends, they do not need to be manually released. Check which users and SQL leads to TEMP growth in two important views:
V $ sort_usage and v $ sort_segment
For non-lmt temp tablespaces, the simplest method is Metalink's method:
Modify the storage parameters of the TEMP tablespace so that the Smon process can view the temporary segments to clean up and TEMP tablespace.
SQL> alter tablespace temp default storage (pctincrease 1 );
SQL> alter tablespace temp default storage (pctincrease 0 );

For the TEMP tablespace managed by LMT, you need to create a new temporary tablespace, specify the default temporary tablespace of all users to the new tablespace, and then offline the old temporary tablespace, and drop it. The procedure is as follows:
First, query the user's default temporary tablespace:

[Oracle @ jumper oracle] $ sqlplus "/as sysdba"

SQL * Plus: Release 9.2.0.4.0-Production on Wed Apr 12 11:11:43 2006

Copyright (c) 1982,200 2, Oracle Corporation. All rights reserved.

Connected:
Oracle9i Enterprise Edition Release 9.2.0.4.0-Production
With the Partitioning option
JServer Release 9.2.0.4.0-Production

SQL> select username, temporary_tablespace from dba_users;

USERNAME TEMPORARY_TABLESPACE
------------------------------------------------------------
SYS TEMP2
SYSTEM TEMP2
OUTLN TEMP2
EYGLE TEMP2
CSMIG TEMP2
TEST TEMP2
REPADMIN TEMP2
......

13 rows selected.

SQL> select name from v $ tempfile;

NAME
---------------------------------------------------------------------
/Opt/oracle/oradata/conner/temp02.dbf
/Opt/oracle/oradata/conner/temp03.dbf

Recreate the temporary tablespace and perform the switchover:

SQL> create temporary tablespace temp tempfile '/opt/oracle/oradata/conner/temp1.dbf' size 10 M;

Tablespace created.

SQL> alter tablespace temp add tempfile '/opt/oracle/oradata/conner/temp2.dbf 'size 20 M;

Tablespace altered.

SQL> alter database default temporary tablespace temp;

Database altered.

SQL> select username, temporary_tablespace from dba_users;

USERNAME TEMPORARY_TABLESPACE
------------------------------------------------------------
SYS TEMP
SYSTEM TEMP
OUTLN TEMP
EYGLE TEMP
CSMIG TEMP
TEST TEMP
REPADMIN TEMP
.......

13 rows selected.

If the original temporary tablespace is used by no user (select tablespace_name, current_users, total_blocks, used_blocks, free_blocks, free_blocks/total_blocks from v $ sort_segment;), you can check the file timestamp if the file is.

We can delete this tablespace: (if the original temporary tablespace is still in use, you cannot delete it! During a temporary tablespace switchover in the production environment, there are still users in the original temporary tablespace. Even if I close the foreground program, there are still users, no user is using the new temporary tablespace. I guess the user process is dead in the original temporary tablespace. Later, only the database can be restarted to delete the old temporary tablespace .)

SQL> drop tablespace temp2;

Tablespace dropped.

SQL>
SQL> select name from v $ tempfile;

NAME
---------------------------------------------------------------
/Opt/oracle/oradata/conner/temp1.dbf
/Opt/oracle/oradata/conner/temp2.dbf

SQL> select file_name, tablespace_name, bytes/1024/1024 MB, autoextensible
2 from dba_temp_files
3/

FILE_NAME TABLESPACE_NAME MB AUTOEXTENSIBLE
----------------------------------------------------------------------------------
/Opt/oracle/oradata/conner/temp2.dbf TEMP 20 NO
/Opt/oracle/oradata/conner/temp1.dbf TEMP 10 NO

Drop tablespace temp including contents and datafiles; -- delete the contents of the tablespace together with the data file.

The following is a query of the time-consuming SQL statements used in the sort sorting area:
Select se. username, se. sid, su. extents, su. blocks * to_number (rtrim (p. value) as Space, tablespace, segtype, SQL _text
From v $ sort_usage su, v $ parameter p, v $ session se, v $ SQL s
Where p. name = 'db _ block_size 'and su. session_addr = se. saddr and s. hash_value = su. sqlhash and s. address = su. sqladdr
Order by se. username, se. sid

Or:

Select su. username, su. Extents, tablespace, segtype, SQL _text
From v $ sort_usage su, v $ SQL s
Where su. SQL _ID = s. SQL _ID

Posted on 2010-08-04 xzc read (740) Comments (2) EDIT favorite category: Oracle