重庆思庄Oracle、Redhat认证学习论坛

标题: Oracle 11g 查看TEMP实际占用 [打印本页]
作者: 郑全    时间: 2024-11-6 17:48
标题: Oracle 11g 查看TEMP实际占用
收到一些库有会话占用了大量temp表空间的告警，通常的做法是查询v$sort_usage找到对应sql_id


  1. select *
  2.   from (select username,
  3.                session_addr,
  4.                sql_id,
  5.                contents,
  6.                segtype,
  7.                blocks * 8 / 1024 / 1024 mb
  8.           from v$sort_usage
  9.          order by blocks desc)
  10. where rownum <= 20;
复制代码
但有时会发现找出来的sql特别简单，怎么看都不像能占用几十几百G temp表空间的样子。
为什么从v$sort_usage找到的sql_id有时是错的？应该怎么找正确的sql_id？


关于第一个问题，查找了网上的一些文章，发现v$sort_usage的sql_id并不对应v$session的sql_id，它对应的是v$session的prev_sql_id有一个V$FIXED_VIEW_DEFINITION视图可以查看固定视图定义，查询可以看到v$sort_usage定义如下
  1. select * from V$FIXED_VIEW_DEFINITION where view_name='V$SORT_USAGE';
  2. --输出如下
  3. select  USERNAME , "USER" , SESSION_ADDR , SESSION_NUM , SQLADDR , SQLHASH, SQL_ID, TABLESPACE , CONTENTS , SEGTYPE , SEGFILE# , SEGBLK# ,EXTENTS , BLOCKS , SEGRFNO# from GV$SORT_USAGE where inst_id = USERENV('Instance');
复制代码

可以看到它来自GV$SORT_USAGE，再查看GV$SORT_USAGE的定义
  1. select * from V$FIXED_VIEW_DEFINITION where view_name='GV$SORT_USAGE';
  2. --输出如下
  3. select x$ktsso.inst_id,
  4.        username,
  5.        username,
  6.        ktssoses,
  7.        ktssosno,
  8.        prev_sql_addr,
  9.         prev_hash_value,
  10.         prev_sql_id,  ----------- 注意这个
  11.         ktssotsn,
  12.         decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY'),
  13.         decode(ktssosegt,
  14.                1,
  15.                'SORT',
  16.                2,
  17.                'HASH',
  18.                3,
  19.                'DATA',
  20.                4,
  21.                'INDEX',
  22.                5,
  23.                'LOB_DATA',
  24.                6,
  25.                'LOB_INDEX',
  26.                'UNDEFINED'),
  27.         ktssofno,
  28.         ktssobno,
  29.         ktssoexts,
  30.         ktssoblks,
  31.         ktssorfno
  32.    from x$ktsso, v$session
  33.   where ktssoses = v$session.saddr
  34.     and ktssosno = v$session.serial#;

  36. -- 12.2.0.1
  37. SELECT so.inst_id,
  38.        username,
  39.        username,
  40.        ktssoses,
  41.        ktssosno,
  42.        prev_sql_addr,
  43.        prev_hash_value,
  44.        prev_sql_id,
  45.        ts.name,
  46.        DECODE (ktssotbst, 1, 'TEMPORARY', 'PERMANENT'),
  47.        DECODE (ktssosegt,
  48.                1, 'SORT',
  49.                2, 'HASH',
  50.                3, 'DATA',
  51.                4, 'INDEX',
  52.                5, 'LOB_DATA',
  53.                6, 'LOB_INDEX',
  54.                7, 'TEMP_UNDO',
  55.                'UNDEFINED'),
  56.        ktssofno,
  57.        ktssobno,
  58.        ktssoexts,
  59.        ktssoblks,
  60.        ktssorfno,
  61.        ktssotsnum,
  62.        so.con_id,
  63.        ktssosqlid
  64.   FROM x$ktsso so, v$session, v$tablespace ts
  65. WHERE     so.ktssotsnum = ts.ts#
  66.        AND so.con_id = ts.con_id
  67.        AND ktssoses = v$session.saddr
  68.        AND ktssosno = v$session.serial#
