-
--检查temp大小
-
set lin 200 pages 100
-
col tablespace_name for a20
-
col FILE_NAME for a40
-
select tablespace_name,file_name,round(bytes/1024/1024)m from dba_temp_files order by 1,2;
-
-
col TABLESPACE_NAME for a20
-
select TABLESPACE_NAME,file_id,round(BYTES_USED/1024/1024,2) BYTES_USED_mb,
-
round(BLOCKS_USED*8/1024,2) BLOCKS_USED_m,BYTES_FREE/1024/1024 free_mb from v$temp_space_header;
-
-
--检查当前使用情况
-
col username for a15
-
col TABLESPACE for a18
-
col EVENT for a30
-
select s.sid,s.username,s.state,s.event,s.sql_id,u.tablespace,u.contents,u.segtype,round(((u.blocks*p.value)/1024/1024),2) mb
-
from v$session s ,v$sort_usage u,v$parameter p
-
where s.saddr=u.session_addr
-
and upper(p.name)='DB_BLOCK_SIZE'
-
order by mb desc;
-
-
--检查历史使用情况
-
select *
-
from (select instance_number, sql_id, max(temp_sum_mb) temp_max
-
from (select instance_number, sample_time, sql_id, round(sum(nvl(temp_space_allocated, 0))/1024/1024) temp_sum_mb
-
from dba_hist_active_sess_history
-
where sample_time between sysdate-1/24 and sysdate
-
group by instance_number, sample_time, sql_id)
-
group by instance_number, sql_id
-
order by temp_max desc)
-
where rownum <= 10;
--看看sql 内容
col sql_text for a80
set lin 80 pages 1000
select sql_text from v$sqltext where sql_id='&sql_id' order by piece;
--看看sql计划
set long 1000000
set longchunksize 1000000
set lin 300 pages 1000
set trim on
set trimspool on
set echo off
set feedback off
select dbms_sqltune.report_sql_monitor(sql_id=>'&sqlid',type=>'TEXT',report_level=>'ALL') mon_rpt from dual;
