23.07.2011 11:35
OlegON
 
Итак, в порядке разбора полетов с проблемой роста UNDO, о чем я уже писал тут, накапливаются диагностические скрипты, которые я решил собирать тут. Описывать их вывод я не буду, возможно будут попадаться дубликаты... Поехали...

Код:
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;
23.07.2011 11:36
OlegON
 
к сожалению, не имею возможности расписывать что делает каждый из них, потому, что это приведет к написанию многостраничного талмуда с изложением кучи теорий
17.08.2011 17:17
OlegON
 
Код:
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;
11.04.2012 09:18
Mr_Vito
 
а как просмотреть, запрос, который сжирает UNDO?
у меня сейчас сожрано 87 гигов чем то
а активных сессий практически нет
оптимайзер при этом постоянно добавляет ему файлики :(
запрос
Код:
  
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
11.04.2012 10:50
OlegON
 
Раз пользуешься оптимизатором, посмотри раздел Transactions, там про анду столбец есть (вроде used_ublk, не помню на память)
11.04.2012 10:50
OlegON
 
А вообще форум покопай, я уже исследовал проблемы анду, кучу скриптов оставлял.
12.04.2012 09:26
Mr_Vito
 
На форуме найти не получилось у меня, но в нете нашёл с коментариями :

Код:
Три запроса для слежения за транзакциями, 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 обычно устанавливают равным или больше , чем время выполнения самого длинного запроса
Часовой пояс GMT +3, время: 12:53.

Форум на базе vBulletin®
Copyright © Jelsoft Enterprises Ltd.
В случае заимствования информации гипертекстовая индексируемая ссылка на Форум обязательна.