set lines 200 pages 200 col "Session Value" format a40 col "Session Value" format a40 col "Instance Value" format a40 SELECT a.ksppinm "Parameter", b.ksppstvl "Session Value", c.ksppstvl "Instance Value" FROM sys.x$ksppi a, sys.x$ksppcv b, sys.x$ksppsv c WHERE a.indx = b.indx AND a.indx = c.indx AND a.ksppinm like '%undo%'; select segment_name, tablespace_name, initial_extent,status from dba_rollback_segs; show parameter %undo% select inst_id, max(maxquerylen) from gv$undostat group by inst_id; select sum(bytes/1024/1024) from dba_free_space where tablespace_name='UNDOTBS'; select sum(bytes/1024/1024) from dba_data_files where tablespace_name='UNDOTBS'; SELECT DISTINCT STATUS, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS; SELECT DISTINCT STATUS, file_id, SUM(BYTES), COUNT(*) FROM DBA_UNDO_EXTENTS GROUP BY STATUS,file_id; select autoextensible from dba_data_files where tablespace_name='UNDOTBS'; SELECT tablespace_name, retention FROM dba_tablespaces; column UNXPSTEALCNT heading "# Unexpired|Stolen" column EXPSTEALCNT heading "# Expired|Reused" column SSOLDERRCNT heading "ORA-1555|Error" column NOSPACEERRCNT heading "Out-Of-space|Error" column MAXQUERYLEN heading "Max Query|Length" select inst_id, to_char(begin_time,'MM/DD/YYYY HH24:MI') begin_time,TO_CHAR(END_TIME, 'MM/DD/YYYY HH24:MI:SS') END_TIME,undoblks, UNXPSTEALCNT, EXPSTEALCNT , SSOLDERRCNT, NOSPACEERRCNT, TUNED_UNDORETENTION , MAXQUERYLEN from gv$undostat ; alter session set nls_date_format='mm/dd/yy hh24:mi:ss'; select sysdate from dual; select (nvl(sum(bytes),0)) from dba_free_space where tablespace_name='UNDO_TBS'; select sum(bytes) from dba_data_files where tablespace_name='UNDOTBS'; select segment_name,nvl(sum(act),0) "ACT BYTES",nvl(sum(unexp),0) "UNEXP BYTES",nvl(sum(exp),0) "EXP BYTES" from ( select segment_name,nvl(sum(bytes),0) act,00 unexp, 00 exp from DBA_UNDO_EXTENTS where status='ACTIVE' group by segment_name union select segment_name,00 act, nvl(sum(bytes),0) unexp, 00 exp from DBA_UNDO_EXTENTS where status='UNEXPIRED' group by segment_name union select segment_name,00 act, 00 unexp, nvl(sum(bytes),0) exp from DBA_UNDO_EXTENTS where status='EXPIRED' group by segment_name) group by segment_name; select xidusn,xidslot,xidsqn,used_ublk from v$transaction; select SEGMENT_NAME,STATUS,TABLESPACE_NAME from dba_rollback_segs; Select count(*) from dba_outstanding_alerts; select object_name, reason from dba_outstanding_alerts;
SELECT distinct s.sid, s.serial#, s.status, s.username,s.sql_id, u.segment_name, t.used_ublk, t.used_urec, s.program FROM v$session s, v$transaction t, dba_undo_extents u WHERE s.taddr = t.addr and u.segment_name = '_SYSSMU'||t.xidusn||'$' and u.status = 'ACTIVE' ORDER BY t.used_ublk desc, t.used_urec desc, s.sid, s.serial#, s.username, s.program;
SELECT distinct s.sid, s.serial#, s.status, s.username,s.sql_id, u.segment_name, t.used_ublk, t.used_urec, s.program FROM v$session s, v$transaction t, dba_undo_extents u WHERE s.taddr = t.addr and u.segment_name = '_SYSSMU'||t.xidusn||'$' and u.status = 'ACTIVE' ORDER BY t.used_ublk desc, t.used_urec desc, s.sid, s.serial#, s.username, s.program;
SID SERIAL# STATUS USERNAME SQL_ID SEGMENT_NAME USED_UBLK USED_UREC PROGRAM
-------------------------------------- -------------------------------------- -------- ------------------------------ ------------- ------------------------------ -------------------------------------- -------------------------------------- ----------------------------------------------------------------
194 185 INACTIVE АНТОН _SYSSMU9$ 16 583
198 944 INACTIVE ИСМАГИЛОВА ckxsjbxf14gvf _SYSSMU10$ 2 5 Три запроса для слежения за транзакциями, undo, сессиями.
Находим сессии с активными транзакциями
SELECT A.SID, A.USERNAME, B.XIDUSN, B.USED_UREC, B.USED_UBLK
FROM V$SESSION A, V$TRANSACTION B WHERE A.SADDR=B.SES_ADDR;
Какие sql-запросы выполняются в данный момент
select * from v$sqltext where sql_id in
(SELECT sql_id FROM V$SESSION A, V$TRANSACTION B WHERE A.SADDR=B.SES_ADDR) order by sql_id,piece;
Какие sql-запросы используют undo-пространство в заданное время:
Select * from v$sqltext where sql_id in
(select maxqueryid from
(select maxqueryid from v$undostat where BEGIN_TIME >=to_date('08.09.2009','dd.mm.yyyy') and END_TIME <=to_date('09.09.2009','dd.mm.yyyy'))
) order by sql_id,piece;
В этом запросе можно менять условия в зависимости от решаемой задачи.
Какой undo - сегмент затронут транзакцией:
select t.status, start_time, segment_name, tablespace_name
from v$transaction t, dba_rollback_segs
where XIDUSN = segment_id
какие пользователи блокируют сегменты отката
select S.Username,S.Sid,S.Serial#,T.Start_time,T.Xidusn
from V$Session S, V$TRANSACTION T, V$ROLLSTAT R
where S.Saddr = T.Ses_addr
and T.Xidusn = R.Usn
and ((R.Curext = T.Start_uext-1)
or
((R.Curext = R.Extents - 1) and (T.Start_uext = 0))
);
или
SELECT
a.usn, a.name, b.status, c.tablespace_name,
d.addr, e.sid, e.serial#, e.username, e.program,
e.machine, e.osuser
FROM v$rollname a, v$rollstat b, dba_rollback_segs c, v$transaction d,
v$session e WHERE a.usn=b.usn AND
a.name=c.segment_name AND
a.usn=d.xidusn AND
d.addr=e.taddr AND
b.status='PENDING OFFLINE';
На что уходят драгоценные МБ пространства отмены?
SELECT STATUS,TABLESPACE_NAME, SUM(BYTES)/1024/1024, COUNT(*)
FROM DBA_UNDO_EXTENTS
GROUP BY STATUS, TABLESPACE_NAME
ACTIVE – мегабайты занятые активными сессиями
EXPIRED – мегабайты, были использованы уже завершенными сессиями, и время хранения, установленное UNDO_RETENTION, уже истекло.
UNEXPIRED - мегабайты, были использованы уже завершенными сессиями, но время хранения, установленное UNDO_RETENTION, еще не истекло. Эти мегабайты будут использоваться, если не стоит RETENTION GUARANTEE, или не возможно расширить undo, не нельзя использовать offline undo-сегментs.
Замечание не по делу. Параметр undo_retention обычно устанавливают равным или больше , чем время выполнения самого длинного запроса