subject         part

stay Oracle in , If the utilization rate of temporary table space is too high, what is the tuning idea ？

     

          Answer section          

The temporary table space is Oracle An important part of the database , Especially for large frequent operations , Such as creating an index 、 Sorting and so on need to be done in the temporary table space to reduce memory overhead . Of course, for the operations that require high query performance, we should try our best to avoid completing these operations on disk .

When SQL In the statement, we use the words such as ORDER BY、GROUP BY When clause ,Oracle The server needs to sort the selected data , At this time, if the amount of sorted data is large , So the sort area of memory （ stay PGA in ） It might not fit , therefore ,Oracle The server needs to write some intermediate sorting results to disk , In the temporary table space . When the user’s SQL Statement often has a large number of multiple sorting and memory sorting area is not enough , Using temporary table spaces can improve the efficiency of the database .

Temporary table spaces can be shared by multiple users , It can’t contain any permanent objects . The sort segment in the temporary table space is created when there is the first sort operation after the instance is started , Sort segments can be assigned when needed EXTENTS To extend and always extend to greater than or equal to the sum of all sorting activities running on the instance .

If the temporary table space is too large , First , To check what session is occupying the temporary table space , How much did it take , What are the specific types of temporary segments . Through query view GV$SORT_USAGE and GV$SESSION You can get information about the occupation of temporary table space and the type of temporary segment , Below SQL You can do this ：

 1SELECT V.INST_ID,
 2       V.SID,
 3       V.SERIAL#,
 4       V.USERNAME,
 5       V.STATUS,
 6       V.ACTION,
 7       V.MACHINE,
 8       V.MODULE,
 9       V.OSUSER,
10       V.TERMINAL,
11       V.PROGRAM,
12       V.SQL_ID,
13       SU.TABLESPACE,
14       (SU.BLOCKS *
15       TO_NUMBER((SELECT RTRIM(VALUE)
16                    FROM V$PARAMETER P
17                   WHERE P.NAME = 'db_block_size'))) / 1024 / 1024 AS SIZE_M,
18       (SELECT ROUND(SUM(BYTES) / (1024 * 1024), 3) FROM V$TEMPFILE) TEMP_TS_SIZE_M,
19       ROUND((SU.BLOCKS *
20             TO_NUMBER((SELECT RTRIM(VALUE)
21                          FROM V$PARAMETER P
22                         WHERE P.NAME = 'db_block_size'))) * 100 /
23             (SELECT SUM(BYTES)
24                FROM V$TEMPFILE),
25             3) C_USED_PERCENT,
26       SU.SEGTYPE,
27       (SELECT A.SQL_TEXT
28          FROM GV$SQLAREA A
29         WHERE A.SQL_ID = NVL(V.SQL_ID,SU.SQL_ID)
30           AND A.INST_ID = V.INST_ID
31           AND ROWNUM = 1) SQL_TEXT,
32       SU.SEGFILE#,
33       SU.SEGBLK#,
34       SU.EXTENTS,
35       SU.BLOCKS,
36       SU.SEGRFNO#
37  FROM GV$SORT_USAGE SU, --GV$TEMPSEG_USAGE
38       GV$SESSION    V
39 WHERE SU.SESSION_ADDR = V.SADDR
40   AND SU.INST_ID = V.INST_ID
41 ORDER BY SU.INST_ID,  SU.BLOCKS DESC
42;

One thing to say here is ,GV$SORT_USAGE and GV$TEMPSEG_USAGE The results of the query are consistent . View GV$SORT_USAGE Medium SEGTYPE The different values of the columns represent the following meanings ：

l SORT：SQL The temporary segment used for sorting , Include ORDER BY、GROUP BY、DISTINCT、 Window function （WINDOW FUNCTION, Such as ROLLUP）、 Merge query （UNION、INTERSECT、MINUS）、 Index creation （CREATE） And reconstruction （REBUILD）、ANALYZE Sort generated by analyzing tables, etc .

l DATA： A temporary table （GLOBAL TEMPORARY TABLE） Segments used to store data .

l INDEX： The segment used by the index built on the temporary table .

l HASH：HASH Algorithm , Such as HASH The temporary segment used by the connection .

l LOB_DATA and LOB_INDEX： temporary LOB The temporary segment used .

According to the above segment type , explain TEMP Table spaces can be roughly divided into four categories ：

① SQL Sentence ordering .

② Hash Join Occupy .

③ A temporary table 、 Index occupancy on temporary table .

④ LOB Object occupancy .

After finding out which sessions occupy too much temporary table space , Analyze these conversations , Make sure the session is abnormal or SQL After abnormality , Then you can clean up these conversations , As shown below ：

1ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;

Last , You can recycle temporary table spaces ：

1ALTER TABLESPACE TEMP COALESCE;

in addition , You can also use diagnostic events to clean up temporary segments . First , determine TEMP Table space TS#, as follows ：

1SYS@lhrdb > SELECT TS#, NAME FROM V$TABLESPACE WHERE NAME='TEMP';
2       TS# NAME
3---------- ------------------------------
4         3 TEMP

then , Set diagnostic events to perform cleanup operations ：

1ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME DROP_SEGMENTS LEVEL 4';

among ,LEVEL The value after is TS#+1. In the example above ,TEMP Table space TS# by 3, therefore TS#+1=4. If you want to clear all the temporary segments of the table space , that TS# Set to 2147483647.