[8i] 롤백 세그먼트 관리(OLN)

2011. 8. 16. 15:11

/********************************************************************************************
-- Title : [8i] 롤백 세그먼트 관리(OLN)
-- Reference : OLN
-- Key word : rollback segment
********************************************************************************************/

/****************************************************************************************
-- 롤백 세그먼트 확인
****************************************************************************************/
SQL> SELECT tablespace_name, segment_name, bytes, blocks
2 FROM dba_segments
3 WHERE tablespace_name = 'RBS';

TABLESPACE_NAME    SEGMENT_NAME BYTES      BLOCKS
------------------ ------------- ---------- ----------
RBS                RBS0             4194304        512
RBS                RBS1             4194304        512
RBS                RBS2             4194304        512
RBS                RBS3             4194304        512
RBS                RBS4             4194304        512
RBS                RBS5             4194304        512
RBS                RBS6             4194304        512

7 rows selected.

/****************************************************************************************
-- 데이터베이스의 롤백 세그먼트
****************************************************************************************/
   :: DBA_ROLLBACK_SEGS
      ㅇ식별(Identification)
        - segment_id
        - segment_name
      ㅇ위치, 유형, 상태
        - tablespace_name
        - owner(public or sys)
        - status(online or offline)

SQL> SELECT segment_name, tablespace_name, owner, status
2 FROM dba_rollback_segs;

SEGMENT_NAME TABLESPACE_NAME OWNER STATUS
------------- ---------------- ------ -------
SYSTEM        SYSTEM           SYS    ONLINE
RBS0          RBS              PUBLIC ONLINE
RBS1          RBS              PUBLIC ONLINE
RBS2          RBS              PUBLIC ONLINE
RBS3          RBS              PUBLIC ONLINE
RBS4          RBS              PUBLIC ONLINE
RBS5          RBS              PUBLIC ONLINE
RBS6          RBS              PUBLIC ONLINE
RBS01         RBS              SYS    OFFLINE

9 rows selected.

/****************************************************************************************
-- 롤백 세그먼트 통계 추출위한 조인
****************************************************************************************/

   - usn       : 롤백 세그먼트 번호. 세그먼트의 이름을 알려면 v$rollname.usn과 조인 필요
   - extents   : 롤백 세그먼트의 익스텐트 개수
   - rssize    : 바이트 단위로 나타낸 세그먼트 현재 크기
   - xacts     : 세그먼트를 사용중인 트랜젝션 개수
   - optsize   : 롤백 세그먼트의 optimal 값
   - hwmsize   : high water mark, 세그먼트가 사용된 이후 최대로 많이 사용하였던 크기를 바이트 단위로 표시
   - aveactive : 시간에 대해 평균한 현재 활성 익스텐트의 크기
   - status    : 롤백 세그먼트의 상태
                 (online은 롤백 세그먼트가 사용 가능함을 의미한다. pending offline은 롤백 세그먼트를
                  오프라인으로 만드는 명령이 수행되었으나 아직 세그먼트를 사용하는 활성 트랜젝션이
                  있음을 의미하며 롤백 세그먼트를 사용하는 트랜젝션이 전부 완료되자마자 offline상태
                  가 될 것임.)
   - curext    : 롤백 세그먼트 헤드의 현재 위치(익스텐트 번호)
   - curblk    : 롤백 세그먼트 헤드의 현재 위치(블록 번호)

SQL> SELECT n.name, s.extents, s.rssize, s.optsize, s.hwmsize, s.xacts, s.status
2 FROM v$rollname n, v$rollstat s
3 WHERE n.usn = s.usn;

NAME EXTENTS RSSIZE OPTSIZE HWMSIZE XACTS STATUS
------- ------- ------- ------- ------- -----   ------
SYSTEM 9 745472  745472 0 ONLINE
RBS0 8 4186112 4194304 4186112 0 ONLINE
RBS1 8 4186112 4194304 4186112 0 ONLINE
RBS2 8 4186112 4194304 4186112 0 ONLINE
RBS3 8 4186112 4194304 4186112 0 ONLINE
RBS4 8 4186112 4194304 4186112 0 ONLINE
RBS5 8 4186112 4194304 4186112 0 ONLINE
RBS6 8 4186112 4194304 4186112 0 ONLINE

/****************************************************************************************
-- 사용중인 롤백 세그먼트 점검위한 조인
****************************************************************************************/

   - ses_addr     : 세션 주소, v$session.saddr과 조인될 수 있음
   - xidusn       : 트랜젝션이 사용하는 롤백 세그먼트 번호
   - ubafil       : 트랜젝션 ID의 일부로 사용됨, 트랜젝션이 기록중인 롤백 세그먼트의 현 위치 지정
   - ubasqn       : 트랜젝션 ID의 일부로 사용됨, 트랜젝션이 기록중인 롤백 세그먼트의 현 위치 지정
   - ubarec       : 트랜젝션 ID의 일부로 사용됨, 트랜젝션이 기록중인 롤백 세그먼트의 현 위치 지정
   - used_ublk    : 트랜젝션이 만들어 내는 실행 취소 블록의 개수
   - start_uext   : 트랜젝션이 쓰기 시작할 롤백 세그먼트 익스텐트
   - start_ubafil : 트랜젝션이 쓰기 시작할 롤백 세그먼트 파일 번호
   - start_ubablk : 트랜젝션이 쓰기 시작할 롤백 세그먼트 블록 번호

