Undo management + Statement restart

_Nikotin
Дата: 05.03.2011 18:08:56
По мотивам недавнего Quiz for 25 February 2011 набросал небольшой тест демонстрирующий суть происходящего.

Пакет для хранения снимков системных представлений:
+ pkg_undo_snap
create or replace package sys.pkg_undo_snap is

  c_last_snap   constant number := 999;
  g_cur_undotbs varchar(30);

  type snap_r is record
  (
    snap_no   number, 
    user_no   number(3),
    used_undo number(8,2),
    trgcnt    number,
    usn       number,
    slot      number,
    sqn       number
  );
  type snap_t is table of snap_r;

  cursor cur_mystat(snap_no number) is
    select snap_no, m.value undo_cvs
    from v$mystat m,
         v$statname s 
    where s.statistic# = m.statistic#
      and s.name = 'undo change vector size';
  type mystat_snaps_t is table of cur_mystat%rowtype;
      
  cursor cur_rollstat(snap_no number) is 
    select snap_no, s.*
    from v$rollstat s
    where s.usn <> 0;
  type rollstat_snaps_t is table of cur_rollstat%rowtype;
  
  cursor cur_undo_extents(snap_no number) is
    select snap_no, u.us# usn, e.*
    from dba_undo_extents e,
         undo$ u
    where e.tablespace_name = g_cur_undotbs
      and e.segment_name = u.name;
  type undo_extents_snaps_t is table of cur_undo_extents%rowtype;
  
  function show_snaps return snap_t pipelined;
  function show_mystat_snaps return mystat_snaps_t pipelined;
  function show_rollstat_snaps return rollstat_snaps_t pipelined;
  function show_undo_extents_snaps return undo_extents_snaps_t pipelined;
  
  procedure init;
  procedure trgfire;
  procedure snap(i_user_no number default null);

end pkg_undo_snap;
/

