- 查询dba_temp_files
select file_id, file_name, tablespace_name, bytes/1048576 "MB" from dba_temp_files;
image.png
- 根据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
- 进一步细化时间，秒级别（注意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
- 从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
- 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 )
- 查詢執行計劃(備清除了就無法通過此方法查詢到)
select * from table(dbms_xplan.display_awr('2wcjbyv5s3dcb'));
网友评论