The reason and solution of the high utilization rate of ORACLE temporary table space

In the daily study of the database, we found that the default temporary table space of the company production database is up to 30G, the utilization rate is 100%, and the usage rate is 100% after being adjusted to 32G, which causes the disk space use to be tense. The temp is automatically released after the database is restarted, depending on the nature of the temporary table space, which is primarily to sort and cache temporary data. I want to mitigate this situation by restarting the database, but after restarting the database, I found that temp is still 100%, not changed. Although the operation of the application has not reported any errors, but this to a certain extent, there are some hidden dangers, to solve the problem. Because temporary table spaces are used primarily in the following situations:

1, order BY or group by (disc sort accounted for the main part);

2, index creation and re-creation;

3, distinct operation;

4, Union & intersect & minus Sort-merge joins;

5, Analyze operation;

6, some anomalies will also cause the surge of the temp.

The phenomenon of an Oracle temporary table space explosion may be caused by the following factors:

1. There is no upper limit for temporary table space, but it allows unlimited growth. However, if you set an upper limit, you may end up with a problem with insufficient space, the temporary table space settings are too small to affect performance, temporary table space is too large also affect performance, as to how much need to set the test carefully.

2. Query when the table query to use too much of the table caused. We know that in the table query, according to the query field and the number of tables will generate a Diescal product, the size of the Diescal product is a query the size of the temporary space, if the query of too many fields and data is too large, then will consume very large temporary table space.

3. Some fields of the query are not indexed. In Oracle, if the table has no indexes, all of the data is copied to the temporary tablespace, and if there is an index, the index's data is typically copied to the temporary tablespace.

For the above analysis, the query statement and index are optimized, the situation is relieved, but need further testing.

Summarize:

1.SQL statements can affect disk consumption, and improper statements can cause disk inflation.

2. The query statement needs careful planning, do not take it for granted to define a query, especially in software that can provide user-defined queries.

3. Carefully plan the table index. If the temporary table space is temporary, the space is not released, but is marked free after the sort is finished, and if it is permanent, the Smon is responsible for releasing it at the end of the sort, without having to manually release it. View two important views of which users and SQL are causing temp growth: v$ sort_usage and V$sort_segment.

By querying the relevant data, we find that there are several solutions:

I. Reconstruction of temporary table space Temp

Temporary tablespace cannot drop the default temporary tablespace directly, but we can do so by 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 Transit temporary table space

Create temporary tablespace TEMP1 tempfile ' D:\ORACLE\ORADATA\ORCL\temp02. DBF ' SIZE 512M reuse autoextend on NEXT 1M

MAXSIZE Unlimited;

2. Change the default temporary tablespace to the new temporary table space that you just created Temp1

ALTER DATABASE default temporary tablespace Temp1;

3. Delete the original temporary table space

Drop Tablespace temp including contents and datafiles;

4. Re-create temporary table space

Create temporary tablespace TEMP tempfile ' D:\ORACLE\ORADATA\ORCL\temp01. DBF ' SIZE 512M reuse autoextend on NEXT 1M MAXSIZE

Unlimited;

5. Reset the default temporary tablespace to the newly created temp table space

ALTER DATABASE default temporary tablespace temp;

6. Remove transit with temporary table space

Drop tablespace Temp1 including contents and datafiles;