create or replace package body sys.pkg_undo_snap is

  c_max_noname_cnt constant number := 10;

  type rollstat_snaps_tt is table of rollstat_snaps_t;
  type undo_extents_snaps_tt is table of undo_extents_snaps_t;

  g_snap_no pls_integer;
  g_last_flag boolean;
  g_cur_noname_cnt pls_integer;
  g_trgcnt pls_integer;
  
  g_snaps snap_t;
  g_mystat_snaps mystat_snaps_t;
  g_rollstat_snaps rollstat_snaps_tt;
  g_undo_extents_snaps undo_extents_snaps_tt;
  
  function show_snaps return snap_t pipelined is
  begin
    for i in 1 .. g_snaps.count loop
      pipe row (g_snaps(i));
    end loop;
    return;
  end;
  
  function show_mystat_snaps return mystat_snaps_t pipelined is
  begin
    for i in 1 .. g_mystat_snaps.count loop
      pipe row (g_mystat_snaps(i));
    end loop;
    return;
  end;
  
  function show_rollstat_snaps return rollstat_snaps_t pipelined is
  begin
    for i in 1 .. g_rollstat_snaps.count loop
      for j in 1 .. g_rollstat_snaps(i).count loop
        pipe row (g_rollstat_snaps(i)(j));
      end loop;
    end loop;
    return;
  end;
  
  function show_undo_extents_snaps return undo_extents_snaps_t pipelined is
  begin
    for i in 1 .. g_undo_extents_snaps.count loop
      for j in 1 .. g_undo_extents_snaps(i).count loop
        pipe row (g_undo_extents_snaps(i)(j));
      end loop;
    end loop;
    return;
  end;
    
  procedure init is
  begin
    g_snap_no := 0;
    g_last_flag := false;
    g_cur_noname_cnt := 0;
    g_trgcnt := 0;
    g_snaps := snap_t();
    g_mystat_snaps := mystat_snaps_t();
    g_rollstat_snaps := rollstat_snaps_tt();
    g_undo_extents_snaps := undo_extents_snaps_tt();
    select value into g_cur_undotbs from v$parameter where upper(name) = 'UNDO_TABLESPACE';
  end; 
  
  procedure trgfire is
  begin
    g_trgcnt := g_trgcnt + 1;
  end;
  
  procedure snap(i_user_no number default null) is
    l_tran_id varchar2(30);
    l_snap snap_r;
    l_mystat_snap cur_mystat%rowtype;
    l_rollstat_snap rollstat_snaps_t;
    l_undo_extents_snap undo_extents_snaps_t;
  begin
    if g_last_flag then
      return;
    end if;
    
    g_last_flag := nvl(i_user_no = c_last_snap, false);
    
    if i_user_no is null and g_cur_noname_cnt > c_max_noname_cnt then
      return;
    elsif i_user_no is null then
      g_cur_noname_cnt := g_cur_noname_cnt + 1;
    else 
      g_cur_noname_cnt := 0;
    end if;
  
    g_snap_no := g_snap_no + 1;    
    
    l_tran_id := dbms_transaction.local_transaction_id;
    l_snap.snap_no := g_snap_no;
    l_snap.user_no := i_user_no;
    l_snap.trgcnt := g_trgcnt;
    l_snap.usn := regexp_substr(l_tran_id, '\d+', 1, 1);
    l_snap.slot := regexp_substr(l_tran_id, '\d+', 1, 2);
    l_snap.sqn := regexp_substr(l_tran_id, '\d+', 1, 3);
    
    open cur_mystat(g_snap_no);
    fetch cur_mystat into l_mystat_snap;
    close cur_mystat;
    
    open cur_rollstat(g_snap_no);
    fetch cur_rollstat bulk collect into l_rollstat_snap;
    close cur_rollstat;
    
    open cur_undo_extents(g_snap_no);
    fetch cur_undo_extents bulk collect into l_undo_extents_snap;
    close cur_undo_extents;

    select (d.bytes - nvl((select sum(f.bytes) 
                           from sys.dba_free_space f 
                           where f.tablespace_name = g_cur_undotbs), 0)) / 1024 / 1024
    into l_snap.used_undo
    from sys.dba_data_files d
    where d.tablespace_name = g_cur_undotbs;
    
    g_snaps.extend;
    g_snaps(g_snap_no) := l_snap;

    g_mystat_snaps.extend;
    g_mystat_snaps(g_snap_no) := l_mystat_snap;
    
    g_rollstat_snaps.extend;
    g_rollstat_snaps(g_snap_no) := l_rollstat_snap;
    
    g_undo_extents_snaps.extend;
    g_undo_extents_snaps(g_snap_no) := l_undo_extents_snap;

  end;
  
end pkg_undo_snap;
/

grant execute on sys.pkg_undo_snap to public;
create public synonym pkg_undo_snap for sys.pkg_undo_snap;

На всякий случай stragg Тома Кайта:
+ stragg
create or replace type sys.stragg_type as object
(
  string varchar2(4000),

  static function ODCIAggregateInitialize
    ( sctx in out stragg_type )
    return number ,

  member function ODCIAggregateIterate
    ( self  in out stragg_type ,
      value in     varchar2
    ) return number ,

  member function ODCIAggregateTerminate
    ( self        in  stragg_type,
      returnvalue out varchar2,
      flags in number
    ) return number ,

  member function ODCIAggregateMerge
    ( self in out stragg_type,
      ctx2 in     stragg_type
    ) return number
);
/

create or replace type body sys.stragg_type
is

  static function ODCIAggregateInitialize
  ( sctx in out stragg_type )
  return number
  is
  begin

    sctx := stragg_type( null ) ;

    return ODCIConst.Success ;

  end;

  member function ODCIAggregateIterate
  ( self  in out stragg_type ,
    value in     varchar2
  ) return number
  is
  begin

    self.string := self.string || ',' || value ;

    return ODCIConst.Success;

  end;

  member function ODCIAggregateTerminate
  ( self        in  stragg_type ,
    returnvalue out varchar2 ,
    flags       in  number
  ) return number
  is
  begin

    returnValue := ltrim( self.string, ',' );

    return ODCIConst.Success;

  end;

  member function ODCIAggregateMerge
  ( self in out stragg_type ,
    ctx2 in     stragg_type
  ) return number
  is
  begin

    self.string := self.string || ctx2.string;

    return ODCIConst.Success;

  end;

