数据库版本11204RAC
执行占用temp表空间sql
SQL> select TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024 ,FREE_SPACE/1024/1024 from dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE/1024/1024 FREE_SPACE/1024/1024
------------------------------ ------------------------- --------------------
TEMP 50 49
SQL> select wm_concat(id) from test;WM_CONCAT(ID)
--------------------------------------------------------------------------------
20,46,28,15,29,3,25,41,54,40,26,17,13,9,43,51,38,7,56,19,14,6,44,21,45,35,5,23,4
SQL> select TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024 ,FREE_SPACE/1024/1024 from dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE/1024/1024 FREE_SPACE/1024/1024
------------------------------ ------------------------- --------------------
TEMP 50 4
SQL> select distinct(sid) from v$mystat;SID
----------
29
在新窗口查看占用temp语句以及大小
SQL> select TABLESPACE_NAME,TABLESPACE_SIZE/1024/1024 ,FREE_SPACE/1024/1024 from dba_temp_free_space;
TABLESPACE_NAME TABLESPACE_SIZE/1024/1024 FREE_SPACE/1024/1024
------------------------------ ------------------------- --------------------
TEMP 50 4
SQL> select a.sql_id,TABLESPACE,SEGTYPE,BLOCKS*8/1024 size_M,sql_text from gv$sort_usage a,v$sql b where a.sql_id=b.sql_id;
INST_ID SQL_ID TABLESPACE SEGTYPE SIZE_M SQL_TEXT
---------- ------------- ------------------------------- --------- ---------- --------------------------------------------------
1 915zybs3jpuk4 TEMP LOB_DATA 45 select distinct(sid) from v$mystat
发现v$sort_usage视图查出的sql _id不是我们所执行的占用temp表空间的sql
查看v$sort_usage视图定义发现视图中的sql _id是v$session中的 prev_sql_id,为当前执行sql。
SQL> select * from V$FIXED_VIEW_DEFINITION where view_name='GV$SORT_USAGE';
VIEW_NAME VIEW_DEFINITION
------------------------------ ----------------------------------------------------------------------------------------------------
GV$SORT_USAGE select x$ktsso.inst_id, username, username, ktssoses, ktssosno, prev_sql_addr, prev_hash_value, prev _sql_id, ktssotsn, decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY'), decode(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX', 5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED'), ktssofno, ktssobno, ktssoexts, ktssoblks, ktssorfno from x$ktsso, v$session where ktssoses = v$session.saddr and ktssosno
= v$session.serial
从11.2.0.2版本之后,x$ktsso增加了KTSSOSQLID列,它真正代表消耗高temp表空间的sql
在1节点执行,2节点x$ktsso为空。
SQL> select b.inst_id,a.sid,b.ktssosno serial#,b.ktssosqlid sql_id,b.ktssoblks*8/1024 mb,c.sql_text from v$session a,x$ktsso b,v$sql c where b.ktssoses=a.saddr and b.ktssosno=a.serial# and b.ktssosqlid=c.sql_id;
INST_ID SID SERIAL# SQL_ID MB SQL_TEXT
---------- ---------- ---------- ------------- ---------- --------------------------------------------------
1 29 27 63mbdmx49z7a5 45 select wm_concat(id) from test
也可以用v$active_session_history的temp_space_allocated字段值大小去查看对应的实际sql
参考
http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html
https://blog.csdn.net/Hehuyi_In/article/details/100749874
Bug 17834663 - Include SQL ID for statement that created a temporary segment in GV$SORT_USAGE (Doc ID 17834663.8)
FIND SQL_ID RESPONSIBLE FOR TEMP SEGMENT USAGE(Bug ID 17834663)