태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

LOB 데이터와 Cache 속성

오라클 2008.05.15 10:03
얼마전 이런 취지의 문의를 받았다.

LOB 컬럼을 CACHE 속성으로 정의했다. 만일 Buffer Cache가 2G이고, LOB 데이터의 크기가 1.9G라고 하자. 이 경우 LOB 데이터를 읽으면 2G중 1.9G가 LOB 데이터로 꽉 차버리는가? 그렇다면 너무 비효율적인 것이 아닌가?


본인 스스로 이런 고민을 해본 적이 없기 때문에 다음과 같은 대답을 할 수 밖에 없었다.

충분히 의문을 가질 수 있다. Document에 의하면 CACHE 속성의 LOB 데이터는 Buffer Cache를 점유할 수 있으며, 다른 버퍼를 밀어낼 위험이 있다고 되어 있다. 하지만 정말 그렇게 무식하게(?) 동작할까?

그래서 간단한 테스트를 통해 검증을 해보았다.

결론은 CACHE 속성의 LOB 데이터는 크기에 무관하게 Buffer Cache를 점유한다는 것이다.

1. SGA의 크기 분포는 다음과 같다.
UKJA@ukja101> show sga

Total System Global Area  171966464 bytes
Fixed Size                   787988 bytes
Variable Size             145488364 bytes
Database Buffers           25165824 bytes  <-- 25M
Redo Buffers                 524288 bytes

2. 큰 크기(30M 이상)의 Btree Index를 경유하는 일반적인 Range Scan을 통해 Buffer Cache를 꽉 채운다.

OBJ_NAME                          OBJ_CNT
------------------------------ ----------
T2_N1                                3006

최대 수용 가능한 블록 수는 (Buffer Cache / Block Size) = 25M/8K = 3200(개)로, Buffer Cache가 거의 Btree Index로 꽉 찼음을 알 수 있다.

3. 큰 크기(100M 이상)의 LOB 데이터를 읽은 후 Buffer Cache를 조사해보면 이상한 이름의 객체로 Buffer Cache가 꽉찬 것을 알 수 있다.

create table t1(c1 int, c2 clob)
  lob(c2) store as (cache)
;
...
OBJ_NAME                          OBJ_CNT
------------------------------ ----------
SYS_LOB0000051135C00002$$            2579
PROCEDUREINFO$                         85
                                       44
MGMT_METRICS_RAW_PK                    25
MGMT_SYSTEM_PERF_LOG_IDX_01            16


dba_lobs 뷰를 통해 어떤 테이블의 LOB 데이터인지 확인해 보면, 우리가 정의한 t1 테이블이라는 것을 확인할 수 있다.

UKJA@ukja101> exec print_table('select * from dba_lobs where segment_name = ''&lob_seg''');
Enter value for lob_seg: SYS_LOB0000051135C00002$$
OWNER                         : UKJA
TABLE_NAME                    : T1
COLUMN_NAME                   : C2
SEGMENT_NAME                  : SYS_LOB0000051135C00002$$
TABLESPACE_NAME               : USERS
INDEX_NAME                    : SYS_IL0000051135C00002$$
CHUNK                         : 8192
PCTVERSION                    : 10
RETENTION                     : 900
FREEPOOLS                     :
CACHE                         : YES
LOGGING                       : YES
IN_ROW                        : YES
FORMAT                        : ENDIAN NEUTRAL
PARTITIONED                   : NO


큰 크기의 LOB은 기본적으로 Buffer Cache를 경유하지 않는 것이 합리적이다.

  • 우선 Buffer Cache내의 다른 중요한 블록들을 밀어 낸다
  • 큰 크기의 LOB를 공유할 필요가 있겠는가? 한번 읽고 버리는 것이 합리적이다.
  • 설사 공유할 필요가 있다고 해도 그 큰 크기를 어떻게 메모리에 보관하겠는가? Recycle Buffer Pool 같은 것을 쓴다고 해도 곧 밀려날 것이다.

한가지 재미있는 것은 Buffer Cache에 존재하지 않는 LOB 데이터를 읽어 들일 때는 db file sequential read 이벤트를 대기하는 것으로 관찰된다는 것이다.

EVENT                                       D_WAITS D_TIMEOUTS D_TIME_WAITED
---------------------------------------- ---------- ---------- -------------
SQL*Net message from client                       6          0          3062
db file sequential read                        2426          0           485
db file scattered read                           71          0             8
rdbms ipc reply                                   1          0             1
SQL*Net message to client                         6          0             0


