stay Oracle in , If the utilization rate of temporary table space is too high, what is the tuning idea ？
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.