end;
/

create or replace function sys.stragg
  ( input varchar2 )
  return varchar2
  deterministic
  parallel_enable
  aggregate using stragg_type
;
/

grant execute on sys.stragg to public;
create public synonym stragg for sys.stragg;

Урезанная вьюха для анализа результатов
+ v_undo_snap_short
create or replace view v_undo_snap_short as
with s  as (select * from table(pkg_undo_snap.show_snaps)),
     m  as (select * from table(pkg_undo_snap.show_mystat_snaps)),
     r  as (select * from table(pkg_undo_snap.show_rollstat_snaps)),
     e  as (select * from table(pkg_undo_snap.show_undo_extents_snaps)),
     es as
     (
       select snap_no, 
              usn, 
              sum(decode(status,'ACTIVE',1)) A,
              sum(decode(status,'UNEXPIRED',1)) U,
              sum(decode(status,'EXPIRED',1)) E
       from e
       group by snap_no, usn
     ),
     ec as 
     (
       select snap_no, stragg(extschg) extschg
       from
       (
         select nvl(e.snap_no, ep.snap_no + 1) snap_no,               
                '['||nvl(e.block_id, ep.block_id)||': '||
                ep.usn || '(' || nvl(substr(ep.status,1,1), 'F') || ') -> ' || 
                e.usn  || '(' || nvl(substr(e.status ,1,1), 'F') || ')]' extschg
         from e full join e ep on ep.snap_no = e.snap_no - 1 and ep.block_id = e.block_id
         where (decode(ep.usn, e.usn, 1) is null or decode(ep.status, e.status, 1) is null)
           and nvl(ep.snap_no, 0) < (select max(s.snap_no) from s)
           and nvl(e.snap_no, 2) > 1
         order by nvl(e.snap_no, ep.snap_no + 1), nvl(e.block_id, ep.block_id)
       )
       group by snap_no     
     )
select decode(s.trgcnt-lag(s.trgcnt)over(order by s.snap_no),null,'',0,'',1,'.',2,'*','**') f,       
       s.snap_no sn,
       s.user_no un,
       s.usn,
       m.undo_cvs-lag(m.undo_cvs)over(order by s.snap_no) undo,
       s.trgcnt-lag(s.trgcnt)over(order by s.snap_no) t,
       r.extents ec,
       r.curext ce,
       r.curblk cb,       
       nullif(r.extents-rp.extents, 0) d,
       nullif(r.extends-rp.extends, 0) x,
       nullif(r.shrinks-rp.shrinks, 0) s,
       es.A,
       es.U,
       es.E,       
       s.used_undo uu,
       e.block_id bid,
       ec.extschg
from s 
join m on m.snap_no = s.snap_no
left join r on r.snap_no = s.snap_no and r.usn = s.usn
left join r rp on rp.snap_no = s.snap_no - 1 and rp.usn  = s.usn
left join e on e.snap_no = s.snap_no and e.usn = s.usn and e.extent_id = r.curext
left join es on es.snap_no = s.snap_no and es.usn = s.usn
left join ec on ec.snap_no = s.snap_no;

Описание столбцов v_undo_snap_short
+ desc v_undo_snap_short
f    - null - no restart, "." 1 restart, "*" - 2 restarts, "**" > 2 restarts
sn   - snapshot number
un   - number from snap call
usn  - undo segment number for current transaction
undo - change of undo change vector size from previous snapshot
t    - change of trigger count
ec   - number of extents in the undo segment
ce   - current extent
cb   - current block
d    - change of number of extents
x    - change of number of times undo segment size is extended
s    - change of number of times undo segment size is decreases
A    - number of active extents
U    - number of unexpired extents
E    - number of expired extents
uu   - used undo
bid  - start block number of the extent (unique for extent within undo tablespace)
extschg - extent changes between snapshot [bid: usn_prev(status) -> usn(status)]

Код для тестирования:
+ test case
/*
CREATE SMALLFILE UNDO TABLESPACE UNDO_TEST DATAFILE '/oracle/db/orcl/UNDO_TEST.ora' SIZE 5M;
ALTER SYSTEM SET UNDO_TABLESPACE = UNDO_TEST;
*/

