Oracle Temp
Oracle Temp space information Oracle Space usage
set linesize 200 pagesize 200
col description for a50
col property_value for a20
col property_name for a30
select * from database_properties where property_name='DEFAULT_TEMP_TABLESPACE';
PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
------------------------------ -------------------- --------------------------------------------------
DEFAULT_TEMP_TABLESPACE TEMP Name of default temporary tablespace
select 'TotalSpace ' || (sum(blocks)*8)/1024/1024 ||' GB' Total from dba_temp_files
where tablespace_name=(select property_value from database_properties where property_name = 'DEFAULT_TEMP_TABLESPACE')
set linesize 200 pagesize 200
select TABLESPACE_NAME,trunc(TABLESPACE_SIZE/1024/1024/1024,2) TABLESPACE_SIZE_GB, trunc(ALLOCATED_SPACE/1024/1024/1024,2) ALLOCATED_SPACE_GB, trunc(FREE_SPACE/1024/1024/1024,2 ) FREE_SPACE_GB,
(ROUND((1- (FREE_SPACE / ALLOCATED_SPACE))*100, 2)) "TotalUsed%"
, case when (ROUND((1- (FREE_SPACE / ALLOCATED_SPACE))*100, 2)>85.00) then '---(85.00)% full :-( ***'
else 'Good :-)'
end as ATTENTION
from dba_temp_free_space;
set linesize 500 pagesize 500
col username for a20
col sql_text for a50
col OSUSER for a15
col PROGRAM for a20
col kill for a18
SELECT distinct
''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill,
a.con_id,
a.username username ,
a.osuser,
a.program,
a.sql_id,
a.sql_child_number,
a.sql_exec_start,
q.plan_hash_value,
b.tablespace,
b.segtype,
sum(b.blocks) as BLOCKS,
substr(q.sql_text,1,50) sql_text
FROM gv$session a, gv$tempseg_usage b, gv$sql q
WHERE a.saddr = b.session_addr
and q.address = a.sql_address
and q.inst_id = a.inst_id
and q.hash_value = a.sql_hash_value
and q.CHILD_NUMBER = a.SQL_CHILD_NUMBER
and a.INST_ID = b.INST_ID
GROUP by GROUPING SETS((''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' , a.con_id,a.username, a.osuser, a.program, a.sql_id, a.sql_child_number, a.sql_exec_start, q.sql_text, q.plan_hash_value, b.tablespace, b.segtype, sysdate),(sysdate));
-- >11g for con_id
set linesize 300 numf 9999.99
col CREATION_TIME for a27
col NAME for a70
col tb_name for a15
select a.con_id,b.name tb_name, a.name, a.bytes/1024/1024/1024 Gb ,CREATE_BYTES/1024/1024/1024 CREATED_SIZE,to_char(CREATION_TIME,'dd-mm-yyyy hh24:mi') CREATION_TIME ,STATUS,ENABLED
from v$tempfile a, v$tablespace b
where a.ts#=b.ts#
and a.con_id= b.con_id
and a.con_id = (SELECT sys_context('USERENV', 'CON_ID') FROM dual)
;
- -tempfile info
set linesize 300
col TABLESPACE_NAME for a25
col FILE_NAME for a70
select tb.tablespace_name, bytes/1024/1024/1024 Gb,MAXBYTES/1024/1024/1024 Max_Gb,tf.file_name,AUTOEXTENSIBLE from dba_tablespaces tb left join dba_temp_files tf on tf.tablespace_name = tb.tablespace_name where tb.contents = 'TEMPORARY';
set linesize 300 numf 9999.99
col CREATION_TIME for a27
col NAME for a70
col tb_name for a15
select b.name tb_name, a.name, a.bytes/1024/1024/1024 Gb ,CREATE_BYTES/1024/1024/1024 CREATED_SIZE,to_char(CREATION_TIME,'dd-mm-yyyy hh24:mi') CREATION_TIME ,STATUS,ENABLED
from v$tempfile a, v$tablespace b
where a.ts#=b.ts#
alter session set nls_date_format='dd-mm-yyyy hh24:mi:ss';
set pages 999 lines 400
col FILE_NAME format a70
col INCREMENT_BY_GB for 9999.99
col MAXSIZE_GB for 999.99
select v.FILE#,d.TABLESPACE_NAME, d.FILE_NAME, d.BYTES/1024/1024/1024 SIZE_GB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024/1024 MAXSIZE_GB, d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024/1024 INCREMENT_BY_GB
,CREATION_TIME
from dba_temp_files d, v$tempfile v
where d.FILE_ID = v.FILE#
order by d.TABLESPACE_NAME, d.FILE_NAME;
-- % !!!!!!!!!
col TABLESPACE_NAME for A15
SELECT D.tablespace_name, SPACE/1024/1024/1024 "SUM_SPACE(GB)",
blocks "SUM_BLOCKS",
used_space /1024/1024/1024 "USED_SPACE(GB)",
(SPACE - used_space ) /1024/1024/1024 "FREE_SPACE(GB)" ,
Round(Nvl(used_space, 0)/SPACE * 100, 2) "USED(%)"
FROM (SELECT tablespace_name, sum(decode(maxbytes, 0, bytes, maxbytes)) SPACE, SUM(blocks) BLOCKS
FROM dba_temp_files
GROUP BY tablespace_name) D,
(SELECT tablespace, SUM(blocks * 8192) USED_SPACE FROM v$sort_usage
GROUP BY tablespace) F
WHERE D.tablespace_name = F.tablespace(+)
-- AND D.tablespace_name='TEMP'
AND D.tablespace_name in (select tb.tablespace_name from dba_tablespaces tb where tb.contents = 'TEMPORARY')
;
set pagesize 10000 linesize 300 tab off
col tablespace_name format A28 heading "Tablespace"
col ts_type format A13 heading "TS Type"
col segments format 999999 heading "Segments"
col files format 9999
col allocated_mb format 999,999,990.00 heading "Allocated Size|(Mb)"
col used_mb format 999,999,990.00 heading "Used Space|(Mb)"
col Free_mb format 999,999,990.00 heading "Free Space|(Mb)"
col used_pct format 999 heading "Used|%"
col max_ext_mb format 999,999,990.00 heading "Max Size|(Mb)"
col max_free_mb format 999,999,990.00 heading "Max Free|(Mb)"
col max_used_pct format 999 heading "Max Used|(%)"
BREAK ON REPORT
COMPUTE SUM LABEL "TOTAL SUM ==========>" AVG LABEL "AVERAGE ==========>" OF segments files allocated_mb used_mb Free_MB max_ext_mb ON REPORT
WITH df AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_data_files GROUP BY tablespace_name),
tf AS (SELECT tablespace_name, SUM(bytes) bytes, COUNT(*) cnt, DECODE(SUM(DECODE(autoextensible,'NO',0,1)), 0, 'NO', 'YES') autoext, sum(DECODE(maxbytes,0,bytes,maxbytes)) maxbytes FROM dba_temp_files GROUP BY tablespace_name),
tm AS (SELECT tablespace_name, used_percent FROM dba_tablespace_usage_metrics),
ts AS (SELECT tablespace_name, COUNT(*) segcnt FROM dba_segments GROUP BY tablespace_name)
-- TEMP TS
SELECT d.tablespace_name,
d.status,
DECODE(d.contents,'PERMANENT',DECODE(d.extent_management,'LOCAL','LM','DM'),'TEMPORARY','TEMP',d.contents)||'-'||DECODE(d.allocation_type,'UNIFORM','UNI','SYS')||'-'||decode(d.segment_space_management,'AUTO','ASSM','MSSM') ts_type,
a.cnt,
0,
ROUND(NVL(a.bytes / 1024 / 1024, 0), 3) Allocated_MB,
ROUND(NVL(t.ub*d.block_size, 0)/1024/1024, 3) Used_MB,
ROUND((NVL(a.bytes ,0)/1024/1024 - NVL((t.ub*d.block_size), 0)/1024/1024), 3) Free_MB,
ROUND(NVL((t.ub*d.block_size) / a.bytes * 100, 0), 2) Used_pct,
ROUND(a.maxbytes / 1024 / 1024, 3) max_size_mb,
ROUND(a.maxbytes / 1024 / 1024, 3) - ROUND(NVL(t.ub*d.block_size, 0)/1024/1024, 3) Total_free_MB,
ROUND(NVL(m.used_percent,0), 2) Max_used_pct
FROM dba_tablespaces d, tf a, tm m, (SELECT ss.tablespace_name , sum(ss.used_blocks) ub FROM gv$sort_segment ss GROUP BY ss.tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.tablespace_name = m.tablespace_name(+)
AND d.extent_management = 'LOCAL'
AND d.contents = 'TEMPORARY' ;
set linesize 300
col name for a12
col "Size (gb)" for a20
col "Used (gb)" for a20
col "Used %" for a20
SELECT d.status "Status", d.tablespace_name "Name",
d.contents "Type",
d.extent_management "ExtMgmnt",
TO_CHAR(NVL(a.bytes / 1024/1024/1024, 0), '99,999,990') "Size (gb)",
TO_CHAR(NVL(t.bytes,0)/1024/1024/1024, '99999,999') "Used (gb)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '99990.00') "Used %"
FROM sys.dba_tablespaces d,
(select tablespace_name, sum(bytes) bytes from dba_temp_files group by tablespace_name) a,
(select tablespace_name, sum(bytes_cached) bytes from v$temp_extent_pool group by tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management like 'LOCAL'
AND d.contents like 'TEMPORARY';
select TABLESPACE_NAME,
trunc(TABLESPACE_SIZE/1024/1024/1024,2) TABLESPACE_SIZE_GB,
trunc(ALLOCATED_SPACE/1024/1024/1024,2) ALLOCATED_SPACE_GB,
trunc(FREE_SPACE/1024/1024/1024,2 ) FREE_SPACE_GB,
(ROUND((1- (FREE_SPACE / ALLOCATED_SPACE))*100, 2)) "TotalUsed%",
case when (ROUND((1- (FREE_SPACE / ALLOCATED_SPACE))*100, 2)>85.00) then '---(85.00)% full :-( ***'
else 'Good :-)'
end as ATTENTION
from dba_temp_free_space;
col FILE_NAME for a30
select * from dba_temp_files where tablespace_name like 'TEMP%';
col sql_text for a30
col error_msg for a30
select
user_id,
session_id,
status,
start_time,
suspend_time,
sql_text,
error_number,
error_msg
from dba_resumable;
set line 300 pagesize 200
col lastcallet for a10
col proginfo for a50
col username for a20
col tablespace for a15
col serial# for 99999999
col kill for a15
col event for a30
cursor temp_use is
select * from (
select
''''||s.sid ||','|| s.serial#||',@'||s.inst_id||'''' kill ,
s.status,
s.username,
u.tablespace,
sum(u.blocks*p.value/1024/1024) mbused ,
sum(u.extents) noexts,
nvl(s.module,s.program) proginfo,
floor(last_call_et/3600)||':'||floor(mod(last_call_et,3600)/60)||':'||mod(mod(last_call_et,3600),60) lastcallet,s.sql_id,PREV_SQL_ID,event
from gv$sort_usage u,gv$session s,v$parameter p
where u.session_addr = s.saddr
and u.inst_id= s.inst_id
and p.name = 'db_block_size'
--and s.USERNAME not in ( 'SYS' ,'SYSTEM','DBSNMP','SYSMAN' ,'OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS', 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
group by ''''||s.sid ||','|| s.serial#||',@'||s.inst_id||'''' ,s.status,s.username,u.tablespace,
nvl(s.module,s.program),floor(last_call_et/3600)||':'||floor(mod(last_call_et,3600)/60)||':'||mod(mod(last_call_et,3600),60),s.sql_id ,PREV_SQL_ID,event
having sum(u.blocks*p.value/1024/1024) >1 -- MB change based on requirement
order by 5 desc,3 )
where 1=1
-- and rownum < 11
;
set linesize 500 pagesize 200
col username for a15
col sid_serial for a15
col sql_text for a100 word_wrapped
col hash_value for 999999999999
col tablespace for a15
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, s.username,t.blocks * tbs.block_size/1024/1024 mb_used, t.tablespace,t.sql_id sql_id, q.hash_value, dbms_lob.substr(q.sql_text,2000,1) sql_text
from gv$sort_usage t, gv$session s, gv$sqlarea q, dba_tablespaces tbs
where t.session_addr = s.saddr
and t.sqladdr = q.address (+)
and t.tablespace = tbs.tablespace_name
and s.sql_id is not null
order by 3 desc;
kill username mb_used tablespace sql_id hash_value sql_text
--------------- --------------- ---------- --------------- ------------- ------------- ----------------------------------------------------------------------------------------------------
'742,59484,@1' ANUJ 314 TEMP g9pw0rahq8jw8 2707703688 select * from TEST1 order by n1,n2 desc
set linesize 300 pagesize 200
col kill for a15
col sid_serial for a15
col osuser for a12
col module for a30
col username for a15
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, s.username, s.sql_id,s.osuser, p.spid, s.module, s.program, t.tablespace, count(*) sort_ops, sum (t.blocks) * tbs.block_size / 1024 / 1024 mb_used
from gv$sort_usage t, gv$session s, dba_tablespaces tbs, gv$process p
where t.session_addr = s.saddr
and t.inst_id = s.inst_id
and s.paddr = p.addr
and s.inst_id = p.inst_id
and t.tablespace = tbs.tablespace_name
and s.sql_id is not null
group by ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''', s.username,s.sql_id, s.osuser, p.spid, s.module,s.program, tbs.block_size, t.tablespace
order by mb_used desc;
KILL USERNAME SQL_ID OSUSER SPID MODULE PROGRAM TABLESPACE SORT_OPS MB_USED
--------------- --------------- ------------- ------------ ------------------------ ------------ ------------------------------------------------ -------------------- ---------- ----------
'742,59484,@1' ANUJ g9pw0rahq8jw8 oracle 19945 SQL*Plus sqlplus@************************* * (TNS V1-V3) TEMP 1 314
--with s.CON_ID>1 !!!!!
set linesize 200 pagesize 200
col kill for a15
col sid_serial for a15
col osuser for a12
col module for a12
col username for a15
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, s.CON_ID,s.username, s.sql_id,s.osuser, p.spid, s.module, s.program, t.tablespace, count(*) sort_ops, sum (t.blocks) * tbs.block_size / 1024 / 1024 mb_used
from gv$sort_usage t, gv$session s, dba_tablespaces tbs, gv$process p
where t.session_addr = s.saddr
and t.inst_id = s.inst_id
and s.paddr = p.addr
and s.inst_id = p.inst_id
and t.tablespace = tbs.tablespace_name
and s.sql_id is not null
--and s.CON_ID>1
group by ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''', s.CON_ID,s.username,s.sql_id, s.osuser, p.spid, s.module,s.program, tbs.block_size, t.tablespace
order by mb_used desc;
col tablespace for a20
col username for a15
col PRGM for a25
col TEMPSIZE for a15
select ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,s.username,rpad(s.program,15,' ') prgm,rpad(su.blocks*16384/(1024*1024)||' mb ',10,' ') tempsize,su.segtype, su.tablespace,s.sql_id
from gv$session s,gv$sort_usage su
where s.saddr=su.session_addr
and s.sql_id is not null
order by su.blocks ;
KILL USERNAME PRGM TEMPSIZE SEGTYPE TABLESPACE SQL_ID
--------------- --------------- ------------------------------------------------------------ ---------------------------------------- --------- -------------------- -------------
'742,59484,@1' ANUJ sqlplus@ibrac01 628 MB SORT TEMP g9pw0rahq8jw8
set linesize 200 pagesize 200
col sql_text for a70
with temp_usage as
(
select ''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill,a.sql_id,a.username, a.osuser, b.tablespace, b.blocks, c.sql_text from gv$session a, gv$tempseg_usage b, gv$sqlarea c
where a.saddr = b.session_addr
and c.address = a.sql_address
and c.hash_value = a.sql_hash_value
and a.username not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
order by b.tablespace, b.blocks
)
select kill ,sql_id, username,sum((BLOCKS)*16)/1024 "MB" ,sql_text from temp_usage
group by kill,sql_id,username,sql_text
order by 4 desc
;
KILL SQL_ID USERNAME MB SQL_TEXT
--------------- ------------- --------------- ---------- ----------------------------------------------------------------------
'742,59484,@1' g9pw0rahq8jw8 ANUJ 628 select * from TEST1 order by n1,n2 desc
set line 200 pagesize 200
col lastcallet for a10
col proginfo for a25
col username for a20
col tablespace for a15
col serial# for 99999999
col kill for a15
col event for a30
cursor temp_use is
select * from (
select
''''||s.sid ||','|| s.serial#||',@'||s.inst_id||'''' kill ,
s.status,
s.username,
u.tablespace,
sum(u.blocks*p.value/1024/1024) mbused ,
sum(u.extents) noexts,
nvl(s.module,s.program) proginfo,
floor(last_call_et/3600)||':'||floor(mod(last_call_et,3600)/60)||':'||mod(mod(last_call_et,3600),60) lastcallet,s.sql_id,event
from gv$sort_usage u,gv$session s,v$parameter p
where u.session_addr = s.saddr
and u.inst_id= s.inst_id
and p.name = 'db_block_size'
and s.USERNAME not in ( 'SYS' ,'SYSTEM','DBSNMP','SYSMAN' ,'OUTLN','MDSYS','ORDSYS','EXFSYS','DMSYS','WMSYS','CTXSYS', 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
group by ''''||s.sid ||','|| s.serial#||',@'||s.inst_id||'''' ,s.status,s.username,u.tablespace,
nvl(s.module,s.program),floor(last_call_et/3600)||':'||floor(mod(last_call_et,3600)/60)||':'||mod(mod(last_call_et,3600),60),s.sql_id ,event
order by 5 desc,3)
where rownum < 11;
KILL STATUS USERNAME TABLESPACE MBUSED NOEXTS PROGINFO LASTCALLET SQL_ID EVENT
--------------- -------- -------------------- --------------- ---------- ---------- ------------------------- ---------- ------------- ------------------------------
'742,59484,@1' INACTIVE ANUJ TEMP 314 314 SQL*Plus 0:0:0 g9pw0rahq8jw8 SQL*Net message from client
set linesize 200 pagesize 200
col kill for a15
col sql_text for a70
col username for a15
with temp_usage as
(
select ''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill,a.sql_id,a.username, a.osuser, b.tablespace, b.blocks, c.sql_text from gv$session a, gv$tempseg_usage b, gv$sqlarea c
where a.saddr = b.session_addr
and c.address = a.sql_address
and c.hash_value = a.sql_hash_value
and a.username not in ( 'SYS' , 'SYSTEM' , 'DBSNMP' , 'SYSMAN' , 'OUTLN' , 'MDSYS' , 'ORDSYS' , 'EXFSYS' , 'DMSYS' , 'WMSYS' , 'CTXSYS' , 'ANONYMOUS' , 'XDB' , 'ORDPLUGINS' , 'OLAPSYS' , 'PUBLIC','WWV_FLOW_PLATFORM' )
order by b.tablespace, b.blocks
)
select kill ,sql_id, username,sql_text,sum((BLOCKS)*16)/1024 "MB" from temp_usage
group by kill,sql_id,username,sql_text
order by 4 desc
;
KILL SQL_ID USERNAME SQL_TEXT MB
--------------- ------------- --------------- ---------------------------------------------------------------------- ----------
'742,59484,@1' g9pw0rahq8jw8 ANUJ select * from TEST1 order by n1,n2 desc 628
set line 200 pagesize 200
col tablespace for a20
col kill for a15
col host_name for a30
col username for a15
select * from (SELECT ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill, u.tablespace, s.username, s.logon_time, program, u.extents, ((u.blocks*8)/1024) as MB,i.inst_id,i.host_name,s.sql_id FROM gv$session s, gv$sort_usage u ,gv$instance i
WHERE s.saddr=u.session_addr
and u.inst_id=i.inst_id
and s.username is not null
order by MB DESC) a
where rownum<11;
KILL TABLESPACE USERNAME LOGON_TIM PROGRAM EXTENTS MB INST_ID HOST_NAME SQL_ID
--------------- -------------------- --------------- --------- ------------------------------------------------ ---------- ---------- ---------- ------------------------------ -------------
'742,59484,@1' TEMP ANUJ 27-AUG-17 sqlplus@**************************** (TNS V1-V3) 314 314 1 ******************************** g9pw0rahq8jw8
set pagesize 50 linesize 200
select
a.inst_id,
a.tablespace_name tablespace,
d.mb_total,
sum (a.used_blocks * d.block_size)/1024/1024 mb_used
-- , d.mb_total - sum (a.used_blocks * d.block_size)/1024/1024 mb_free
from gv$sort_segment a,
( select
b.name,
c.block_size,
sum (c.bytes) / 1024 / 1024 mb_total
from v$tablespace b, v$tempfile c
where b.ts#= c.ts#
group by b.name, c.block_size
) d
where a.tablespace_name = d.name
group by a.inst_id,a.tablespace_name, d.mb_total
/
INST_ID TABLESPACE MB_TOTAL MB_USED
---------- --------------- ---------- ----------
2 TEMP 639 2
1 TEMP 639 316
select min(snap_id),max(snap_id) from dba_hist_snapshot;
VARIABLE BgnSnap NUMBER
VARIABLE EndSnap NUMBER
exec select max(snap_id) -1 into :BgnSnap from dba_hist_snapshot ;
exec select max(snap_id) into :EndSnap from dba_hist_snapshot ;
set linesize 300
col DURATION for a25
col MODULE for a15
col ACTION for a15
select * from (
select instance_number as inst_id,
sql_id,
to_char(sql_exec_start,'dd.mm.yyyy hh24:mi:ss') as SQL_EXEC_START,
max(sample_time) - SQL_EXEC_START as duration,
sql_exec_id,
sql_plan_hash_value,
module,
action,
round(max(temp_space_allocated) / 1024 / 1024 / 1024, 3) as max_temp_gb,
round(max(pga_allocated) / 1024 / 1024 / 1024, 3) as max_pga_gb,
max(px_used) as max_px_used
from (select instance_number, sql_id, sql_exec_start, sql_exec_id, sql_plan_hash_value, module, action, sample_id,
sum(temp_space_allocated) as temp_space_allocated,
sum(pga_allocated) as pga_allocated,
count(distinct session_serial#) - 1 as px_used,
sample_time
from dba_hist_active_sess_history
where 1=1
and snap_id between :BgnSnap and :EndSnap --- <<<<<< change this
and sql_exec_id > 0
group by instance_number, sql_exec_start, sql_id, sql_exec_id, sql_plan_hash_value, module, action, sample_id, sample_time
having sum(temp_space_allocated) is not null)
group by instance_number, sql_id, SQL_EXEC_START, sql_exec_id, sql_plan_hash_value, module, action
having max(temp_space_allocated) / 1024 / 1024 / 1024 > 2 -- GB
order by 9 desc
) where rownum <= nvl('&3',10)
/
INST_ID SQL_ID SQL_EXEC_START DURATION SQL_EXEC_ID SQL_PLAN_HASH_VALUE MODULE ACTION MAX_TEMP_GB MAX_PGA_GB MAX_PX_USED
---------- ------------- ------------------- ------------------------- ----------- ------------------- --------------- --------------- ----------- ---------- -----------
1 c8drdcj8g767v 07.01.2019 13:23:15 +000000000 08:45:03.377 16777216 421534165 SQL Developer 93.828 1.025 0
select * from (
select instance_number as inst_id,
sql_id,
to_char(sql_exec_start,'dd.mm.yyyy hh24:mi:ss') as SQL_EXEC_START,
max(sample_time) - SQL_EXEC_START as duration,
sql_exec_id,
sql_plan_hash_value,
module,
action,
round(max(temp_space_allocated) / 1024 / 1024 / 1024, 3) as max_temp_gb,
round(max(pga_allocated) / 1024 / 1024 / 1024, 3) as max_pga_gb,
max(px_used) as max_px_used
from (select instance_number, sql_id, sql_exec_start, sql_exec_id, sql_plan_hash_value, module, action, sample_id,
sum(temp_space_allocated) as temp_space_allocated,
sum(pga_allocated) as pga_allocated,
count(distinct session_serial#) - 1 as px_used,
sample_time
from dba_hist_active_sess_history
where 1=1
-- and snap_id > (select/*+ NO_UNNEST*/ min(snap_id) from dba_hist_snapshot where begin_interval_time > sysdate - nvl('&1',30))
and sql_exec_id > 0
group by instance_number, sql_exec_start, sql_id, sql_exec_id, sql_plan_hash_value, module, action, sample_id, sample_time
having sum(temp_space_allocated) is not null)
group by instance_number, sql_id, SQL_EXEC_START, sql_exec_id, sql_plan_hash_value, module, action
having max(temp_space_allocated) / 1024 / 1024 / 1024 > 2 -- GB
order by 9 desc
) where rownum <= nvl('&2',10)
===========
var snap_id1 number ;
var snap_id2 number;
var vsqlid varchar2(30);
begin :snap_id1 := '11111'; end;
/
begin :snap_id2:= '11112'; end;
/
begin :vsqlid:= 'XXXXXXXXX'; end;
/
set linesize 500 pagesize 300
col SQL_PROFILE for a20
col DURATION for a25
col MODULE for a20
col ACTION for a23
select to_char(sql_exec_start,'dd.mm.yyyy hh24:mi:ss') as SQL_EXEC_START,
max(sample_time) - SQL_EXEC_START as duration,
sql_id ,
sql_exec_id,
sql_plan_hash_value,
module,
action,
round(max(temp_space_allocated) / 1024 / 1024 / 1024, 3) as max_temp_gb,
round(max(pga_allocated) / 1024 / 1024 / 1024, 3) as max_pga_gb,
max(px_used) as max_px_used
from (select sql_id,sql_exec_start, sql_exec_id, sql_plan_hash_value, module, action, sample_id,
sum(temp_space_allocated) as temp_space_allocated,
sum(pga_allocated) as pga_allocated,
count(distinct session_serial#) - 1 as px_used,
sample_time
from dba_hist_active_sess_history
where 1=1
--and sql_id = :vsqlid
-- and snap_id between &2 and nvl('&&3', &&2)
and sql_exec_id > 0
-- and sample_time >sysdate -1
group by sql_id,sql_exec_start, sql_id, sql_exec_id, sql_plan_hash_value, module, action, sample_id, sample_time
having sum(temp_space_allocated) is not null)
group by SQL_EXEC_START, sql_id,sql_exec_id, sql_plan_hash_value, module, action
having max(temp_space_allocated) / 1024 / 1024 / 1024 > 2 -- GB
order by 1 ;
===============
SET LINESIZE 200 PAGESIZE 200
UNDEF num_days
COL startup_time FOR a30
COL db_name FOR a10
COL snap_start FOR 9999999
COL snap_end FOR 9999999
COL start_interval FOR a25
COL end_interval FOR a25
COL range_interval FOR a40
COL qtd_snaps FOR 999
SELECT
s.startup_time,
di.instance_name,
MIN(snap_id) snap_start,
MAX(snap_id) snap_end,
MIN(end_interval_time) start_interval,
MAX(end_interval_time) end_interval,
EXTRACT(DAY FROM(MAX(end_interval_time) ) - MIN(end_interval_time) ) || ' Days(s) ' || EXTRACT(HOUR FROM(MAX(end_interval_time) ) - MIN(end_interval_time) ) || ' Hour(s) '
|| EXTRACT(MINUTE FROM(MAX(end_interval_time) ) - MIN(end_interval_time) ) || ' Minute(s) ' range_interval, MAX(snap_id) - MIN(snap_id) qtd_snaps
FROM dba_hist_snapshot s, dba_hist_database_instance di
WHERE di.dbid = s.dbid
AND di.instance_number = s.instance_number
AND end_interval_time > DECODE(&num_days,0,TO_DATE('08-JAN-2019','DD-MON-YYYY'),3.14,s.end_interval_time,TO_DATE(SYSDATE,'dd/mm/yyyy') - (&num_days - 1) )
GROUP BY s.startup_time, di.instance_name
ORDER BY startup_time ASC;
set linesize 300 pagesize 300
col kill for a15
col USERNAME for a20
col SQL_ID for a13
col TEXT80 for a80 wrap
col CONTENTS for a20 just l
col SEGTYPE for a20
select /*+ ORDERED*/
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,
tu.USERNAME,
NVL(s.SQL_ID, s.prev_sql_id) SQL_ID,
tu.CONTENTS,
tu.SEGTYPE,
count(tu.SEGBLK#) SEG_COUNT,
count(distinct tu.SESSION_ADDR) PROC_COUNT,
round(sum(tu.BLOCKS * t.BLOCK_SIZE) / 1024 / 1024) size_MB,
substr(rpad(sa.SQL_TEXT, 80, '*'), 1, 80) as TEXT80
from gv$tempseg_usage tu, gv$session s, dba_tablespaces t, gv$sqlarea sa
where tu.SESSION_ADDR = s.SADDR
and tu.INST_ID = s.INST_ID
and s.SQL_ID = sa.SQL_ID
and s.INST_ID = sa.INST_ID
and tu.TABLESPACE = t.TABLESPACE_NAME
-- and s.SQL_ID = '1'
group by ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' , tu.USERNAME, NVL(s.SQL_ID, s.prev_sql_id), substr(rpad(sa.SQL_TEXT, 80, '*'), 1, 80), tu.CONTENTS, tu.SEGTYPE
order by sum(tu.BLOCKS) desc
/
define top=10
col STAR for a20
select SQL_ID,TEMP_MB,percent,rpad('*',percent*10/100,'*') star
from
(
select SQL_ID,sum(DELTA_TEMP_MB) TEMP_MB ,(ratio_to_report(sum(DELTA_TEMP_MB)) over ())*100 percent,rank() over(order by sum(DELTA_TEMP_MB) desc) rank
from
(
select SESSION_ID,SESSION_SERIAL#,sample_id,SQL_ID,SAMPLE_TIME,IS_SQLID_CURRENT,SQL_CHILD_NUMBER,temp_space_allocated,
greatest(temp_space_allocated - first_value(temp_space_allocated) over (partition by SESSION_ID,SESSION_SERIAL# order by sample_time rows 1 preceding),0)/power(1024,2) "DELTA_TEMP_MB"
from gv$active_session_history
where 1=1
and IS_SQLID_CURRENT='Y'
and sample_time > sysdate -1
order by 1,2,3,4
)
group by sql_id
having sum(DELTA_TEMP_MB) > 0
)
where rank < (&top+1)
order by rank
/
===-- with Con_id > 11g
alter session set nls_date_format='dd-mon-yy hh24:mi' ;
set linesize 500 pagesize 300
col sql_text for a70
col username for a20
col osuser for a15
col kill for a16
select
''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' as kill,
a.CON_ID,
sysdate,
a.username,
a.osuser,
a.program,
a.sql_id,
a.sql_child_number,
a.sql_exec_start,
q.plan_hash_value,
b.tablespace,
b.segtype,
sum(b.blocks) as BLOCKS,
q.sql_text
from gv$session a, gv$tempseg_usage b, gv$sql q
where a.saddr = b.session_addr
and q.address = a.sql_address
and q.inst_id = a.inst_id
and q.hash_value = a.sql_hash_value
and q.child_number = a.sql_child_number
and a.inst_id = b.inst_id
group by grouping SETS((''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''',a.con_id, a.username, a.osuser, a.program, a.sql_id, a.sql_child_number, a.sql_exec_start, q.sql_text, q.plan_hash_value, b.tablespace, b.segtype, sysdate),(sysdate));
====
select
* from (
select instance_number as inst_id,
ash.sql_id,
sql_opname,
to_char(sql_exec_start,'dd.mm.yyyy hh24:mi:ss') as SQL_EXEC_START,
max(sample_time) - SQL_EXEC_START as duration,
sql_exec_id,
sql_plan_hash_value,
module,
action,
machine,
round(max(temp_space_allocated) / 1024 / 1024 / 1024, 3) as max_temp_gb,
round(max(pga_allocated) / 1024 / 1024 / 1024, 3) as max_pga_gb,
max(px_used) as max_px_used
, replace(replace(dbms_lob.substr(t.SQL_TEXT,200),chr(10),' '),chr(13),' ') as SQL_TEXT
from (select instance_number, sql_id, sql_exec_start, sql_exec_id, sql_plan_hash_value, sql_opname, module, action, sample_id, machine,
sum(temp_space_allocated) as temp_space_allocated,
sum(pga_allocated) as pga_allocated,
count(distinct session_serial#) - 1 as px_used,
sample_time
from dba_hist_active_sess_history ash
where snap_id > (select/*+ NO_UNNEST*/ min(snap_id) from dba_hist_snapshot where begin_interval_time > sysdate - nvl('&1',30))
and sql_exec_id > 0
group by instance_number, sql_exec_start, sql_id, sql_exec_id, sql_plan_hash_value, sql_opname, module, action, sample_id, sample_time, machine
having sum(temp_space_allocated) is not null) ash
left join dba_hist_sqltext t on t.sql_id = ash.sql_id
group by instance_number, ash.sql_id, SQL_EXEC_START, sql_exec_id, sql_plan_hash_value, sql_opname, module, action, machine
, replace(replace(dbms_lob.substr(t.SQL_TEXT,200),chr(10),' '),chr(13),' ')
-- having max(temp_space_allocated) / 1024 / 1024 / 1024 > 100 -- GB
having max(temp_space_allocated) / 1024 / 1024 / 1024 > 1
order by 11 desc
) where rownum <= nvl('&2',10)
/
===
set linesize 500 pagesize 200
col sql_text for a50 wrap
col service for a15
col action for a10
col module for a10
col username for a10
col kill for a15
select
''''||sid ||','|| serial#||',@'||gv$session.inst_id ||'''' kill ,username, logon_time, round((sysdate-logon_time)*1440) timeonline_min, c.tempsize_gb ,a.*
from gv$session, ( select inst_id, sql_id, round(sum(tempseg_size) /1024/1024/1024) tempsize_gb from gv$sql_workarea_active
where 1=1
-- and tempseg_size > 1000000000 ---- for 1Gb
group by inst_id, sql_id) c, (
select inst_id instance_number,sql_id,
round((conc_wait_sec_exec / elap_sec_exec)*100) con_perc,
round((clu_wait_sec_exec / elap_sec_exec)*100) clust_perc,
round((user_io_wait_sec_exec / elap_sec_exec)*100) io_perc,
conc_wait_sec_exec, clu_wait_sec_exec, user_io_wait_sec_exec, cpu_time_sec_exec, elap_sec_exec, buffer_gets,
round((buffer_gets*32678)/1024/1024/1024) buffer_gb,
disk_reads, rows_processed, module,service, action,sql_text
from (
select inst_id, sql_id, sql_text,
round((concurrency_wait_time/1000000)/decode(executions,null,1,0,1, executions),2) conc_wait_sec_exec,
round((cluster_wait_time/1000000)/decode(executions,null,1,0,1,executions),2) clu_wait_sec_exec,
round((user_io_wait_time/1000000)/decode(executions,null,1,0,1,executions),2) user_io_wait_sec_exec,
round((direct_writes/decode(executions,null,1,0,1,executions)),2) direct_writes_exec,
round((cpu_time/1000000)/decode(executions,null,1,0,1,executions),2) cpu_time_sec_exec,
round(( elapsed_time/1000000)/decode(executions,null,1,0,1,executions),2) elap_sec_exec,
round((io_interconnect_bytes/decode(executions,null,1,0,1,executions)),2) io_inter_by_exec,
concurrency_wait_time, cluster_wait_time, user_io_wait_time, direct_writes, cpu_time, elapsed_time, io_interconnect_bytes,
round(sorts/decode(executions,null,1,0,1,executions),2) sort_exec, fetches, rows_processed, executions, parse_calls,
round(disk_reads/decode(executions,null,1,0,1,executions),2) disk_exec,
round(buffer_gets/decode(executions,null,1, 0,1,executions),2) buff_exec,
service, module, action, buffer_gets, disk_reads
from gv$sql
where users_opening > 0
and elapsed_time/decode(executions, null, 1, 0, 1, executions) >= 30000000)
) a
where a.sql_id = gv$session.sql_id
and a.instance_number = gv$session.inst_id
and a.sql_id = c.sql_id
and a.instance_number = c.inst_id;
select to_char(sql_exec_start,'dd.mm.yyyy hh24:mi:ss') as SQL_EXEC_START,
max(sample_time) - SQL_EXEC_START as duration,
sql_exec_id,
sql_plan_hash_value,
module,
action,
round(max(temp_space_allocated) / 1024 / 1024 / 1024, 3) as max_temp_gb,
round(max(pga_allocated) / 1024 / 1024 / 1024, 3) as max_pga_gb,
max(px_used) as max_px_used
from (select sql_exec_start, sql_exec_id, sql_plan_hash_value, module, action, sample_id,
sum(temp_space_allocated) as temp_space_allocated,
sum(pga_allocated) as pga_allocated,
count(distinct session_serial#) - 1 as px_used,
sample_time
from dba_hist_active_sess_history
where sql_id = 'gtfzbk4ajr15q' --- !!!!
-- and snap_id between &2 and nvl('&&3', &&2)
and sql_exec_id > 0
group by sql_exec_start, sql_id, sql_exec_id, sql_plan_hash_value, module, action, sample_id, sample_time
having sum(temp_space_allocated) is not null)
group by SQL_EXEC_START, sql_exec_id, sql_plan_hash_value, module, action
--having max(temp_space_allocated) / 1024 / 1024 / 1024 > 2 -- GB
order by 1
===
set pages 500 lines 500
col MIN_SAMPLE_TIME for a16
col MAX_SAMPLE_TIME for a16
col sql_text for a200
select * from (
select inst_id,
to_char(min(sample_time),'dd.mm.yyyy hh24:mi') as min_sample_time,
to_char(max(sample_time),'dd.mm.yyyy hh24:mi') as max_sample_time,
module,
sql_id,
sql_plan_hash_value as plan_hash_value,
count(distinct sql_exec_id) as execs,
to_char(max(temp_space_allocated),'999,999,999,999,999')as max_temp_space,
round(max(temp_space_allocated) / (select sum(user_bytes) from dba_temp_files where tablespace_name = 'TEMP') *100) as "TEMP%",
to_char(max(pga_allocated),'999,999,999,999') as max_pga,
max(px) - 1 as px
--(select substr(sql_text,1,200) as sql_text from gv$sqlarea a where a.sql_id = sql_id union select dbms_lob.substr(sql_text,200) from dba_hist_sqltext a where a.sql_id = sql_id) as sql_text
--,(select NVL(substr(a.sql_text,1,200), dbms_lob.substr(h.sql_text,200)) as sql_text from gv$sqlarea a, dba_hist_sqltext h where a.sql_id = s.sql_id and a.sql_id = h.sql_id) as sql_text
from (select inst_id as inst_id,
sample_time,
sql_id,
sql_plan_hash_value,
sql_exec_id,
module,
sum(temp_space_allocated) as temp_space_allocated,
sum(pga_allocated) as pga_allocated,
count(distinct session_serial#) - 1 as px
from gv$active_session_history
where sql_exec_id > 0
and sample_time > sysdate -2
group by inst_id,
sample_time,
sql_id,
sql_plan_hash_value,
sql_exec_id,
module
) s
group by inst_id, sql_id, sql_plan_hash_value, module
order by max(temp_space_allocated) desc nulls last)
where rownum <= nvl('&1', 10)
/
INST_ID MIN_SAMPLE_TIME MAX_SAMPLE_TIME MODULE SQL_ID PLAN_HASH_VALUE EXECS MAX_TEMP_SPACE TEMP% MAX_PGA PX
-------- ---------------- ---------------- -------------------------------------------------- ------------- --------------- ---------- -------------------- ---------- ---------------- ----------
1 15.01.2022 06:00 15.01.2022 06:00 DBMS_SCHEDULER 4kvt2r09bz39r 2912237509 2 8,388,608 0 38,593,536 -1
2 13.01.2022 14:20 15.01.2022 12:36 MMON_SLAVE 8mdz49zkajhw3 2609763588 24 6,291,456 0 51,159,040 -1
2 13.01.2022 22:00 13.01.2022 22:00 DBMS_SCHEDULER 4kvt2r09bz39r 2912237509 2 6,291,456 0 36,496,384 -1
1 15.01.2022 03:40 15.01.2022 12:36 MMON_SLAVE 8mdz49zkajhw3 2609763588 54 5,242,880 0 98,362,368 -1
2 13.01.2022 22:11 13.01.2022 22:12 DBMS_SCHEDULER bb9kv1sq4fq9x 1186311642 7 5,242,880 0 34,399,232 -1
2 13.01.2022 22:12 13.01.2022 22:12 DBMS_SCHEDULER 531x22ur6bxc1 3026497435 1 5,242,880 0 39,117,824 -1
2 13.01.2022 22:12 13.01.2022 22:12 DBMS_SCHEDULER 531x22ur6bxc1 0 1 5,242,880 0 34,399,232 -1
2 13.01.2022 22:03 13.01.2022 22:03 DBMS_SCHEDULER f705bwx3q0ydq 2485401174 1 5,242,880 0 28,435,456 -1
1 15.01.2022 04:02 15.01.2022 09:47 MMON_SLAVE fg4skgcja2cyj 1186311642 4 5,242,880 0 34,202,624 -1
SQL>
===
set linesize 300 pagesize 300
col inst_id for 99
col username for a20
col sql_id for a13
col text80 for a80
col contents for a20 just l
col segtype for a20
select tu.inst_id,
tu.username,
nvl(s.sql_id, s.prev_sql_id) sql_id,
tu.contents,
tu.segtype,
count(tu.segblk#) seg_count,
count(distinct tu.session_addr) proc_count,
round(sum(tu.blocks * t.block_size) / 1024 / 1024) size_mb,
substr(rpad(sa.sql_text, 80, '*'), 1, 80) as text80
from gv$tempseg_usage tu, gv$session s, dba_tablespaces t, gv$sqlarea sa
where tu.session_addr = s.saddr
and tu.inst_id = s.inst_id
and s.sql_id = sa.sql_id
and s.inst_id = sa.inst_id
and tu.tablespace = t.tablespace_name
--and s.sql_id = '&1'
group by tu.inst_id,
tu.username,
nvl(s.sql_id, s.prev_sql_id),
substr(rpad(sa.SQL_TEXT, 80, '*'), 1, 80),
tu.contents,
tu.segtype
order by sum(tu.blocks) desc
/
====
set linesize 300 pagesize 300
col sql_text for a30
select t.inst_id,to_char(t.sample_time,'DD-MON-YYYY HH24:MI:SS') sample_time, t.sql_id, t.temp_mb, t.temp_diff, substr(s.sql_text,1,30) sql_text
from (
select
inst_id,
--session_id,session_serial#,
--'alter system kill session ''' || session_id || ',' || session_serial# || ''' immediate;' kill_session_cmd,
trunc(sample_time) sample_time,sql_id, sum(temp_mb) temp_mb, sum(temp_diff) temp_diff
, row_number() over (partition by trunc(sample_time) order by sum(temp_mb) desc nulls last) as rn
from (
select inst_id,sample_time,session_id,session_serial#,sql_id,temp_space_allocated/1024/1024 temp_mb,
temp_space_allocated/1024/1024-lag(temp_space_allocated/1024/1024,1,0) over (order by sample_time) as temp_diff
--from dba_hist_active_sess_history
from gv$active_session_history
where 1 = 1
-- session_id=par1
-- and session_serial#=par2
)
group by inst_id,
--session_id,session_serial#,
trunc(sample_time),
sql_id
) t
left join gv$sqlarea s
on s.sql_id = t.sql_id and s.inst_id = t.inst_id
where 1 = 1
and rn <=5
and sample_time >= trunc(sysdate) - 1
order by temp_mb desc, sample_time desc;
set linesize 300 pagesize 300
col sql_text for a30
select t.INSTANCE_NUMBER,to_char(t.sample_time,'DD-MON-YYYY HH24:MI:SS') sample_time, t.sql_id, t.temp_mb, t.temp_diff, substr(s.sql_text,1,30) sql_text
from (
select
INSTANCE_NUMBER,
--session_id,session_serial#,
--'alter system kill session ''' || session_id || ',' || session_serial# || ''' immediate;' kill_session_cmd,
trunc(sample_time) sample_time,sql_id, sum(temp_mb) temp_mb, sum(temp_diff) temp_diff
, row_number() over (partition by trunc(sample_time) order by sum(temp_mb) desc nulls last) as rn
from (
select INSTANCE_NUMBER,sample_time,session_id,session_serial#,sql_id,temp_space_allocated/1024/1024 temp_mb,
temp_space_allocated/1024/1024-lag(temp_space_allocated/1024/1024,1,0) over (order by sample_time) as temp_diff
from dba_hist_active_sess_history
--from gv$active_session_history
where 1 = 1
-- session_id=par1
-- and session_serial#=par2
)
group by INSTANCE_NUMBER,
--session_id,session_serial#,
trunc(sample_time),
sql_id
) t
left join gv$sqlstats s
on s.sql_id = t.sql_id and s.inst_id = t.instance_number
where 1 = 1
and rn <=5
and sample_time >= trunc(sysdate) - 7
order by temp_mb desc, sample_time desc;
col kill for a15
set linesize 140 pagesize 400 head on
col iid for 990
col sid_serial for a11
column username for a10
col module for a20
col action for a15
col state for a10
column segfile# for 9,999 heading 'file|id'
col orapid for 999999 heading "orapid||oracle|pid"
col dbpid for a6 heading "spid|unix|pid"
col apppid for a10
column program for a55
column segblk# for 999,999,999 heading 'block|id'
set timi off
clear computes
break on iid
select distinct
''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''' kill,
p.pid orapid,
p.spid dbpid,
s.username,
round(sum (((u.blocks*par.value)/1024/1024)),2) size_mb,
s.status,
s.state,
u.sql_id,
segfile#,
s.module,
s.action
from gv$sort_usage u, gv$session s, gv$process p, v$parameter par
where 1=1
and s.inst_id=u.inst_id
and s.saddr(+) = u.session_addr
and s.paddr = p.addr
and par.name='db_block_size'
group by ''''||s.sid ||','|| s.serial#||',@'||s.inst_id ||'''', p.pid, p.spid, s.username, s.status, s.state, u.sql_id, segfile#, s.module, s.action
having round(sum (((u.blocks*par.value)/1024/1024)),2) >10 ---MB
order by 1,2
;
set linesize 400 pagesize 300
col CLIENT_ID for a20
col MODULE for a25
col ACTION for a20
select sql_id ,sql_exec_id, sql_plan_hash_value, module, action, CLIENT_ID,
round(max(temp_space_allocated) / 1024 / 1024 / 1024, 3) as max_temp_gb,
round(max(pga_allocated) / 1024 / 1024 / 1024, 3) as max_pga_gb,
max(px_used) as max_px_used
from (select sql_id ,sql_exec_id, sql_plan_hash_value, REGEXP_SUBSTR(client_id, '.+\#') as CLIENT_ID, module, action, sample_id,
sum(temp_space_allocated) as temp_space_allocated,
sum(pga_allocated) as pga_allocated,
count(distinct session_serial#) - 1 as px_used
from gv$active_session_history
where 1=1
-- and sql_id = '&&1'
and sql_exec_id > 0
and sql_plan_hash_value > 0
group by sql_id, sql_exec_id, sql_plan_hash_value, module, action, sample_id, REGEXP_SUBSTR(client_id, '.+\#')
having sum(temp_space_allocated) is not null)
group by sql_id ,sql_exec_id, sql_plan_hash_value, module, action, CLIENT_ID
having max(temp_space_allocated) / 1024 / 1024 / 1024 > 1
order by 2
set linesize 700 pagesize 500
VARIABLE dbid NUMBER
VARIABLE bid NUMBER
VARIABLE eid NUMBER
VARIABLE INST_NUMBER number
exec select max(snap_id) -1 into :bid from dba_hist_snapshot ;
exec select max(snap_id) into :eid from dba_hist_snapshot ;
exec select DBID into :dbid from v$database;
col SQL_EXEC_START for a20
col DURATION for a25
select * from (
select instance_number as inst_id,
sql_id,
to_char(sql_exec_start,'dd.mm.yyyy hh24:mi:ss') as SQL_EXEC_START,
max(sample_time) - SQL_EXEC_START as duration,
sql_exec_id,
sql_plan_hash_value,
module,
action,
round(max(temp_space_allocated) / 1024 / 1024 / 1024, 3) as max_temp_gb,
round(max(pga_allocated) / 1024 / 1024 / 1024, 3) as max_pga_gb,
max(px_used) as max_px_used
from (select instance_number, sql_id, sql_exec_start, sql_exec_id, sql_plan_hash_value, module, action, sample_id,
sum(temp_space_allocated) as temp_space_allocated,
sum(pga_allocated) as pga_allocated,
count(distinct session_serial#) - 1 as px_used,
sample_time
from dba_hist_active_sess_history
where snap_id between :bid and :eid
and sql_exec_id > 0
group by instance_number, sql_exec_start, sql_id, sql_exec_id, sql_plan_hash_value, module, action, sample_id, sample_time
having sum(temp_space_allocated) is not null)
group by instance_number, sql_id, SQL_EXEC_START, sql_exec_id, sql_plan_hash_value, module, action
having max(temp_space_allocated) / 1024 / 1024 / 1024 > .5 -- GB
order by 9 desc
) where rownum <= nvl('&3',10)
/
col SAMPLE_TIME for a27
select * from (
select instance_number as inst_id,
sample_id,
sample_time,
round(max(temp_space_allocated) / 1024 / 1024 / 1024, 3) as max_temp_gb,
round(max(pga_allocated) / 1024 / 1024 / 1024, 3) as max_pga_gb
from (select instance_number, sample_time, sample_id,
sum(temp_space_allocated) as temp_space_allocated,
sum(pga_allocated) as pga_allocated
from dba_hist_active_sess_history
where snap_id between :bid and :eid
group by instance_number, sample_id, sample_time
having sum(temp_space_allocated) is not null)
group by instance_number, sample_time, sample_id
having max(temp_space_allocated) / 1024 / 1024 / 1024 > .5 -- GB
order by 4 desc
) where rownum <= nvl('&3',10)
/
define seconds= 1000
define top=10
col percent head '%' for 99990.99
col star for A10 head ''
select SQL_ID,TEMP_MB,percent,rpad('*',percent*10/100,'*') star
from
(
select SQL_ID,sum(DELTA_TEMP_MB) TEMP_MB ,(ratio_to_report(sum(DELTA_TEMP_MB)) over ())*100 percent,rank() over(order by sum(DELTA_TEMP_MB) desc) rank
from
(
select SESSION_ID,SESSION_SERIAL#,sample_id,SQL_ID,SAMPLE_TIME,IS_SQLID_CURRENT,SQL_CHILD_NUMBER,temp_space_allocated,greatest(temp_space_allocated - first_value(temp_space_allocated) over (partition by SESSION_ID,SESSION_SERIAL# order by sample_time rows 1 preceding),0)/power(1024,2) "DELTA_TEMP_MB"
from gv$active_session_history
where 1=1
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '10' second
-- and SAMPLE_TIME > SYSTIMESTAMP - INTERVAL '1000' second
and sample_time>sysdate- INTERVAL '1' HOUR
and IS_SQLID_CURRENT='Y'
order by 1,2,3,4
)
group by sql_id
-- having sum(DELTA_TEMP_MB) > 0
)
where rank < (&top+1)
order by rank
/
SQL_ID TEMP_MB %
------------- ---------- --------- ----------
0t3pbwd42tt9t 3438 42.93 ****
===
- -tempfile info
set linesize 300
col TABLESPACE_NAME for a25
col FILE_NAME for a70
select tb.tablespace_name, bytes/1024/1024/1024 Gb,MAXBYTES/1024/1024/1024 Max_Gb,tf.file_name,AUTOEXTENSIBLE from dba_tablespaces tb left join dba_temp_files tf on tf.tablespace_name = tb.tablespace_name where tb.contents = 'TEMPORARY';
set linesize 300 numf 9999.99
col CREATION_TIME for a27
col NAME for a70
col tb_name for a15
select b.name tb_name, a.name, a.bytes/1024/1024/1024 Gb ,CREATE_BYTES/1024/1024/1024 CREATED_SIZE,to_char(CREATION_TIME,'dd-mm-yyyy hh24:mi') CREATION_TIME ,STATUS,ENABLED
from v$tempfile a, v$tablespace b
where a.ts#=b.ts#
set pagesize 50 linesize 200
col file_name for a40
select t.inst_id,substr(t.name,1,50) as file_name,f.phyblkrd as blocks_read,f.phyblkwrt as blocks_written,f.phyblkrd + f.phyblkwrt as total_io
from gv$tempstat f,gv$tempfile t
where t.file# = f.file#
and t.inst_id=f.inst_id
order by f.phyblkrd + f.phyblkwrt desc;
INST_ID FILE_NAME BLOCKS_READ BLOCKS_WRITTEN TOTAL_IO
---------- ---------------------------------------- ----------- -------------- ----------
1 +DATA/rac/temp01.dbf 525400 573493 1098893
2 +DATA/rac/temp01.dbf 77424 70562 147986
add temp file ..
alter database tempfile '+DATA/prd/tempfile/temp_02.dbf' resize 10G ;
alter database tempfile '+DATA/prd/tempfile/temp_02.dbf' autoextend on maxsize 10G;
To shrink ..
alter tablespace TEMP shrink tempfile '+DATA/prd/tempfile/temp_02.dbf' KEEP 2G;
set linesize 120 pagesize 0
select 'ALTER TABLESPACE ' || tablespace_name || ' SHRINK TEMPFILE ''' || file_name || ''' KEEP 10M;' stmt from dba_temp_files
-- where tablespace_name = 'TEMP'
;
----
select * from V$TEMPORARY_LOBS
col PROGRAM for a50
col kill for a16
SELECT
''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill, a.sql_id,b.tablespace,ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,a.inst_id as Instance,NVL(a.username, '(oracle)') AS username,a.program, a.status
FROM gv$session a, gv$sort_usage b, gv$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
AND a.inst_id=b.inst_id
AND a.inst_id=p.inst_id
and b.contents = 'TEMPORARY'
--and b.segtype='LOB_DATA'
ORDER BY temp_size desc;
col PROGRAM for a50
col kill for a16
SELECT
''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill, a.sql_id,b.tablespace,ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,a.inst_id as Instance,NVL(a.username, '(oracle)') AS username,a.program, a.status
FROM gv$session a, gv$sort_usage b, gv$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
AND a.inst_id=b.inst_id
AND a.inst_id=p.inst_id
and b.contents = 'TEMPORARY'
and b.segtype='LOB_DATA'
ORDER BY temp_size desc;
col PROGRAM for a50
col kill for a16
SELECT
''''||a.sid ||','|| a.serial#||',@'||a.inst_id ||'''' kill,
a.sql_id,
b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' AS temp_size,
a.inst_id as Instance,
-- a.sid||','||a.serial# AS sid_serial,
NVL(a.username, '(oracle)') AS username,
a.program,
a.status
FROM gv$session a,
gv$sort_usage b,
gv$parameter p
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr
AND a.inst_id=b.inst_id
AND a.inst_id=p.inst_id
ORDER BY b.tablespace, b.blocks
select * from v$temporary_lobs
order by
cache_lobs desc
SELECT A.inst_id,
A.tablespace_name TABLESPACE,
D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM gv$sort_segment A,
(SELECT B.INST_ID,
B.name,
C.block_size,
SUM (C.bytes) / 1024 / 1024 mb_total
FROM gv$tablespace B,
gv$tempfile C
WHERE B.ts# = C.ts#
AND c.inst_id=b.inst_id
GROUP BY B.INST_ID,
B.name,
C.block_size
) D
WHERE A.tablespace_name = D.name
--and a.tablespace_name='TEMP'
AND A.inst_id =D.inst_id
GROUP BY a.inst_id,
A.tablespace_name,
D.mb_total
ORDER BY 1,2;
set pagesize 300
select /*+ parallel(8) */ sql_id,trunc(max(TEMP_SPACE_ALLOCATED)/(1024*1024*1024),2) GB from DBA_HIST_ACTIVE_SESS_HISTORY
where 1=1
and sample_time > sysdate-2
and TEMP_SPACE_ALLOCATED > (1*1024*1024*1024)
group by sql_id
order by 2 desc;
---with con_id
set pagesize 300
select /*+ parallel(8) */ CON_ID,sql_id,trunc(max(TEMP_SPACE_ALLOCATED)/(1024*1024*1024),2) GB from DBA_HIST_ACTIVE_SESS_HISTORY
where 1=1
and sample_time > sysdate-2
and TEMP_SPACE_ALLOCATED > (1*1024*1024*1024)
group by CON_ID,sql_id
order by 2 desc;
====
from History
define when="19/10/2022 10:00"
define div=1/1024/1024/1024
col "size (Gb)" format 999,999,999.99
col "used size (Gb)" format 999,999,999.99
col "max size (Gb)" format 999,999,999.99
col "free size (Gb)" format 999,999,999.99
set pagesize 300
set linesize 300
with A as ( select name tablespace_name,
ts# tablespace_id
from v$tablespace ),
B as ( select tablespace_name,
block_size*&div factor
from dba_tablespaces ),
C as ( select snap_id,
to_char(end_interval_time, 'dd/mm/yyyy hh24:mi:ss') end_interval_time
from dba_hist_snapshot
where 1=1
--and to_char(end_interval_time, 'dd/mm/yyyy hh24:mi:ss') like '&when%'
and end_interval_time > sysdate -1
)
select C.end_interval_time "end interval time",
B.tablespace_name,
D.tablespace_size *factor "size (Gb)",
D.tablespace_usedsize *factor "used size (Gb)",
(D.tablespace_size-D.tablespace_usedsize)*factor "free size (Gb)",
D.tablespace_maxsize *factor "max size (Gb)"
from
A, B, C, dba_hist_tbspc_space_usage D
where
D.snap_id = C.snap_id
and D.tablespace_id = A.tablespace_id
and A.tablespace_name = B.tablespace_name
and A.tablespace_name='TEMP'
and CON_ID=5
order by 1;
====
set linesize 300 pagesize 300
col CLIENT_ID for a15
col MODULE for a30
col ACTION for a15
select con_id,sql_id, sql_exec_id, sql_plan_hash_value, module, action, CLIENT_ID,
round(max(temp_space_allocated) / 1024 / 1024 / 1024, 3) as max_temp_gb,
round(max(pga_allocated) / 1024 / 1024 / 1024, 3) as max_pga_gb,
max(px_used) as max_px_used
from (select con_id,sql_id,sql_exec_id, sql_plan_hash_value, REGEXP_SUBSTR(client_id, '.+\#') as CLIENT_ID, module, action, sample_id,
sum(temp_space_allocated) as temp_space_allocated,
sum(pga_allocated) as pga_allocated,
count(distinct session_serial#) - 1 as px_used
-- from gv$active_session_history
from dba_hist_active_sess_history
where 1=1
--and sql_id = '&&1'
-- and sql_exec_id > 0
and sql_plan_hash_value > 0
and SAMPLE_TIME >sysdate -7
group by con_id,sql_id, sql_exec_id, sql_plan_hash_value, module, action, sample_id, REGEXP_SUBSTR(client_id, '.+\#')
having sum(temp_space_allocated) is not null
)
group by con_id,sql_id,sql_exec_id, sql_plan_hash_value, module, action, CLIENT_ID
having max(temp_space_allocated) / 1024 / 1024 / 1024 > 6 -- GB
order by 2
/
alter session set nls_date_format='dd-mm-YYYY hh24:Mi';
set linesize 300 pagesize 300
col PROGRAM for a50
col MODULE for a20
col ACTION for a25
col MACHINE for a15
col USERNAME for a15
select con_id,d.sql_id,u.USER_ID,u.USERNAME,trunc(max(TEMP_SPACE_ALLOCATED)/(1024*1024*1024),2) GB ,d.PROGRAM,d.MODULE,d.ACTION,MACHINE,max(d.SQL_EXEC_START) SQL_START_time
from DBA_HIST_ACTIVE_SESS_HISTORY d,dba_users u
where 1=1
and d.sample_time > sysdate-5
and TEMP_SPACE_ALLOCATED > (1*1024*1024*1024)
-- and sql_id='&Sql_id'
and u.USER_ID=d.USER_ID
group by con_id,d.sql_id,u.USER_ID,u.USERNAME ,d.PROGRAM,d.MODULE,d.ACTION,d.MACHINE ;