SQL> SELECT s.username, t.xidusn, t.ubafil, t.ubablk, t.used_ublk
2 FROM v$session s, v$transaction t
3 WHERE s.saddr = t.ses_addr;

USERNAME          XIDUSN      UBAFIL        UBABLK     USED_UBLK
------------ ---------- ----------   -----------   -----------
SYSTEM                 2           2             7             1
SCOTT                  1           2           163             1

2 rows selected.

/****************************************************************************************
-- 관리용
****************************************************************************************/
-- rollback_seg.sql
select a.tablespace_name, a.segment_name, b.segment_type
, a.min_extents, a.pct_increase, a.status, b.bytes, b.blocks
from dba_rollback_segs a, dba_segments b
where a.segment_name = b.segment_name
/

-- rollback_seg_stats.sql
select n.name, s.extents, s.rssize, s.optsize, s.hwmsize, s.xacts, s.status
from v$rollname n, v$rollstat s
where n.usn = s.usn
/

-- rollback_seg_blocking.sql
SELECT s.sid, s.serial#, t.start_time, t.xidusn, s.username
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));

-- rollback_seg_cont.sql
select n.name, round(100 * s.waits/s.gets)
from v$rollname n, v$rollstat s
where n.usn = s.usn
/

/****************************************************************************************
-- 트랜젝션 일관선 요구
****************************************************************************************/
SQL> SET TRANSACTION READ ONLY;

SQL> SET TRANSACTION SERIALIZABLE;

/****************************************************************************************
-- 롤백 세그먼트 생성 및 확인
****************************************************************************************/
SQL> CREATE ROLLBACK SEGMENT rbs01
2 TABLESPACE rbs
3 STORAGE( INITIAL 100K
4           NEXT 100K
5           OPTIMAL 4M
6           MINEXTENTS 20
7           MAXEXTENTS 100);

Rollback segment created.

SQL> SELECT tablespace_name, segment_name, bytes, blocks
2 FROM dba_segments
3 WHERE tablespace_name = 'RBS';

8 rows selected.

/****************************************************************************************
-- 온라인 롤백 세그먼트로 만들기
****************************************************************************************/
SQL> ALTER ROLLBACK SEGMENT rbs01 ONLINE;

Rollback segment altered.

/* 인스턴스가 롤백 세그먼트를 항상 온라인 상태가 되도록 파라미터 설정 */
$ more $ORACLE_HOME/dbs/initORCL.ora
...
rollback_segments = ( RBS0, RBS1, RBS2, RBS3, RBS4, RBS5, RBS6, rbs01)
...

SQL> show parameter roll

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
fast_start_parallel_rollback         string LOW
gc_rollback_locks                    string 0-128=32!8REACH
max_rollback_segments                integer 30
rollback_segments                    string rbs0, rbs1, rbs2, rbs3, rbs4,
                                             rbs5, rbs6, rbs7
transactions_per_rollback_segment    integer 5

/****************************************************************************************
-- 롤백 세그먼트 스토리지 설정 변경
****************************************************************************************/
SQL> ALTER ROLLBACK SEGMENT rbs01
2 STORAGE (MAXEXTENTS 200);

Rollback segment altered.

/****************************************************************************************
-- 롤백 세그먼트의 공간 할당 해제
****************************************************************************************/
SQL> ALTER ROLLBACK SEGMENT rbs01
2 SHRINK TO 4M;

Rollback segment altered.

/****************************************************************************************
-- 오프라인 롤백 세그먼트 및 확인
****************************************************************************************/
SQL> ALTER ROLLBACK SEGMENT rbs01 OFFLINE;

Rollback segment altered.

SQL> SELECT segment_name, owner, tablespace_name, status
2 FROM dba_rollback_segs
3 WHERE segment_name = 'RBS01'

SEGMENT_NAME OWNER TABLESPACE_NAME STATUS
------------- ------ ---------------- -------
RBS01 SYS RBS OFFLINE

1 rows selected.

/****************************************************************************************
-- 롤백 세그먼트에서의 Blocking 세션 검출
****************************************************************************************/
SQL> SELECT s.sid, s.serial#, t.start_time, t.xidusn, s.username
2 FROM v$session s, v$transaction t, v$rollstat r
3 WHERE s.saddr = t.ses_addr
4      AND t.xidusn = r.usn
5      AND ((r.curext = t.start_uext-1) OR
             ((r.curext = r.extents-1) AND t.start_uext=0));

SID      SERIAL$     START_TIME            XIDUSN   USERNAME
---      -------    -----------------      ------   --------
9           27    10/30/97 21:10 41           2   SYSTEM

1 row selected.

저작자표시 비영리

디비랑[dɪ'bɪraŋ]

[8i] 롤백 세그먼트 관리(OLN)

+ Recent posts

티스토리툴바