Oracle 52일차

[Oracle block]

DB_BLOCK_SIZE 파라미터로 사이즈 조절 가능. 동적 변경은 불가.
사용자가 입력한 데이터를 하드디스크에 저장하거나 읽어들일 때 1건씩 처리하는 구조가 아닌 block단위로 작업한다.
block size가 크면 데이터가 작은 경우 공간이 낭비될 수 있고, wait time이 많이 생길 수 있다.
block size가 작으면 한 번에 담을 수 있는 데이터 양이 작아져 disk I/O가 많아진다.
OLTP면 block size를 8k, DW면 16k정도를 쓴다.

** 상세 구조
block header : 일반적인 blcok의 정보(위치, segment 형태)를 가짐.
table/row directory : 클러스터에 있는 테이블에 관한 정보 / block내의 row관련 정보.
free space : new row insert나 update 시 사용. PCTFREE, PCTUSED에 의해 결정.
row data : 실제 테이블 데이터와 인덱스 데이터가 저장됨.

** PCTUSED : 이걸로 지정된 값을 넘는 free space가 생기면 해당 block을 빈 공간으로 인식하고 block에 insert를 한다. PCTUSED+PCTFREE<=100이어야 한다.

** 만약 block에 delete가 돼서 빈 공간이 생겨도 11g이상 oracle부터는 빈 공간을 빈 공간으로 인식하지 않는다.(=PCTUSED가 없다.) 왜냐면 빈 공간으로 인식하면 그 공간에 데이터를 써야되는데 그러면 여기저기 왔다갔다 하면서 써야되고 header같은 것도 다시 써줘야 해서 시간이 오래 걸린다.

** Row Chaining : block size보다 큰 값이 insert 되면 2개 block이 묶이게 된다. 만약 이러면 I/O과정에서 2개의 block을 올렸다내렸다 해야돼서 안 좋다. 해결방법은 block size 크게 만드는 것.

** Row Migration : 기존에 데이터가 있던 block에 free space보다 큰 데이터를 insert를 할 때 발생. 여유 공간이 없으니 다른 block으로 이사를 가는 느낌. PCTFREE가 작다면 migration이 자주 발생. 늘리면 해결됨. 또는 reorg 작업을 해서 해결.

** reorg : 빈 공간을 반환해서 조회 성능을 높여주고 저장공간을 효율적으로 사용하도록 만든다. 이 작업을 할 때는 주말출근해서 작업해야된다.

[실습 – block 수 확인]

어제(51일차) 마지막에 한 [extent 할당 과정] 에 이어서 실습.

6. 테이블 정보 조회

select TABLE_NAME,
       TABLESPACE_NAME,
       PCT_FREE,                    -- default:10
       PCT_USED,                   -- ASMM방식에선 사용X
       INITIAL_EXTENT/1024,    -- 초기할당 extent size
       NEXT_EXTENT/1024,      -- 다음할당 extent size
       PCT_INCREASE             -- 다음할당 extent size 증가값 (default 0)
from dba_tables
where table_name='EXTENT_TEST1';

7. delete 수행 후 통계정보 수집 후 block수 확인

delete from scott.extent_test1;
commit;

-- 통계 정보 수집
analyze table scott.extent_test1 compute statistics;

-- block 수 확인
select TABLE_NAME, 
       NUM_ROWS,
       BLOCKS,
       EMPTY_BLOCKS                
  from dba_tables
 where table_name = 'EXTENT_TEST1';

** delete 후에도 저장된 블럭 수는 이전과 동일. 저장공간을 반환하지 않았기 때문

8. truncate 후 통계정보 수집 후 block수 확인

truncate table scott.extent_test1;

-- 통계 정보 수집
analyze table scott.extent_test1 compute statistics;

-- block 수 확인
select TABLE_NAME, 
       NUM_ROWS,
       BLOCKS,
       EMPTY_BLOCKS                
  from dba_tables
 where table_name = 'EXTENT_TEST1';

