Oracle管理命令之temp

 

------查看temp表空间使用情况
set linesize 1000 pagesize 5000
col EVENT for a30
SELECT *
  FROM (SELECT a.INST_ID,
               s.sid ,
               a.sql_id,
               a.TABLESPACE,
               a.USERNAME,
               s.event,
               s.status,
               a.CONTENTS,
               a.SEGTYPE,
               round(a.BLOCKS * 8 / 1024,2) size_mb
          FROM gv$sort_usage a
          left join gv$session s 
            on a.INST_ID = s.INST_ID
               and a.SESSION_ADDR = s.SADDR
          --where sid=4370
         ORDER BY a.INST_ID,a.BLOCKS DESC)
 WHERE rownum < 21;

------temp表空间使用情况
--11G下：
select tablespace_name,
       round(tablespace_size / 1024 / 1024 / 1024, 2) as total,
       round(free_space / 1024 / 1024 / 1024, 2) as free,
       round((tablespace_size - free_space) / 1024 / 1024 / 1024, 2) used_size,
       round(nvl(free_space, 0) * 100 / tablespace_size, 3) pct_free
  from dba_temp_free_space;

       
       
---10G下：
SELECT temp_used.tablespace_name,
       total - used as "Free",
       total as "Total",
       round(nvl(total - used, 0) * 100 / total, 3) "Free percent"
  FROM (SELECT tablespace_name, SUM(bytes_used) / 1024 / 1024 used
          FROM GV_$TEMP_SPACE_HEADER
         GROUP BY tablespace_name) temp_used,
       (SELECT tablespace_name, SUM(bytes) / 1024 / 1024 total
          FROM dba_temp_files
         GROUP BY tablespace_name) temp_total
 WHERE temp_used.tablespace_name = temp_total.tablespace_name;

set linesize 1000 pagesize 5000
col SAMPLE_TIME for a25
col SQL_ID for a15
col  PROGRAM for a30
col MACHINE for a30
select *
  from (select to_char(SAMPLE_TIME, 'yyyy-mm-dd hh24:mi:ss') SAMPLE_TIME,
               USER_ID,
               SQL_ID,
               PROGRAM,
               MACHINE,
               sum(TEMP_SPACE_ALLOCATED) / 1024 / 1024 / 1024 size_gb
          from V$ACTIVE_SESSION_HISTORY
         where SAMPLE_TIME > sysdate - 2 / 24
         group by to_char(SAMPLE_TIME, 'yyyy-mm-dd hh24:mi:ss') ,
                  USER_ID,
                  SQL_ID,
                  PROGRAM,
                  MACHINE)
 where size_gb > 1
 order by SAMPLE_TIME;




-----查看会话打开的游标
SELECT a.SQL_ID, a.SORTS, a.ROWS_PROCESSED / a.EXECUTIONS
  FROM gv$sql a
  join gv$open_cursor b
    on a.INST_ID = b.INST_ID
       and a.sql_id = b.sql_id
 WHERE a.PARSING_SCHEMA_NAME = 'ADMIN'
       AND a.EXECUTIONS > 0
       AND a.SORTS > 0
       AND b.sid = '5138'
 ORDER BY 3;



--------改良一下v$sort_usage    11.2.0.2及以上
set linesize 1000 pagesize 500
col OSUSER for a15
col USERNAME for a15
col TABLESPACE for a15
col  CONTENTS for a15
col SEGTYPE for a15
select *
  from (SELECT k.INST_ID,
               k.KTSSOSES SADDR,
               s.SID,
               s.status,
               k.KTSSOSNO SERIAL#,
               s.USERNAME,
               s.OSUSER,
               k.KTSSOSQLID sql_id,
               k.KTSSOTSN tablespace,
               decode(k.KTSSOCNT, 0, 'PERMANENT', 1, 'TEMPORARY') contents,
               decode(k.KTSSOSEGT,
                      1,
                      'SORT',
                      2,
                      'HASH',
                      3,
                      'DATA',
                      4,
                      'INDEX',
                      5,
                      'LOB_DATA',
                      6,
                      'LOB_INDEX',
                      'UNDEFINED') segtype,
               k.KTSSOFNO,
               k.KTSSOBNO,
               k.KTSSOEXTS,
               k.KTSSOBLKS,
               round(k.KTSSOBLKS * p.value / 1024 / 1024, 2) size_Mb,
               k.KTSSOFNO segrfno#
          FROM x$ktsso k,
               gv$session s,
               (SELECT VALUE
                  FROM v$parameter a
                 WHERE a.NAME = 'db_block_size') p
         WHERE k.KTSSOSES = s.SADDR
           AND k.KTSSOSNO = s.SERIAL#
           and k.INST_ID=s.INST_ID
           /*and USERNAME in ('UIMPDB',
                            'IWEB',
                            'ADMIN',
                            'ZQ_CYYW',
                            'HKJTCY_HOSTING_ADMIN',
                            'PORTA',
                            'hkjtcy_aepemcdb',
                            'hkjtcy_bfmdb',
                            'hkjtcy_bfmsso',
                            'HKJTCY_AEPSTATDB',
                            'HKJTCY_REPORT')*/
         order by size_Mb desc)
 where rownum < 21;
