[20180102]11g的V$SORT_USAGE视图.txt
--//当出现排序使用临时表空间的情况时,查询V$SORT_USAGE那个会话使用,但是执行的sql语句不一定是问题语句,通过例子说明.
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.测试:
--//session 1:
SCOTT@book> @ &r/spid
SID SERIAL# PROCESS SERVER SPID PID P_SERIAL# C50
---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------
274 5 16928 DEDICATED 16929 21 3 alter system kill session '274,5' immediate;
SCOTT@book> select wmsys.wm_concat(dname) c60 from dept;
C60
------------------------------------------------------------
ACCOUNTING,RESEARCH,SALES,OPERATIONS
--//注:11G定义wmsys.wm_concat的返回lob类型,使用临时表空间.
--//session 2:
SCOTT@book> select * from GV$SORT_USAGE;
INST_ID USERNAME USER SESSION_ADDR SESSION_NUM SQLADDR SQLHASH SQL_ID TABLESPACE CONTENTS SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS SEGRFNO#
------- -------- ----- ---------------- ----------- ---------------- ---------- ------------- ---------- --------- --------- ---------- ---------- ---------- ---------- ----------
1 SCOTT SCOTT 000000008561FD00 5 000000007D502F70 2424055223 3905nhf87s9dr TEMP TEMPORARY LOB_DATA 201 52864 1 128 1
SCOTT@book> @ &r/sqlid 3905nhf87s9dr
SQL_ID SQLTEXT
------------- --------------------------------------------
3905nhf87s9dr select wmsys.wm_concat(dname) c60 from dept
--//OK,现在是对的.
--//session 1:
SCOTT@book> select sysdate from dual ;
SYSDATE
-------------------
2018-01-02 14:52:56
--//session 2:
SCOTT@book> select * from GV$SORT_USAGE;
INST_ID USERNAME USER SESSION_ADDR SESSION_NUM SQLADDR SQLHASH SQL_ID TABLESPACE CONTENTS SEGTYPE SEGFILE# SEGBLK# EXTENTS BLOCKS SEGRFNO#
---------- -------- ----- ---------------- ----------- ---------------- ---------- ------------- ---------- --------- --------- ---------- ---------- ---------- ---------- ----------
1 SCOTT SCOTT 000000008561FD00 5 000000007C67F058 409144692 f0wzs9nc663bn TEMP TEMPORARY LOB_DATA 201 52864 1 128 1
SCOTT@book> @ &r/sql_id f0wzs9nc663bn
SQL_ID SQLTEXT
------------- -------------------------
f0wzs9nc663bn select sysdate from dual
--//现在看到的是select sysdate from dual语句,如果这样定位有问题语句,就存在错误.
SCOTT@book> column VIEW_DEFINITION format a140
SCOTT@book> 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'), kt
ssofno, ktssobno, ktssoexts, ktssoblks, ktssorfno from x$ktsso, v$session where ktssoses = v$session.saddr and ktssosno = v$session.serial#
--//可以发现GV$SORT_USAGE里面取的sql_id是v$session的prev_sql_id,如果当前正在执行,判断OK,如果过后调用其它语句,看到就是错误的语句.
--//如果看看熊工的帖子:http://www.laoxiong.net/temporary_tablespace_excessive_usage_case.html
--//x$ktsso中增加了一个字段ktssosqlid,表示该临时段真正关联的SQL.可惜即使11.2.0.4版本,oracle定义的GV$SORT_USAGE视图依旧存在问题.
select ktssosqlid from x$ktsso, v$session where ktssoses = v$session.saddr
and ktssosno = v$session.serial#
and v$session.sid=274;
KTSSOSQLID
-------------
3905nhf87s9dr
SYS@book> @ &r/sql_id 3905nhf87s9dr
SQL_ID SQLTEXT
------------- -------------------------------------------
3905nhf87s9dr select wmsys.wm_concat(dname) c60 from dept
--//熊工还改动视图定义,总之遇到临时表空间消耗问题,注意这个问题.
select k.inst_id "INST_ID",
ktssoses "SADDR",
sid,
ktssosno "SERIAL#",
username "USERNAME",
osuser "OSUSER",
ktssosqlid "SQL_ID",
ktssotsn "TABLESPACE",
decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY') "CONTENTS",
--注意在12c的v$sort_usage定义中TABLESPACE和CONTENTS已经发生变化了。
decode(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX',
5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED') "SEGTYPE",
ktssofno "SEGFILE#",
ktssobno "SEGBLK#",
ktssoexts "EXTENTS",
ktssoblks "BLOCKS",
round(ktssoblks*p.value/1024/1024, 2) "SIZE_MB",
ktssorfno "SEGRFNO#"
from x$ktsso k, v$session s,
(select value from v$parameter where name='db_block_size') p
where ktssoses = s.saddr
and ktssosno = s.serial#;