复制代码
可以看到v$sort_usage的sql_id对应的是v$session的prev_sql_id。如果当前sql是最新正在执行的语句，查出来的结果是对的；如果过后执行了其它sql，看到就是错误的语句。
真正查询消耗高temp表空间的sql语句应该为
  1. SELECT S.sid,
  2.        S.serial# sid_serial,
  3.        S.username,
  4.        S.osuser,
  5.        P.spid,
  6.        S.module,
  7.        S.program,
  8.        SUM(T.blocks) * TBS.block_size / 1024 / 1024 mb_used,
  9.        T.tablespace,
  10.         COUNT(*) sort_ops
  11.    FROM v$sort_usage T, v$session S, dba_tablespaces TBS, v$process P
  12.   WHERE T.session_addr = S.saddr
  13.     AND S.paddr = P.addr
  14.     AND T.tablespace = TBS.tablespace_name
  15.   GROUP BY S.sid,
  16.            S.serial#,
  17.            S.username,
  18.            S.osuser,
  19.            P.spid,
  20.            S.module,
  21.            S.program,
  22.            TBS.block_size,
  23.            T.tablespace
  24. HAVING SUM(blocks) > 10000 -- 80MB
  25.   ORDER BY sid_serial;
  26. --将sid代入下面sql查出真正的sql_id
  27. select ktssosqlid from x$ktsso, v$session where ktssoses = v$session.saddr and ktssosno = v$session.serial# and v$session.sid=2111;
复制代码
或者直接利用下面的sql
  1. select k.inst_id "INST_ID",  
  2.        ktssoses "SADDR",  
  3.        sid,  
  4.        ktssosno "SERIAL#",  
  5.        username "USERNAME",  
  6.        osuser "OSUSER",   
  7.        ktssosqlid "SQL_ID",  
  8.        ktssotsn "TABLESPACE",  
  9.        decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY') "CONTENTS",  
  10.         --注意在12c的v$sort_usage定义中TABLESPACE和CONTENTS已经发生变化了。
  11.         decode(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX',   
  12.            5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED') "SEGTYPE",  
  13.         ktssofno "SEGFILE#",  
  14.         ktssobno "SEGBLK#",  
  15.         ktssoexts "EXTENTS",  
  16.         ktssoblks "BLOCKS",  
  17.         round(ktssoblks*p.value/1024/1024, 2) "SIZE_MB",  
  18.         ktssorfno "SEGRFNO#"  
  19. from x$ktsso k, v$session s,   
  20.       (select value from v$parameter where name='db_block_size') p   
  21. where ktssoses = s.saddr  
  22.    and ktssosno = s.serial#;
复制代码
v$sort_usage中的SEGTYPE列含义
  1. select *
  2.   from (select t.sample_time,
  3.            s.PARSING_SCHEMA_NAME,
  4.            t.sql_id,
  5.            t.sql_child_number as sql_child,
  6.            round(t.temp_space_allocated / 1024 / 1024 / 1024, 2) || ' G' as temp_used,
  7.            round(t.temp_space_allocated /
  8.                  (select sum(decode(d.autoextensible, 'YES', d.maxbytes, d.bytes))
  9.                     from dba_temp_files d),
  10.                   2) * 100 || ' %' as temp_pct,
  11.             t.program,
  12.             t.module,
  13.             s.SQL_TEXT
  14.        from v$active_session_history t, v$sql s
  15.       where t.sample_time > to_date('2019-07-15 22:00:00', 'yyyy-mm-dd hh24:mi:ss')
  16.         and t.sample_time < to_date('2019-07-15 23:00:00', 'yyyy-mm-dd hh24:mi:ss')
  17.         and t.temp_space_allocated is not null
  18.         and t.sql_id = s.SQL_ID
  19.       order by t.temp_space_allocated desc)
  20.   where rownum < 50
  21.   order by temp_used desc;
复制代码





欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) Powered by Discuz! X3.2