** 저장된 block수 0으로 바뀜.
empty_blocks는 8임. -> 테이블 초기 extent=block 8개는 무조건 할당됨. 그래서 DEFERRED_SEGMENT_CREATION 을 끄면 create table하자마자 초기 extent(8block)이 생성되는 것.
초기 extent : 표준 테이블 header block, 메타데이터 저장용.

[reorg]

테이블 내 빈 블럭을 정리하고 저장된 행을 재배치하는 기법.
테이블 내 데이터가 추가, 삭제, 갱신되면서 공간이 흩어져 저장되고 중간에 비어있는 공간이 발생.
PCTUSED를 사용하지 않으면 delete로 인해 공간이 비어있어도 해당 공간을 재활용하지 않고 그대로 테이블 내 dirty block으로 가지고 있음.
비어 있는 공간이 많은 경우 실제 저장된 데이터에 비해 많은 블럭을 조회하면서 성능 문제가 발생함.

** reorg 방법
1) tablespace 재배치(동일 tablespace도 가능)
> alter table 테이블명 move tablespace 테이블스페이스명;

2) index rebuild
> alter index 인덱스명 rebuild;

** reorg 대상 확인
테이블 내 저장된 실블럭수와 할당된 블럭 수의 차이가 심한 경우 reorg를 진행.
delete, update가 자주 발생하는 테이블의 경우 주기적으로 reorg를 진행.

1. 테이블 할당된 블럭 수 확인

 select TABLE_NAME, 
       NUM_ROWS,
       BLOCKS,
       EMPTY_BLOCKS                
  from dba_tables
 where table_name = 'EXTENT_TEST1';

2. 실 블럭수 확인(실제 데이터가 차지하고 있는 블럭수)

 SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) ||
       DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "실사용 블록수"
  FROM scott.extent_test1;

[실습 – reorg 전후 할당된 블럭수 비교]

1. 테이블 생성

create table scott.extent_test2(no number, name varchar2(20), addr varchar2(20));

2. 대용량 insert

begin
for i in 1..500000 loop
    insert into scott.extent_test2 values(i, dbms_random.string('a',19), dbms_random.string('q',19));
end loop;
commit;
end;
/

3. 현재 할당된 block수 확인

analyze table scott.extent_test2 compute statistics;

select table_name,
       num_rows,
       blocks,
       empty_blocks
from dba_tables
where table_name='EXTENT_TEST2';

4. delete 후 할당된 블럭수 확인

delete from scott.extent_test2;
commit;

-- 통계 정보 수집
analyze table scott.extent_test2 compute statistics;

-- block 수 확인
select TABLE_NAME, 
       NUM_ROWS,
       BLOCKS,
       EMPTY_BLOCKS                
  from dba_tables
 where table_name = 'EXTENT_TEST2';

-- 조회
select * from scott.extent_test2;
-- 데이터는 0건이더라도 총 3520개 블럭을 불러오며 조회함. = 성능저하 발생

5. 실제 블럭수 확인

 SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(rowid) ||
       DBMS_ROWID.ROWID_RELATIVE_FNO(rowid)) "실사용 블록수"
  FROM scott.extent_test2;
-- 이걸 쓰면 0블럭 나오는데 위에 4번에서 block수 확인 쿼리 쓰면 3520블럭 나옴.
-- 실제 사용 블럭 수와 해당 테이블에 할당된 블럭 수의 차이가 많이 나는 경우 reorg 진행 필요.

6. reorg 진행

alter table scott.extent_test2 move tablespace users;
또는
alter table scott.extent_test2 move;
-- tbs생략 시 기존 tbs에서 reorg 진행됨.
-- 정석적인 reorg는 이게 맞고. truncate를 해도 reorg와 같은 효과가 나오긴 한다.

7. 실 블럭수 및 할당 블럭수 확인

-- 통계 정보 수집
analyze table scott.extent_test2 compute statistics;

-- block 수 확인
select TABLE_NAME, 
       NUM_ROWS,
       BLOCKS,
       EMPTY_BLOCKS                
  from dba_tables
 where table_name = 'EXTENT_TEST2';
-- blocks가 3520에서 0으로 바뀜.

[extent management]