The above method only temporarily released the temporary tablespace disk space, is a symptom but not the root of the solution, the real root of the method is to find the database consumes a large amount of SQL statements, and then optimize the processing. The following is the SQL that the query uses in the sort sort area for time-consuming execution:

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 the original temporary table space is not used by users (select Tablespace_name,current_users,total_blocks,used_blocks,free_blocks,free_blocks/total_ Blocks from v$sort_segment; If the file system can look at the timestamp of the file.

We can delete this table space: if the original temporary table space and the user is in use, you cannot delete this tablespace. In a temporary table space switch in a production environment, the original temporary table space always has a user on it, even if I close the foreground program, there are still users, the new temporary table space has no users in use. I estimate that the user process is dead in the original temporary tablespace, and later only the database can be restarted to remove the old temporary tablespace.

Second, modify the parameters (this scheme is tightly applicable to 8i and 8i version)

Modify the temp table Space storage parameter, let the Smon process view the temporary segment, so as to achieve cleanup and temp table space.

Sql>alter tablespace temp Increase 1;

Sql>alter tablespace temp Increase 0;

Third, Kill session

1. Use the following statement A to see who is using the temporary paragraph

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 is using the process of the temporary segment

Sql>alter system kill session ' sid,serial# ';

3, the temp table space back to shrink

Sql>alter tablespace TEMP Coalesce;

Note: This method can only manage Tablespace (Dictionary Managed tablespace) for the dictionary. In the Local Management table space (lmt:local Managed tablespace), no need to organize. Only locally managed tablespaces can be created after 9i.

CREATE tablespace TEST datafile ' D:\TEST01.dbf ' SIZE 5M EXTENT MANAGEMENT DICTIONARY

CREATE tablespace TEST datafile ' D:\TEST01.dbf ' SIZE 5M EXTENT MANAGEMENT Local;

Iv. use of 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 cleanup operations

Sql>alter session Set Events ' immediate trace name drop_segments level 4 ';

Description: Temp table space is ts# 3, so ts#+ 1 = 4.

Reason for excessive Oracle temp table space

2009-05-12 11:22

The Oracle temporary tablespace is used primarily for querying and storing some cached data. One of the main reasons for disk consumption is the need to sort the results of a query, and if it's not mistaken, Oracle uses a greedy algorithm for allocating disk space (memory), if the last disk space consumption reaches 1GB , the temp table space is 1GB, and if there is growth, then the temporary table space remains at one of the largest caps. An analysis of the explosion of Oracle temporary table space may be caused by several reasons.

1. There is no upper limit for temporary table space, but it allows unlimited growth. However, if you set an upper limit, you may end up with a problem with insufficient space, the temporary table space settings are too small to affect performance, temporary table space is too large also affect performance, as to how much need to set the test carefully.

2. Query when the table query to use too much of the table caused. We know that in the table query, according to the query field and the number of tables will generate a Diescal product, the size of the Diescal product is a query the size of the temporary space, if the query of too many fields and data is too large, then will consume very large temporary table space.

3. Some fields of the query are not indexed. In Oracle, if the table has no indexes, all of the data is copied to the temporary tablespace, and if there is an index, the index's data is typically copied to the temporary tablespace.

For the above analysis, the query statement and index are optimized, the situation is relieved, but need further testing.

Summarize:

1.SQL statements can affect disk consumption, and improper statements can cause disk inflation.

2. The query statement needs careful planning, do not take it for granted to define a query, especially in software that can provide user-defined queries.

3. Carefully plan the table index.

If the temporary table space is temporary, the space is not released, but is marked free after the sort is finished, and if it is permanent, the Smon is responsible for releasing it at the end of the sort, without having to manually release it. View two important views of which users and SQL are causing temp growth:

V$sort_usage and V$sort_segment

The simplest approach to the temp table space for LMT management is a method given by Metalink:

Modify the temp table Space storage parameter, let the Smon process view the temporary segment, so as to achieve cleanup and temp table space.

Sql>alter tablespace Temp Default storage (Pctincrease 1);

Sql>alter tablespace Temp Default storage (pctincrease 0);

For LMT-Managed temp tablespaces, a new temporary table space needs to be created, the default temporary table space for all users assigned to the new tablespace, and then offline the old temporary tablespace and dropped. The specific steps are as follows:

First query the user's default temp table space:

[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, 2002, Oracle Corporation. All rights reserved.

Connected to:

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

......

Rows selected.

Sql> select name from V$tempfile;

NAME

---------------------------------------------------------------------

/opt/oracle/oradata/conner/temp02.dbf

/opt/oracle/oradata/conner/temp03.dbf

To rebuild a new temporary table space and switch it:

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

Tablespace created.

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

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

.......

Rows selected.

If the original temporary table space is not used by users (select Tablespace_name,current_users,total_blocks,used_blocks,free_blocks,free_blocks/total_ Blocks from v$sort_segment; If the file system can look at the timestamp of the file.

We can delete the table space: (If the original temporary table space and the user is in use, you will not be able to delete the table space.) In a temporary table space switch in a production environment, the original temporary table space always has a user on it, even if I close the foreground program, there are still users, the new temporary table space has no users in use. I estimate that the user process is dead in the original temp table space. Later, only the database can be restarted to remove 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 NO

/OPT/ORACLE/ORADATA/CONNER/TEMP1.DBF TEMP NO

Drop Tablespace temp including contents and datafiles; --Delete the contents of the table space and the data file.

The following is the SQL that the query uses in the sort sort area for time-consuming execution:

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