drop table t1;
create table t1 as select lpad('x',4000,'x') x from dual;

create or replace trigger trg1
before update on t1
for each row
begin
  pkg_undo_snap.trgfire;
end;
/

declare
  tran_id varchar2(30);
begin
  pkg_undo_snap.init;
  pkg_undo_snap.snap(-1);
  tran_id := dbms_transaction.local_transaction_id(true);
  pkg_undo_snap.snap(0);
  for i in 1 .. 64 loop
    update t1 set x=x;
    pkg_undo_snap.snap(i);
  end loop;
  commit;
end;
/

select * from v_undo_snap_short order by sn;

Результат теста на 10.2.0.4
+ result
F    SN   UN  USN  UNDO  T  EC  CE CB  D  X  S   A   U   E   UU  BID EXTSCHG
-- ---- ---- ---- ----- -- --- --- -- -- -- -- --- --- --- ---- ---- --------------------------------------------
      1   -1                                                4.8
      2    0   20   152  0  25  24  3            1  24      4.8  217 [217: 20(U) -> 20(A)],[233: (F) -> 12(E)]
.     3    1   20  4196  1  25  24  4            1  24      4.8  217
*     4    2   20  8216  2  26  24  5  1  1      1  24   1  4.9  217 [241: (F) -> 20(E)]
.     5    3   20  4108  1  26  24  6            1  24   1  4.9  217
.     6    4   20  4108  1  26  24  7            1  24   1  4.9  217
.     7    5   20  4108  1  26  25  0            2  24      4.9  241 [241: 20(E) -> 20(A)]
.     8    6   20  4108  1  26  25  1            2  24      4.9  241
.     9    7   20  4108  1  26  25  2            2  24      4.9  241
.    10    8   20  4108  1  26  25  3            2  24      4.9  241
.    11    9   20  4108  1  26  25  4            2  24      4.9  241
*    12   10   20  8216  2  27  25  5  1  1      2  24   1  4.9  241 [249: (F) -> 20(E)]
.    13   11   20  4108  1  27  25  6            2  24   1  4.9  241
.    14   12   20  4108  1  27  25  7            2  24   1  4.9  241
.    15   13   20  4108  1  27  26  0            3  24      4.9  249 [249: 20(E) -> 20(A)]
.    16   14   20  4108  1  27  26  1            3  24      4.9  249
.    17   15   20  4108  1  27  26  2            3  24      4.9  249
.    18   16   20  4108  1  27  26  3            3  24      4.9  249
.    19   17   20  4108  1  27  26  4            3  24      4.9  249
*    20   18   20  8216  2  28  26  5  1  1      3  24   1  5.0  249 [257: (F) -> 20(E)]
.    21   19   20  4108  1  28  26  6            3  24   1  5.0  249
.    22   20   20  4108  1  28  26  7            3  24   1  5.0  249
.    23   21   20  4108  1  28  27  0            4  24      5.0  257 [257: 20(E) -> 20(A)]
.    24   22   20  4108  1  28  27  1            4  24      5.0  257
.    25   23   20  4108  1  28  27  2            4  24      5.0  257
.    26   24   20  4108  1  28  27  3            4  24      5.0  257
.    27   25   20  4108  1  28  27  4            4  24      5.0  257
*    28   26   20  8292  2  29  27  5  1  1      4  24   1  5.0  257 [233: 12(E) -> 20(E)]
.    29   27   20  4108  1  29  27  6            4  24   1  5.0  257
.    30   28   20  4108  1  29  27  7            4  24   1  5.0  257
.    31   29   20  4108  1  29  28  0            5  24      5.0  233 [233: 20(E) -> 20(A)]
.    32   30   20  4108  1  29  28  1            5  24      5.0  233
.    33   31   20  4108  1  29  28  2            5  24      5.0  233
.    34   32   20  4108  1  29  28  3            5  24      5.0  233
.    35   33   20  4108  1  29  28  4            5  24      5.0  233
*    36   34   20  8292  2  30  28  5  1  1      5  24   1  5.0  233 [225: 19(E) -> 20(E)]
.    37   35   20  4108  1  30  28  6            5  24   1  5.0  233
.    38   36   20  4108  1  30  28  7            5  24   1  5.0  233
.    39   37   20  4108  1  30  29  0            6  24      5.0  225 [225: 20(E) -> 20(A)]
.    40   38   20  4108  1  30  29  1            6  24      5.0  225
.    41   39   20  4108  1  30  29  2            6  24      5.0  225
.    42   40   20  4108  1  30  29  3            6  24      5.0  225
.    43   41   20  4108  1  30  29  4            6  24      5.0  225
*    44   42   20  8216  2  30  29  5            6  24      5.0  225
*    45   43   20  8216  2  30  29  6            6  24      5.0  225
*    46   44   20  8216  2  30  29  7            6  24      5.0  225
*    47   45   20  8216  2  30   0  0            7  23      5.0  153 [153: 20(U) -> 20(A)]
.    48   46   20  4108  1  30   0  1            7  23      5.0  153
.    49   47   20  4108  1  30   0  2            7  23      5.0  153
.    50   48   20  4108  1  30   0  3            7  23      5.0  153
*    51   49   20  8216  2  30   0  4            7  23      5.0  153
*    52   50   20  8216  2  30   0  5            7  23      5.0  153
*    53   51   20  8216  2  30   0  6            7  23      5.0  153
*    54   52   20  8216  2  30   1  0            8  22      5.0  161 [161: 20(U) -> 20(A)]
.    55   53   20  4108  1  30   1  1            8  22      5.0  161
.    56   54   20  4108  1  30   1  2            8  22      5.0  161
.    57   55   20  4108  1  30   1  3            8  22      5.0  161
.    58   56   20  4108  1  30   1  4            8  22      5.0  161
*    59   57   20  8216  2  30   1  5            8  22      5.0  161
*    60   58   20  8216  2  30   1  6            8  22      5.0  161
*    61   59   20  8216  2  30   1  7            8  22      5.0  161
*    62   60   20  8216  2  30   2  0            9  21      5.0  193 [193: 20(U) -> 20(A)]
.    63   61   20  4108  1  30   2  1            9  21      5.0  193
.    64   62   20  4108  1  30   2  2            9  21      5.0  193
.    65   63   20  4108  1  30   2  3            9  21      5.0  193
.    66   64   20  4108  1  30   2  4            9  21      5.0  193

