【DB笔试面试669】在Oracle中，若临时表空间使用率过高有什么调优思路？
【DB笔试面试669】在Oracle中，若临时表空间使用率过高有什么调优思路？
题目部分
在Oracle中，若临时表空间使用率过高有什么调优思路？
答案部分
临时表空间是Oracle数据库的重要组成部分，尤其是对于大型的频繁操作，如创建索引、排序等等都需要在临时表空间完成来减少内存的开销。当然对于查询性能要求较高的操作应尽可能的避免在磁盘上完成这些操作。
当SQL语句中使用了诸如ORDER BY、GROUP BY子句时，Oracle服务器就需要对所选取的数据进行排序，这时如果排序的数据量很大，那么内存的排序区（在PGA中）就可能装不下，所以，Oracle服务器就需要把一些中间的排序结果写到磁盘上，即临时表空间中。当用户的SQL语句中经常有大规模的多重排序而内存的排序区不够时，使用临时表空间就可以改进数据库的效率。
临时表空间可以被多个用户共享，它不能包含任何永久对象。临时表空间中的排序段是在实例启动后当有第一个排序操作时创建的，排序段在需要时可以通过分配EXTENTS来扩展并一直可以扩展到大于或等于在该实例上所运行的所有排序活动的总和。
若临时表空间占用过大，首先，要去检查是什么会话占用了临时表空间，具体占用了多少，临时段的具体类型是什么。通过查询视图GV$SORT_USAGE和GV$SESSION可以获取到临时表空间的占用情况和临时段的类型等信息，下面的SQL可以完成这个功能：
SELECT V.INST_ID,
V.SID,
V.SERIAL#,
V.USERNAME,
V.STATUS,
V.ACTION,
V.MACHINE,
V.MODULE,
V.OSUSER,
V.TERMINAL,
V.PROGRAM,
V.SQL_ID,
SU.TABLESPACE,
(SU.BLOCKS *
TO_NUMBER((SELECT RTRIM(VALUE)
FROM V$PARAMETER P
WHERE P.NAME = 'db_block_size'))) / 1024 / 1024 AS SIZE_M,
(SELECT ROUND(SUM(BYTES) / (1024 * 1024), 3) FROM V$TEMPFILE) TEMP_TS_SIZE_M,
ROUND((SU.BLOCKS *
TO_NUMBER((SELECT RTRIM(VALUE)
FROM V$PARAMETER P
WHERE P.NAME = 'db_block_size'))) * 100 /
(SELECT SUM(BYTES)
FROM V$TEMPFILE),
3) C_USED_PERCENT,
SU.SEGTYPE,
(SELECT A.SQL_TEXT
FROM GV$SQLAREA A
WHERE A.SQL_ID = NVL(V.SQL_ID,SU.SQL_ID)
AND A.INST_ID = V.INST_ID
AND ROWNUM = 1) SQL_TEXT,
SU.SEGFILE#,
SU.SEGBLK#,
SU.EXTENTS,
SU.BLOCKS,
SU.SEGRFNO#
FROM GV$SORT_USAGE SU, --GV$TEMPSEG_USAGE
GV$SESSION V
WHERE SU.SESSION_ADDR = V.SADDR
AND SU.INST_ID = V.INST_ID
ORDER BY SU.INST_ID, SU.BLOCKS DESC
;
这里需要说明的一点是，GV$SORT_USAGE和GV$TEMPSEG_USAGE查询的结果是一致的。视图GV$SORT_USAGE中的SEGTYPE列的不同的值所代表的含义如下所示：
l SORT：SQL排序使用的临时段，包括ORDER BY、GROUP BY、DISTINCT、窗口函数（WINDOW FUNCTION，如ROLLUP）、合并查询（UNION、INTERSECT、MINUS）、索引的创建（CREATE）和重建（REBUILD）、ANALYZE分析表等产生的排序。
l DATA：临时表（GLOBAL TEMPORARY TABLE）存储数据使用的段。
l INDEX：临时表上建的索引使用的段。
l HASH：HASH算法，如HASH连接所使用的临时段。
l LOB_DATA和LOB_INDEX：临时LOB使用的临时段。
根据上述的段类型，说明TEMP表空间大体可以分为四类占用：
① SQL语句排序。
② Hash Join占用。
③ 临时表、临时表上的索引占用。
④ LOB对象占用。
在找到了哪些会话占用临时表空间过大后，分析这些会话，确保会话异常或SQL异常后，接着就可以将这些会话清理掉，如下所示：
ALTER SYSTEM KILL SESSION 'SID,SERIAL#' IMMEDIATE;
最后，可以执行临时表空间的回收操作：
ALTER TABLESPACE TEMP COALESCE;
另外，还可以使用诊断事件来清理临时段。首先，确定TEMP表空间的TS#，如下：
SYS@lhrdb > SELECT TS#, NAME FROM V$TABLESPACE WHERE NAME='TEMP';
TS# NAME
---------- ------------------------------
3 TEMP
然后，设置诊断事件来执行清理操作：
ALTER SESSION SET EVENTS 'IMMEDIATE TRACE NAME DROP_SEGMENTS LEVEL 4';
其中，LEVEL后的值为TS#+1。在以上例子中，TEMP表空间的TS#为3，所以TS#+1=4。如果想清除所有表空间的临时段，那么TS#设置为2147483647。
& 说明：
有关具体的解决过程案例可以参考我的BLOG：http://blog.itpub.net/26736162/viewspace-2140629/
本文选自《Oracle程序员面试笔试宝典》，作者：小麦苗