언두
- 서버프로세스가 사용자별로 언두세그먼트를 할당해서 언두 데이터를 관리
- 인스턴스당 여러개가 존재하지만 - 사용되는것은 하나
- 9i 부터는 aum (automatic undo management)
- 트랜잭셔널 롤백
- 리두 콘시스턴씨 ( Read Consistency = CR ) - 씨알 작업을 통해 트랜잭션이 끝나지 않은 데이터는 변경전 데이터를 보여줌
select rowid, dbms_rowid.rowid_relative_fno(rowid) as fno,
dbms_rowid.rowid_block_number(rowid) as blkno
from A where rownum = 1;
ALTER SYSTEM DUMP DATAFILE 1 BLOCK 77481 ;
Block header dump: 0x00412ea9
Object id on Block? Y
seg/obj: 0x127b5 csc: 0x00.10a22c itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Block header dump: 0x00412ea9
Object id on Block? Y
seg/obj: 0x127b5 csc: 0x00.10bb16 itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0005.00a.0000039e 0x00c0060a.0122.09 ---- 1 fsc 0x0000.00000000
0x02 0x0004.01e.000002b2 0x00c00199.00e1.0a C--- 0 scn 0x0000.0010a2a4
bdba: 0x00412ea9
data_block_dump,data header at 0x7f33105a1a5c
===============
tsiz: 0x1fa0
hsiz: 0x14
pbl: 0x7f33105a1a5c
76543210
flag=--------
ntab=1
nrow=1
frre=-1
fsbo=0x14
fseo=0x1f8c
avsp=0x1f83
tosp=0x1f83
0xe:pti[0] nrow=1 offs=0
0x12:pri[0] offs=0x1f8c
block_row_dump:
tab 0, row 0, @0x1f8c
tl: 7 fb: --H-FL-- lb: 0x1 cc: 1
col 0: [ 3] 42 43 44
end_of_block_dump
End dump data blocks tsn: 0 file#: 1 minblk 77481 maxblk 77481
0x01 0x0016.026.00003222 0x01c0003f.0410.26 ---- 1 fsc …
0x02 0x0016.025.00003222 0x01c0003f.0410.25 C--- 0 scn …
Slot = itl 내부의 하나 slot 엔트리 번호
ITL = 트랜잭션 리스트
XID = ITL SLOT 과 관계를 맺은 트랜잭션 XID
UBA : 트랜잭션이 참조하고 있는 UNDO 정보 (BEfore 이미지가 저장된 곳을 찾을때 이것을 사용)
LCK : 해당 itl slot 을 사용하는 트랜택션이 해당 block 에 대해 얼마나 많은 row 에 lock 을 걸었는지
select xidusn,xidslot,xidsqn from v$transaction
where addr = (select taddr from v$session where sid = 162);
. Xid = 0x0016.026.00003222 인데 이 값을 십진수로 변환하면 USN=22, SLOT=38, SQN=12384가 된다. V$TRANSACTION 뷰에서
로우 레벨 락은 해당 [로우의 변경여부 + 로우를 변경한 트랜잭션정보(ITL) + 언두영역의 트랜잭션 테이블 슬롯 + TX 락]의 정보들이 조합된 일종의 논리적인 락이다
undo retention
언두 세그먼트 여분이 있을 경우 사용
커밋 롤백 후 -( 트랜잭션 완료후) 언두 세그먼트에 언두 데이터를 다른 서버 프로세스가 덮어 쓰지 못하게 막아주는 시간
undo_retention_guarantee = 언두 세그먼트 덮어쓰기 안되게 설정
alter tablespace undo retention guratee; noguarantee;
언두 용량은 줄어들지 않음
- 커밋이나, 롤백 후 다른 사용자로부터 덮어쓰기가 되지만 안에 있는 언두데이터는 지워지지 않고 그대로 남아있거나,
덮어쓰기로 인해 새로운 언두데이터가 들어오기 때문
- 줄이는 법
= 언두테이블스페이스를 생성후 - 변환작업 = 삭제
### UNDO 반환 RULE
1. RETENTION 확인
select TABLESPACE_NAME, RETENTION
from DBA_TABLESPACES
WHERE TABLESPACE_NAME like 'UNDO%';
TABLESPACE_NAME RETENTION
------------------------------ -----------
UNDOTBS1 NOGUARANTEE
NOGUARANTEE : 언두가 부족할 경우 언두 훔치기가 발생됨.(Default)
GUARANTEE : 언두 훔치기가 발생하지 않음 ( ora - 30036 : unable to extend segment by %s in undo tablespace 발생)
2. AUTOEXTENSIBLE 확인
select TABLESPACE_NAME, AUTOEXTENSIBLE from DBA_DATA_FILES
where tablespace_name like 'UNDO%';
TABLESPACE_NAME AUTOEXTENSIBLE
------------------------------ --------------
UNDOTBS1 NO
AUTOEXTENSIBLE
ON : 데이터파일 자동 확장 기능 사용
NO : 데이터파일 자동 확장 기능 미사용 (Default)
MAXSIZE : 자동 확장시 최대 파일 크기 지정, 기본값 = UNLIMITED 데이터 파일에 위치한 파일 시스템 공간이 허용하는 범위
3. 언두 유지 시간 값 확인 (10분간격으로 동기화 됨 : 스크립트 UNDO_TUNE_RETION.sql)
select BEGIN_TIME,END_TIME,MAXQUERYID, MAXQUERYLEN, TUNED_UNDORETENTION
from v$undostat
where MAXQUERYID ='6ns7h3ssphu47'
order by END_TIME desc ;
BEGIN_TIME : 통계 수집 시작 시간
END_TIME : 통계 수집 종료 시간
MAXQUERYLEN : 데이터 수집 구간 중 가장 오래 수행된 sql 수행 시간(sec)
TUNED_UNDORETENTION : 오라클이 인스턴스에서 최적으로 설정된 언두 시간
** UNDO_RETENTION 파라미터 값은 최소 언두 유지 시간을 의미
select NAME, VALUE from v$parameter
where name like '%undo%';
NAME VALUE
------------------------ -------------------------
undo_management AUTO
undo_tablespace UNDOTBS1
undo_retention 900
* 10gR2이상부터 AUTOEXTENSIBLE 옵션을 사용 하지 않을 경우 TUNED_UNDORETENTION 값은 345,600(sec) 고정 됨
| GUARANTEE | AUTO EXTEND | 언두 유지시간 | 의미 | |
| 1 | NO | NO | 345,600 , UNDO_RETENTION 중 큰 값 | 언두 TBS공간이 부족한 경우 언두 유지시간 값에 상관없이 훔치기 발생됨. |
| 2 | NO | YES | MAXQUERYLEN(SEC)+ 300초와 RETENTION 값중 큰값 | 언두 TBS공간 확장이 불가능한 경우 훔치기 발생됨. |
| 3 | YES | NO | 345,600 , UNDO_RETENTION 중 큰 값 | 언두 TBS공간이 없을 경우 ora-30036 발생 후 트랜잭션 실패 |
| 4 | YES | YES | MAXQUERYLEN(SEC)+ 300초와 RETENTION 값중 큰값 | 언두 TBS공간 확장이 불가능한 경우 ora-30036 발생 후 트랜잭션 실패 |
# 언두 사용량 확인
select status , sum(bytes/1024/1024) MB
FROM DBA_UNDO_EXTENTS
WHERE TABLESPACE_NAME ='UNDOTBS1'
GROUP BY FILE_ID, STATUS
ORDER BY STATUS, FILE_ID;
ACTIVE : 현재 UNDO 를 사용하고 있는 값
EXPIRED : undo_retention 값이 지난 값으로 undo 부족 시 비워질 수 있는 segment 양
UNEXPIRED : undo 를 사용하는 작업이 종료되었지만 아직 undo_retention 값이 지나지 않은 segment 양
-- 현재 undo 영역 사용자 확인
select a.username, a.sid, a.serial#, a.osuser, b.tablespace, b.blocks, c.sql_text
from v$session a, v$tempseg_usage b, v$sqlarea c
where a.saddr = b.session_addr
and c.address = a.sql_address
and c.hash_value = a.sql_hash_value;
-- EXTENTS = MAX_EXTENTS 일 경우 rolback segment 가 maxextent 에 도달하여 발생한 오류
-- 그 외에는 transaction 에 필요한 space 가 rollback tablespace free space 가 없어서 발생한 것
select SEGMENT_NAME, OWNER, TABLESPACE_NAME, INITIAL_EXTENT, NEXT_EXTENT, MAX_EXTENTS, RSSIZE, EXTENTS, XACTS
from DBA_ROLLBACK_SEGS a, v$rollstat b
where a.segment_id = b.usn;
TEMP 영역 사용 쿼리 확인
select USERNAME,
SQL_ID,
CONTENTS,
SEGTYPE,
sum(SIZE_MB)/1024 SIZE_GB,
(select substr(sql_text, 1, 100)
from v$sql
where sql_id=a.SQL_ID
GROUP BY substr(sql_text, 1, 100)) sql_text
from (select username "USERNAME",
ktssosqlid "SQL_ID",
decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY') "CONTENTS",
decode(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX', 5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED') "SEGTYPE",
round(ktssoblks*p.value/1024/1024, 2) "SIZE_MB"
from x$ktsso k,
v$session s,
v$parameter p
where ktssoses = s.saddr
and ktssosno = s.serial#
and p.name = 'db_block_size'
--and ktssoblks*p.value >= 1024*1024
) a
group by USERNAME,
SQL_ID,
CONTENTS,
SEGTYPE
order by SIZE_GB desc;
select username "USERNAME",
ktssosqlid "SQL_ID",
decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY') "CONTENTS",
decode(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX', 5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED') "SEGTYPE",
round(ktssoblks*p.value/1024/1024, 2) "SIZE_MB"
from x$ktsso k,
v$session s,
v$parameter p
where ktssoses = s.saddr
and ktssosno = s.serial#
and p.name = 'db_block_size'
order by "SIZE_MB" desc ;
select k.inst_id "INST_ID",
ktssoses "SADDR",
sid "SID",
ktssosno "SERIAL#",
username "USERNAME",
osuser "OSUSER",
ktssosqlid "SQL_ID",
ktssotsn "TABLESPACE",
decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMPORARY') "CONTENTS",
decode(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX', 5, 'LOB_DATA', 6, 'LOB_INDEX' , 'UNDEFINED') "SEGTYPE",
ktssofno "SEGFILE#",
ktssobno "SEGBLK#",
ktssoexts "EXTENTS",
ktssoblks "BLOCKS",
round(ktssoblks*p.value/1024/1024, 2) "SIZE_MB",
ktssorfno "SEGRFNO#"
from x$ktsso k,
v$session s,
v$parameter p
where ktssoses = s.saddr
and ktssosno = s.serial#
and p.name = 'db_block_size'
order by sid;
select 쿼리라면
1. clob 컬럼을 가공하지 않았을 경우
Temp segment가 발생하지 않았습니다.
2. clob 컬럼을 substr을 사용했을 경우
temp segment가 발생했으며 size가 계속 증가합니다.
3. clob 컬럼을 substr을 사용 했으며, 코딩시 free Temporary을 사용 했을 경우
Temp segment가 발생 했다가 없어집니다.
즉, clob컬럼을 가공하여 사용할 경우 코딩시 free Temporary을 사용해야 합니다.
insert 쿼리라면
1. free Temporary을 사용 하지 않은경우
temp segments가 계속 증가 했으며, 트랜잭션 종료 후에도 size가 release되지 않았음.
(size는 clob컬럼의 block크기만큼 증가하는걸로 판단됨)
2. free Temporary을 사용한 경우
Temp segment가 발생 했다가 없어집니다.
즉, insert쿼리에서는 코딩시에도 free Temporary을 사용해야 함.
참고
How to Release the Temp LOB Space and Avoid Hitting ORA-1652 (문서 ID 802897.1)
How to Release Temporary LOB Segments without Closing the JDBC Connection (문서 ID 1384829.1)
*** update 소요시간 체크
Update 발생시 undo 데이터를 통해 소요되는 시간을 예측 할 수 있는 쿼리를 만들어봄. (통계정보기반)
UPDATE 수행 중 해당 쿼리를 통해 예측 가능
통계정보와, 바인드 값(update column)에 따라 데이터가 달라짐.
쿼리를 보시기 전에 기본적인 처리방식을 알아야 할 것 같아 아래와 같이 서술함.
ORACLE 에서 UPDATE 발생 시
1. Update 는 DB내부적으로 insert, delete 과정을 거침
2. Oracle 은 변경 전 데이터를 Undo 영역에 레코드 형태로 쌓아둠(일정기간)
3. DML 처리 시 사용되는 Undo 레코드 수치 계산법
1) UPDATE = Update Rows * ( Update Column 포함된 인덱스 수 ) * 2 ( insert, delete)
2) delete = Delete Rows * ( Delete Column 포함된 인덱스 수 )
3) Insert = Insert Rows * ( Insert Column 포함된 인덱스 수 )
Ex) 테스트 서버에 SCOTT.EMPTEST 테이블을 가지고 테스트 완료
테이블 명 : scott.emptest
총 건수 : 500,000 건
인덱스 : pk_emptest(empno), ix_emptest_01(empno, deptno)
통계정보 : 2019/12/18 14:33:56
시나리오 1 ) Pk 컬럼인 empno 의 모든 값을 update 칠 경우.
WITH COLMN_CNT AS ( -- Update Column이 포함된 인덱스 갯수 확인을 위한 With 절
SELECT column_name, count(*) AS CNT
from (select decode(DIC.COLUMN_POSITION,1,DIC.INDEX_OWNER,null) as INDEX_OWNER,
decode(DIC.COLUMN_POSITION, 1, DIC.table_name, null) as table_name ,
DIC.INDEX_NAME,
DIC.COLUMN_NAME,
DIC.COLUMN_POSITION,
CC.COMMENTS
from DBA_IND_COLUMNS DIC, ALL_COL_COMMENTS CC
where DIC.INDEX_OWNER = CC.OWNER
AND DIC.TABLE_NAME = CC.TABLE_NAME
AND DIC.COLUMN_NAME = CC.COLUMN_NAME
AND DIC.table_name = :TABLE_NAME
AND DIC.INDEX_OWNER = :OWNER
order by DIC.INDEX_NAME ,
DIC.COLUMN_POSITION
) A
group by column_name )
select
SID
, SERIAL#
, MACHINE
, PROGRAM
, SQL_ID
, SCHEMANAME
, status
, used_ublk
, used_urec
, max_urec
, start_time
, timeS
from (
select
SE.SID
, SE.SERIAL#
, SE.MACHINE
, SE.PROGRAM
, SE.SQL_ID
, SE.SCHEMANAME
,START_TIME
--,to_date(replace(replace(replace(TR.START_TIME,'/',''),':',''),' ',''),'MM/DD/YY HH24MI:SS') as START_TIME
, TR.STATUS
, TR.USED_UBLK -- 사용된 undo 블록 수
, TR.USED_UREC -- 사용된 undo 레코드 수 (update = update rows * (update column 포함된 인덱스 수 *2), delete = delete rows * 인덱스 수, insert = insert rows * 인덱스 수)
,((SELECT NUM_ROWS FROM SYS.DBA_TABLES WHERE OWNER = :OWNER AND TABLE_NAME = :TABLE_NAME) * -- 통계정보를 통해 총 건수 체크
(SELECT
(CASE -- 해당 컬럼으로 인덱스가 없을 경우 1 반환
WHEN CNT IS NULL THEN 1
ELSE CNT
END) CNT
FROM COLMN_CNT WHERE COLUMN_NAME=:COLUMN_NAME) * 2
)+1 AS max_urec
, TRUNC ((( SYSDATE-to_date(replace(replace(replace(TR.START_TIME,'/',''),':',''),' ',''),'MM/DD/YY HH24MI:SS')) * 86400) / (60 * 60 * 24)) || 'days ' ||
LPAD (MOD (TRUNC ((( SYSDATE- to_date(replace(replace(replace(TR.START_TIME,'/',''),':',''),' ',''),'MM/DD/YY HH24MI:SS')) * 86400) / (60 * 60)), 24), 2, 0) || ':' ||
LPAD (MOD (TRUNC ((( SYSDATE- to_date(replace(replace(replace(TR.START_TIME,'/',''),':',''),' ',''),'MM/DD/YY HH24MI:SS')) * 86400) / 60), 60), 2, 0) || ':' ||
LPAD (MOD ((( SYSDATE- to_date(replace(replace(replace(TR.START_TIME,'/',''),':',''),' ',''),'MM/DD/YY HH24MI:SS')) * 86400), 60), 2, 0) as times
from v$transaction TR, V$SESSION SE
WHERE TR.ADDR = SE.TADDR
) ;