Позднее могу прокомментировать и показать пример, как restart может происходить сотни тысяч раз.
Владимир Бегун
Дата: 26.03.2011 01:28:07
Давно-давно была вот эта дискуссия. Успехов на поприще! :-)
_Nikotin
Дата: 26.03.2011 01:37:41
Владимир Бегун,

Читал, спасибо, вот комментарий
ИМХО, Rollback segment увеличивался -- временный затык, не было куда
сложить данные, триггер отрабатывал 2-й раз после появления места в
rollback segmente.

Это не так, segment начинает пытаться увеличиться за 3 блока до конца последнего экстента. То есть место куда сложить undo - есть.
Владимир Бегун
Дата: 26.03.2011 04:47:48
_Nikotin
Владимир Бегун,

Читал, спасибо, вот комментарий
ИМХО, Rollback segment увеличивался -- временный затык, не было куда
сложить данные, триггер отрабатывал 2-й раз после появления места в
rollback segmente.

Это не так, segment начинает пытаться увеличиться за 3 блока до конца последнего экстента. То есть место куда сложить undo - есть.
Сегменту требуется дополнительное место, сколько там ещё свободных блоков логике, которая выполняет SQL, PL/SQL неведомо, но ей сообщили что произошел "сбой", пробуй мол ещё. Твои "ведические" знания о трёх свободных блоках "до конца" экстента ей (логике) также неизвестны, её задача другая. Вот ещё пример, тоже старый. "Не было куда сложить данные" зависит от. Повторяю, сколько там ещё места под undo есть -- сие есть епархия других уровней выполения. Суть дискуссии и примеров по ссылке выше проиллюстрировать почему рестарт, а не почему нет место а undo -- там много всего может быть.

P.S.: старые грабли -- новые лбы :) (с)