테이블스페이스 내 테이블 데이터가 입력되면 extent가 할당됨.
이때 extent 관리하는 방법을 정의할 수 있음.

1) DMT(Dictionary Management Tablespace)
– extent 할당 및 관리를 Dictionary에서 관리.
– Dictionary는 system tablespace에 저장.
– 중앙에서 관리하는 것은 용이하지만 속도가 느리다.
– 11g부터 사용 불가.

2) LMT(Locally Management Tablespace)
– extent 할당 및 관리를 각 tablespace에서 각각 관리.
– extent 현황을 각 tbs header에 저장.
– 8i 이후로는 LMT가 default.
– extent할당 방식에 따라 uniform, autoallocate 2가지로 나뉨.

** uniform
– 추가 할당 extent size를 일정하게 유지.

** autoallocate
– insert 양에 따라 추가 할당 extent size를 자동으로 조절.

[실습]

1. tablespace 내 extent 관리 방식 조회

select TABLESPACE_NAME,
       BLOCK_SIZE,                -- tbs별로 block size 지정 가능(default db_block_size와 동일)
       INITIAL_EXTENT,
       NEXT_EXTENT,
       PCT_INCREASE,
       EXTENT_MANAGEMENT,  -- DMT vs LMT
       ALLOCATION_TYPE         -- UNIFORM vs SYSTEM(AUTOALLOCATE)
from dba_tablespaces;

2. tablespace 생성

1) DMT(12c 생성 불가)
create tablespace tbs_test1
       datafile '/home/oracle/oradata/db1/tbs_test01.dbf' size 10m
       extent management dictionary;
-- ORA-12913 : Cannot create dictionary managed tablespace

2) LMT
2-1) uniform
create tablespace tbs_test1
       datafile '/home/oracle/oradata/db1/tbs_test01.dbf' size 50m
       extent management local
       uniform size 10m;

2-2) system(autoallocate)
create tablespace tbs_test2
       datafile '/home/oracle/oradata/db1/tbs_test02.dbf' size 50m
       extent management local
       autoallocate;

-- 조회
select TABLESPACE_NAME,
       BLOCK_SIZE,
       INITIAL_EXTENT,
       NEXT_EXTENT,
       PCT_INCREASE,
       EXTENT_MANAGEMENT,
       ALLOCATION_TYPE
from dba_tablespaces
where tablespace_name like 'TBS_TEST%';

3. table 생성

create table scott.lmt_test1(no number) tablespace tbs_test1;
create table scott.lmt_test2(no number) tablespace tbs_test2;
insert into scott.lmt_test1 values(1);
insert into scott.lmt_test2 values(1);
commit;

select TABLE_NAME,
       TABLESPACE_NAME,
       INITIAL_EXTENT/1024,  -- 초기 할당 extent size
       NEXT_EXTENT/1024,    -- next 할당 extent size
       PCT_INCREASE
from dba_tables
where table_name like 'LMT_TEST%';
-- 자동관리 방식인 TBS_TEST2 tablespace에 생성된 LMT_TEST2 테이블의 NEXT_EXTENT size는 1m로 확인됨.

4. 통계정보 수집 및 블럭 현황

-- 통계 정보 수집
analyze table scott.lmt_test1 compute statistics;
analyze table scott.lmt_test2 compute statistics;

-- block 수 확인
select TABLE_NAME, 
       NUM_ROWS,
       BLOCKS,
       EMPTY_BLOCKS                
  from dba_tables
 where table_name like 'LMT_TEST%';

[segment space management]

segment 내 공간 할당 및 관리 방법을 정의하는 기능.
auto, manual로 관리.
tablespace별로 설정 가능.
dba_tablespaces 에서 조회 가능.
pct_free, pct_used, pctincrease 등을 설정.

1. manual segment space management (MSSM) : segment 내 저장 공간을 수동으로 관리하는 방식.
2. auto segment space management (ASSM) : segment 내 저장 공간을 자동으로 관리하는 방식. pctused 사용X.

