UNDO usage
This shows you the amount of undo size used by each session that is currently active.
select s.sid, s.username, sum(ss.value) / 1024 / 1024 as undo_size_mb from v$sesstat ss join v$session s on s.sid = ss.sid join v$statname stat on stat.statistic# = ss.statistic# where stat.name = 'undo change vector size' and s.type 'BACKGROUND' and s.username IS NOT NULL group by s.sid, s.username;
TOTAL TEMP USAGE
select
b.Total_MB,
      Â
b.Total_MB - round(a.used_blocks*8/1024) Current_Free_MB,
      Â
round(used_blocks*8/1024)Â Â Â Â Â Â Â Â Â Â Â Â Â Â Â Current_Used_MB,
     Â
round(max_used_blocks*8/1024)Â Â Â Â Â Â Â Â Â Â Â Â Max_used_MB
from
v$sort_segment a,
Â
(
select
round(
sum
(bytes)/1024/1024) Total_MB
from
dba_temp_files ) b;
TOTAL_MB CURRENT_FREE_MB CURRENT_USED_MB MAX_USED_MB ---------- --------------- --------------- ----------- 188416 23036 165380 165381 session temp usage
col hash_value for a40col tablespace for a10col username for a15set linesize 132 pagesize 1000SELECT s.sid, s.username, u.tablespace, s.sql_hash_value||'/'||u.sqlhash hash_value, u.segtype, u.contents, u.blocksFROM v$session s, v$tempseg_usage uWHERE s.saddr=u.session_addrorder by u.blocks; SID USERNAME TABLESPACE HASH_VALUE SEGTYPE CONTENTS BLOCKS ---------- --------------- ---------- ---------------------------------------- --------- --------- ------- 23 SYS TEMP 3732391352/3732391352 DATA TEMPORARY 128 23 SYS TEMP 3732391352/3732391352 INDEX TEMPORARY 128 :
23 SYS TEMP 3732391352/3732391352 DATA TEMPORARY 128
23 SYS TEMP 3732391352/3732391352 INDEX TEMPORARY 128
:
357 SYS TEMP 1357698168/693605571 SORT TEMPORARY 861952
357 SYS TEMP 1357698168/693605571 SORT TEMPORARY 872704
357 SYS TEMP 1357698168/693605571 SORT TEMPORARY 931456
357 SYS TEMP 1357698168/693605571 SORT TEMPORARY 945280
select
hash_value, sorts, rows_processed/executions
from
v$sql
where
hash_value
in
(
select
hash_value
from
v$open_cursor
where
sid=357)
and
sorts > 0
and
PARSING_SCHEMA_NAME=
'SYS'
order
by
rows_processed/executions;
Discussion ¬