ORACLE 臨時表空間使用分析

1. 查询dba_temp_files

select file_id, file_name, tablespace_name, bytes/1048576 "MB" from dba_temp_files;

image.png

2. 根据DBA_HIST_ACTIVE_SESS_HISTORY中的TEMP_SPACE_ALLOCATED字段进一步定位突發增長量

select instance_number, to_char(sample_time,'mm-dd hh24:mi'), sum(TEMP_SPACE_ALLOCATED)/(1024*1024) as ALLOCATED
from dba_hist_active_sess_history 
where sample_time between trunc(sysdate) and trunc(sysdate)+0.5/24
group by instance_number, to_char(sample_time,'mm-dd hh24:mi')
order by 1, 2;

image.png

3. 进一步细化时间，秒级别（注意DBA_HIST_ACTIVE_SESS_HISTORY默认采样数据间隔为10s）

select instance_number, to_char(sample_time,'mm-dd hh24:mi:ss'), sum(TEMP_SPACE_ALLOCATED)/(1024*1024) as ALLOCATED
from dba_hist_active_sess_history 
where sample_time between trunc(sysdate)+4/(24*60) and trunc(sysdate)+7/(24*60)
group by instance_number, to_char(sample_time,'mm-dd hh24:mi:ss')
order by 1, 2;

image.png

4. 从00:04:47 开始，到 00:05:57 结束，temp增长到624M，上面的查询可以直接加入sql_id字段定位，发现都是同一个SQL导致的：

select instance_number, to_char(sample_time,'mm-dd hh24:mi:ss'),sql_id,sum(TEMP_SPACE_ALLOCATED)/(1024*1024) as ALLOCATED
from dba_hist_active_sess_history 
where sample_time between trunc(sysdate)+4/(24*60) and trunc(sysdate)+7/(24*60)
group by instance_number, to_char(sample_time,'mm-dd hh24:mi:ss'),sql_id
having sum(TEMP_SPACE_ALLOCATED)/(1024*1024)>100
order by 1, 2; 

image.png

5. SQL_ID 2wcjbyv5s3dcb对应的文本为(通過 dba_hist_sqltext 或 v$sql查詢)

select t."SQL_FULLTEXT" from v$sql t where sql_id = '2wcjbyv5s3dcb';

#輸出
MERGE INTO MEC_ACTIVITY_COST_ACCOUNT A USING 
( SELECT D.ACTIVITY_ID,D.ACTIVITY_NO,D.ACTIVITY_NAME
,SUM(D.MER_DISCOUNT_VALUE + D.MP_DISCOUNT_VALUE) AS MONEY_COST_USE ,COUNT(1) AS DISCOUNT_TIMES 
FROM V_MER_DISCOUNT_DETAIL D 
WHERE D.ACCOUNT_DATE = :B1 
GROUP BY D.ACTIVITY_ID,D.ACTIVITY_NO,D.ACTIVITY_NAME 
)B ON (B.ACTIVITY_ID=A.ACTIVITY_ID AND A.ACCOUNT_DATE = :B1 ) 
WHEN MATCHED THEN UPDATE SET 
A.END_VALUE = (NVL(A.TOTAL_COST_VALUE,0) - NVL(A.PRE_TOTAL_COST_VALUE,0)) + NVL(A.PRE_END_VALUE,0) - NVL(B.MONEY_COST_USE,0)
, A.DISCOUNT_TIMES = B.DISCOUNT_TIMES 
WHEN NOT MATCHED THEN INSERT 
(ACTIVITY_ID,ACTIVITY_NO,PRE_TOTAL_COST_VALUE,PRE_END_VALUE,TOTAL_COST_VALUE,END_VALUE,DISCOUNT_TIMES, CREATE_BY, CREATE_DATE, ACCOUNT_DATE) 
VALUES 
(B.ACTIVITY_ID,B.ACTIVITY_NO,NULL,0,NULL,0-B.MONEY_COST_USE,B.DISCOUNT_TIMES,'bill_job_account_3',SYSDATE,:B1 )

6. 查詢執行計劃(備清除了就無法通過此方法查詢到)

select * from table(dbms_xplan.display_awr('2wcjbyv5s3dcb'));