db file sequential read 이벤트를 대기한다고 해서 항상 Btree Index 경유를 의미하지 않는다는 것에 주의할 필요가 있겠다.


(참조) 아래는 테스트에 사용한 스크립트다.

--------------------------------------------
-- Test for how lob is cached into memory
--------------------------------------------

spool result.txt
set echo on
-- version
select * from v$version;

-- show sga (buffer cache를 25M 정도로 지정)
show sga

drop table t1 purge;
drop table t2 purge;

create table t1(c1 int, c2 clob)
  lob(c2) store as (cache)
;

create table t2(c1 char(1000));

insert into t2
select level
from dual
connect by level <= 30000
;

create index t2_n1 on t2(c1)
;

insert into t1 values(1, '0123456789');

declare
  src_lob clob;
  tar_lob clob;
begin
  select c2 into tar_lob
  from t1
  where rownum = 1 for update
  ;
   
  for idx in 1 .. 20 loop
     select c2 into src_lob
      from t1
      where rownum = 1 for update;
     dbms_lob.append(tar_lob, src_lob);
  end loop;
 
  commit;

end;
/

select dbms_lob.getlength(c2) from t1
;


-- flush buffer cache with index
explain plan for
select /*+ index(t2) */ *
from t2
where c1 > ' '
;

@plan

alter system flush buffer_cache;

select /*+ index(t2) */ count(c1)
from t2
where c1 > ' '
;

col obj_name format a30

select
  (select object_name
    from all_objects
    where object_id = v.objd) as obj_name,
  v.obj_cnt
from (
  select objd,
       count(*) as obj_cnt
  from v$bh
  group by objd
  order by 2 desc
) v
where rownum <= 5
;


-- read lob
declare
  l_pos  pls_integer := 1;
  l_amount binary_integer := 32767;
  v_buffer varchar2(32767);
  l_clob_len pls_integer;
  src_lob clob;
begin
  select c2 into src_lob from t1 where rownum = 1;
 
  l_clob_len := dbms_lob.getlength(src_lob);
 
  while l_pos < l_clob_len loop
    dbms_lob.read(src_lob, l_amount, l_pos, v_buffer);
    l_pos := l_pos + l_amount;
  end loop;
end;
/


select
  (select object_name
    from all_objects
    where object_id = v.objd) as obj_name,
  v.obj_cnt
from (
  select objd,
       count(*) as obj_cnt
  from v$bh
  group by objd
  order by 2 desc
) v
where rownum <= 5
;

exec print_table('select * from dba_lobs where segment_name = ''&lob_seg''');
-- or
--set long 100000000    -- if you can wait
--select * from t1;

spool off
ed result.txt



신고
Trackback 0 : Comments 3
  1. oracler 2008.06.01 18:53 신고 Modify/Delete Reply

    선생님의 OWI 책 344 페이지에 보면..

    CACHE 옵션을 사용하여 LOB 컬럼을 생성하면
    버퍼캐시를 경유하게되므로 "일반 데이타와 동일한 메커니즘"을 따르게 된다.
    ..중략..
    만일 SGA 의 크기가 여유가 없거나 LOB 데이타의 크기가 크다면
    NOCACHE 를 사용하는것이 유리하다.
    LOB 데이타가 크지 않고, 자주 액세스되는 범위가 정해져 있다면
    CACHE 옵션을 사용함으로써 성능개선 효과를 얻을수 있다.

    라고 나와 있네요.
    CACHE 옵션 사용시 일반 데이타와 동일한 메커니즘을 따른다는 부분이 핵심입니다.

  2. lee 2008.08.12 19:19 신고 Modify/Delete Reply

    in-line lob의 경우 buffer cache를 경유하며 이 때는 cache 옵션을 사용한 것 처럼 작동한다.
    그러나 실제 nocache로 선언한 경우 nologging을 같이 선언 할 수 있는데 cache 옵션을 명시적으로 사용하면무조건 logging 이다.

    그렇다면 위와 같이 in-line lob를 nocache로 선언하고 nologging으로 사용한다면 redo가 사용 될것인가?

  3. 욱짜 2008.08.13 11:34 신고 Modify/Delete Reply

    http://ukja.tistory.com/157 참조하세요.

Write a comment

티스토리 툴바