안녕하세요, Surmin입니다! ^^
이번 포스팅에서는 인덱스 리빌드와 TEMP에 관련된 내용을 다뤄보겠습니다. 도움이 되셨으면 좋겠습니다.
개념
- 인덱스 rebuild 시 불필요한 redo 로그가 쌓이는 현상이 발생합니다. 이를 어느 정도 방지하고 싶다면, 인덱스 rebuild시에만 노로깅(nologging)으로 설정한 후, 작업이 끝난 후 다시 로깅(logging)으로 전환하면 됩니다.
- 데이터 건수가 몇만 건, 몇십만 건인 경우는 그리 많지 않다고 볼 수 있습니다.
- 반면, 몇백만 건부터는 상당한 양으로 간주할 수 있습니다.
- 인덱스는 깊이가 깊을 수록 논리적으로 I/O가 많이 발생한다.
리빌드 대상 기준은?
#4이상인 경우
SELECT owner, index_name, blevel
FROM all_indexes
WHERE blevel >=4
ORDER BY blevel DESC;
#20%이상인 경우
analyze index [인덱스명] validate structure;
select (del_lf_rows_len / lf_rows_len) * 100
from index_stats;
index_stat 뷰 신뢰하지 않은 이유
- pct_used 값이 통계 정보를 기반으로 측정되는데, 과연 이 통계 정보를 100% 신뢰할 수 있을지에 대한 의문과, analyze를 수행하는 것 자체가 부담이 될 수 있습니다.
ETC
- HDD와 달리 SSD는 디스크 조각모음이 필요없다 그렇다면 Oracle에서 ALTER INDEX REBUILD를 통한 인덱스 재구성이 SSD 환경에서도 의미가 있나?
- SSD가 물리적으로 조각모음이 필요없다고 해도, Oracle의 인덱스 재구성은 논리적으로 필요 (디스크조각 모음은 물리적임)
rebuild 단점
- rebuild 과정에서 LOCK이 발생하게 됩니다.
- 온라인 기능을 사용하면 트랜잭션 처리는 가능하지만, 트랜잭션이 많이 쌓일 경우 성능 저하가 발생할 수 있으며, 운영 체제에서 행을 유발할 수 있습니다.
- 온라인 기능을 사용하지 않는 경우 DML 작업이 불가능합니다.
- 온라인 기능이 있다고 하더라도 성능상의 문제로 인해, 가능한 경우 서비스 외 시간이나 서비스 중단 후 rebuild 을 해야한다.
대처 방안
- 온라인 rebuild 기능을 활용하여 작업을 수행합니다.
핵심
- 성능떨어진 인덱스를 정상화시키는 작업에서 인덱스탄다 못탄다는 큰의미가 없다.
- 작업이 빠른 방법보다는 안전한 방법, 사용자가 작업이 있었는지도 모르는 방법으로 작업해야 한다.
- 24시간 돌아가야하는 서비스지만 사용자가 적은 시간대가 있다-> online rebuild 가능 24시간 돌아가야하는 서비스지만 사용자가 많이 없다. -> 잠깐 lock 감수하고 offline rebuild 가능 주간 시간대만 돌아가는 서비스 -> offline rebuild 가능
인덱스 rebuild 과정 (리빌드 실습 참고)
[공통사항]
- 인덱스 리빌딩을 위하여 temp가 필요하고 공간이 부족하면 ORA-1652 에러 발생
[alter index index_name rebuild; 경우] 2. index fast full scan (멀티블락 스캔[가장빠른 스캔]) 통하여 index를 생성, 할당받은 PGA 공간이 부족시 temp에서 진행 합니다. 3. 완성 시 기존 index는 drop 후 교체함
이유는?
=> 테스트 시 LMODE 가 4 와 3을 확인 하였고, 4 와 3은 호환이 안됨
[alter index index_name rebuild online 경우] 2. full table scan 해서 신규 index 를 생성, 할당받은 PGA 공간이 부족시 temp에서 진행 합니다. (운영중 임으로, 싱글 블락 스캔 이용)
- 리빌드 하는동안 SYS_JOURNAL_xxx 저널 테이블(임시테이블)에 DML 작업 기록
- TEMP의 새 인덱스에 SYS_JOURNAL_xxx의 DML 기록을 반영 (기존의 인덱스 및 저널 테이블은 삭제됨) (이 과정을 ‘merge’라고 함)
===============
- 두 개의 인덱스가 필요한 이유
- 인덱스 리빌드 세션에서 에러가 발생하거나 기타 이유로 작업이 완료되지 않았을 경우, 기존 인덱스를 계속 사용할 수 있어야 합니다.
- ‘ALTER INDEX XXX REBUILD;’ 명령어만을 사용했을 경우, 다른 세션에서는 기존 인덱스를 사용할 수 있으나, 이 경우 SELECT 작업만 가능합니다.
- (테이블을 다른 테이블스페이스로 옮긴 후에는 인덱스를 사용할 수 없음)
장점 및 단점
- 온라인 옵션을 주지 않았을 때
- 장점: 작업 속도가 빠름
- 단점: DML 사용 불가 (SELECT만 가능)
- 온라인 옵션을 주었을 때
- 장점: DML 사용 가능
- 단점: 작업 속도가 느림
실습 방향
- 인덱스 rebuild 시 임시 테이블스페이스 사용 여부와 함께, 인덱스 생성 및 rebuild 시 온라인 옵션에 따라 DML 작업 수행 여부를 확인합니다.
- 임시 테이블스페이스가 부족할 경우 발생할 수 있는 문제
- 중간에 인덱스 rebuild을 중단하면?
실습
- 작업 전 temp 할당량 및 사용량을 먼저 확인해 봅니다.
- 중요한 부분: 사용된 TEMP 확인(사용중이 아니어도 used가 한번 늘어난 temp는 줄지 않음(재활용하기위해 used로 표시됨))
set lines 200
set pages 1000
col total for 999,999,999,999
col used for 999,999,999,999
col free for 999,999,999,999
col "used(%)" for a10
select s.tablespace_name, sum(s.BYTES_USED+bytes_free) total,
sum(s.BYTES_USED) used,
sum(bytes_free) free,
round(sum(s.BYTES_USED)/sum(s.BYTES_USED+bytes_free)*100,2)||' %' "used(%)"
from v$temp_space_header s
group by s.tablespace_name
/
#확인 시 15% 사용중이며, 3M 사용
TABLESPACE_NAME | TOTAL| USED| FREE|used(%)
------------------------------|----------------|----------------|----------------|----------
TEMP | 20,971,520| 3,145,728| 17,825,792|15 %
- 만약 실제 사용한 공간만 보고싶으면?
- 실제로는 2M 사용중이다.
set pages 40
set line 132
col tbs_name format a15
col Used_mega format a15
col Used_PCT format 999.99
col Cache_PCT format 999.99
SELECT d.tablespace_name tbs_name, d.status Status, d.CONTENTS Type,
d.extent_management Ext_manage,
NVL(a.BYTES / 1024 / 1024, 0) Total_mega,
NVL(t.BYTES, 1)/1024/1024 ||' / '|| NVL(a.BYTES / 1024 / 1024, 1) Used_mega,
NVL(t.BYTES / a.BYTES * 100, 1) Used_PCT,
NVL(t.curnt_byte/1024/1024, 1) Cache_mega,
(NVL(t.curnt_byte/1024/1024, 1)/NVL(a.BYTES / 1024 / 1024, 0)*100) Cache_PCT
FROM SYS.dba_tablespaces d,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_temp_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (BYTES_USED) curnt_byte, sum(BYTES_CACHED) BYTES
FROM v$temp_extent_pool
GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management LIKE 'LOCAL'
AND d.CONTENTS LIKE 'TEMPORARY'
/
TBS_NAME |STATUS |TYPE |EXT_MANAGE|TOTAL_MEGA|USED_MEGA |USED_PCT|CACHE_MEGA|CACHE_PCT
---------------|----------|---------------------|----------|----------|---------------|--------|----------|---------
TEMP |ONLINE |TEMPORARY |LOCAL | 20|2 / 20 | 10.00| 0| .00
- 테스트를 위하여 테이블을 생성합니다.
-- 기존 테이블 삭제 (있다면)
DROP TABLE test_table PURGE;
-- 새 테스트 테이블 생성
CREATE TABLE test_table (
id NUMBER PRIMARY KEY,
name VARCHAR2(100),
description VARCHAR2(1000)
);
-- 인덱스 생성
CREATE INDEX idx_test_name ON test_table(name);
-- 데이터 대량 삽입
BEGIN
FOR i IN 1..5000000 LOOP -- 500만건
INSERT INTO test_table
VALUES (
i,
DBMS_RANDOM.STRING('A', 50) || i,
DBMS_RANDOM.STRING('A', 200) || ' Description ' || i
);
IF MOD(i, 10000) = 0 THEN -- 1만건마다 커밋
COMMIT;
END IF;
END LOOP;
COMMIT;
END;
/
- 리빌드 진행
ALTER INDEX idx_test_name REBUILD;
- TMI) nologging 을 사용하면 index를 만들면서 로그정보가 로그버퍼 영역에 저장되지 않아 rebuild 가 빠르게 된다.
- 인덱스의 경우 재생성 하는것이 복구이므로 logging 모드일 필요 없음.
alter index index_name rebuild online nologging;
- 리빌드 완료 후 임시 테이블스페이스(TEMP)를 확인한 결과, 사용량이 증가한 것을 확인하였습니다. 이를 바탕으로 리빌드 과정에서 TEMP를 활용하고 있습니다.
SELECT d.tablespace_name tbs_name, d.status Status, d.CONTENTS Type,
d.extent_management Ext_manage,
NVL(a.BYTES / 1024 / 1024, 0) Total_mega,
NVL(t.BYTES, 1)/1024/1024 ||' / '|| NVL(a.BYTES / 1024 / 1024, 1) Used_mega,
NVL(t.BYTES / a.BYTES * 100, 1) Used_PCT,
NVL(t.curnt_byte/1024/1024, 1) Cache_mega,
(NVL(t.curnt_byte/1024/1024, 1)/NVL(a.BYTES / 1024 / 1024, 0)*100) Cache_PCT
FROM SYS.dba_tablespaces d,
(SELECT tablespace_name, SUM (BYTES) BYTES
FROM dba_temp_files
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM (BYTES_USED) curnt_byte, sum(BYTES_CACHED) BYTES
FROM v$temp_extent_pool
GROUP BY tablespace_name) t
WHERE d.tablespace_name = a.tablespace_name(+)
AND d.tablespace_name = t.tablespace_name(+)
AND d.extent_management LIKE 'LOCAL'
AND d.CONTENTS LIKE 'TEMPORARY'
/
TBS_NAME |STATUS |TYPE |EXT_MANAGE|TOTAL_MEGA|USED_MEGA |USED_PCT|CACHE_MEGA|CACHE_PCT
---------------|----------|---------------------|----------|----------|---------------|--------|----------|---------
TEMP |ONLINE |TEMPORARY |LOCAL | 368|367 / 368 | 99.73| 0| .00
- 위 결과를 보면 전과 많은 차이를 보이는 것을 알 수가 있으며, 특히 TOTAL_MEGA 넘은 것을 확인하였다. 왜 이런지 확인을 해보자
- 확인 시 AUTOEXTENSIBLE: YES (자동 확장 가능) 확인 및 MAXBYTES 약 34G 확인 하였습니다.
-- 라인 크기 설정
SET LINESIZE 200
SET PAGESIZE 100
-- 열 크기 조정
COLUMN file_name FORMAT A40
COLUMN tablespace_name FORMAT A15
COLUMN status FORMAT A6
COLUMN autoextensible FORMAT A3
COLUMN maxbytes FORMAT 999,999,999,999
COLUMN bytes FORMAT 999,999,999,999
COLUMN increment_by FORMAT 9,999
-- 쿼리 실행
SELECT file_name,
tablespace_name,
bytes,
maxbytes,
autoextensible aut,
increment_by,
status
FROM dba_temp_files
WHERE tablespace_name = 'TEMP';
FILE_NAME |TABLESPACE_NAME| BYTES| MAXBYTES|AUT|INCREMENT_BY|STATUS
-----------------------------------------|---------------|----------------|----------------|---|------------|------
/u01/app/oracle/oradata/ORA19C/temp01.dbf|TEMP | 385,875,968| 34,359,721,984|YES| 80|ONLINE
| | | | | |
- temp 사용 중 유저 실시간 보고싶다면?
set line 150
set concat "+"
col username format a10
col osuser format a10
col tablespace format a15
SELECT b.tablespace,
b.segfile#,
b.segblk#,
b.blocks,
a.sid,
a.serial#,
c.spid,
a.username,
a.osuser,
a.status,
a.sql_hash_value
FROM v$session a,
v$sort_usage b,
v$process c
WHERE a.saddr = b.session_addr and a.paddr=c.addr
ORDER BY b.tablespace, b.segfile#, b.segblk#, b.blocks
/
아주 적은량의 데이터를 리빌드 할경우는?
- 할당받은 PGA 안에서 정렬이 가능 할경우 TEMP를 사용하지 않는다.
- 온라인 옵션 주던 안주던 똑같음
#테스트 테이블 생성
CREATE TABLE small_test AS
SELECT * FROM dba_objects WHERE rownum <= 100;
#인덱스 생성
SQL> CREATE INDEX small_idx ON small_test(object_id);
#리빌드전 확인
SELECT d.tablespace_name,
d.temp_total_bytes,
NVL(f.temp_free_bytes,0) temp_free_bytes,
d.temp_total_bytes - NVL(f.temp_free_bytes,0) temp_used_bytes,
ROUND(NVL((d.temp_total_bytes - NVL(f.temp_free_bytes,0))/d.temp_total_bytes * 100, 0), 2) percent_used
FROM
(SELECT tablespace_name,
SUM(bytes) temp_total_bytes
FROM dba_temp_files
GROUP BY tablespace_name) d,
(SELECT tablespace_name,
SUM(bytes_free) temp_free_bytes
FROM v$temp_space_header
GROUP BY tablespace_name) f
WHERE d.tablespace_name = f.tablespace_name(+)
16 ORDER BY percent_used;
TABLESPACE_NAME |TEMP_TOTAL_BYTES|TEMP_FREE_BYTES|TEMP_USED_BYTES|PERCENT_USED
------------------------------|----------------|---------------|---------------|------------
TEMP | 20971520| 13631488| 7340032| 35
1 row selected.
SELECT
name,
CASE
WHEN name IN ('session pga memory', 'session pga memory max')
THEN ROUND(value/1024/1024, 2) || ' MB'
ELSE TO_CHAR(value)
END as VALUE
FROM v$sysstat
WHERE name IN (
'sorts (memory)',
'sorts (disk)',
'session pga memory',
'session pga memory max'
14 );
NAME |VALUE
----------------------------------------------------------------|-------------------------------------------
session pga memory |223.52 MB
session pga memory max |283.72 MB
sorts (memory) |27771
sorts (disk) |0
4 rows selected.
- 리빌드 진행
#여기엔 없지만, ONLINE도 PGA 안에서 정렬 가능하면 TEMP 안쓰는거 TEST를 통해 확인 하였습니다.
SQL> ALTER INDEX small_idx REBUILD;
Index altered.
SELECT d.tablespace_name,
d.temp_total_bytes,
NVL(f.temp_free_bytes,0) temp_free_bytes,
d.temp_total_bytes - NVL(f.temp_free_bytes,0) temp_used_bytes,
ROUND(NVL((d.temp_total_bytes - NVL(f.temp_free_bytes,0))/d.temp_total_bytes * 100, 0), 2) percent_used
FROM
(SELECT tablespace_name,
SUM(bytes) temp_total_bytes
FROM dba_temp_files
GROUP BY tablespace_name) d,
(SELECT tablespace_name,
SUM(bytes_free) temp_free_bytes
FROM v$temp_space_header
GROUP BY tablespace_name) f
WHERE d.tablespace_name = f.tablespace_name(+)
ORDER BY percent_used;
TABLESPACE_NAME |TEMP_TOTAL_BYTES|TEMP_FREE_BYTES|TEMP_USED_BYTES|PERCENT_USED
------------------------------|----------------|---------------|---------------|------------
TEMP | 20971520| 13631488| 7340032| 35
1 row selected.
SELECT
name,
CASE
WHEN name IN ('session pga memory', 'session pga memory max')
THEN ROUND(value/1024/1024, 2) || ' MB'
ELSE TO_CHAR(value)
END as VALUE
FROM v$sysstat
WHERE name IN (
'sorts (memory)',
'sorts (disk)',
'session pga memory',
'session pga memory max'
14 );
NAME |VALUE
----------------------------------------------------------------|-------------------------------------------
session pga memory |223.7 MB
session pga memory max |283.72 MB
sorts (memory) |27794
sorts (disk) |0
4 rows selected.
- 결과
TEMP 사용량:
# 전/후 모두 동일
TEMP_TOTAL: 20,971,520 bytes
TEMP_FREE: 13,631,488 bytes
TEMP_USED: 7,340,032 bytes
PERCENT: 35%
→ TEMP 사용량 변화 없음
메모리 사용량:
# REBUILD 전
session pga memory: 223.52 MB
session pga memory max: 283.72 MB
sorts (memory): 27771
sorts (disk): 0
# REBUILD 후
session pga memory: 223.7 MB
session pga memory max: 283.72 MB
sorts (memory): 27794
sorts (disk): 0
→ 아주 작은 변화만 있음:
- PGA 메모리: 0.18MB 증가
- 메모리 정렬: 23회 증가
- 디스크 정렬: 변화없음 (0)
결론: 작은 크기의 인덱스 REBUILD는
- TEMP를 사용하지 않고
- 메모리 내에서만 처리됨을 확인할 수 있습니다
인덱스 생성 및 리빌드 시 온라인 옵션에 따라 DML 작업 수행 여부를 확인
-테스트에 앞서 중요 포인트는 리빌드 시 온라인은 트랜잭션을 commit; 혹은 rollback;을 해야 리빌드 작업이 마무리됩니다. 트랜잭션을 마무리 안할 경우 대기합니다. (정렬이 안되니)
- Test를 위하여 실습환경을 준비합니다.
drop table idxtest purge;
create table idxtest as select * from dba_segments;
direct patch write 수행
- 오라클의 SGA를 거치지 않고 데이터 파일에 직접 쓰기 작업을 수행
- DBWR에 의해 쓰기 작업이 이루어지는것이 아니라 서버 프로세스에 의해 직접 쓰기작업이 수행된다.
- HWM(High Water Mark) 이후에 블록을 추가한다. (append)
- 테이블의 nologging 옵션이 설정된 경우 리두 발생량을 줄일 수 있다.
- 테이블의 TM LOCK을 EXCLUSIVE 하게 휙득하기 때문에 다른 세션에서 DML 허용안함 (COMMIT,ROLLBACK하기전까지 LOCK걸려있음)
- 다른 세션 뿐만 아니라 명령어를 입력한 후 해당 세션에서 동일한 명령어 시 아래와 같이 ORA-12838 발생함
#원하는 만큼 실행
insert /*+ append */ into idxtest select * from idxtest;
commit;
#테이블 용량 확인
select bytes/1024/1024 mb
from dba_segments
where segment_name = 'IDXTEST';
MB
----------
6277
#세션 3개 접속 후 2개의 세션에서 실행
select sid from v$mystat where rownum<=1;
SID -- 세션 1
----------
1168
SID -- 세션 2
----------
1226
- online 옵션 미사용 테스트
create index idxtest_ix1 on idxtest(owner, segment_name, segment_type, tablespace_name);
https://blog.naver.com/qowndyd/222167001572 [이미지 참고]
- row share (RS): 2
- LOCK된 테이블에 대한 동시 액세스를 허용하지만 세션이 전체 테이블을 LOCK하는 것은 금지합니다.
- row exclusive (RX): 3
- row share와 동일하지만 SHARE 모드에서도 LOCK을 금지합니다. row exclusive LOCK은 데이터에 대해 갱신, 삭제, 입력 시 자동으로 획득됩니다.
- share (S): 4
- 동시 QUERY는 허용하지만 LOCK된 테이블에 대한 변경은 금지됩니다.
- 테이블에서 인덱스를 생성하려면 SHARE LOCK이 필요하며 자동으로 요청됩니다.
- SHARE ROW EXCLUSIVE (SRX): 5
- 전체 테이블을 QUERY하는 데 사용되며 다른 유저가 테이블의 행을 QUERY하는 것은 허용하지만 해당 테이블을 SHARE 모드에서 LOCK하거나 행을 갱신하는 것은 금지됩니다.
- EXCLUSIVE (X): 6
- LOCK된 테이블에서의 QUERY는 허용하지만 해당 테이블에서 다른 작업은 금지됩니다.
- 세션 3에서 lock 확인
- SID 변경필요
set lines 200 pages 1000
col sid for 999999
col usn for a20
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid in (1168, 1226)
and TYPE in ('TX','TM')
order by sid, type
;
SID TYPE ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- ---- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
1168 TM 73228 0 4 0 0 1 7692 0
1168 TM 18 0 3 0 0 0 18 0
1168 TX 131087 874 6 0 0 2 15 874
=> 1168 세션에 TM lock ID1 18(OBJ$) 대해 LMODE 3 (row-X (SX))이 걸려있고,
TM lock ID1 73228(IDXTEST) 에 대해 LMODE 4 (share (S))가 걸려있고,
TX lock 에 대해 LMODE 6 (exclusive (X)) 이 걸려있음
*TM: 테이블락 / TX: 로우락
*LMODE 4 (share (S)) 와 LMODE 3 (row-X (SX)) 가 호환이 안된다.
- TM lock 오브젝트 확인
col object_name for a10
select object_id, object_name, object_type from dba_objects
where object_id in ( 73228, 18);
OBJECT_ID OBJECT_NAM OBJECT_TYPE
---------- ---------- ----------------------------------------------
18 OBJ$ TABLE
73228 IDXTEST TABLE
#OBJ$ 오브젝트와 IDXTEST 테이블임
- 세션 2에서 insert, update, delete 각각 시도 후 lock 확인
- lock 때문에 dml이 수행되지 않음
- 인데스 생성 후 순차적으로 실행됨
SQL> insert into idxtest select * from dba_segments where rownum<=1;
(대기)
update idxtest set owner = 'IMSI';
(대기)
delete idxtest where rownum<=1;
(대기)
- online 옵션 사용 테스트
drop index idxtest_ix1;
create index idxtest_ix1 on idxtest(owner, segment_name, segment_type, tablespace_name) online;
- 세션 3에서 lock 확인 및 인덱스 맵핑 확인
select sid, type, id1, id2, lmode, request, block
, to_char(trunc(id1/power(2,16))) USN
, bitand(id1, to_number('ffff', 'xxxx')) + 0 SLOT
, id2 SQN
from v$lock
where 1=1
and sid in (1168, 1226)
and TYPE in ('TX','TM')
order by sid, type;
SID TYPE ID1 ID2 LMODE REQUEST BLOCK USN SLOT SQN
------- ---- ---------- ---------- ---------- ---------- ---------- -------------------- ---------- ----------
1168 TM 73228 0 2 0 0 1 7692 0
1168 TM 73241 0 4 0 0 1 7705 0
1168 TX 196609 919 6 0 0 3 1 919
1168 TX 196627 913 6 0 0 3 19 913
1168 세션에 TM lock ID1 73228(IDXTEST) 에 대해 LMODE 2 (row-S (SS))가 걸려있고,
(*online 옵션 미사용시에는 TM lock ID1 73241(IDXTEST) 에 대해 LMODE 4 (share (S)) 가 있었음)
TM lock ID1 73241(SYS_JOURNAL_2022460) 에 대해 LMODE 4 (share (S))가 걸려있고,
TX lock 에 대해 LMODE 6 (exclusive (X))이 걸려있음
#인덱스가 임시 테이블에 맵핑되어있음을 확인 -> 인덱스 리빌딩 후 다시 확인 시 임시 테이블 없어진 것을 확인
select a.object_name, b.table_name temp_table_name
from dba_objects a,
( select substr(object_name,13) as obj_id,
object_name as table_name
from dba_objects
where object_name like 'SYS_JOURNAL_%') b
where a.OBJECT_ID = b.obj_id;
OBJECT_NAME TEMP_TABLE_NAME
--------------- ----------------
IDXTEST_IX1 SYS_JOURNAL_73240
- TM lock 오브젝트 확인
select object_id, object_name, object_type from dba_objects
where object_id in (73228, 73244);
OBJECT_ID OBJECT_NAM OBJECT_TYPE
---------- ---------- ----------------------------------------------
73228 IDXTEST TABLE
73241 SYS_JOURNA TABLE
L_73240
SYS_JOURNAL_73240오브젝트와 IDXTEST 테이블임
SYS_JOURNAL_73240 오브젝트는 왜 TM lock이 걸리는지 정확히 알수없음(online 으로 인덱스를 만들기 위해서 사용하는 오브젝트로 보임)
- 해당 테이블 정보 확인
- 데이터가 없는 컬럼 7개 짜리 테이블임
desc SYS_JOURNAL_73243
Name Null? Type
----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
C0 NOT NULL VARCHAR2(128)
C1 NOT NULL VARCHAR2(128)
C2 NOT NULL VARCHAR2(18)
C3 NOT NULL VARCHAR2(30)
OPCODE CHAR(1)
PARTNO NUMBER
RID NOT NULL ROWID
SQL> select count(*) from SYS_JOURNAL_73243
;
COUNT(*)
----------
0
- 세션 2에서 insert, update, delete 각각 시도 후 lock 확인
- 모든 dml이 각각 성공적으로 수행됨
- 여기서 중요하게 볼것은 update를 할경우 delete수행 후 insert 를 하는 매커니즘이라는 것을 알수가 있다. 왜냐하면, 원본 데이터에 바로 update를 할경우 문제가 발생 시 원복를 할 수 없음으로 히스토리 즉 undo에 이전 값을 남겨 원복(장애) 대비를 한다.
SQL> insert into idxtest select * from dba_segments where rownum<=1;
1 row selected.
SQL> update idxtest set owner = 'IMSI' where rownum<=1;
1 row updated.
#SYS_JOURNAL_73243 테이블 확인
select * from SYS_JOURNAL_73243;
C0 | C1 | C2 | C3 | O | PARTNO | RID
--------|-----------------|---------|--------|---|---------|------------------
IMSI | C_FILE#_BLOCK# | CLUSTER | SYSTEM | I | 0 | D/////AABAAAclBAAA
SYS | C_FILE#_BLOCK# | CLUSTER | SYSTEM | D | 0 | D/////AABAAAclBAAA
SYS | C_FILE#_BLOCK# | CLUSTER | SYSTEM | I | 0 | D/////AABAAA3cBAAA
SQL> delete idxtest where rownum<=1;
1 row deleted.
⇒ 리빌드 과정에서도 마찬가지로, 온라인 옵션이 아닐 경우에는 트랜잭션이 대기하게 되며, 온라인일 경우에는 트랜잭션이 정상적으로 수행됩니다.
#rebuild는 LMODE 4 임 그래서 불가 dml
alter index idxtest_ix1 rebuild;
#onlind은 LMODE 2,4 임 그래서 dml 가능
alter index idxtest_ix1 rebuild online;
임시 테이블스페이스가 부족할 경우 발생할 수 있는 문제
#현제 temp를 확인 합니다.
#확인 시 auto를 확인
SELECT file_name,
tablespace_name,
bytes,
maxbytes,
autoextensible aut,
increment_by,
status
FROM dba_temp_files
WHERE tablespace_name = 'TEMP';
FILE_NAME |TABLESPACE_NAME | BYTES| MAXBYTES|AUT|INCREMENT_BY|STATUS
--------------------------------------------------|------------------------------|----------|----------|---|------------|----------
/u01/app/oracle/oradata/ORA19C/temp01.dbf |TEMP | 385875968|3.4360E+10|YES| 80|ONLINE
- Test를 위하여 temp 새로 생성 합니다. (auto 해제, 200M고정)
create temporary tablespace temp2 tempfile '/u01/app/oracle/oradata/ORA19C/temp02.dbf' size 200m autoextend off;
#새로추가한 temp 확인
SQL> select tablespace_name, file_id, file_name, bytes/1024/1024 mb, autoextensible from dba_temp_files;
TABLESPACE_NAME | FILE_ID|FILE_NAME | MB|AUT
------------------------------|----------|--------------------------------------------------|----------|---
TEMP2 | 1|/u01/app/oracle/oradata/ORA19C/temp02.dbf | 200|NO
#신규 temp를 default temporary 테이블스페이스로 고정
alter database default temporary tablespace temp2;
#기존 temp 삭제
drop tablespace temp including contents and datafiles;
- Test를 위하여 인덱스 삭제
DROP INDEX idx_test_name;
#인덱스 생성하였지만, temp 부족으로 실패 확인
CREATE INDEX idx_test_name ON test_table(name);
==
ERROR at line 1:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP2
==
#다른세션에서 아래의 명령어로 모니터링 하기
SELECT d.tablespace_name,
d.temp_total_mb,
NVL(f.temp_free_mb,0) temp_free_mb,
d.temp_total_mb - NVL(f.temp_free_mb,0) temp_used_mb,
ROUND(NVL((d.temp_total_mb - NVL(f.temp_free_mb,0))/d.temp_total_mb * 100, 0), 2) percent_used
FROM (SELECT tablespace_name,
ROUND(SUM(bytes)/1024/1024) temp_total_mb
FROM dba_temp_files
GROUP BY tablespace_name) d,
(SELECT tablespace_name,
ROUND(SUM(bytes_free)/1024/1024) temp_free_mb
FROM v$temp_space_header
GROUP BY tablespace_name) f
WHERE d.tablespace_name = f.tablespace_name(+)
ORDER BY percent_used;
TABLESPACE_NAME |TEMP_TOTAL_MB|TEMP_FREE_MB|TEMP_USED_MB|PERCENT_USED
------------------------------|-------------|------------|------------|------------
TEMP2 | 200| 0| 200| 100
#명령어 입력 시 세션별 TEMP 사용량: 92MB
SELECT s.sid, s.serial#, s.username, s.program,
round(t.blocks*8/1024,2) temp_mb
FROM v$sort_usage t, v$session s
WHERE t.session_addr = s.saddr
ORDER BY t.blocks DESC;
SID | SERIAL# | USERNAME | PROGRAM | TEMP_MB
-----|---------|----------|---------------------------------|---------
390 | 33284 | SYS | sqlplus@ora19c (TNS V1-V3) | 92
해결방안
- temp resize 합니다.
⇒리사이즈를 진행했지만 여전히 공간 부족 문제가 발생하여 중간중간 600MB로 늘렸습니다. 결론적으로, AUTO설정이 더 편리할 것 같습니다.
ALTER DATABASE TEMPFILE '/u01/app/oracle/oradata/ORA19C/temp02.dbf' RESIZE 600M;
Temp 관리는 어떻게??
- Oracle Tablespace 따른 temp 관리 Oracle Tablespace 관리 노트 Smallfile vs Bigfile Tablespace (8K block 기준) Smallfile Tablespace
- 데이터파일 1개당 최대 크기: 32GB
- 최대 데이터파일 개수: 1,022개
- 총 최대 용량: 약 32TB (32GB × 1,022)
- 대부분의 사이트에서 사용하는 일반적인 옵션
- 데이터파일 개수: 1개만 가능
- 데이터파일 최대 크기: 32TB
- 대형 사이트에서 주로 사용
- 수백 GB 이상의 TEMP 공간이 필요할 때 적합 (temp file 수십 수백개 추가하기 번거로움 해소)
- auto extend 설정으로 자동 확장 활성화
- 필요에 따라 자동으로 크기가 증가함
- 32GB 한계 도달 시 에러 발생하면:
- 새로운 데이터파일 추가
- 에러 발생 시 확인사항:
- 에러 유발 쿼리 확인
- 특히 임시(temp) 공간 사용량이 많은 쿼리 체크
- 의미: 일회성으로 실행하는 임시 쿼리
- 주의점: temp 공간을 과도하게 사용할 수 있음
- 모니터링: 응용프로그램이 아닌 수동으로 실행된 쿼리 추적 필요
- Tempfile 공간 강제 할당 수행 (11.2.0.3 이후)
- *원인: Oracle 임시 파일 생성은 필요할 때까지 파일 시스템에 공간을 할당하지 않았습니다. 이렇게 하면 시스템 오버헤드 없이 임시 파일을 빠르게 생성할 수 있습니다.
- *작업: 이 이벤트를 활성화하여 Oracle이 파일 생성 시 임시 파일에 빈 블록을 채우도록 합니다. (=세그먼트를 채운다.)
- 활용도
- temp size 늘다가 file system 꽉차면 db 죽는 경우가 있음, 예방 가능
- file system만 이런 event 설정 후 생성이 필요하고, asm 은 기본적으로 미리 공간이 할당되어 불필요 합니다.
SQL> !df -Th / Filesystem Type Size Used Avail Use% Mounted on /dev/mapper/ol-root xfs 47G 38G 9.9G 79% / #일반적인 temporary tablespace 생성 create temporary tablespace temp_noevent tempfile '/u01/app/oracle/oradata/ORA19C/temp_noevent_01.dbf' size 1g; #1G 추가하였지만 확인 시 변화가 없음 SQL> !df -Th / Filesystem Type Size Used Avail Use% Mounted on /dev/mapper/ol-root xfs 47G 38G 9.9G 79% /- 확인해본 결과, 실제 데이터가 1M이어서 변화가 없었던 것입니다.
[oracle@ora19c ORA19C]$ ls -alh temp_noevent_01.dbf -rw-r-----. 1 oracle oinstall 1.1G Jan 9 15:43 temp_noevent_01.dbf [oracle@ora19c ORA19C]$ du -h temp_noevent_01.dbf 1.0M temp_noevent_01.dbf- events ‘60063’ 기능 사용해보자
- 용량에 변화가 있음을 확인.
alter session set events '60063'; create temporary tablespace temp_event tempfile '/u01/app/oracle/oradata/ORA19C/temp_event.dbf' size 1g; [oracle@ora19c ORA19C]$ df -Th / Filesystem Type Size Used Avail Use% Mounted on /dev/mapper/ol-root xfs 47G 39G 8.9G 82% / [oracle@ora19c ORA19C]$ ls -alh temp_event.dbf -rw-r-----. 1 oracle oinstall 1.1G Jan 9 15:53 temp_event.dbf #실제 데이터가 1.1G 다 (세그먼트 확장시킴) [oracle@ora19c ORA19C]$ du -h temp_event.dbf 1.1G temp_event.dbf #AUTO도 가능함 CREATE TEMPORARY TABLESPACE temp_event TEMPFILE '/u01/app/oracle/oradata/ORA19C/temp_event.dbf' SIZE 1G AUTOEXTEND ON ; #확인 SELECT tablespace_name, file_name, bytes/1024/1024 MB, maxbytes/1024/1024 MAX_MB, autoextensible, increment_by * 8192/1024/1024 "NEXT_SIZE(MB)" FROM dba_temp_files HERE tablespace_name = 'TEMP_EVENT'; (1G) TABLESPACE_NAME |FILE_NAME | MB| MAX_MB|AUT|NEXT_SIZE(MB) ------------------------------|--------------------------------------------------|----------|----------|---|------------- TEMP_EVENT |/u01/app/oracle/oradata/ORA19C/temp_event.dbf | 1024|32767.9844|YES| .0078125 1 row selected. #삭제 할려면??? #기본 tbs 아니기때문에 교체없이 가능함 DROP TABLESPACE temp_event INCLUDING CONTENTS AND DATAFILES;
중간에 rebuild 중단하면?
- ORA-08104: this index object is being online built or rebuilt 에러가 발생할 수 있다.
- alter index rebuild online 명령으로 인덱스를 온라인 리빌드 하다가 강제로 종료할 경우 인덱스가 제대로 리빌드 되지 않은 상태로 남아 이후 리빌드 명령을 재수행 할 때 에러가 발생할 수 있음
- 해결 방법
- 일반적으로 이런 문제가 발생한 경우 SMON에 의해 1시간에 한번씩 정리가 됨 그렇기 떄문에 당장 리빌드를 다시 해야하는게 아니라면 기다리면 자동으로 해소가 됨
만약 당장 리빌드를 해야하는 경우에는 DBMS_REPAIR.ONLINE_INDEX_CLEAN 를 사용해서 수동으로 인덱스 리빌드중 발생한 찌꺼기?를 제거해줄 수 있음
(sys 유저로 접속 후 아래 명령 수행)
#dbms_repair.online_index_clean 프로시저가 정상적으로 수행되었다면 result= True 로 표시됨
#이후 인덱스 리빌드 재시도
SQL>
set serveroutput on
declare
result boolean := false;
begin
result := dbms_repair.online_index_clean();
if result then
dbms_output.put_line('result= True');
else
dbms_output.put_line('result= False');
end if;
end;
/
result= True
PL/SQL procedure successfully completed.