----SEGTYPE列的不同的值各有什么意义：
SORT：SQL排序使用的临时段，包括order by、group by、union、distinct、窗口函数(window function)、建索引等产生的排序。
DATA：临时表(Global Temporary Table)存储数据使有的段。
INDEX：临时表上建的索引使用的段。
HASH：hash算法，如hash连接所使用的临时段。
LOB_DATA和LOB_INDEX：临时LOB使用的临时段。

----根据上述的段类型，大体可以分为三类占用：
1、SQL语句排序、HASH JOIN占用
2、临时表占用
3、临时LOB对象占用



--------------------------采集temp使用问题
create or replace procedure proc_ly_temp_message is
  V_ERROR VARCHAR2(4000);
  V_SQL   VARCHAR2(4000);
  V_DELETE   VARCHAR2(4000);

  /*
  --20160405 修改脚本中计算used_percent ，增加 *100
  create table xjmon.ly_t_collect_error
(
  hostname        VARCHAR2(50),
  instance_name   VARCHAR2(50),
  log_date        DATE,
  tablespace_name VARCHAR2(200),
  temp_total_mb   NUMBER,
  temp_used_mb    NUMBER,
  temp_free_mb    NUMBER,
  used_percent    NUMBER,
  partition_id    NUMBER,
  beizhu          VARCHAR2(4000)
) tablespace IT_XINJU;
create table xjmon.LY_T_TEMP_MESSAGE
(
  collect_date DATE,
  inst_id      NUMBER,
  sid          NUMBER,
  sql_id       VARCHAR2(13),
  tablespace   VARCHAR2(31),
  username     VARCHAR2(30),
  event        VARCHAR2(64),
  status       VARCHAR2(8),
  contents     VARCHAR2(9),
  segtype      VARCHAR2(9),
  size_mb      NUMBER
) tablespace IT_XINJU;
create index xjmon.ind_TEMP_MESSAGE_date on  xjmon.LY_T_TEMP_MESSAGE(collect_date)  tablespace IT_XINJU;
  */

begin
  V_SQL := 'insert into xjmon.ly_t_temp_message
    SELECT *
  FROM (SELECT sysdate collect_date,
               a.INST_ID,
               s.sid,
               a.sql_id,
               a.TABLESPACE,
               a.USERNAME,
               s.event,
               s.status,
               a.CONTENTS,
               a.SEGTYPE,
               round(a.BLOCKS * 8 / 1024, 2) size_mb
          FROM gv$sort_usage a
          left join gv$session s
            on a.INST_ID = s.INST_ID
               and a.SESSION_ADDR = s.SADDR
         ORDER BY a.INST_ID, a.BLOCKS DESC)
 WHERE rownum < 21 and size_mb>500';
 V_DELETE := 'delete from xjmon.ly_t_temp_message where collect_date<sysdate-10';
  ------
  BEGIN
    EXECUTE IMMEDIATE V_SQL;
    EXECUTE IMMEDIATE v_delete;
    COMMIT;
  EXCEPTION
    WHEN OTHERS THEN
      V_ERROR := SUBSTR(SQLERRM, 1, 2500);
      INSERT INTO xjmon.ly_t_collect_error
        (LOG_DATE, BEIZHU, partition_id)
      VALUES
        (SYSDATE, V_ERROR, to_number(to_char(sysdate, 'dd')));
      COMMIT;
  END;
end;
/



DECLARE
  JOBS INT;
BEGIN

  SYS.DBMS_JOB.SUBMIT(JOB       => JOBS,
                      WHAT      => 'PROC_LY_TEMP_MESSAGE;',
                      NEXT_DATE => TO_DATE('2016-07-15 08:17:00',
                                           'YYYY-MM-DD HH24:MI:SS'),
                      INTERVAL  => 'SYSDATE+1/(24*60*60)');
  COMMIT;
END;
/


-----------------11g查询历史temp消耗较高的语句
select instance_number, username, sql_id, count(*), max(size_gb)
  from (select to_char(a.sample_time, 'yyyy-mm-dd hh24:mi:ss') sample_time,
               a.instance_number,
               b.username,
               a.sql_id,
               a.temp_space_allocated / 1024 / 1024 / 1024 size_gb
          from dba_hist_active_sess_history a, dba_users b
         where a.sample_time > sysdate - 5
               and a.user_id = b.user_id
               --and a.instance_number = 3
               )
 where size_gb > 10
 group by instance_number, username, sql_id;
 
set linesize 1000 pagesize 5000
select *
  from (select to_char(a.sample_time, 'yyyy-mm-dd hh24:mi:ss') sample_time,
               a.INST_ID,
               b.username,
               a.sql_id,
               a.temp_space_allocated / 1024 / 1024 / 1024 size_gb
          from gV$active_session_history a, dba_users b
         where a.sample_time > sysdate - 2
               and a.user_id = b.user_id)
 where size_gb > 10
 order by sample_time, INST_ID;