** segment 내 space 관리 정책
1) pct_free
– 블럭 내 미리 할당하는 여유 공간
– update만을 위해 남겨두는 공간
– update 이후 row size가 커지는 경우 블럭 내 저장공간이 부족할 경우 row migration 발생(성능 저하 발생)
– update가 빈번한 테이블의 경우 pct_free 여유있게 설정
– pct_free가 큰 경우 보다 적은 수의 row가 저장
– default 10

2) pct_used
– 해당 블럭을 다시 재사용 블럭으로 인식하기 위한 임계값
– pct_used가 50%인 경우 전체 블럭의 50%미만으로 사용율이 떨어지면 freelist에 등록, 재사용 가능
– ASSM일 경우 사용X

3) pctincrease
– next extent의 사이즈의 증가 비율

[실습]

1. 조회

select tablespace_name,
       segment_space_management
from dba_tablespaces;

2. tablespace 생성

-- 2-1) auto
create tablespace TBS_TEST3
       datafile '/home/oracle/oradata/db1/tbs_test03.dbf' size 10m
       segment space management auto;


-- 2-2) manual
create tablespace TBS_TEST4
       datafile '/home/oracle/oradata/db1/tbs_test04.dbf' size 10m
       default storage(initial 1m
                       next 1m
                       pctincrease 0);

create tablespace TBS_TEST5
       datafile '/home/oracle/oradata/db1/tbs_test05.dbf' size 10m
       segment space management manual
       default storage(initial 1m
                       next 1m
                       pctincrease 0);

-- 조회
select TABLESPACE_NAME,
       SEGMENT_SPACE_MANAGEMENT,
       INITIAL_EXTENT,
       NEXT_EXTENT,
       PCT_INCREASE
from dba_tablespaces
where tablespace_name like 'TBS_TEST%';

3. table 생성

create table SCOTT.assm_test1(no number) tablespace TBS_TEST3;
create table SCOTT.assm_test2(no number) tablespace TBS_TEST5;
create table SCOTT.assm_test3(no number)
    tablespace TBS_TEST4
    pctfree 20
    pctused 20
    storage(initial 2m
            next 2m
            pctincrease 50);

insert into scott.assm_test1 values(10);
insert into scott.assm_test2 values(10);
insert into scott.assm_test3 values(10);
commit;

select TABLE_NAME,
       TABLESPACE_NAME,
       PCT_FREE,
       PCT_USED,
       INITIAL_EXTENT,
       NEXT_EXTENT,
       PCT_INCREASE
from dba_tables
where table_name like 'ASSM_TEST%';

--> ASSM_TEST1 테이블의 경우 테이블 생성 시 별도 storage 정책을 명시하지 않았기 때문에 tablespace 관리 정책(AUTO)을 따름. TBS_TEST3 테이블스페이스 생성 시 별도의 storage 정책이 없었으므로 default 관리 정책에 따라 테이블이 생성됨.
--> ASSM_TEST2 테이블의 경우 TBS_TEST5 테이블스페이스에 저장되어 있는데 이 테이블스페이스는 수동관리 방식이므로 테이블스페이스 생성 시 명시한 storage 정책대로 테이블이 관리되면 PCT_USED 지정이 가능.
--> ASSM_TEST3 테이블의 경우 TBS_TEST4 테이블스페이스에 저장되어 있는데 별도의 storage 옵션을 갖는 ASSM 관리 방식이다. 하지만 테이블 생성 시 별도의 storage 옵션을 사용하였기 때문에 몇몇 옵션(PCT_USED, PCT_INCREASE 등)을 제외하고는 테이블 자체적인 storage 옵션이 지정되어 관리된다.

[실습 – pct_free 크기에 따른 저장공간 차이]

pct_test1 table : pct_free 10%(default)
pct_test2 table : pct_free 50% 로 생성 후
동일한 데이터를 대량 insert 시 최종적으로 할당된 블럭수를 비교

1. table 생성 및 데이터 삽입

create table scott.pct_test1(no number, name varchar2(10));
create table scott.pct_test2(no number, name varchar2(10)) pctfree 50;

begin
for i in 1..500000 loop
    insert into scott.pct_test1
    values(i, DBMS_RANDOM.string('A',4));
end loop;
commit;
end;
/

