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 :

 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,
14       (SU.BLOCKS *
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 *
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,
28          FROM GV$SQLAREA A
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#
38       GV$SESSION    V


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 :



Last , You can recycle temporary table spaces :



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

2       TS# NAME
3---------- ------------------------------
4         3 TEMP


then , Set diagnostic events to perform cleanup operations :



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.