▣ 예제 126. 저장 공간 절약을 위해서 테이블 압축을 할 수 있어야 해요
테이블을 압축하는 이유는 ? 저장공간을 절약하기 위해서 입니다.
윈도우에서 압축 프로그램으로 압축을 하는것은 파일의 압축을 풀어야 내용을 볼 수 있는데 오라클의 압축 테이블은 압축을 풀지 않아도 select 할 수 있습니다. 대신 insert 작업이 느려집니다. 그래서 주로 쿼리 위주의 변경이 필요없는
히스토리성 데이터들을 담은 테이블을 압축합니다.
테이블에 insert 를 하면 블럭의 pct free 영역 아래에 데이터가 입력 되는데 입력되는 데이터가 pct free 에 도달하면 data 를 압축합니다.
계속 저장공간을 확보합니다.
-
오라클 압축 방법 2가지 ?
-
basic table compression
insert 시 테이블 압축이 시작되는데 HWM 위에 입력하는 Iinsert 문에 대해서만
압축이 수행됩니다.insert into emp01(empno, ename, sal)
values(1111,'aaa', 3000); -------> 압축 xinsert /+ append / into emp01 -------> 압축 o
select *
from emp; -
oltp table compression
insert 시 테이블 압축이 되는데 아래의 두개의 insert 문장 전부 압축해주는 기술입니다.
-
1) HWM 아래에 입력하는 insert
insert into emp01(empno, ename, sal) ------> 압축 o
values(1111,'aaa',3000);
2) HWM 위에 입력하는 insert
insert /*+ append */ into emp01
select * from emp;■ 압축의 원리
scott analyst 3000 20 5000 10 dallas
allen analyst 3000 20 5000 10 dallas
ford analyst 3000 20 5000 10 dallas
king analyst 3000 20 5000 10 dallas
jones analyst 3000 20 5000 10 dallas
↓ 압축
analyst 3000 20 5000 10 dallas 를 . (점) 하나로 표시
scott . allen . ford . king . jones . 장점 : 공간이 확보 된다
단점 : insert 속도가 느려진다.
■ 압축 테이블 실습
@demo.sql-
압축되지 않는 테이블 생성
create table normal
as
select * from emp; -
압축된 테이블을 생성한다 (basic)
create table basic
compress
as
select * from emp; -
압축된 테이블을 생성한다 (oltp)
create table oltp
compress for all operations
as
select * from emp; -
위의 3개의 테이블에 아래의 insert 작업을 시도한다. (10번 수행)
insert /+ append / into normal select * from normal;
commit;
insert /+ append / into basic select * from basic;
commit;
insert /+ append / into oltp select * from oltp;
commit;
-
3개의 테이블의 건수를 확인한다.
select count() from normal;
select count() from basic;
select count(*) from oltp;
- 압축이 잘되어서 공간절약이 되고있는지 비교하시오 !
select count(distinct dbms_rowid.rowid_block_number(rowid) ) as blocks
from normal;
select count(distinct dbms_rowid.rowid_block_number(rowid) ) as blocks
from basic;
select count(distinct dbms_rowid.rowid_block_number(rowid) ) as blocks
from oltp;
일반 테이블에 비해 공간이 절약되고 있다.
문제1. 이번에는 dept 테이블에 대해서 아래와 같이 테이블을 생성하고 압축 테이블이 더 공간을 적게 사용하는지 확인하시오
create table dept_normal
as select * from dept;
create table dept_basic
compress
as select * from dept;insert /*+ append */ into dept_normal select * from dept_normal;
commit;
insert /*+ append */ into dept_basic select * from dept_basic;
commit;
select count(*) from dept_normal;
select count(*) from dept_basic;
select count(distinct dbms_rowid.rowid_block_number(rowid) ) as blocks
from dept_normal;
select count(distinct dbms_rowid.rowid_block_number(rowid) ) as blocks
from dept_basic;
※ 12c 버젼에서 압축 테이블 관련한 새로운 기능(뉴피쳐) 는 ?
create table oltp
row store compress advanced
as
select * from emp; 설명: row store compress advanced 로 문법이 바뀌면서 oltp 성 테이블에 조금 더
적당하게 DML 작업 속도를 더 원활하게 하면서 압축을 하는 기능이 강화되었습니다.
압축할 때 발생하는 오버헤드를 최소화 했습니다. (OCP 시험문제)
▣ 예제127. deferred 세그먼트 라는 용어를 알고 있어야 해요
테이블 생성 시 deferred 세그먼트로 생성할 수 있습니다.
-
segment creation immediate:
테이블을 생성하자 마자 바로 세그 먼트를 할당
-
segment creation deferred :
테이블을 생성하자 마자 바로 세그먼트를 할당 하지 않고 insert 할 때 할당수백개의 테이블을 갑자기 동시에 생성해야하는 작업을 할 때 에러없이
수월하게 수행되게하는 기술입니다.
■ 실습
#1. segment creation immediate 옵션으로 테이블 생성
create table emp700
( empno number(10),
ename varchar2(500) )
segment creation immediate tablespace users;
#2. 공간이 할당되었는지 확인
select segment_name
from user_segments
where segment_name='EMP700';
세그먼트가 생성되어서 바로 보입니다.
#3. segment creation deferred 옵션으로 테이블 생성
create table emp800
( empno number(10),
ename varchar2(500) )
segment creation deferred tablespace users;
#4. 공간이 할당 되었는지 확인
select segment_name
from user_segments
where segment_name='EMP800';
문제1. dept 테이블과 똑같은 dept800 테이블을 생성하는데 테이블을 생성 할 때에 바로 segment 가 할당되지 않게 테이블을 생성하시오
create table dept800
segment creation deferred tablespace users
as
select * from dept;
select segment_name
from user_segments
where segment_name='DEPT800';
문제2. emp 테이블에 sal 에 인덱스를 생성하시오
create index emp_sal on emp(sal);
문제3. emp 테이블에 sal 인덱스를 생성할 때 segment 가 즉시 만들어지게 하시오 !
drop index emp_sal;
create index emp_sal
on emp(sal)
segment creation immediate;
segment creation 옵션은 테이블 생성 시에 사용 가능하고
인덱스 생성 시에는 쓸 수 없다.
정리하면 오라클의 저장공간을 효율적으로 사용하기 위한 방법 3가지
- db reorg 작업
- 테이블 압축
- segment creation deferred
▣ 예제128. ASM 이 무엇인지 알아야 합니다.
ASM 이란 ?
Automatic Storage Management 의 약자로
오라클이 알아서 자동으로 스토리지를 관리하기 위해서 만든 소프트 웨어
☞ 스토리지 3가지?
-
raw device -----> 현업에서 가장 많이 사용하는 스토리지 (튼튼함) 장애가 덜 발생한다.
-
file system -----> 관리가 편합니다. raw device 보다는 장애가 더 발생한다.
rm 으로 파일 지우기가 편해서 장애가 자주 발생한다. -
ASM -------> 오라클이 다 알아서 자동화를 하기 때문에 편한듯 하나 공부해야할 것이 많다.
☞ ASM 을 꼭 써야 하는가 ?
오라클이 계속 ASM 을 사용하도록 유도를 하고 있는데 오라클 설치 할 때 선택할 수 있는 스토리지 옵션이 2개 밖에 없다.
ASM 또는 file system이다. raw device 에 db 를 구성하기 위해서 create database 명령어로 db 를 생성한다.
오라클 exa data 시스템이 ASM 기반으로 되어있다.
오라클에서 대용량 데이터 검색을 빠르게 하기 위해서 만든 소프트웨어+하드웨어의 집합이다.
ASM을 사용했을 때의 장점 ?
RAID 0 + 1 기술을 오라클이 알아서 구현
디스크 볼륨 구성 기술 : RAID0 + RAID 1
스트라이핑 + 미러링 으로 구성됨.
스트라이핑 후 스트라이핑 된 디스크 중 하나가 깨지면 select 가 안되기 때문에 미러링이 존재하는 것.
스트라이핑 + 미러링 으로 구성되어 비용이 많이 든다.
중요도가 낮은 서버의 경우는 RAID 5 로 구성합니다.
혹시라도 디스크가 깨지면 다른쪽 디스크를 사용하면 되니까
철저하게 백업을 한다
적은 디스크로 RAID0 + RAID 1 효과를 보는 것이 RAID 5 이다.
비용은 적게 드나 성능이 느립니다.
튜닝을 하러 고객사에 갔는데 sql 튜닝을 아무리해도 성능의 개선이 안보이면 고객에게 물어봐야 합니다.
디스크 볼륨구성을 RAID0 + RAID 1 로 했는지 RAID 5 로 했는지를 물어봐야 한다.
그런데 ASM 은 오라클이 알아서 RAID 0+1 기술을 구현한다.
그냥 디스크만 서버에 추가하고 ASM 디스크 그룹에서 추가만 해주면 된다.
사실상 TA 엔지니어가 필요 없을 정도의 기술이다.
ASM을 사용하면 좋은 기능이 디스크 공간이 부족해서 디스크를 추가해야하면 서버와 DB 를 내리지 않고 바로 할 수 있다.
■ 실습
#1. asm 디스크가 몇개가 있는지 확인한다
col path for a20
select group_number, mount_status, path, total_mb
from v$asm_disk;
#2. asm 디스크 그룹이 무엇이 있는지 확인합니다.
select name, total_mb, free_mb
from v$asm_diskgroup;
create tablespace ts300 datafile '+FRA' size 3m;
문제3. 지금 만든 ts300 테이블 스페이스의 데이터 파일의 위치를 확인하고 거기로 가서 데이터 파일을 rm 으로 지워보시오
ASMCMD> cd +DATA
ASMCMD> pwd
+DATA
ASMCMD> ls
ASM/
ORCL/
현재 db 가 운영중일 때는 rm 으로 datafile 을 지울수 가 없습니다.
그런데 asm 이 아닌 파일 시스템에 있는 data file 은 db 운영과 관계없이 지워집니다.
asm 으로 디스크를 관리한다고 한다면 위와 같이 asmcmd 명령어로 접속해야
data file을 볼 수 있습니다.
※ 정리를 하면? 1. ASM 으로 운영하면 오라클이 알아서
문제4. 테이블 스페이스를 drop 하면 ASM 에 있는 관련된 datafile 이 같이 삭제되는지 확인하시오
drop tablespace ts200;
설명 including contents and datafiles 를 안 썼는데 ASM 에 있는 관련된 datafile 이 같이 삭제 된다.
#1.
#2. 현재 disk group 의 공간을 확인 합니다.
문제 6. 그럼 남는 디스크를 한개 더 data 디스크 그룹에 추가해서 공간을 확보 하시오
alter diskgroup data
add disk 'ORCL:ASMDISK12' rebalance power 2;
select name, total_mb, free_mb
from v$asm_diskgroup;
NAME TOTAL_MB FREE_MB
DATA 13824 9562
FRA 9216 8961
2개의 프로세서가 rebalance 를 합니다.
rebalance 는 새로 추가한 디스크에 기존 데이터를 분배하는 것입니다.
▣ 예제 129. ASM 인스턴스의 역할
ASM 인스턴스의 역할은 ASM 영역을 관리하기 위한 백그라운드 프로세서와 메모리가 있는 영역으로 이 인스턴스가 있어야 raid 0+1 의 효과도 누릴 수 있고 asm 의 장점을 누릴 수 있습니다.
문제1 ASM 인스턴스가 떠 있는지를 확인하시오 !
[+ASM:~]$ ps -ef | grep pmon
oracle 5278 1 0 13:46 ? 00:00:00 ora_pmon_jhs
oracle 5558 1 0 13:46 ? 00:00:00 asm_pmon_+ASM
oracle 6236 1 0 14:03 ? 00:00:00 ora_pmon_orcl
oracle 6599 5596 0 14:27 pts/1 00:00:00 grep pmon
문제2. 디스크에 데이터 분배를 시도하는 rbal 프로세서가 몇개가 떠 있는지 확인하시오 !
[+ASM:~]$ ps -ef | grep rbal
oracle 5582 1 0 13:46 ? 00:00:00 asm_rbal_+ASM
oracle 6264 1 0 14:03 ? 00:00:00 ora_rbal_orcl
oracle 6616 5596 0 14:29 pts/1 00:00:00 grep rbal
rbal 는 rebalancing 을 주도하는 총 사령관이고 실제로 일을 하는 부하 데몬 프로세서들이
ARB 로 시작하는 프로세서들이다.
문제3. rbal 프로세서의 개수를 2개로 늘리시오
늘리게 되면 asm 디스크 그룹에 디스크를 추가할 때 속도가 빨라집니다.
답
[+ASM:~]$ sqlplus / as sysasm
SQL> @i
#1. asm 인스턴스에서 asm_power_limit 파라미터의 개수를 확인합니다.
show parameter asm_power_limit
NAME TYPE VALUE
asm_power_limit integer 1#2. asm_power_limit 파라미터의 값을 2로 세팅합니다.
alter system set asm_power_limit=2 scope=both;
#3. db 인스턴스를 내린다 (orcl 쪽 터미널에서 한다)
orcl(SYS) > @i
INSTANCE_NAME STATUS
---------------- ------------
orcl OPENorcl(SYS) > shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.#4. asm 인스턴스를 내린다
SQL> shutdown immediate
ASM diskgroups dismounted
ASM instance shutdown#5. asm 인스턴스를 올린다.
SQL> startup
ASM instance started
Total System Global Area 284565504 bytes
Fixed Size 1336036 bytes
Variable Size 258063644 bytes
ASM Cache 25165824 bytes
ASM diskgroups mounted#6. db 인스턴스를 올린다.
orcl(SYS) > startup
ORACLE instance started.
Total System Global Area 523108352 bytes
Fixed Size 1337632 bytes
Variable Size 398460640 bytes
Database Buffers 117440512 bytes
Redo Buffers 5869568 bytes
Database mounted.
Database opened#7. asm 인스턴스에서 asm_power_limit 이 2로 변경되었는지 확인
SQL> @i
INSTANCE_NAME STATUS
+ASM STARTEDSQL> show parameter asm_power_limit
NAME TYPE VALUE
asm_power_limit integer 2#8. os 에서 rbal 프로세서가 몇개인지 확인하시오 !
$ ps -ef | grep rbal
[+ASM:~]$ ps -ef |grep rbal
oracle 7438 1 0 14:58 ? 00:00:00 asm_rbal_+ASM
oracle 7551 1 0 14:58 ? 00:00:00 ora_rbal_orcl
oracle 7673 5596 0 15:02 pts/1 00:00:00 grep rbal기본은 1개인데 rebalancing 이 실제로 실행되면 2개로 활성화 됩니다.
문제1. asm_power_limit 을 10으로 늘리시오 !
alter system set asm_power_limit=10 scope=both;
db asm asm db 내렸다가 올리기
SQL> @i
SQL> show parameter asm_power_limit;
