[20180102]11g的V$SORT_USAGE视图.txt-优快云博客

本文详细解析了Oracle 11g环境下V$SORT_USAGE视图的使用场景与限制，特别是关于如何准确获取导致临时表空间过度使用的SQL语句，通过实例对比分析，指出视图定义中存在的问题并提出改进方案。

[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#;