대량 INSERT 작업에서 로그발생 최소화 및 성능을 위해 APPEND 힌트 사용 시
테이블 logging/nologging 에 의한 로그발생량 차이가 있는가?
| 요약 : - 테이블 nologging + APPEND 사용 시 로그 발생량 가장 적음(테이블 logging 모드에서 append 힌트만 사용한 경우도 로그발생량 감소 효과는 있음) - 인덱스는 logging / nologging 에 상관없이 무조건 log 사용(인덱스 nologging은 생성 시 적용되는 옵션) - 파티션 구문 추가하여 파티션 단위 작업 시 해당 파티션만 LOCK 잡힘(여러개 파티션 동시 작업 가능) 주의사항 : - APPEND 힌트 사용 시 TABLE LOCK 발생으로 다른 세션에서 DML 불가능 - insert 중에는 segment_type 이 Temporary Segment 이고 insert 작업 종료후 기존의 테이블에 MERGE 된다. - insert ... select 에서만 동작 merge 문에서는 insert 시 동작함 업데이트 부분은 동작 안함 |
테이블 logging / nologging + APPEND 힌트 사용 유무 로그 발생 테스트
-- 테이블 logging
SQL> SELECT * FROM dba_indexes
WHERE owner = 'SK'
;
2 3
no rows selected
SQL>
SQL> SELECT owner,table_name, logging
FROM DBA_TABLES
WHERE owner = 'SK'
; 2 3 4
OWNER TABLE_NAME LOGGING
-------------------- -------------------- ----------
SK T2 YES
SK T1 YES
SQL> select b.name, a.value
from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and b.name in ('redo entries', 'redo size')
;
2 3 4 5
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 0
redo size 0
SQL> insert into sk.t1 select * from sk.t2 ;
1000000 rows created.
SQL> commit ;
Commit complete.
SQL> select b.name, a.value
from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and b.name in ('redo entries', 'redo size')
;
2 3 4 5
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 738616
redo size 513706504
SQL> truncate table sk.t1 ;
Table truncated.
-- 테이블 nologging + APPEND 힌트
SQL> alter table sk.t1 nologging ;
Table altered.
SQL> SELECT owner,table_name, logging
FROM DBA_TABLES
WHERE owner = 'SK'
; 2 3 4
OWNER TABLE_NAME LOGGING
-------------------- -------------------- ----------
SK T2 YES
SK T1 NO
SQL> select b.name, a.value
from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and b.name in ('redo entries', 'redo size')
; 2 3 4 5
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 740165
redo size 513957192
SQL> insert /*+ append */ into sk.t1 select * from sk.t2 ;
1000000 rows created.
SQL> commit ;
Commit complete.
SQL> select b.name, a.value
from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and b.name in ('redo entries', 'redo size')
; 2 3 4 5
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 807740
redo size 709795036
SQL> truncate table sk.t1 ;
Table truncated.
-- 테이블 logging + APPEND 힌트
SQL> alter table sk.t1 logging ;
Table altered.
SQL> SELECT owner,table_name, logging
FROM DBA_TABLES
WHERE owner = 'SK'
; 2 3 4
OWNER TABLE_NAME LOGGING
-------------------- -------------------- ----------
SK T2 YES
SK T1 YES
SQL> select b.name, a.value
from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and b.name in ('redo entries', 'redo size')
; 2 3 4 5
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 809146
redo size 710023120
SQL> insert /*+ append */ into sk.t1 select * from sk.t2 ;
1000000 rows created.
SQL> commit ;
Commit complete.
SQL> select b.name, a.value
from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and b.name in ('redo entries', 'redo size')
; 2 3 4 5
NAME VALUE
---------------------------------------------------------------- ----------
redo entries 895864
redo size 1066590804
SQL>반응형
테이블 nologging + 인덱스 logging/nologging 에 따른 로그 발생 테스트
-- table nologging, index logging
SQL> select * from sk.tab1 ;
no rows selected
SQL> select b.name, a.value
from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and b.name in ('redo entries', 'redo size')
;
NAME VALUE
------------------------------ ---------------------
redo entries 36666
redo size 270607988
SQL> insert /*+ append */ into sk.tab1 select * from sk.tab2 ;
2303104 rows created.
SQL> commit ;
Commit complete.
SQL> select b.name, a.value
from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and b.name in ('redo entries', 'redo size')
; 2 3 4 5
NAME VALUE
------------------------------ ---------------------
redo entries 221892
redo size 821189788
SQL> truncate table sk.tab1 ;
Table truncated.-- table nologging, index nologging
SQL> alter index sk.tab1_ix nologging ;
Index altered.
SQL> select b.name, a.value
from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and b.name in ('redo entries', 'redo size')
;
NAME VALUE
------------------------------ ---------------------
redo entries 223652
redo size 821507600
SQL> insert /*+ append */ into sk.tab1 select * from sk.tab2 ;
2303104 rows created.
SQL> commit ;
Commit complete.
SQL> select b.name, a.value
from v$mystat a, v$statname b
where a.STATISTIC# = b.STATISTIC#
and b.name in ('redo entries', 'redo size')
; 2 3 4 5
NAME VALUE
------------------------------ ---------------------
redo entries 408806
redo size 1371689668
SQL>
파티션 테이블에서 특정 파티셔만 APPEND 힌트 사용 시 해당 파티션만 LOCK 잡힘(여러개 파티션 동시에 작업 가능)
## 파티션 구문 추가 작업 시 lock wait 없음
-- session 1
SQL> INSERT /*+ append */ INTO sk.tab1 PARTITION (p9) SELECT * FROM sk.p9 ;
-- session 2
SQL> INSERT /*+ append */ INTO sk.tab1 PARTITION (p10) SELECT * FROM sk.p10 ;
-- active session
INST_ID USERNAME SID SERIAL# SQL_ID CMD LAST_CALL_ET SQL_EXEC_TIME WAIT_S STATE WAIT_CLASS EVENT PLSQL_ENTRY_OBJECT_ID MACHINE
------- -------- ---------- ---------- ------------- -------------------- ------------ ------------- ------ ------------------- -------------------- ------------------------------- --------------------- --------------------
1 SYS 41 61447 d1c86yq4atc5j INSERT 6 6 0 WAITING User I/O direct path read svr1
1 SYS 40 19575 15r50gy6kq02n INSERT 8 8 0 WAITING User I/O direct path write svr1
-- lock wait
INST_ID SESSION_ID SERIAL# ORACLE_USERNAME OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE MODULE STATE STATUS OS_USER_NAME MACHINE SQL_ID EVENT LMO LAST_CALL_ET HOLDER
---------- ---------- ---------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------------------------------- ------------------- -------- -------------------- ---------------------------------------------------------------- ------------- ---------------------------------------------------------------- --- ------------ ----------
1 40 19575 SYS TAB1 TABLE sqlplus@svr1 (TNS V1-V3) WAITING ACTIVE oracle svr1 15r50gy6kq02n direct path write RX 11
1 41 61447 SYS TAB1 TABLE sqlplus@svr1 (TNS V1-V3) WAITING ACTIVE oracle svr1 d1c86yq4atc5j direct path read RX 9
1 41 61447 SYS TAB1 P9 TABLE PARTITION sqlplus@svr1 (TNS V1-V3) WAITING ACTIVE oracle svr1 d1c86yq4atc5j direct path read X 9
1 40 19575 SYS TAB1 P10 TABLE PARTITION sqlplus@svr1 (TNS V1-V3) WAITING ACTIVE oracle svr1 15r50gy6kq02n direct path write X 11
## 파티션 구문 없이 작업 시 lock wait 발생
-- session 1
SQL> INSERT /*+ append */ INTO sk.tab1 SELECT * FROM sk.p10 ;
-- session 2
SQL> INSERT /*+ append */ INTO sk.tab1 SELECT * FROM sk.p9 ;
-- active session
INST_ID USERNAME SID SERIAL# SQL_ID CMD LAST_CALL_ET SQL_EXEC_TIME WAIT_S STATE WAIT_CLASS EVENT PLSQL_ENTRY_OBJECT_ID MACHINE
------- -------- ---------- ---------- ------------- -------------------- ------------ ------------- ------ ------------------- -------------------- ------------------------------- --------------------- --------------------
1 SYS 41 61447 d5qj4aq62m1r5 INSERT 7 8 0 WAITING User I/O direct path read svr1
1 SYS 40 19575 gm38274mjt6sd INSERT 6 6 WAITING Application enq: TM - contention svr1
-- lock wait
INST_ID SESSION_ID SERIAL# ORACLE_USERNAME OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE MODULE STATE STATUS OS_USER_NAME MACHINE SQL_ID EVENT LMO LAST_CALL_ET HOLDER
---------- ---------- ---------- -------------------- -------------------- -------------------- -------------------- ---------------------------------------------------------------- ------------------- -------- -------------------- ---------------------------------------------------------------- ------------- ---------------------------------------------------------------- --- ------------ ----------
1 40 19575 SYS TAB1 TABLE sqlplus@svr1 (TNS V1-V3) WAITING ACTIVE oracle svr1 gm38274mjt6sd enq: TM - contention NO 6 41
1 41 61447 SYS TAB1 TABLE sqlplus@svr1 (TNS V1-V3) WAITED SHORT TIME ACTIVE oracle svr1 d5qj4aq62m1r5 direct path read X 7'DBMS > ORACLE' 카테고리의 다른 글
| 권한 부여 시 ROLE, PRIVILEGE 차이 (0) | 2022.03.14 |
|---|---|
| 테이블 OWNER가 아닌 다른 유저로 인덱스 생성 시 필요 권한 (0) | 2022.02.28 |
| 테이블 조회 시 다른 세션에서도 uncommit block 을 읽을까? (0) | 2022.02.25 |
| oracle index 사이즈 증가 테스트 (0) | 2022.02.16 |
| ORA-1653 테이블스페이스 여유 공간 있는데 INSERT 실패 (0) | 2022.02.16 |
댓글