전문가를 위한 오라클 데이터베이스 아키텍처 (2014년)
LOB 타입
오라클에서 네 가지 LOB타입을 지원
- CLOB : 문자 LOB, XML또는 일반문장과 같은 문자 정보를 대량으로 저장
- NCLOB: 문자 LOB, CLOB과 같으나 내셔널 케릭터 셋
- BLOB : 이진 LOB, 워드, 이미지 이진정보를 저장
- BFILE : 이진 파일 LOB, 파일에 대한 포인터 일기모드만 접근 가능
내부 LOB
SQL> DROP TABLE T;
Table dropped.
SQL>
SQL> CREATE TABLE T
2 (
3 ID INT PRIMARY KEY,
4 TXT CLOB
5 );
Table created.
SQL> SELECT DBMS_METADATA.GET_DLL('TABLE','T');
SELECT DBMS_METADATA.GET_DLL('TABLE','T')
*
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
SQL> SELECT DBMS_METADATA.GET_DLL('TABLE','T') FROM DUAL;
SELECT DBMS_METADATA.GET_DLL('TABLE','T') FROM DUAL
*
ERROR at line 1:
ORA-00904: "DBMS_METADATA"."GET_DLL": invalid identifier
SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','T') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','T')
--------------------------------------------------------------------------------
'CREATE TABLE "BSHMAN"."T"
( "ID" NUMBER(*,0),
"TXT" CLOB,
PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION DEFERRED
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
TABLESPACE "USERS"
LOB ("TXT") STORE AS BASICFILE (
TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION
NOCACHE LOGGING )'
--LOB의 주요속성(테이블스페이스, STORAGE IN ROW, CHUNK 8192, RETENTION, NOCACHE, STORAGE 절)
-- 멀티 세크먼트
SQL> SELECT SEGMENT_NAME, SEGMENT_TYPE
2 FROM USER_SEGMENTS;
SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------------------------
A TABLE
BIN$V0XPJ2kWRXeXkMio4Pes/Q==$0 TABLE
SOURCE TABLE
DR$SOURCE_IDX$I TABLE
DR$SOURCE_IDX$R TABLE
SYS_IL0000075319C00006$$ LOBINDEX
SYS_IOT_TOP_75322 INDEX
SYS_IL0000075324C00002$$ LOBINDEX
SYS_IOT_TOP_75327 INDEX
DR$SOURCE_IDX$X INDEX
SYS_LOB0000075319C00006$$ LOBSEGMENT
SYS_LOB0000075324C00002$$ LOBSEGMENT
12 rows selected.
-- LOBINDEX : LOB을 찾기위해 사용
-- LOBSEGMENT : 실제 데이터가 저장되는 공간
LOB 테이블스페이스
- 실제 테이블 데이터를 저장하는 테이블스페이스와 관리적인 측면과 성능적인 측면으로 다를 수 있다.
- 버퍼 캐쉬에 캐쉬되지 않는다.
- LOB 액세스는 디스크에서 DIRECT READ나 디스크에 DIRECT WRITE 등 물리적 I/O가 발생
IN ROW 절
SQL> DROP TABLE T;
Table dropped.
SQL>
SQL> CREATE TABLE T
2 (
3 ID INT PRIMARY KEY,
4 IN_ROW CLOB,
5 OUT_ROW CLOB
6 )
7 LOB (IN_ROW) STORE AS (ENABLE STORAGE IN ROW)
8 LOB (OUT_ROW) STORE AS (DISABLE STORAGE IN ROW)
9 ;
Table created.
SQL> DROP TABLE T;
Table dropped.
Elapsed: 00:00:00.37
-- 테이블에 길이가 4,000바이트 미만인 문자열 데이터를 입력
SQL> CREATE TABLE T
2 (
3 ID INT PRIMARY KEY,
4 IN_ROW CLOB,
5 OUT_ROW CLOB
6 )
7 LOB (IN_ROW) STORE AS (ENABLE STORAGE IN ROW)
8 LOB (OUT_ROW) STORE AS (DISABLE STORAGE IN ROW);
Table created.
Elapsed: 00:00:00.12
SQL>
SQL> INSERT INTO T
2 SELECT ROWNUM
3 , OWNER || ' ' || OBJECT_NAME || ' ' || OBJECT_TYPE || ' ' || STATUS
4 , OWNER || ' ' || OBJECT_NAME || ' ' || OBJECT_TYPE || ' ' || STATUS
5 FROM ALL_OBJECTS;
71641 rows created.
Elapsed: 00:01:24.61
SQL> DECLARE
2 L_CNT NUMBER;
3 L_DATA VARCHAR2(32765);
4 BEGIN
5
6 SELECT COUNT(*)
7 INTO L_CNT
8 FROM T;
9
10 DBMS_MONITOR.SESSION_TRACE_ENABLE;
11 FOR I IN 1 .. L_CNT
12 LOOP
13 SELECT IN_ROW INTO L_DATA FROM T WHERE ID = I;
14 SELECT OUT_ROW INTO L_DATA FROM T WHERE ID = I;
15 END LOOP;
16 END;
17 /
PL/SQL procedure successfully completed.
Elapsed: 00:01:24.83
SELECT IN_ROW
FROM
T WHERE ID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 71638 1.57 1.53 0 0 0 0
Fetch 71638 1.21 1.36 0 214914 0 71638
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 143277 2.79 2.89 0 214914 0 71638
SELECT OUT_ROW
FROM
T WHERE ID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 71638 1.26 1.16 0 0 0 0
Fetch 71638 15.49 63.37 71638 501469 0 71638
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 143277 16.75 64.53 71638 501469 0 71638
Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 91 (recursive depth: 1)
Rows Row Source Operation
------- ---------------------------------------------------
1 TABLE ACCESS BY INDEX ROWID T (cr=3 pr=0 pw=0 time=0 us cost=1 size=253 card=1)
1 INDEX UNIQUE SCAN SYS_C0011335 (cr=2 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 75339)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
direct path read 71638 0.14 51.95
asynch descriptor resize 71638 0.00 0.14
-- IN_ROW 보다 OUT_ROW 더 많은 논리 I/O를 발생
SQL> CREATE SEQUENCE S START WITH 200000;
Sequence created.
Elapsed: 00:00:00.06
SQL> DECLARE
2 L_CNT NUMBER;
3 L_DATA VARCHAR2(32765);
4 BEGIN
5 DBMS_MONITOR.SESSION_TRACE_ENABLE;
6 FOR I IN 1 .. 100
7 LOOP
8 UPDATE T SET IN_ROW = TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') WHERE
ID = I;
9 UPDATE T SET OUT_ROW = TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') WHERE
ID = I;
10 INSERT INTO T (ID, IN_ROW) VALUES (S.NEXTVAL, 'Hello World');
11 INSERT INTO T (ID, OUT_ROW) VALUES (S.NEXTVAL, 'Hello World');
12 END LOOP;
13 END;
14 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.29
UPDATE T SET IN_ROW = TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
WHERE
ID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.00 0.00 0 200 206 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.00 0.00 0 200 206 100
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE T (cr=2 pr=0 pw=0 time=0 us)
1 INDEX UNIQUE SCAN SYS_C0011339 (cr=2 pr=0 pw=0 time=0 us cost=1 size=2015 card=1)(object id 75437)
UPDATE T SET OUT_ROW = TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS')
WHERE
ID = :B1
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.03 0.13 0 1499 2244 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.03 0.13 0 1499 2244 100
Rows Row Source Operation
------- ---------------------------------------------------
0 UPDATE T (cr=12 pr=0 pw=1 time=0 us)
1 INDEX UNIQUE SCAN SYS_C0011339 (cr=2 pr=0 pw=0 time=0 us cost=1 size=253 card=1)(object id 75437)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 1 0.00 0.00
direct path write 91 0.01 0.04
asynch descriptor resize 175 0.00 0.00
********************************************************************************
INSERT INTO T (ID, IN_ROW)
VALUES
(S.NEXTVAL, 'Hello World')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 100 0.01 0.00 0 2 321 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.03 0.01 0 2 321 100
INSERT INTO T (ID, OUT_ROW)
VALUES
(S.NEXTVAL, 'Hello World')
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 100 0.01 0.07 0 1007 1531 100
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 101 0.01 0.07 0 1007 1531 100
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
direct path write 100 0.00 0.04
asynch descriptor resize 192 0.00 0.00
********************************************************************************
-- 동일한 현상 발생 기본적으로 ENABLE STORAGE IN ROW를 사용하는것이 좋음
CHUNK
- 일반적으로 가장 작은 할당 단위가 블록인데, LOB에서는 청크가 가장 작은 단위이다.
- LOB인스턴스는 적어도 하나의 청크를 사용
- LOB인스턴스당 청크의 수를 최소화할 때 주의(공간낭비를 최소화하여 적당한 크기지정)
RETENTION절
SQL> DROP TABLE T;
Table dropped.
SQL> CREATE TABLE T
2 (
3 ID INT PRIMARY KEY,
4 TXT CLOB
5 );
Table created.
SQL> INSERT INTO T VALUES(1,'HELLO WORLD');
1 row created.
SQL> COMMIT;
Commit complete.
SQL> DECLARE
2 L_CLOB CLOB;
3
4 CURSOR C IS SELECT ID FROM T;
5 L_ID NUMBER;
6 BEGIN
7 SELECT TXT INTO L_CLOB FROM T;
8 OPEN C;
9
10 UPDATE T SET ID = 2, TXT = 'GoodBye';
11 COMMIT;
12
13 DBMS_OUTPUT.PUT_LINE( DBMS_LOB.SUBSTR(L_CLOB, 100, 1));
14 FETCH C INTO L_ID;
15 DBMS_OUTPUT.PUT_LINE( 'ID = ' || L_ID );
16 CLOSE C;
17 END;
18 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM T;
ID TXT
---------- ----------------------------
2 GOOBBYE
-- 커서 C 읽기 일관성 이미지 UNDO SEGMENT 사용
-- LOB의 읽기 일관성 이미지는 LOBSEGMENT 사용
-- RETENTION은 UNDO_RETENTION 설정에 따라 데이터베이스가 변경된 LOG 세그먼트 데이터를 LOG 세그먼트에 보유
-- LOB 세그먼트의 재사용 가능한 빈 공간이 계속적인 INSERT , UPDATE에 의해 즉시 재사용도는 것이 아니다("왜 LOB세그먼트는 자꾸 증가하는가?")
-- 보유기간이 만료되지 않았기 때문에 정보를 재적하면서 대량 삭제로 인해 LOB세그먼가 커지는 경향이 존재
-- RETENTION 옵션대신 LOG STORAGE절에 PCTVERSION을 사용
-- 다른세션이 LOB을 수정하는 동안 LOB을 자주 읽기에는 10% 기본값은 너무 작을 수 있다.
-- 그렇다면 언두 테이블스페이스를 늘리거나, 언두 RETENTION을 늘리거나, RBS 공간을 추가
CACHE절
- NOCACHE 대신 CACHE나 CACHE READS를 선택가능
- 기본값은 NOCACHE
- CACHE READ는 LOB데이터를 디스크에서 버퍼에 캐시하지만, 쓰기는 캐시하지않고 디스크에 직접씀
- CACHE는 읽기와 쓰기 모두 LOB 데이터를 캐시
문법
- ALTER TABLE TABNAME MODIFY LOB(LOBNAME) (CACHE);
- ALTER TABLE TABNAME MODIFY LOB(LOBNAME) (NOCACHE);
LOB STORAGE절
- LOBSEGMENT 와 LOBINDEX 에 동일 하게 적용
BFILE
-- BFILE은 운영체제에서 파일의 포인터다
-- 읽기모드로 접근할 수 있다.
-- BFILE데이터는 읽기 일관성을 제공하지 않는다.
SQL> DROP TABLE T;
Table dropped.
SQL>
SQL> CREATE TABLE T
2 (
3 ID INT PRIMARY KEY,
4 OS_FILE BFILE
5 );
Table created.
SQL>
SQL> CREATE OR REPLACE DIRECTORY MY_DIR AS '/TMP';
Directory created.
SQL> INSERT INTO T VALUES(1, BFILENAME('MY_DIR', 'test.dbf'));
1 row created.
SQL>
SQL> SELECT DBMS_LOB.GETLENGTH(OS_FILE) FROM T;
SELECT DBMS_LOB.GETLENGTH(OS_FILE) FROM T
*
ERROR at line 1:
ORA-22288: file or LOB operation GETLENGTH failed
?????？?？?? ？?? ?? ?????？？
ORA-06512: at "SYS.DBMS_LOB", line 787
SQL> COMMIT;
Commit complete.
SQL> UPDATE T
2 SET OS_FILE = BFILENAME('my_dir','test.dbf');
1 row updated.
SQL> SELECT DBMS_LOB.GETLENGTH(OS_FILE) FROM T;
SELECT DBMS_LOB.GETLENGTH(OS_FILE) FROM T
*
ERROR at line 1:
ORA-22285: non-existent directory or file for
GETLENGTH operation
ORA-06512: at "SYS.DBMS_LOB", line 787
SQL> CREATE OR REPLACE DIRECTORY "my_dir" AS '/tmp';
Directory created.
SQL> SELECT DBMS_LOB.GETLENGTH(OS_FILE) FROM T;
SELECT DBMS_LOB.GETLENGTH(OS_FILE) FROM T
*
ERROR at line 1:
ORA-22288: file or LOB operation GETLENGTH failed
?????？?？?? ？?? ?? ?????？？
ORA-06512: at "SYS.DBMS_LOB", line 787
"데이터베이스 스터디모임" 에서 2014년에 "전문가를 위한 오라클 데이터베이스 아키텍처
" 도서를 스터디하면서 정리한 내용 입니다.
- 강좌 URL : http://www.gurubee.net/lecture/4055
- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^
- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.