Undo management + Statement restart / Oracle / Sql.ru

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;

Re: Undo management + Statement restart [new]
Владимир Бегун Member Откуда: Redwood Shores, CA USA Сообщений: 1707	Давно-давно была вот эта дискуссия. Успехов на поприще! :-)
26 мар 11, 01:28 [10426502] Ответить \| Цитировать Сообщить модератору

Добро пожаловать в форум, Guest >> Войти \| Регистрация \| Поиск \| Правила \|			В избранное \| Подписаться
Все форумы / Oracle