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

總結

以上是生活随笔為你收集整理的[20180102]11g的V$SORT_USAGE视图.txt的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。