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
5 I
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