begin
for i in 1..500000 loop
    insert into scott.pct_test2
    values(i, DBMS_RANDOM.string('A',4));
end loop;
commit;
end;
/

select table_name, pct_free, pct_used
from dba_tables
where table_name like 'PCT_TEST%';

2. 두 테이블에 저장된 블럭 수 비교

-- 통계 정보 수집
analyze table scott.pct_test1 compute statistics;
analyze table scott.pct_test2 compute statistics;

-- block 수 확인
select TABLE_NAME, 
       NUM_ROWS,
       BLOCKS,
       EMPTY_BLOCKS                
  from dba_tables
 where table_name like 'PCT_TEST%';
-- PCT_TEST1 : 1126 + 26 블럭
-- PCT_TEST2 : 1882 + 38 블럭

pct_test1의 경우 free가 작아서 한 블럭에 더 많은 데이터를 담을 수 있어서 블럭 수가 더 적다.

[실습 – pct_free 크기에 따른 row migration 차이]

1. 전체 update 수행

update scott.pct_test1
set name = 'AAAAAAAAAA';
commit;

update scott.pct_test2
set name = 'AAAAAAAAAA';
commit;

2. 블럭 수 비교

-- 통계 정보 수집
analyze table scott.pct_test1 compute statistics;
analyze table scott.pct_test2 compute statistics;

-- block 수 확인
select TABLE_NAME, 
       NUM_ROWS,
       BLOCKS,
       EMPTY_BLOCKS                
  from dba_tables
 where table_name like 'PCT_TEST%';
-- PCT_TEST1 : 2638 + 50 블럭
-- PCT_TEST2 : 1882 + 38 블럭

pct_test1의 경우 free가 작아서 update 이후 많은 수의 블럭이 추가됨. -> row migration 발생.
pct_test2의 경우 free가 커서 update 이후 블럭수 변화 없음. -> 기존 블럭의 free에서 처리 다 함.

[row chain]

block size에 비해 큰 row가 입력되는 경우 인근에 있는 둘 이상의 블럭에 저장되는 상태

[실습]

1. table 생성

create table scott.row_chain1(a varchar2(4000), b varchar2(4000), c varchar2(4000));

2. insert 수행

insert into scott.row_chain1 values(DBMS_RANDOM.STRING('A',4000),DBMS_RANDOM.STRING('A',4000),DBMS_RANDOM.STRING('A',4000));
insert into scott.row_chain1 values(lpad('A',4000,'A'),lpad('B',4000,'B'),lpad('C',4000,'C'));
commit;

3. 블럭 수 확인

analyze table scott.row_chain1 compute statistics;  

select TABLE_NAME, 
       NUM_ROWS,
       BLOCKS,
       EMPTY_BLOCKS,
       chain_cnt           -- row chain 횟수
  from dba_tables
 where table_name like 'ROW_CHAIN%';    

[deferred segment creation]

– table 생성 시 segment를 즉시 할당할 지 여부를 결정하는 파라미터
– deferred_segment_creation
– 11g 부터 true default

select name, value
from v$parameter
where name like %deferred%';

[case1. users tbs에 새로 table 생성 후 seq 적용]

아무리해도 안된다 ㅋㅋㅋ

-- sequence 생성
create sequence sequence_test1;

-- table 생성
create table scott.deferred_test1
(no number default sequence_test1.nextval,
name varchar2(10));

-- insert 후 조회
insert into scott.deferred_test1 (name) values ('a');
commit;

insert into scott.deferred_test1 values(sequence_test1.nextval, 'b');
commit;

select * from scott.deferred_test1;


create sequence seq_test1;
create table scott.deferred_test11 (no number);
insert into scott.deferred_test11 values(seq_test1.nextval);
select * from scott.deferred_test11;

-- scott으로 실행
create sequence seq_test111;
create table deferred_test111 (no number);
insert into deferred_test111 values(seq_test111.nextval);
commit;
select * from scott.deferred_test111;

[case2. users tbs에 기존 table truncate 후 seq 적용]

[case3. system tbs에 새로 table 생성 후 seq 적용]