Block( 데이터를 담는 최소단위 ) --> Extent --> Segment --> Tablespace --> Database
Segment와 Object의 차이
Object : 생성하는 모든 것(view, constraint, table, index, etc)
Segment : Object중에서 저장공간을 가지는 것(table, index, undo, temp)
*그래서 view는 object입니다.
Extent를 사용하는 이유
Tablespace을 만들고 해당 Datafile을 지정하고 Table안에 자료들을 insert하다보면 Disk(datafile)에 자료들이 차례대로 들어가지 않고,
분산되어서 저장됩니다. 이렇게 분산이 되어서 저장된다면, 해당 자료들을 select하면 아무래도 I/O가 많이 발생할 수 밖에 없게되고
곧 속도저하로 이어집니다.
그래서 자료들이 차례대로 딱딱 들어가게 하기 위해서 Extent 를 도입하게 되었습니다.
(통지서를 발부해야되는데
A : 대구, B : 서울, C : 땅끝마을 에 살면 통지서를 발부하는 시간은 엄청나게 오래 걸리는데 반해
A : 서초1동, B : 서초2동, C : 서초3동 에 살면 빨리 발부할 수 있습니다.)
Oracle의 Fragmentation ( OS에서와 유사 )
Datafile의 빈 공간이 4MB 남았는데, 3MB되는 자료를 넣었을때 자료가 입력이 안되는 경우가 있습니다.
이유는 datafile안의 Extent들이 따닥따닥 붙어서 모인 공간이 3MB가 안되기 떄문입니다.
(총 빈공간은 4M인데, 군데군데 뻥뻥비어서 3M를 쪼개지 않고는 넣을 수 없는경우)
이럴 경우 3MB되는 자료를 넣어주기 위해서는 조각모음(Defragmentation)을 해야합니다.
*주의사항 : oracle자체의 조각모음을 실행하지 않고, 운영체제(windows, Linux는 없음)의 조각모음을 실행하게되면
OS의 Block size와 DATABASE의 Block size(DB_BLOCK_SIZE)가 다르므로, DB가 완전히 손상되어버립니다.
Oracle의 Database Block
OS block하나 또는 하나이상으로 구성됩니다.
(OS block size ≠ Oracle block size, Oracle block size ≥ OS block size)
Database Block 의 공간할당을 세부화하는 파라미터들
INITRANS
MAXTRANS
PCTFREE
PCTUSED
*PCT : percentage
** 위 파라미터들 설명하기 전 알아야 할 것들
ITL(Interested Transaction List) : 해당 block에 Transaction을 일으킬 때 대기자들을 적어놓는 공간
설명 : A, B 사용자가 동시에 update할 때, 바꾸려는 값들이 같은 block내에 있으면 동시에 update할 수 없습니다
(동시에 하는것처럼 느껴질 수도 있지만, 하나 처리하고, 다음꺼 처리합니다.)
이렇게 동시에 쿼리가 들어왔을때, ITL이라는 대기자명단을 두어서 여러작업이 동시에 들어오면, 각 작업마다 순위를 붙혀줍니다.
이 ITL과 관련된 파라미터들이 INITRANS, MAXTRANS입니다.
Row Migration : 어떤 값을 update할 때, 해당 블럭에 빈 공간이 없으면, 해당 행 전체는 다른 블럭으로 이사를 가버리고, 원래 있던 공간에 이사간 블럭의 주소만 적어둡니다. 이와같이 블럭이 이사간 것을 보고 row migration이라고 합니다.
* 문제점 : 속도저하(2개의 블럭을 읽어야함), 작지만 공간낭비(원래 있던 공간에 주소포인터만 남겨두므로 공간낭비)
이럴때 사용하는 파라미터가 PCTFREE입니다.
Block의 Free list, Dirty list
블럭들은 딕셔너리에 어떠한 상태인지 모두 기록됩니다.
그 중 oracle에 의해 '쓸 수 있는 공간이 있다' 라고 판명된 block은 free list, '쓸 수 있는 공간이 없다' 라고 판명된 block는 dirty list에 쓰여지게 되는데, 이런상황을 가정해봅시다.
한 사용자가 삭제와 삽입을 반복하는데, 그게 마침 꽉찬 블럭에서 삭제, 삽입을 하고 있습니다. 그러면 oracle은 삭제할떄마다 free list, 삽입할때마다 dirty list에 적어줘야 하는데, 이렇게 되면 쓸데없는 부하가 너무 많이 발생을 하게 됩니다.
위 같은 상황을 방지하기 위해 사용하는 파라미터가 PCTUSED입니다.
INITRANS : ITL의 min값 (1)
MAXTRANS : ITL의 max값(255)
PCTFREE : row migration을 방지하기 위해 의도적으로 자료를 채우지 않는 공간의 퍼센티지
PCTUSED : 100-PCTFREE % 만큼 자료가 채워져서 dirty 가 된 블럭이 다시 free block으로 되려면 PCTUSED%이하로 block이 비워지면 다시 free block이 됩니다. ( PCTUSED = 30이면, dirty block의 꽉 찬 정도가 30%이하로 떨어져야 다시 free list에 기록이 됩니다.)
( http://jiginet.cafe24.com/entry/%EC%98%A4%EB%9D%BC%ED%81%B4-Admin-4?category=2 참조 )
* free list를 통한 free block관리방법 2가지(수동, 자동) ( http://www.oracleclub.com/lecture/1896 )
FLM(수동) : oracle 8i 이전 버전까지는 PCTFREE, PCTUSED 를 DBA가 직접 상황에 맞게 설정하면서 최적화를 시켜줬습니다.
ASSM(자동) : oracle 9i이후 버전(10g이후는 default)부터는 PCTFREE, PCTUSED를 직접 지정하는 것을 권장하지 않고, SEGMENT SPACE MANAGEMENT AUTO 절을 넣어서 자동으로 관리하는 방법을 권장합니다.
(MANUAL이면 FLM방식으로 DBA가 직접 파라미터들을 지정합니다, 10g이후부터는 안쓰면 자동으로 ASSM)
ASSM의 대표적인 장점
** 왜 이런 정보를 사용해야 하느냐?
FLM방식일때에는 아무리 block이 free list에 있다고 하더라도, 얼마나 넣을 수 있는 free block인 줄 알 수가 없었기 때문에 아래와 같은 문제가 발생합니다.
어떤 블럭이 2행 들어갈 수 있는 free block이었을때, 어떤 사용자가 insert를 해서 10행을 넣어버리면 2행만 넣고, 또 대기열의 맨 마지막으로 가서 한참 기다리다가 다른 free block을 찾아서 나머지 8행을 넣게 됩니다.
ASSM을 사용하게 되면 block에 BMB라는 정보가 생기게 되어, 대충 통밥으로 요건 요기에 넣으면 되겠구나, 판단이 서게 되어 위와같은 상황이 대폭적으로 줄어들게 되어 속도향상이 있습니다.
Storage 관련 딕셔너리
DBA_EXTENTS
DBA_SEGMENTS
DBA_TABLESPACES
DBA_DATA_FILES
DBA_FREE_SPACE
Undo Segment (=Rollback Segment)
Undo부분 대부분 오라클클럽에서 발췌( http://www.oracleclub.com/lecture/1869 )
Undo Segment 사용목적
1) Transaction Rollback
2) Read consistency
3) Transaction recovery(Instance Recovery)
(용어 http://gyh214.tistory.com/73 참조)
Undo Data관리방법
1) AUM(Automatic Undo Management)
Tablespace 생성하면 끝(9i 부터)
2) MUM(Manual)
Tablespace 생성, Rollback segment 생성, Enable
초기화 파라미터 환경구성
① UNDO_MANAGEMENT
- 테이타베이스의 Undo 모드를 자동 모드로 사용 할지 수동 모드를 사용할지 여부를 결정 합니다.
- AUTO 또는 MANUAL 값 중 하나로 설정할 수 있으며 초기화 파라미터 파일에서 설정해야 합니다.
- 데이터베이스가 운영중에는 UNDO_MANAGEMENT 를 동적으로 변경할 수 없습니다.
- AUTO로 설정하게 되면 데이터베이스는 자동 Undo 관리 모드로 설정되며 undo 테이블스테이스가 필요합니다.
② UNDO_TABLESPACE
- 사용 할 특정 UNDO 테이블스페이스를 지정 합니다.
- 최소한 하나의 UNDO 테이블 스페이스 생성이 필요 합니다.
- 초기화 파일에서 설정하거나 데이터베이스의 운영중에도 ALTER SYSYTEM 명령을 사용하여 동적으로 변경할 수 있습니다.
③ UNDO_RETENTION
- 일관성 읽기를 위해 제공되는 Undo 데이타의 보유 기간을 결정합니다.
- 초기화 파일에서 설정하거나, ALTER SYSTEM 명령을 사용하여 동적으로 수정할 수 있습니다.
- 이 parameter는 초 단위로 지정됩니다. 기본값은 900초이며, 이는 Undo 데이타를 15분 동안 보유합니다.
- UNDO_RETENTION을 설정한 후에도 UNDO 테이블스페이스의 크기가 너무 작으면 지정한 시간 동안 Undo 데이타가 보유되지 않습니다.
- UNDO_RETENTION 파라미터는 현재 Undo 테이블스페이스에 UNDO_RETENTION 기간 동안 발생하는 모든 트랜잭션을 수용할 수 있을 만큼 충분한 커야 합니다.
④ UNDO_SUPPRESS_ERRORS
- 이 파라미터를 사용하면 자동 Undo 관리 모드에서 수동 관리 모드 작업 시 에러가 발생하는 것을 방지합니다.
- 즉, ALTER ROLLBACK SEGMENT ONLINE, SET TRANSACTION USE ROLLBACK SEGMENT 문을 수행하면 ORA-30019 에러가 발생하는 것을 방지합니다.
Undo 테이블스페이스 생성
- 자동 Undo 관리에는 Undo 테이블스페이스가 필요합니다. Undo 테이블스페이스는 데이타베이스에 두개 이상 가능하지만, 그 중에 하나만 활성화되어 있습니다.
- Undo 테이블스페이스를 생성하는 방법은 CREATE DATABASE문에 절을 추가하여 데이타베이스와 함께 생성하는 방법과, 데이터베이스가 생성된 후에도 CREATE UNDO TABLESPACE 명령을 사용하여 생성할 수 있습니다.
① 데이터베이스 생성시 지정
SQL> CREATE DATABASE db01 . . . UNDO TABLESPACE UNDOTBS1 DATAFILE 'D:\oracle\oradata\oracle\UNDOTBS1.dbf' SIZE 20M AUTOEXTEND ON;
② 데이터베이스 생성 후 별도 생성
SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE 'D:\oracle\oradata\oracle\UNDOTBS2.dbf' SIZE 30M;
Undo 테이블스페이스 변경
① Undo 테이블스페이스 전환
- 현재 사용하고 있는 Undo 테이블스페이스를 다른 테이블스페이스로 변경 할 수 있습니다. (활성 Undo 테이블스페이스에 있는 Undo 세그먼트를 오프라인 상태로 변경할 수는 없습니다.)
- 인스턴스당 하나의 Undo 테이블스페이스만 활성 Undo 테이블스페이스로 지정될 수 있습니다.
- ALTER SYSTEM 명령을 이용하면 됩니다.
SQL> ALTER SYSTEM SET UNDO_TABLESPACE=UNDOTBS2
- Undo 테이블스페이스를 전환하면 새로운 트랜잭션은 새로 지정한 UNDO 테이블스페이스를 사용하게 됩니다.
- 하지만 모든 현재 트랜잭션, 즉 이미 존재하던 UNDO 테이블스페이스에 할당된 트랜잭션은 완료될 때까지 계속해서 기존의 Undo 테이블스페이스를 사용합니다.
② Undo 테이블스페이스 삭제
- UNDO 테이블스페이스 삭제는 인스턴스에서 현재 사용되지 않을 때 , 커밋되지 않은 트랜잭션이 포함되어 있지 않아야 가능합니다.
- 삭제하려는 UNDO 테이블스페이스가 데이타베이스의 현재 Active한 UNDO 테이블스페이스인 경우는 먼저 새 UNDO 테이블스페이스를 설정해야 합니다.
- UNDO 테이블스페이스를 삭제하려면, 테이블스페이스내의 모든 트랜잭션이 완료되어야 합니다.
-- 쿼리 결과 만약 PENDING OFFLINE 상태의 Undo 세그먼트가 존재한다면
-- 이 UNDO 세그먼트에는 Active 트랜잭션이 아직 포함되어 있는 것입니다.
SQL> SELECT a.name,b.status ,b.xacts FROM V$ROLLNAME a, V$ROLLSTAT b WHERE a.name IN (SELECT segment_name FROM DBA_SEGMENTS) AND a.usn = b.usn; -- Undo 테이블스페이스 삭제 SQL> DROP TABLESPACE UNDOTBS1;
Undo 테이블스페이스 크기 결정
① Undo 세그먼트 통계조회
- V$UNDOSTAT 뷰를 사용하여 Undo에 대한 공간 할당과 사용을 모니터 합니다. (각 행은 10분 간격으로 인스턴스에서 수집된 통계가 저장됩니다. 시간 간격은 10분이라고 하였지만 10분 미만의 시간이 반환 될 때도 있습니다. )
- 이 뷰를 사용하면 현재 작업 로드에 필요한 Undo 공간의 크기를 예측할 수 있으며 Undo 사용을 튜닝 할 수 있습니다 (자동 모드와 수동 모드에서 모두 사용할 수 있습니다.)
SQL> SELECT end_time, begin_time, undoblks, txncount, maxquerylen FROM V$UNDOSTAT; END_TIME BEGIN_TI UNDOBLKS TXNCOUNT MAXQUERYLEN -------- -------- ---------- ---------- ----------- 05/04/06 05/04/06 9 889 3 05/04/06 05/04/06 33 812 3
② 크기 결정에 필요한 정보
UNDO 테이블스페이스의 크기를 조정 하려면 세 가지 가 필요 합니다.
두 가지는 초기화 파일에서 얻을 수 있는 UNDO_RETENTION과 DB_BLOCK_SIZE이며, 세 번째는 초당 생성되는 Undo block의 수를 V$UNDOSTAT에서 얻을 수 있습니다.
- - (UR) UNDO_RETENTION (초)
- - (UPS) 초당 생성되는 Undo 데이터 블록 수
- - (DBS) 익스텐트 및 파일크기에 따라 달라지는 오버헤드 (DB_BLOCK_SIZE)
-- 아래는 초당 생성되는 undo block수를 알 수 있는 공식입니다. -- 생성되는 전체 block의 수를 계산하여 모니터되는 시간(초)으로 나눕니다. SQL> SELECT (SUM(undoblks)/SUM (((end_time-begin_time)*86400))) FROM V$UNDOSTAT; -- END_TIME 과 BEGIN_TIME 은 DATE형이기 때문에 뺄셈을 수행하면 -- 결과가 날짜로 표시됩니다. 그러므로 날짜를 초로 변환하려면 -- 하루를 초로 계산한 86400을 곱합니다. -- 완성된 SQL문장 SQL> SELECT (UR * (UPS * DBS)) + (DBS * 24) AS "Bytes" FROM (SELECT value AS UR FROM V$PARAMETER WHERE name = 'undo_retention'), (SELECT (SUM(undoblks)/SUM(((end_time-begin_time)*86400))) AS UPS FROM V$UNDOSTAT), (SELECT value AS DBS FROM V$PARAMETER WHERE name = 'db_block_size');
화면상의 표시된 공식에 의한 결과를 가지고 undo tablespace크기를 결정할수 있습니다. 좋은 결과를 얻으려면 데이타베이스의 작업 로드가 가장 많은 시간에 계산하는 것이 좋습니다.
Undo 세그먼트 정보 조회
SQL> SELECT segment_name, owner, tablespace_name, status FROM DBA_ROLLBACK_SEGS; SQL> SELECT n.name, s.extents, s.rssize, s.hwmsize, s.xacts, s.status FROM V$ROLLNAME n, V$ROLLSTAT s WHERE n.usn = s.usn; SQL> SELECT s.username, t.xidusn, t.ubafil,t.ubablk, t.used_ublk FROM V$SESSION s, V$TRANSACTION t WHERE s.saddr = t.ses_addr;