| # 요약 | |
| ■ Transaction 처리 순서 ■ Commit ■ Latch ■ Lock ■ Redo 기능 ■ LGWR에 의한 Redo 기록 ■ Log Force at Commit ■ Write Ahead Log |
|
■ Transaction 처리 순서
1. undo segment 할당, enq : US - contention
2. undo segment header에 transaction table slot 생성(못잡으면 대기)
- transaction 정보를 저장하는 곳, TXID(Transaction ID)를 생성한다.
3. Transaction의 대상이 되는 블록을 데이터 버퍼 캐시에 실행계획을 통해서 찾는다.
block header에 ITL(Interested Transaction List)에 transaction entry(slot) 등록
만약에 ITL에 entry 등록할 공간이 없다면 공간이 확보할 때까지 대기해야한다.
이때 발생한 대기 이벤트는 enq : TX - allocate ITL entry
4. 변경할 블록들의 변경 정보를 PGA 영역에서 change vector(이력정보)를 생성한다.
- undo segment header 정보(change vector #1)
- undo block (change vector #2)
- Transaction의 대상이 되는 블록 (change vector #3)
PGA 영역안에 chane vector 정보를 redo entry 라는 이름으로 redo log buffer로 복사한다.
wait event : reoo copy latch, redo allocation latch, redo writing latch를 획득해야 한다.
이 과정에서 latch 경합이 발생하면
latch : redo copy
latch : redo allocation
latch : redo writing
log buffer space wait event
log file switch completion wait event : 현재 리두로그 알이이 꽉차서 로그 스위치가 발생하는 경우
LGWR은 이 작업이 끝날 때까지 대기애해야한다.
5. 이전 이미지(값)에 대한 정보를 undo block에 기록하고 데이터 블록을 변경한다.
변경된 block은 dirty 상태가 된다.
변경된 block header에 CR(Consistent Read) 블록으로 작성해놓는다.
변경하는 행에 대해서 lock을 생성한다.
다른 세션에서 이행에 대해서 lock을 걸고 있으면 기다려야 한다.
이때 발생한 대기 이벤트는 enq : TX - row lock contentions
select * from dba_tables where table_name = 'EMPLOYEES';
INI_TRANS MAX_TRANS
---------- ----------
1 255
- ini trans : 데이터 블록에 동시에 접근 가능한 트랜잭션의 초기 수 (기본값 1, 23byte)
트랜잭션이 많지 않은 경우 낭비를 하게 되므로 굳이 크게 설정할 필요는 없다.
- max trans : 최대 수 (10g, 255 고정)
- pct free
■ Commit
- SCN(System Commit Number) 할당, commit 정보를 redo log buffer에 저장
- undo segment header의 transaction table에 commit이 완료되었다는 정보를 저장
- transaction entry, lock 해제
- LGWR 작동된다. redo log buffer의 내용을 redo log file에 기록
■ Latch
- 가벼운 lock(light-weight lock)
- 공유 메모리 영역(SGA)을 보호하기 위한 동기화 객체
■ Lock
- latch 보다는 무거운 동기화 객체
- 데이터베이스와 관련된 객체(object)를 보호하는 동기화 객체
- enqueue lock
| HR1> drop table hr.emp purge; create table hr.emp as select * from hr.employees; update hr.emp set salary = salary * 1.1 where employee_id = 100; HR2> drop table hr.emp purge; ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired >> 테이블에 대해서는 TM lock, 트랜잭션 대상 행에 대해서는 TX lock update hr.emp set salary = 3000 where employee_id = 100; wait... SYS> select SID from v$session where username = 'HR'; SID ------- 27 HR1 179 HR2 select * from v$lock where sid in (27,179); ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- ------- ---- ---------- ---------- ---------- ---------- ---------- ---------- 4956B4C8 4956B4F4 27 AE 100 0 4 0 6174 0 4956BB14 4956BB40 179 AE 100 0 4 0 124 0 4956BC1C 4956BC48 27 TO 79605 1 3 0 911 0 4956BE88 4956BEB4 179 TX 131091 3018 0 6 v 75 0 F6FAF810 F6FAF840 27 TM 88698 0 3 0 793 0 F6FAF810 F6FAF840 179 TM 88698 0 3 0 75 0 48F61060 48F610A0 27 TX 131091 3018 6 v 0 793 1 v TYPE : TM ID1 : object ID 88698 select owner,object_name,object_id,data_object_id,object_type,status from dba_objects where object_id = 88698; OWNER OBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE STATUS ---------- -------------------- ---------- -------------- ------------------- ------------- HR EMP 88698 88698 TABLE VALID > TM객체 정보 확인 TYPE : TX ID1 : undo segment 번호 + transaction slot 번호 ID2 : transaction slot sequence LMODE > 0 이면 Lock을 보유중인 세션 REQUEST > 0 이면 Lock을 요청중인 세션 CTIME : 현재 lock 모드가 허용된 이후의 시간(second), 즉 lock을 보유하거나 요청한 이후부터의 시간(초) BLOCK : 현재 lock이 다른 lock을 블로킹하고 있는지 여부 1 : 다른 lock을 블로킹 중이다. 0 : 블로킹하지 않음. select sid,blocking_session,event,prev_sql_id from v$session where username = 'HR'; SID BLOCKING_SESSION EVENT PREV_SQL_ID ------- ---------------- --------------------------------------------- ------------- 27 SQL*Net message from client 43qd0u7n4j2nk 179 27 enq: TX - row lock contention dyk4dprp70d74 select sql_text from v$sql where sql_id = '43qd0u7n4j2nk'; SQL_TEXT -------------------------------------------------- update hr.emp set salary = salary * 1.1 where employee_id = 100 HR1> rollback; Rollback complete. HR2> 1 row updated. select sid,blocking_session,event,prev_sql_id from v$session where username = 'HR'; SID BLOCKING_SESSION EVENT PREV_SQL_ID ------- ---------------- --------------------------------------------- ------------- 27 SQL*Net message from client 43qd0u7n4j2nk 179 SQL*Net message from client bupask1tubt3r |
LMODE
- 2 : Row Share(RS), Sub Share(SS)
lock 된 테이블에 대한 동시 엑세스를 허용하지만 세션이 배타적(exclusive) 액세스를 위해 전체 테이블 lock하는 것은 금지한다.
LOCK TABLE hr.emp IN ROW SHARE MODE;
- 3 : Row eXclusive(RX), Sub eXclusive(SX)
Row Share 와 동일하지만 Shared mode에서도 lock을 금지한다.
Row eXclusive Lock은 데이터 갱신, 입력, 삭제 시 자동으로 획득한다.
LOCK TABLE hr.emp IN ROW EXCLUSIVE MODE;
- 4 : Share(S)
동시 query는 허용하지만 lock된 테이블에 대해 변경은 금지한다.
테이블에서 인덱스 생성 시 share lock이 필요하며 자동으로 요청한다.
unique, primary key 충돌 시 발생
LOCK TABLE hr.emp IN ROW SHARE MODE;
- 5 : Share Row eXclusive(SRX), Share Sub eXclusive(SSX)
전체 테이블을 query하는데 사용되며 다른 유저가 테이블 행을 query하는 것은 허용하지만 해당 테이블을 share mode에서
lock하거나 행을 갱신하는 것은 금지한다. 일반테이블보단 딕셔너리 테이블
row cache lock(sequence nocache 속성에서 nextval 호출할 때마다 dictionary 변경)
LOCK TABLE hr.emp IN SHARE ROW EXCLUSIVE MODE;
- 6 : eXclusive(X)
lock된 테이블에서 query는 허용하지만 해당 테이블에서의 다른 작업은 금지 한다.
즉, 테이블에 DDL 작업 시에 exclusive lock이 필요하다.
LOCK TABLE hr.emp IN EXCLUSIVE MODE;
■ Redo 기능
- Database 복구를 목적으로 설계
- Database에 적용된 모든 변경 사항에 대한 이력 저장
- DML/DDL/Recursive SQL(select ~ from ~ for update)에 의해 변경된 모든 Data 이력 (nologging 제외)
- DDL Text 저장 (DML Text 제외)
■ LGWR에 의한 Redo 기록
- Redo Buffer 내용을 Redo Log File 에 기록하는 시점
- 매 3초 마다
- Log Buffer의 1/3 또는 1MB 가 저장될 때
- User Process가 Commit 또는 Rollback으로 Transaction을 종료할 때 (Log Force at Commit)
- DBWR Process에 의해 신호를 받을 때 (write ahead logging)
■ Log Force at Commit
- Transaction과 관련된 모든 Redo Record를 Log File에 저장 후 Commit 완료
■ Write Ahead Log
- Data Buffer에 기록하기 전에 Log Buffer에 먼저 기록
- Data File에 기록하기 전에 Log File에 먼저 기록
8i : 버전 까지는 redo allocation latch 시스템에 1개여서 오직 하나의 프로세스만이 redo buffer에서 메모리 할당할 수 있었다.
9i : shared redo strands : redo buffer의 영역을 일정 개수로 분할해서 사용. 분할된 영역을 strand라고 한다.
_log_parallelism 파라미터의 값을 이용해서 redo strand의 개수를 지정.(cpu수/8) 권장사항
각 strand 마다 redo allocation latch 사용
10g : - shared redo strands 개수를 오라클이 동적으로 관리한다. _log_parallelism_dynamic 히든 파라미터를 TRUE로 설정.
- private redo strands 기능을 사용함으로써 리두데이터를 pga 영역에서 change vector를 생성하는것이 아닌
shared pool에 private strands 영역에 저장하며 이 영역에 저장된 로그 데이터는 redo buffer를 거치지 않고 redo log file에 저장함으로써
latch 경합을 최소화 한다. _log_private_parallelism 히든 파라미터의 값이 true로 설정하면 private redo strands 기능이 활성화된다.
10gR2 private redo strands를 위한 공간은 LOG_BUFFER내에 생성 되며 _log_pivate_mul 에 지정된 비율만큼(5퍼센트)을 private redo strands공간으로 사용,
'zero copy redo' 라고도 불린다.
SELECT a.ksppinm Parameter, b.ksppstvl Value
FROM x$ksppi a, x$ksppcv b
WHERE a.indx = b.indx AND a.ksppinm in ('_log_parallelism_dynamic','_log_private_mul');
PARAMETER VALUE
-------------------------------------------------- ----------
_log_parallelism_dynamic TRUE
_log_private_mul
redo log buffer latch
- latch : redo copy
- latch : redo allocation
- latch : redo writing
| # Redo Log : Insert | |
| SYS> # 리두 모니터링 통계 정보 select n.name, sum(s.value) from v$sesstat s, v$statname n where n.name in ('redo entries','redo size', 'redo synch writes','redo writes','redo blocks written', 'redo log space requests','redo log space wait time') and s.statistic# = n.statistic# and s.sid = (select sid from v$mystat where rownum = 1) group by n.name; NAME SUM(S.VALUE) ------------------------------ ------------ redo entries 4 redo entry가 redo log buffer에 기록된 횟수 redo size 892 redo size (byte) redo log space requests 0 redo log buffer에 redo entry들을 LGWR가 redo log file에 쓰려고 하는데 log file이 꽉 차서 log switch 발생한 횟수 redo log space wait time 0 redo log space requests에 소요된 시간(1/100초) redo synch writes 1 commit, rollback에 의해 수행된 redo write 수 redo blocks written 0 redo log file에 write된 redo log block 수 redo writes 0 LGWR 수행한 수 drop table hr.redo_table purge; create table hr.redo_table(id number, name char(100)); > create > 딕셔너리 조작, 갱신 insert into hr.redo_table(id,name) select object_id, object_name from dba_objects; select t.used_ublk, t.used_urec from v$transaction t, v$session s where s.sid = (select sid from v$mystat where rownum = 1) and s.taddr = t.addr; USED_UBLK USED_UREC ---------- ---------- 43 2487 select n.name, sum(s.value) from v$sesstat s, v$statname n where n.name in ('redo entries','redo size', 'redo synch writes','redo writes','redo blocks written', 'redo log space requests','redo log space wait time') and s.statistic# = n.statistic# and s.sid = (select sid from v$mystat where rownum = 1) group by n.name; NAME SUM(S.VALUE) ------------------------------ ------------ redo entries 7816 redo size 10964516 redo log space requests 0 redo log space wait time 0 redo synch writes 4 redo blocks written 0 redo writes 0 select blocks, bytes/1024/1024 mb from dba_segments where segment_name = 'REDO_TABLE'; BLOCKS MB ---------- ---------- 1408 11 >> 사용한 블럭과 용량은 그대로 있다. SYS@ora11g> rollback; |
|
| # Redo Log : Logging Mode + Append | |
| SYS2> new session select n.name, sum(s.value) from v$sesstat s, v$statname n where n.name in ('redo entries','redo size', 'redo synch writes','redo writes','redo blocks written', 'redo log space requests','redo log space wait time') and s.statistic# = n.statistic# and s.sid = (select sid from v$mystat where rownum = 1) group by n.name; NAME SUM(S.VALUE) --------------------------------------------- ------------ redo entries 0 redo size 0 redo log space requests 0 redo log space wait time 0 redo synch writes 0 redo blocks written 0 redo writes 0 insert /*+ append */ into hr.redo_table(id,name) select object_id, object_name from dba_objects; select t.used_ublk, t.used_urec from v$transaction t, v$session s where s.sid = (select sid from v$mystat where rownum = 1) and s.taddr = t.addr; USED_UBLK USED_UREC ---------- ---------- 2 2 select n.name, sum(s.value) from v$sesstat s, v$statname n where n.name in ('redo entries','redo size', 'redo synch writes','redo writes','redo blocks written', 'redo log space requests','redo log space wait time') and s.statistic# = n.statistic# and s.sid = (select sid from v$mystat where rownum = 1) group by n.name; NAME SUM(S.VALUE) --------------------------------------------- ------------ redo entries 1535 redo size 11027116 redo log space requests 0 redo log space wait time 0 redo synch writes 1 redo blocks written 0 redo writes 0 select blocks, bytes/1024/1024 mb from dba_segments where segment_name = 'REDO_TABLE'; BLOCKS MB ---------- ---------- 2816 22 rollback; truncate table hr.redo_table; select blocks, bytes/1024/1024 mb from dba_segments where segment_name = 'REDO_TABLE'; BLOCKS MB ---------- ---------- 8 .0625 > truncate 사용 시 min extent 1개 남김 > /*+ append */ > HWM 뒤에 있는 블럭을 사용하여 추가 > 앞에 빈공간 무시하고 새로운 extent 를 맨 뒤에 추가하여 사용 사용으로 언두, 리두 사용량 줄어듬 -> 복구 목적이 없기 때문에 사용량이 적다 > 기본 원본데이터가 따로 있어 , 블럭 사용량은 늘어남 블럭사용량 처음 insert + insert |
|
| # Redo Log : No Logging Mode + Append | |
| new session> select n.name, sum(s.value) from v$sesstat s, v$statname n where n.name in ('redo entries','redo size', 'redo synch writes','redo writes','redo blocks written', 'redo log space requests','redo log space wait time') and s.statistic# = n.statistic# and s.sid = (select sid from v$mystat where rownum = 1) group by n.name; NAME SUM(S.VALUE) --------------------------------------------- ------------ redo entries 0 redo size 0 redo log space requests 0 redo log space wait time 0 redo synch writes 0 redo blocks written 0 redo writes 0 select logging from dba_tables where table_name = 'REDO_TABLE'; LOGGING ------- YES alter table hr.redo_table nologging; select logging from dba_tables where table_name = 'REDO_TABLE'; LOGGING ------- NO select n.name, sum(s.value) from v$sesstat s, v$statname n where n.name in ('redo entries','redo size', 'redo synch writes','redo writes','redo blocks written', 'redo log space requests','redo log space wait time') and s.statistic# = n.statistic# and s.sid = (select sid from v$mystat where rownum = 1) group by n.name; NAME SUM(S.VALUE) --------------------------------------------- ------------ redo entries 6 redo size 2296 redo log space requests 0 redo log space wait time 0 redo synch writes 1 redo blocks written 0 redo writes 0 >> 딕셔너리에 대한 변경 작업 ddl 문장 수행 |
|
| new session> select n.name, sum(s.value) from v$sesstat s, v$statname n where n.name in ('redo entries','redo size', 'redo synch writes','redo writes','redo blocks written', 'redo log space requests','redo log space wait time') and s.statistic# = n.statistic# and s.sid = (select sid from v$mystat where rownum = 1) group by n.name; NAME SUM(S.VALUE) --------------------------------------------- ------------ redo entries 0 redo size 0 redo log space requests 0 redo log space wait time 0 redo synch writes 0 redo blocks written 0 redo writes 0 insert /*+ append */ into hr.redo_table(id,name) select object_id, object_name from dba_objects; select t.used_ublk, t.used_urec from v$transaction t, v$session s where s.sid = (select sid from v$mystat where rownum = 1) and s.taddr = t.addr; USED_UBLK USED_UREC ---------- ---------- 1 1 select n.name, sum(s.value) from v$sesstat s, v$statname n where n.name in ('redo entries','redo size', 'redo synch writes','redo writes','redo blocks written', 'redo log space requests','redo log space wait time') and s.statistic# = n.statistic# and s.sid = (select sid from v$mystat where rownum = 1) group by n.name; NAME SUM(S.VALUE) --------------------------------------------- ------------ redo entries 419 redo size 50200 redo log space requests 0 redo log space wait time 0 redo synch writes 1 redo blocks written 0 redo writes 0 select blocks, bytes/1024/1024 mb from dba_segments where segment_name = 'REDO_TABLE'; BLOCKS MB ---------- ---------- 1408 11 SYS@ora11g> rollback; select logging from dba_tables where table_name = 'REDO_TABLE'; LOGGING ------- NO alter table hr.redo_table logging; select logging from dba_tables where table_name = 'REDO_TABLE'; LOGGING ------- YES > 테이블에 대한 리두가 필요없다면 nologging mode 로 성능 향상 > 대량 데이터를 부어넣을 때 사용 > logging mode로 복구 |
|
'Oracle 11g > Tuning' 카테고리의 다른 글
| 83 Explain Plan, AutoTrace, v$sql_plan, Buffer Pinning, SQL TRACE (0) | 2024.02.13 |
|---|---|
| 82 TX LOCK, Full Table Scan, Rowid Scan, Index Scan (1) | 2024.02.08 |
| 80 Data Buffer Cache, Buffer Busy Wait, LRU, LRUW (1) | 2024.02.06 |
| 79 Shared Pool, Library Cache (1) | 2024.02.05 |
| 78 SGA, Shared Pool, Select (0) | 2024.02.02 |