------查看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;
浙公网安备 33010602011771号