个人简介
偶尔有空上来看看
文章分类
文章存档
最近访客
相关博文
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
分类： oracle
2021-02-02 13:54:40
--指定时间1小时内的占用temp过高的sql
set pages 300 lin 120
col sql_opname for a20
col module for a40 trunc
col sql_id for a13
select * from (
select user_id,sql_id,sql_exec_id,sql_opname,module,round(sum(temp_space_allocated)/1024/1024/1024,1) g
from dba_hist_active_sess_history
where sample_time>sysdate-1 and sample_time
and temp_space_allocated is not null
group by user_id,sql_id,sql_exec_id,sql_opname,module
order by g desc) where rownum<11;
建议将上述内容存为 high_temp_space.sql，调用时：
sql> @high_temp_space sysdate
也可以将 temp_space_allocated替换为pga_allocated，来查看pga占用过高的sql_id.
根据查出来sql_id，可以看出对应的sql：
col sql_text for a80
select sql_text from v$sqltext where sql_id='&sql_id' order by piece;
set pages 300 lin 120
col sql_opname for a20
col module for a40 trunc
col sql_id for a13
select * from (
select user_id,sql_id,sql_exec_id,sql_opname,module,round(sum(temp_space_allocated)/1024/1024/1024,1) g
from dba_hist_active_sess_history
where sample_time>sysdate-1 and sample_time
group by user_id,sql_id,sql_exec_id,sql_opname,module
order by g desc) where rownum<11;
建议将上述内容存为 high_temp_space.sql，调用时：
sql> @high_temp_space sysdate
也可以将 temp_space_allocated替换为pga_allocated，来查看pga占用过高的sql_id.
根据查出来sql_id，可以看出对应的sql：
col sql_text for a80
select sql_text from v$sqltext where sql_id='&sql_id' order by piece;
给主人留下些什么吧！~~