ORACLE 9i 물리설계\(物理設計\)

Transcription

1 ORACLE 9i 물리설계 ( 物理設計 ) 작성및번역 : 김도근 ( 롯데정보통신 LOTTE CARD IS팀, OTN ACE ) 본자료는 OTN JAPAN에서 1년에걸쳐서연재되어서많은호응을받았던자료로서내용을번역하고재구성한것입니다. 많은분들이참조하셔서도움이되셨음합니다. 연재자 : Oracle Japan CROSS 인터스트리부 OracleDirect SC 그룹 中家裕之 목차 제 1 부 DB전체설계제1장블록사이즈해결방안제2장캐릭터셋해결방안제3장콘트롤파일에있어서고려사항제4장 REDO로그파일생성에있어서고려사항제5장 OMF구성과소개 제 2 부테이블스페이스의설계제1장테이블스페이스분할및배치관점제2장로컬관리테이블스페이스영역제3장시스템테이블스페이스의설계제4장 UNDO테이블스페이스의설계제5장 TEMP 테이블스페이스의설계제6장 IOT 테이블스페이스의설계제7장 1부,2부의설명추가 SQL문 SAMPLE

2 제 3 부테이블설계제1장테이블용량의산정제2장테이블의산정예제3장예에열거된순의지침제4장 CREATE TABLE의파라미터의설정 제 4 부인덱스의설계제1장인덱스용량의산정제2장인덱스의용량산정의예제3장 CREATE INDEX의파라미터의설정 제 5 부영역감시제1장영역감시의대상제2장데이터파일레벨의단편화제3장세그먼트레벨의단편화제4장익스텐트레벨의단편화제5장블록레벨의단편화 제 6 부단편화의해결제1장단편화해결의요점제2장데이터파일 ( 테이블스페이스 ) 레벨의단편화대상제3장세그먼트레벨의단편화대상제4장블록레밸의단편화대상제5장정리 제 1 부 :DB 전체의설계 제 1 장블록사이즈해결방안 블록사이즈를결정하는기본적인지침블록사이즈는 CREATE DATABASE 커멘드의실행시에초기화파라미터 DB_BLOCK_SIZE에의해서지정된다. 기본적으로 2,048, 4,096, 8,192, 16,384, 32,768 바이트중하나가선택되어진다. 단, 바이트는 64비트계열의일부 OS 버전용 ORACLE 밖에지정되지않는다. 디폴트의경우대부분 2,048 바이트가지정된다. 어떤블록사이즈를지정하는것은시스템의특징에의존적이다. 테이블의블록사이즈의크기에의한 DB의영향

3 을고려한다음종합적으로판단되어져야할것이다. ( 표 ) 블록사이즈크기에따른 DB 의영향 블록사이즈가작음 블록사이즈가큼 블록에저장되는레코드수작다많다 블록 I/O 비용낮다. 높다 풀스캔 I/O 비용 DB_FILE_MULTIBLOCK_READ_COUNT 에의존 같은값이라면블록사이즈가큰편이 COST 가높다. 캐시히트율높다낮다 트랙잭션경합발생가능성작다크다 ROW CHANING 발생가능성크다작다 세그먼트압축효과작다크다 적합한시스템형태 OLTP 계열 DSS/DWH 계열 일반적으로 동일한블록에대해복수개의세션으로부터동일하게갱신되는트랙잭션이발생할가능성이높다. 테이블중의일부레코드에대해삭제가빈번하게발생할수있다. 캐시히트율을높이고싶다. 이런경우에는작은블록사이즈를사용하는것이좋다 한편 LOB 타입과 LONG/LONG RAW 타입의데이터가많이이용되고있다. 레코드의길이가긴테이블이많다. 테이블과인덱스의압축기능을이용하고싶다 이럴경우에는큰블록사이즈를사용하는것이좋다 표에있는특징에대해서설계하려는시스템이복합적인성격혹은모호할경우일반적으로 8,192 바이트를사용 하는것을적극추천한다.

4 멀티블록사이즈에대해서 오라클 9 버전부터테이블스페이스에블록사이즈를복수개로선택하는것이가능해졌다. 테이블스페이스영역 에있어블록사이즈를지정하는것은 아래와같이 CREATE TABLESPACE 문으로지정된다. SQL> create tablespace tbs1 datafile blocksize 16k; 하지만, ALTER TABLESPACE문으로블록사이즈를변경하는것은불가능하다. 초기화파라미터 DB_BLOCK_SIZE에지정한블록사이즈는 CREATE TABLESPACE문으로블록사이즈를지정하지않고실행하면디폴트로이값을이용한다. 멀티블록사이즈를이용하면, 버퍼 캐쉬가블록사이즈마다확보된다. 블록사이즈가한종류의경우는초기화파라미터 DB_BOCK_BUFFERS 혹은 DB_CACHE_SIZE로버퍼 캐쉬의크기로지정하고있다. 멀티블록사이즈를사용했을경우는초기화파라미터 DB_CACHE_SIZE는디폴트의블록사이즈의테이블스페이스의캐쉬가되며, 그외의블록사이즈의테이블스페이스용의캐쉬는초기화파라미터 DB_nK_CACHE_SIZE(n는2/4/8/16/32의어느쪽이든으로, 블록사이즈에맞춘다 ) 로크기를지정합니다. 디폴트의블록사이즈용의 DB_nK_CACHE_SIZE는지정해도무시된다. 이블록사이즈가다르면버퍼 캐쉬가따로설정된다는특징을이용하여, 집중적으로캐싱하고싶은세그먼트 (segment) 와캐쉬의필요성의적은세그먼트 (segment) 를다른블록사이즈의테이블스페이스영역에배치해, 캐쉬를이용하는테크닉을이용할수있다. 또, LOB 테이블과같은레코드사이즈가긴테이블을저장하는테이블스페이스영역의블록사이즈만크게하여행체인의발생을억제하는방법을사용하는것도팁이될수있다. 제 2 장캐릭터 셋의결정방법 Oracle9i 으로이용할수있는캐릭터셋 Oracle9i 로한글을취급하는경우, 테이블에있는캐릭터 셋 (Oracle 상의문자코드 ) 을선택할수있다. ( 표 ) Oracle9i 로지정할수있는, 한글을취급할수있는주요캐릭터 셋 캐릭터셋 KO16KSC5601 KO16MSWIN949 UTF8 AL32UTF8 한글 2350 자 KO16KSC 한글 자 한글 자 한글지원상태 확장 8822 자 ( 총 자 ) 한글완성형 완성형코드포함 이전 : Unicode 2.1 9i Rel1: Unicode 3.0 캐릭터셋 / 인코딩 확장된 8822 자는 이후 : Unicode 3. 9i Rel2 : Unicode 3.1 버전 MSWin 코드페이 10g Rel1 : Unicode 3.2

5 지 949 에따라배열 1/0g Rel2 : Unicode 4.0 한글바이트 2 바이트 2 바이트 3 바이트 3 바이트 지원버전 7.x 이상 8.0 이상 9i R1 이상 Database 가능가능가능가능 Characterset 으 로설정가능여 부 National 불가능불가능가능불가능 Characterset 으 로설정가능여 부 캐릭터 세트를결정하는관점 한국어 영어이외의문자를동시에취급할필요가있을까? 예를들면중국어나포르투갈어등을한글과동시에취급할필요가있는경우, KO16KSC5601이나 KO16MSWIN949에서는중국어나포르투갈어는취급할수없다. 이러한경우에는 Unicode를취급할수있는 AL32UTF8을이용하는것이좋다. Oracle8i 이전부터의데이터로하위버전과의호환성을중시하는경우에는 UTF8를이용한다. 다국어캐릭터 셋에대해 다국어캐릭터셋 (create database문의 national character set구로지정하는캐릭터세트.nchar형이나 NVARCHAR2형, NCLOB형등에서사용되는문자코드 ) 는디폴트로 AL16UTF16를지정하는것이좋다. 구버젼의 Oracle에서는 KO16KSC5601 등에대응한캐릭터세트를사용할수있지만, Oracle9i이후에서는다국어캐릭터셋는 Unicode가전제가된다. 제 3 장제어파일의작성에있어서의고려사항 다중화의권유 제어파일은 1 개가깨지면 DB 가정지해버린다. 복구를위해서도컨트롤파일을다중화하는것이좋다. 통상 Oracle 에서는 3 파일이상의다중화를추천하고있다. 또, 내부적인장해에대비하기위해, 다중화한제어파일은 가능한한다른디스크에배치한다.

6 제어파일의용량산정 제어파일의용량에관해서는통상수 MB 정도의크기이므로, 크기에대해서는특히고려할필요는없다. 다만, 백업툴의 Recovery Manager 의리커버리 카탈로그 ( 백업에관한데이터 ) 를제어파일내에작성하는경우는, DB 규모나백업방침에따라서는수백 MB 까지사이즈가부풀기도한다 제 4 장 REDO 로그파일의작성에있어서의고려사항 그룹과멤버의차이 DB 의변경정보를기록하는 REDO 로그파일에는그룹과멤버가존재한다. 어느정도 DB 관리의경험이있는사람 이라도혼동하고있는케이스가많기때문에, 그룹과멤버에대한설명을먼저해보자. 우선그룹이란, 어떤시점에써대상이되는, 1개내지 2개이상의 REDO 로그파일의집합이다. 한편멤버란, 어떤 A 그룹에포함되는개별의 REDO 로그파일을말한다. REDO 로그파일에변경정보를쓸때는최초의그룹의 REDO 로그파일로부터차례로변경정보가써져있는그룹의 REDO 로그파일의끝까지변경정보가써지면, 다음의그룹에바뀌어 ( 로그스위치 ) 변경정보가써진다. 이때멤버가다수개가존재하면, 이전멤버에대해서동시에같은내용의기입한다. 따라서같은그룹내의멤버는항상같은내용이다. 그룹의구성에관한고려사항 REDO 로그파일의초기작성은 CREATE DATABASE 커멘드로실시하지만, 이때최소 2 개의그룹을지정할필 요가있다. 아카이브 (archive) 로그 모드로 DB 를운용하는경우는, 아카이브 (archive) 중에 REDO 로그가순환되어 져서 REDO 로그파일에 REWRITE 되는것을방지하기위한시스템 HANGUP 현상을방지하기위해서, 최소한

7 3 개의그룹을작성하도록한다. 아카이브 (archive) 로그 모드로 DB 를운용하는경우는, 각그룹을다른물리디스크에배치하면, 아카이브 (archive) 와 WRITE I/O 가분산되어퍼포먼스가향상된다. 멤버의구성에관한고려사항 멤버는최소하나에서도가동한다. 하지만두개이상의멤버를작성하면, 어떤 A 멤버가망가져도, 그룹중최소하나의멤버가살아남고있다면 DB는계속해서가동이된다. 그러므로장해대책으로서멤버는반드시두개이상작성해둔다.. 같은그룹에소속하는멤버는동시에 WRITE를한다. I/O를분산시켜퍼포먼스를향상시키기위해서, 각멤버는각기다른물리디스크에배치해두도록하자. REDO 로그파일의용량산정 REDO 로그파일에쓰여지는데이터량은, 대상의 SQL나갱신데이터양등에의해서크게바뀌기위해, 사전에정확하게추측하는것은불가능하다. 그러므로소규모시스템이면 1~10 MB, 중규모시스템이면 10~100 MB, 대규모시스템이면 100 MB이상의크기로우선작성해, 실제의 REDO 발생량을보고운용의과정에서따로조정하는것이일반적이다.. 트랜잭션 (transaction) 처리중에로그스위치 (LOG SWITCH) 가발생하면그트랜잭션 (transaction) 의퍼포먼스가떨어지기때문에, 특히 OLTP계의처리가중심의경우는, 처리의피크타임시에는로그스위치가발생하지않게할필요가있다. 운용 REDO 로그갱신량에관한데이터를모아피크타임시에로그스위치가발생되지않을정도크기로조정하도록하자. 또, 일반적으로 REDO 로그파일의크기가큰만큼로그스위치의발생의가능성이적기때문에, 퍼포먼스는향상되게된다. 그러나한편리커버리에필요로하는시간은길어지기때문에, 이러한트레이드오프도고려하도록한다 제 5 장 OMF 구성의소개 OMF 란? OMF(Oracle Managed Files) 는지정디렉토리에정리해테이블스페이스이나제어파일, REDO 로그파일을배치 하는것으로, DB 관리의번거로움을크게줄여주는 Oracle9i 의신기능입니다. OMF 를이용하는경우는초기화파라미터 DB_CREATE_FILE_DEST 에파일을작성하고싶은디렉토리를지정한

8 다. 이설정을하는것으로, 테이블스페이스나제어파일등을작성할때에파일명을지정할필요가없어진다. 테이블스페이스를작성하는 SQL 문은아래와단순하게작성할수있다. 이때파일명은 ORACLE 이자동으로명 명한다. 또테이블스페이스를삭제하면데이터파일도자동으로삭제가된다. SQL> create tablespace tbs1; 또, 파일용량이나확장방법등도자동으로이하와같이정해진다 파일용량 :100MB 데이터파일의확장 :1 블록사이즈씩무제한하게자동확장 ( 물론 OS나디스크용량에의한제한되어지는 ) extent 관리 : 로컬관리 (AUTOALLOCATE) 다만, CREATE/ALTER TABLESPACE 문으로디폴트를바꾸어작성 변경할수있다. 그러나 AUTOEXTENT 은권 장하지않는옵션으로적당하게변경해서생성하는것을권장한다. 이하는파일용량은 200 MB 에, AUTOEXTENT 기능없이변경해서 OMF 를이용해테이블스페이스를작성하는예이다. SQL> create tablespace tbs1 datafile size 200m autoextend off; OMF 에적절한시스템 OMF 는아래와같은시스템에서권장된다. 소규모시스템 디스크가 1 개밖에없는듯한소규모시스템에서는물리배치를생각하는여지가없기때문에, 보다 간편하게관리하기위해서권장되어진다. 패키지 소프트의연구최종단계에 Oracle 를이용하는경우 패키지 소프트의경우 DB 의관리요소를별로늘릴수없는것이많기때문에, OMF 를이용하는것으 로데이터파일의관리요소를줄일수있다 SAME 구성을이용하는경우 SAME(Stripe And Mirror Everything) 란, 어떤 A 디스크장치에있는디스크전부를정리해스트라이 핑해서, 1 개의거대한디스크로서취급하는방식이다. SAME 구성을취하면, 모든디스크에대해서

9 I/O 부하가균등하게분산되기위해, 비교적간단히높은 I/O 퍼포먼스를유지시키는것이가능하게된 다. 또, 디스크가 1 개가되기위해, 물리배치에관한설계가편해진다.OMF 를 SAME 구성과병용하 면보다설계 관리의부하를줄일수있다. 제어파일 REDO 로그파일의 OMF 관리 앞에서서명한제어파일, REDO 로그파일도 OMF에의한관리가가능하다. 그러나, 제어파일이나 REDO 로그파일을다중화해도다른디스크에분산배치할수없게된다. 이문제에대한대책으로서초기화파라미터 DB_CREATE_ONLINE_LOG_DEST_n가준비되어있다. 이파라미터에제어파일 REDO 로그파일파일을배치하고싶은디렉토리를지정한다. n( 은 ) 는 1~5까지의숫자가들어간다. 예를들면 DB_CREATE_ONLINE_LOG_DEST_1, DB_CREATE_ONLINE_LOG_DEST_ 2, DB_CREATE_ONLINE_LOG_DEST_3의 3개의파라미터를지정해, 각각다른디렉토리를지정하면, 제어파일 REDO 로그파일이 3개에다중화가된다. 이파라미터를지정하지않으면초기화파라미터 DB_CREATE_FILE_DEST로지정한디렉토리에작성이된다. 제 2 부 : 테이블영역의설계 제 1 장테이블스페이스분할 배치의관점 테이블스페이스분할 배치의관점 데이터베이스는다수의테이블스페이스로구성된다. 테이블스페이스의분할을검토할때, 주로 3 개의관점이있습 니다. 관리성 내장해성 ( 耐障害性 ) 퍼포먼스 관리성의관점 데이타베이스관리를용이하게하기위해서, 이하열거하는목표점에유의한다. 단편화등퍼포먼스의문제가발생하기않게한다 디스크의추가등테이블스페이스재편성에수반하는작업수를줄인다

10 관리자에게있어서알기쉽다. 등과같은메리트를얻을수있다. (1) 시스템테이블스페이스, UNDO 테이블스페이스, TEMPOARAY 스페이스영역은분리되어서작성하자 시스템테이블스페이스, UNDO 테이블스페이스, TEMPOARAY 스페이스독립된테이블스페이스로작성한 다. 이러한테이블스페이스영역에는일반유저의테이블이나인덱스는생성하지않게주의한다. (2) OMF(Oracle Managed Files) 를이용하자 OMF 는테이블스페이스영역의자동관리를목적으로하는기능이다 (3) 읽기전용세그먼트 (segment) 를별도의공간에분리한다. 읽기전용의테이블이나인덱스는읽기전용의테이블스페이스영역에저장한다. 읽기전용테이블스페이스에저장하는것으로인해읽기전용인것을명시적으로알수있게한다. 또, 한번백업을하면, 갱신이없기때문에정기적으로백업을할필요가없어진다. 테이블이나인덱스를파티션화해서파티션마다테이블스페이스를나누면, 갱신이발생하지않는파티션만읽기전용으로할수도있다. (4) 저장오브젝트로나누자. 예를들면테이블과인덱스는다른테이블스페이스영역에생성한다, 혹은같은테이블에서도통상의테이블과 LOB 테이블은다른테이블스페이스영역에생성한다, 이와같이, 오브젝트마다테이블스페이스영역을나누면 DB규모가커지는만큼, 나중에관리가용이해진다. (5) 유저 (schema) 로분리해서나누자 예를들면 SCOTT 유저소유의오브젝트와 SMITH 유저소유의오브젝트를다른테이블스페이스에생성한다, 이와같이, 오브젝트소유유저마다테이블스페이스영역을나눈다. DB 규모가큰만큼관리가용이해진다. (6) 업무용도로나누자예를들면경리시스템으로사용하는오브젝트와인사시스템으로사용하는오브젝트, 양쪽모두로사용하는오브젝트를다른테이블스페이스영역에생성한다. 즉, 업무마다테이블영역따로나눈다. DB규모가커짐에따라관리가용이해진다. 내장해성 ( 耐障害性 ) 의관점 장해가발생되지않도록, 혹은장해가발생해도영향범위가줄어들도록, 아래사항참조로유의한다.

11 (1) 디스크의분할대용량의디스크를소량준비하는것보다도, 소용량의디스크를대량으로준비해, 최대한으로데이터를분산시키는것으로, 장해의영향을작게한다고하는의미로내장해성을확보할수있다. 무엇보다, 디스크의대용량화에의해서이대책을세우는것은어려울지도모른다. (2) 동일디스크내있을정도의파일을나누어배치하자 동일디스크상에서파티션, 디렉토리, 파일과같은단위로나누는것도디스크를분할하는것에비교하면효과는 작다, 장해범위의축소하는하는효과는있다. (3) 데이터의중요도에따라내장해성이높은디스크장치를이용하자간단히실행할수있는캐이스는아니지만, DB내의데이터의중요도에따라차이가있어, 비교적디스크로할애할수있는예산이한정되어있는경우는, 중요한데이터는 RAID 구성디스크장치의테이블스페이스영역을배치하고, 중요하지않은데이터는 RAID가아닌디스크장치의테이블스페이스영역을배치한다고하는것도생각할수있다. 퍼포먼스의관점 대용량의디스크를소량준비하는것보다도, 소용량의디스크를대량으로준비해, 최대한으로데이터를분산시키는 것으로, 내장해성뿐만아니라퍼포먼스도향상시킬수있다. (1) 테이블과그테이블에대한인덱스 인덱스스캔이실행되는경우, 같은디스크에테이블과인덱스가있으면, 테이블과인덱스의사이의 HDD 의 ARM 의이동이빈번히발생한다. 검색대상레코드수가많을정도퍼포먼스에영향을미친다.. (2) 결합대상의테이블사이 결합방법에의해차이가있지만, 결합대상의테이블사이의 HDD 의 ARM 이동이빈번히발생한다. 검색대상레 코드수가많을정도퍼포먼스에영향을미친다. (3) 병렬처리의대상이되는테이블이나인덱스병렬처리는복수의프로세스를시작하고, 각프로세스가같은테이블이나인덱스에액세스하기위해, 같은디스크에배치하고있으면 I/O의경합이발생해, 오히려퍼포먼스가떨어지는일이있다. 따라서세그먼트 (segment) 에대해서복수의데이터파일을작성하든지, 파티션화해서데이터파일을나누는등, 별도의디스크에배치하도록한다.

12 (4) 시스템테이블스페이스시스템테이블스페이스는기타테이블스페이스, REDO 로그파일과는다른디스크에배치하도록한다. 특히오브젝트수가많은시스템, 감사이력을테이블에기록하는시스템, 멀티마스터 replication나 advanced 큐잉을이용하는시스템에서는시스템테이블스페이스의 I/O가다분히발생한다. 시스템테이블스페이스에서의 I/O가다발하는경우, 시스템테이블스페이스자체를복수의데이터파일로작성해, 데이터파일을분산시키면유리하다. (5) UNDO 테이블스페이스 UNDO테이블스페이스은기타테이블스페이스, REDO 로그파일과는다른디스크에배치하도록한다. 특히갱신처리가많은시스템에서는 UNDO테이블스페이스에의 I/O가다발적으로발생한다. UNDO테이블스페이스에서의 I/O가다발하는경우, UNDO테이블스페이스를복수의데이터파일로작성해, 데이터파일을분산시키면유리하다. (6) 임시테이블스페이스임시테이블스페이스영역은기타테이블스페이스, REDO 로그파일과는다른디스크에배치하도록한다특히대량소트처리가많은시스템에서는임시테이블스페이스에서의 I/O가다발적으로발생한다. 임시테이블스페이스에서의 I/O가다발하는경우, 임시테이블스페이스자체를복수의데이터파일로작성해, 데이터파일을분산시키면유리하다. 임시테이블스페이스의경우는임시테이블스페이스그자체를복수작성해, 유저에따라서각각다른임시테이블스페이스를할당해서사용하는방법도있다. 제 2 장로컬관리테이블스페이스 (LMT) 로컬관리테이블스페이스란? 로컬관리테이블스페이스는 Oracle8i부터채용된, 새로운 extent 관리의방법을채용한테이블스페이스이다. 로컬관리테이블스페이스에대해, 기존버전의 extent 관리의방법을채용한테이블스페이스를딕셔너리관리테이블스페이스이라고부른다. 양자의 extent 관리의차이는, 우선딕셔너리관리테이블스페이스는그이름과같이시스템테이블스페이스에있는딕셔너리에서 extent의관리를실시하고있다. 한편로컬관리테이 블스페이스는 테이블스페이스의데이터파일의헤더부분에 64 KB 의영역을잡아, 이영역에서비트맵을 이용해 extent 관리를실시하고있다. Oracle9iR1 로부터로컬관리테이블스페이스가디폴트의테이블스페이 스가되었다. Oracle9iR2 에서는시스템테이블스페이스를로컬관리로할수있게되었다 ( R1 에는디폴트는 시스템테이블스페이스는딕셔너리관리하고로컬관리로할수없다 ) 로컬관리테이블스페이스의장점

13 로컬관리테이블스페이스는딕셔너리관리방식에비해많은장점이있다. (1) 퍼포먼스의향상 딕셔너리테이블스페이스에서는 extent 관리는테이블스페이스에서집중되어실행된다. 따라서 extent 의 추가나삭제의처리는딕셔너리에대한 I/O 가 많기때문에, 이러한처리가하면퍼포먼스에큰영향을준 다. 한편로컬관리테이블스페이스는 extent 관리를각데이블스페이스에서실시하기위해, 시스템테이블스페이스에서의 I/O는최소한으로억제시킨다. 또, 딕셔너리의갱신은 UNDO의생성을수반하지만, 로컬관리테이블스페이스의헤더의비트맵의갱신은 UNDO를거의생성하지않는다. 로컬관리테이블스페이스에서는딕셔너리관리테이블스페이스에비해다음과같은처리가빠르게진행된다. extent의확장 UNDO 세그먼트 (segment) 를이용하는처리 ( 갱신 SQL등 ) TRUNCATE 세그먼트 (segment) 의 DROP (2) 세그먼트 (segment) 의설계 관리의간소화로컬관리테이블스페이스에서의 extent 관리방법의변경에수반해, extent 관리를위한파라미터가줄어든다. 따라서, 설계 관리가간단해진다. 또, extent 확보의방법이단순하게되기위해, 그점에서도관리가편해진다 ( 자세한것은 (3) 을참조 ). 로컬관리테이블스페이스에있어 CREATE/ALTER TABLESPACE문으로지정불필요하게된파라미터는다음과같다. DEFAULT STORAGE 구 MINIMUM EXTENT TEMPORARY CREATE/ALTER TABLE, CREATE/ALTER INDEX 의파라미터에대해서는다음번이후에설명한다. (3) 단편화의감소로컬테이블스페이스에서는 extent 확장하는방법이 2가지가있다. 하나는디폴트의 AUTOALLOCATE 지정으로, 세그먼트 (segment) 사이즈에따라 64KB, 1MB, 8MB, 64 MB의 extent를 Oracle가자동으로확보합니다. 또하나는 UNIFORM 지정으로, CREATE TABLESPACE시로지정한고정사이즈로 extent를확보합니다.autoallocate 지정때는소수의, UNIFORM 지정때는하나의사이즈의 extent 밖에작성되지않기때문에, extent 레벨로미사용이되는영역의발생이방지된다. (4) 테이블스페이스에대한 coalesce 가불필요

14 딕셔너리관리테이블스페이스에서는연속한빈 extent 를하나의큰 extent 에결합하기위해서 coalescing 처 리가필요했다. 그러나로컬관리에서는 coalesce 하지않아도연속한빈 extent 를인식할수있다. 로컬관리테이블스페이스의단점 로컬관리테이블스페이스는딕셔내리방식에비해많은장점이있지만, 단점도일부존재한다. (1) DIRECT 처리에대해퍼포먼스저하다이렉트 로드나다이렉트 로드 인서트처리는, 데이터를일단임시세그먼트 (segment) 에쓰고, 실제로쓴크기에서임시세그먼트 (segment) 를테이블내지인덱스의 extent로변환하는것이고속화의한요인이되어있다. 그러나로컬관리테이블스페이스는 extent 사이즈가정해져있기위해, 실제로쓴크기는아니고정해진크기로임시세그먼트 (segment) 를작성하지않으면안되기때문에, extent의 formatting에관 한 LOSS 가발생한다. 하지만, 늦어진다고해도딕셔너리관리방식에 비해늦은것뿐이어서, 로컬관리 방식상의종래패스보다는속도가더빨라진다. (2) 치밀한 EXTENT 관리를할수없다. 관리가간단하게되는반면, 테이블이나인덱스에필요한크기에딱맞는 extent 사이즈를개별지정하는것이불가능해진다. 딕셔너리관리방식에서필요용량을빈틈없이결정해관리했을경우에비하면, extent 내의미사용영역이발생하기쉽다 로컬관리방식과딕셔너리관리방식중어느것을사용할까? 로컬관리방식은딕셔너리관리방식에비해많은장점이있는것은일반적으로알려진사실이다.Oracle9iR1 에서는테이블스페이스의디폴트가되어있는것도있기때문에, 기본적으로는로컬관리방식을사용하도록 권장하고있다. 로컬관리방식이대세이고앞으로는로컬관리방식을기본적으로사용하자. 로컬관리테이블스페이스설정에관한 Tips (1) 데이터파일의용량산정 이하의순서로산정합니다.

15 1. 데이터파일에생성하는테이블이나인덱스의견적합계사이즈에여유치를더한값을산출합니다 의값을, AUTOALLOCATE 지정의경우는 64 KB의배수에, UNIFORM 지정의경우는 UNIFORM SIZE의배수가되도록한다 의값에테이블스페이스의헤더와 extent 관리용의비트맵의영역으로서 64 KB를더한다. 여유치가충분한값이면, 2. 나 3 을실행할필요는없지만, 이계산방법이라면데이터파일이최대한이용 되었을때에파일의마지막에쓸데없는빈곳이생기지않는다. 무엇보다요즈음의디스크용량이면수 MB 정도의낭비가있어도문제가되지않는다고생각되므로, 계산이귀찮으면 1 만으로도계산이충분하다. (2) AUTOALLOCATE와 UNIFORM의어느쪽을이용해야할까? AUTOALLOCATE 지정은세그먼트 (segment) 사이즈에대한 extent를자동으로할당하기위해, UNIFORM 에비해영역이효율적으로이용할수있다. 한편 extent 사이즈가몇종류가되기위해, extent의추가가반복할때에, extent간에이용할수없는단편화영역이발생할가능성이있다. 한편 UNIFORM 지정은 extent 사이즈가고정이므로매우알기쉽다고하는이점이있다. 영역의효율적인이용에대해서는, UNIFORM SIZE의조정으로그만큼헛됨은나오지않게된다. 또, UNIFORM 지정은 (1) 방법으로사이즈를설정하면 extent 레벨로의미사용영역의단편화는발생하지않는다. 기본적으로 UNIFORM 지정을이용하는것을추천한다. AUTOALLOCATE 지정은영역의지정이느슨해도상관없다고판단할수있는시스템 ( 소규모시스템이나개발기의 DB 등 ), 소량데이터용의테이블스페이스영역등에사용하는것을추천한다. (3) 자동확장이지나치게되면어떤가? 기본적으로자동확장 (AUTOEXTEND구) 에의지하는것은좋지않다. 자동확장을 ON로하면, 파일의레벨로단편화가발생할가능성이있고, 어떠한 SQL 처리중의데이터파일의확장은부하가높은처리가된다. 데이터파일로서필요한용량을제대로추측해, 자동확장이발생하지않도록한다. 단지, 산정을제대로한다음보험용으로서넉넉한값을설정해두는것도나쁜일것은아니다. 제 3 장시스템테이블스페이스의설계 데이터파일의용량산정 Partitioning Option 를추가한 Enterpirse Edition 에서, DBCA 로표시되는옵션을일절선택하지않고인스톨 하면약 200 MB, 반대로모두선택해인스톨하면약 400 MB 의시스템테이블스페이스영역이잡힌다. 오브젝트정보용의공간도확보되고있기때문에, 기본적으로는이러한용량에여유치를봐두면좋다.

16 다만, 다음과같은케이스에서는더욱용량이필요하게되는일이있다. 감사로그가테이블에쌓이는경우 replication 를이용하는경우 오브젝트수가많아지는경우 ( 특히 STORED 나파티션등 ) 로컬관리테이블스페이스영역에지나치게되면 로컬관리테이블스페이스로하는것이퍼포먼스가뛰어나다. 한편으로아래와같은제약이있기때문에, 제 약에신경이쓰이는경우는딕셔너리관리로한다. 덧붙여로컬관리의시스템테이블스페이스영역은 AUTOALLOCATE 지정만가능한반면에 UNIFORM 지정은할수없다. 디폴트임시테이블스페이스영역의지정이필요 시스템테이블스페이스를딕셔너리관리방식으로변경할수없다. 신규딕셔너리관리테이블스페이스영역생성할수없다. 제 4 장 UNDO 테이블스페이스의설계 자동 UNDO 관리와수동 UNDO 관리의차이점과선택기준 Oracle9i으로부터롤백 (rollback) 세그먼트 (segment) 전용으로생성되는테이블스페이스를 UNDO 테이블스페이스라고부른다. 그리고롤백 (rollback) 세그먼트 (segment) 의작성 관리를 Oracle 이알아서자동으로관리해주는기능도추가되었다. 이것이자동 UNDO 관리이다. Oracle8i까지 DBA가알아서롤백 (rollback) 세그먼트 (segment) 를생성하는케이스는수동 UNDO 관리라고부른다. Oracle9i그렇지만수동 UNDO 관리를선택할수가있다. 이것은초기화파라미터 UNDO_MANAGEMENT로결정되어진다. 디폴트가 MANUAL라면수동 UNDO 관리, AUTO라면자동 UNDO 관리가된다. 자동 UNDO 관리와수동 UNDO 관리는인스턴스기동시는어느한쪽만이용할수있다. 그리고기동중의변환도할수없다. 따라서, 어느쪽을선택해서이용할지는설계의단계에서결정할필요가있다. 이때자동 UNDO 관리를선택하면, 수동 UNDO 관리에비해이하와같은장점을얻을수있다. (1) 설계 관리가용이설계나관리가어려운롤백 (rollback) 세그먼트 (segment) 에대해거의아무것도고려하지않아도괜찮다고하는것은, DBA에있어서매우큰장점이다. 자동 UNDO로설계할때단지설정하는것은테이블스페이스의이름과데이터파일의용량, 초기화파라미터 UNDO_RETENTION의값, 초기에작성하는세먼 ( ) 의수 ( 기본은이자동계산 ) 뿐이다

17 하는 UNDO 세그먼트 (segment) 의수 ( 기본은 Oracle 이자동계산 ) 뿐이다. (2) ORA 에러발생가능성을방지어느정도경험이있는 DBA분이라면, 한번은대량갱신처리에있어서의 ORA-01555의발생에골치를썩였던적이있을까생각된다. 대량갱신처리에있어서의 ORA-01555는읽기일관성을위해확보된 UNDO 세그먼트 (segment) 상의데이터가다른트랜잭션 (transaction) 에서 OVERWRITE 되고나서, 그데이터에대한참조요구가왔을때에발생한다. 자동 UNDO 관리로하면초기화파라미터 UNDO_RETENTION로설정한시간동안 UNDO 데이터가 OVERWRITE되지않고남기때문에, ORA 에러발생을막을수있다. (3) 영역부족에러발생가능성방지자동 UNDO 관리로하면, A라는 UNDO 세그먼트 (segment) 가 UNDO 테이블스페이스의나머지영역을다사용해테이블스페이스의확장도할수없는경우, 다른 UNDO 세그먼트 (segment) 에빈곳이있으면그빈곳을미사용영역으로서인식해서, 처리를계속한다. 수동 UNDO 관리로같은상황에빠졌을경우는다른롤백 (rollback) 세그먼트 (segment) 에빈영역이있었다고해도영역부족에러가발생하게된다. (4) 플래시백 쿼리가보다확실히이용이가능하다플래시백 쿼리란, 커밋된과거의데이터를거슬러올라가볼수있는 Oracle9i신기능이다. 잘못해서데이터를갱신해서커밋해버렸을경우의리커버리에편리한기능이다. 수동 UNDO 관리에서도이기능은이용할수있지만, 과거의데이터보존기간이보증되지않는다. 자동 UNDO 관리로하면초기화파라미터 UNDO_RETENTION를지정할수있게되어, 이파라미터로설정한시간내는 UNDO 데이터가보존이가능하다. 위와같은장점이있으므로, 기본적으로는자동 UNDO 관리가이용되는것을추천한다. 한편, 관리하는요소가없다고하는것은반대로말하면퍼포먼스 튜닝의여지가없다고하는것이된다. 시스템의사정에맞추어퍼포먼스를최대한으로끌어올릴수있는것은수동 UNDO 관리이다. 예를들면동시트랜잭션 (transaction) 수가많기때문에많은롤백 (rollback) 세그먼트 (segment) 를사용하는, 대량배치용으로거대사이즈의롤백 (rollback) 세그먼트 (segment) 를사용하는것이이와같은캐이스이다. 퍼포먼스를엄격하게강화할경우수동 UNDO 관리로사용하는것도좋다. 데이터파일의용량산정 자동 UNDO 관리의경우, UNDO 세그먼트 (segment) 를자동으로작성하기때문에, UNDO 세그먼트 ( ) 의기에관한산정이나설정은불필요하다데이터파일의용량만추측해주면된다산정식은

18 (segment) 의크기에관한산정이나설정은불필요하다. 데이터파일의용량만추측해주면된다. 산정식은 이하와같다. (A B + 64 C + D 2) C + 여유치 ( 단위 : 킬로바이트 ) A:UNDO_RETENTION의값 (0의경우는 1) B : V$UNDOSTAT 뷰의 UNDOBLKS의값을 600으로제산한값제일갱신이많은시간대의레코드를바탕으로하는것사전산정의경우는 1초당의발생 UNDO 블록수를가정 C: 블록사이즈 (2/4/8/16/32의어느쪽이든상관없음 ) D:V$UNDOSTAT 뷰의 MAXCONCURRENCY의값사전산정의경우는최대동시트랜잭션 (transaction) 수를가정 초기화파라미터 UNDO_TABELSPACE 의설정 이파라미터에는, UNDO 테이블스페이스영역으로서이용하고싶은테이블스페이스영역의이름을지정 한다. 지정하지않는경우시스템 롤백 (rollback) 세그먼트 (segment) 가 UNDO 영역으로서이용될가능성이 있으므로, 자동 UNDO 관리의경우는반드시지정하도록한다.. 초기화파라미터 UNDO_RETENTION 의설정 UNDO_RETENTION의값은시스템의요구에따라결정한다. 크게하는경우는확실히좀더긴과거의 UNDO 정보를남길수있고, 남기고싶은양에따라 UNDO표영역의크기를크게설계할필요가있다. UNDO_RETENTION의디폴트값은 900( 초 ) 입니다. 또, 이파라미터는아래와같이 ALTER SYSTEM문으로변경이가능하다. SQL> alter system set undo_retention = 1200; 초기 UNDO 세그먼트 (segment) 의수

19 초기 UNDO 세그먼트 (segment) 의수는초기화파라미터 SESSIONS수를참조하고, 아래의계산식에서결정된다. 초기치는 2~10의범위로설정된다. 기본적으로 Oracle 에맡겨도상관없지만, 기동직후부터대량의동시트랜잭션 (transaction) 가발생하는것을알수있고있는경우는 SESSIONS의값을크게설정한다.SESSIONS의값이 46이상이면, 초기 UNDO 세그먼트 (segment) 의수는최대한 10이된다. least(greatest(1.1 * SESSIONS / 5, 2), 10) ( 개 ) * least 는리스트중의제일작은값을, greatest 는리스트중의제일큰값을돌려준 다 제 5 임시테이블스페이스의설계 임시파일을이용한전용임시테이블스페이스 Oracle9i에서는임시파일을이용한전용임시테이블스페이스를작성하는것을추천한다. 다른타입의임시테이블스페이스는, 퍼포먼스면에서도설계면에서도장점이없다. 굳이말하자면구버젼으로의임시테이블스페이스작성스크립트를그대로이용할수있다는정도밖에없다. 임시파일을이용한전용임시테이블스페이스의장점는아래와같다. (1) 퍼포먼스가좋다 임시파일을이용한전용임시테이블스페이스는 REDO 로그를생성하지않기때문에, 다른타입의 임시테이블스페이스에비해매우퍼포먼스가뛰어나다. (2) 향상된속도의백업 리커버리가가능하다 임시파일을이용한전용임시테이블스페이스는백업의대상이아니다. 따라서그만큼백업과리커버 리가빨라진다 extent 사이즈의견적 extent 사이즈는초기화파라미터 SORT_AREA_SIZE 의배수에블록 사이즈를더한값이 I/O 효율이좋아진

20 다. 어느정도의배수로하면좋은가는, OLTP 처리중심이면수MB, DSS계처리중심이면수십 ~ 수백 MB의 extent 사이즈로검토한다. 처리내용이다르고 extent 사이즈가다른복수의임시테이블스페이스를작성해구분하여사용하는것도하나의방편이다또, 임시테이블스페이스를사용하는트랜잭션 (transaction) 가동시에발생하는경우, 1개의 extent를복수의트랜잭션 (transaction) 이공유할수없기때문에, extent가너무크면영역의실패가발생하기쉬워진다. 현재확보하고있는 extent수이상의동시트랜잭션 (transaction) 이발생하면, 역시 extent의확장이발생한다. 데이터파일의용량산정 임시테이블스페이스는주로메모리로처리할수없는정렬을위한영역으로서사용된다. 어떤 A 소트에필요한영역은최대로소트대상데이터량의 2배정도이다. 가장큰소트처리의대상이되는데이터용량의 2배정도로여유치를더한용량을확보한다. 만약그러한처리가동시에복수세션으로처리된다면, 당연히그만큼을확보해둘필요가있다. 사전산정을예측하지못할경우는우선의지침으로서제일큰테이블의용량의 2배정도를확보해두면좋을것이다 ( 실제로는상당한많은산정값이되기쉽다 ). 또, Oracle8i 로부터이용가능한임시테이블은, 임시테이블스페이스에생성된다. 임시테이블스페이스를 많이사용하는되고, 특히큰임시테이블을많이만드는경우는그만큼을산정한다. 임시테이블자체의산 정은통상의테이블과같게추측한다. 디폴트임시테이블스페이스의권장 Oracle9i 보다, CREATE DATABASE 문을실행할때에이하의예문과같이디폴트의임시테이블스페이스 영역을생성할수있다. SQL> create database... default temporary tablespace 임시테이블스페이스명 tempfile... 이지정이없고, CREATE USER 문으로디폴트의임시테이블스페이스를지정하지않으면시스템테이블 스페이스영역이디폴트의임시테이블스페이스영역이되어버린다. 시스템테이블스페이스에임시세그먼 트 (segment) 를작성하면, 퍼포먼스에심각한영향을주므로이지정은반드시하도록한다. 제 6 장데이터및인덱스용테이블스페이스의설계 UNIFORM 지정시의 Tips

21 extent 관리방식으로 UNIFORM 옵션의로컬관리테이블스페이스를지정했을경우, 크기가닮은테이블마다, 혹은인덱스마다같은테이블스페이스에정리하면, 영역의손실이적게된다. 데이터파일의용량산정의보충 테이블의이동 (ALTER TABLE MOVE) 이나인덱스의재작성 (ALTER INDEX REBUILD) 등을실시하는경우, 생성전의테이블스페이스에임시세그먼트 (segment) 를작성한다. 특히원래의세그먼트 (segment) 와같은테이블스페이스에이러한처리를실시하는경우, 해당테이블영역에 SQL 커멘드실시후에작성되는세그먼트 (segment) 의크기와동일한스페이스를여분으로필요로한다. 제 7 장제 1 부 제 2 부의설명을보충한 SQL 문샘플 이장의목적 본장에서는위의장들을해설한내용을바탕으로, 실제의 CREATE DATABASE 문의샘플을제시합니다. Windows계 OS의 Oracle9iR2를전제로하고있다. 패스명을고치면 UNIX/Linux계 OS의 Oracle9iR2에서도이용할수있다. 실제의환경에서이용하기위해서는, 파라미터의상세한값의재검토, 본SQL를실행하는전후의작업 ( 환경변수의설정, catalog.sql의실행등 ) 이필요하다. 본스크립트는필자의 PC로동작확인을취하고있지만. 디스크의사정으로전파일과도같은드라이브에작성한다. OMF(Oracle Managed Files) 를이용하지않는케이스, 사용한케이스의 2 종류를게재하고있다. OMF 를이용하지않는케이스 (1) 초기화파라미터 ( 관련부분만 ) db_name=case1

22 undo_management=auto undo_tablespace=undotbs1 control_files=("c: oracle oradata case1 CONTROL01.CTL", "C: oracle oradata case1 CONTROL02.CTL", "C: oracle oradata case1 CONTROL03.CTL") (2) CREATE DATABASE 문 행번호 CREATE DATABASE 문 CREATE DATABASE case1 USER SYS IDENTIFIED BY TEST123 USER SYSTEM IDENTIFIED BY TEST456 MAXINSTANCES 1 MAXLOGFILES 10 MAXLOGMEMBERS 3 MAXDATAFILES 100 DATAFILE 'C: oracle oradata case1 system01.dbf' SIZE 250M REUSE EXTENT MANAGEMENT LOCAL DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'C: oracle oradata case1 temp01.dbf' SIZE 40M REUSE UNDO TABLESPACE UNDOTBS1 DATAFILE 'C: oracle oradata case1 undotbs01.dbf' SIZE 200M REUSE CHARACTER SET KO16MSWIN949 NATIONAL CHARACTER SET AL16UTF16 LOGFILE GROUP 1 ('C: oracle oradata case1 redo01_1.log', 'C: oracle oradata case1 redo01_2.log') SIZE K, GROUP 2 ('C: oracle oradata case1 redo02_1.log', 'C: oracle oradata case1 redo02_2.log') SIZE K, GROUP 3 ('C: oracle oradata case1 redo03_1.log', 'C: oracle oradata case1 redo03_2.log') SIZE K; (3) CREATE DATABASE 문의해설 2~3 행째 유저 SYS 및 SYSTEM 에디폴트가아닌패스워드를설정해있습니다. 시큐러티를높이는관점에

23 서, 디폴트가아닌패스워드를설정하는것을추천한다. 8~9행째시스템테이블스페이스는로컬관리테이블스페이스로서작성된다. 10~11행째디폴트의임시테이블스페이스를설정해있다 13행목 DB의캐릭터 셋로서 KO16MSWIN949를지정하고있다. 16~21행째 REDO 로그는그룹을 3개, 멤버를 2개작성한다. OMF 를이용한케이스 (1) 초기화파라미터 ( 관련부분만 ) db_name=case2 undo_management=auto undo_tablespace=undotbs1 db_create_file_dest=c: oracle oradata case5 db_create_online_log_dest_1=c: oracle oradata case5 log1 db_create_online_log_dest_2=c: oracle oradata case5 log2 control_files OMF 에서하는경우는컨트롤파일을설정하지않는다. CREATE DATABASE 실행시에 SPFILE를사용하는경우는, 작성된제어파일이자동적으로본파라미터에등록된다. 한편 PFILE를사용하는경우는, CREATE DATABASE 실시후에 PFILE에본파라미터를추가할필요가있다. 엔트리에기술해야할제어파일의명칭은, 초기화파라미터 DB_CREATE_ONLINE_LOG_DEST_n, 혹 은 CREATE_FILE_DEST(DB_CREATE_ONLINE_LOG_DEST_n 를지정하고있지않는경우 ) 로지정 한디렉토리에확장자 (extension).ctl 로작성된다. (2) CREATE DATABASE 문 행번호 CREATE DATABASE 문

24 USER SYSTEM IDENTIFIED BY TEST456 MAXINSTANCES 1 MAXLOGFILES 10 MAXLOGMEMBERS 3 MAXDATAFILES 100 DATAFILE SIZE 250M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 40M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED UNDO TABLESPACE UNDOTBS1 DATAFILE SIZE 200M AUTOEXTEND ON NEXT 5M MAXSIZE UNLIMITED CHARACTER SET KO16MSWIN949 NATIONAL CHARACTER SET AL16UTF16 LOGFILE GROUP 1 SIZE K, GROUP 2 SIZE K, GROUP 3 SIZE K; (3) CREATE DATABASE 문의해설 전반 o OMF를이용하고있으므로파일명을지정해있지않다. o 파일명이외의파라미터 ( 파일사이즈등 ) 도지정을줄일수있지만, 본스크립트에서는값을설정하고있다. o 본스크립트에서는데이터파일의자동확장을지정해있다. 8~13행째시스템테이블스페이스, 임시테이블스페이스, undo 테이블스페이스의데이터파일은초기화파라미터 DB_CREATE_FILE_DEST에서지정한디렉토리에작성된다. 16~18행째 REDO 로그파일은초기화파라미터 DB_CREATE_ONLINE_LOG_DEST_1및 DB_CREATE_ONLINE_LOG_DEST_2에서지정한디렉토리에멤버가분산되어작성된다. 그룹은각디렉토리에 3개씩작성된다. 제 3 부 : 테이블의설계

25 제 1 장테이블용량의산정 처음 여기서설명하고있는산정방법은, 자료 영역사이즈의견적방법 의생각을약간간략화하고, 계산하기쉽게한방법이다. 또, 지침을내는것을우선해, 플랫폼이나버젼에의한차이를시작으로세세한요소를생략하고있으므로, 여기서해설하고있는견적방법은완벽하게정확하지는않다. 그렇다고해도, 오차는실제의필요용량이보다많은경우에서도산정후에예상할여유치만큼허용할수있을정도이다. 또, 본견적인산정방법은다음의경우에는산정방법을지원하지않는다. 블록사이즈보다긴레코드길이의테이블의산정 LOB 에관한산정 테이블의견적방법개요 테이블의산정순서는, 대략적으로는다음의순서가된다. ( 순서 1) 1 개의레코드의평균길이을요구한다 ( 순서 2) 1 개의블록에들어가는레코드수를요구한다 ( 순서 3) CEIL( 예상레코드수 순서 2 의값 ) 블록사이즈 = 테이블용량 CEIL 는지정된수보다크거나같은최소정수값을반환하는함수 1 개의레코드의평균길이를구하는방법 레코드는대략적으로는그림 1 과같은방식로저장된다. 레코드는레코드헤더가하나와각열의헤더, 및열데이 터로구성된다. 그림 1: 레코드의구조 레코드 헤더 열헤더열데이터열헤더열데이터열헤더열데이터 (1) 레코드헤더의사이즈

26 레코드헤더의사이즈는 3 바이트이다. (2) 열헤더의사이즈 대응하는열의데이터길이가 250 바이트이하 (NULL 포함 ) 의경우는 1 바이트, 251 바이트이상의경우는 3 바 이트이다. (3) 열데이터의사이즈 데이터형에따라서다르다. 주요데이터형에대해서는다음의표 1 을참조한다. 데이터가 NULL 의경우는어 떤데이터형도 0 바이트이다. 표 1: 데이터형에의한실점유사이즈 고정 데이터형 / 가 변 데이터가저장되었을때의길이 고정 바이트수지정시는테이블정의의길이의고정길이 문자수지정시는문자수에대해서고정이지만, 실점유바이트수는 캐릭터 셋및실제의데이터에의해다르다. 예를들면 CHAR / 가 변 KO16KSC5601 의 DB 로 char(6 char) 의컬럼에 AAA 라고 하는데이터를넣으면 3 바이트소비되지만, 아아아 라고하는데 이터를넣으면 6 바이트소비한다. VARCHAR2 가변실제로저장되고있는데이터의길이 ( 바이트수지정시 문자수지정시 ) NCHAR 고정테이블정의문자수의 2 배 (AL16UTF16 지정시 ) NVARCHAR2 가변저장문자수의 2 배 (AL16UTF16 지정시 ) 길이 = 1 + CEIL (n / 2) CEIL 는가장가까운정수값을반환 NUMBER 가변 n 는저장된수치의정수부 소수부를합한총자리수. n>38 의경우에는 38 이다 유효자리수 38 자리수미만의음수의경우는 1 바이트가산 DATE 고정 7 바이트 TIMESTAMP 가변 초의소수부분에데이터가있는경우 :11 바이트고정 초의소수부분에데이터가없는경우 :7 바이트고정

27 TIMESTAMP WITH TIME ZONE 고정 13 바이트 TIMESTAMP LOCAL TIME ZONE WITH 고정 11 바이트 INTERVAL YEAR TO MONTH 고정 5 바이트 INTERVAL DAY TO SECOND 고정 11 바이트 RAW 가변실제로저장되고있는데이터의길이 LONG 가변실제로저장되고있는데이터의길이 LONG RAW 가변실제로저장되고있는데이터의길이 DISABLE IN ROW 지정시 :20 바이트 ENABLE IN ROW 지정으로행내에저장시 : 실데이터길이 +36 바이 BLOB/CLOB/NCLOB 지정 에 의한 트 ENABLE IN ROW 지정으로행이외 (LOB 테이블 ) 에저장시 : 실데이터길이에의해 36~86바이트. 산정시는여유있게 86바이트고정으로계산한다. LOB 테이블의산정에대해서는나중에기술 EMPTY의경우는데이터길이의부분을 0으로계산 BFILE 고정 530 바이트 ROWID 고정 6 바이트 (~Oracle7) 10 바이트 (Oracle8~) 1 개의블록에들어가는레코드수의계산하는법 DB 블록의구조는대략적으로는그림 2 와같다. 블록은블록헤더와데이터저장부, 그리고테이블파라미터 PCTFREE 의지정으로확보한예비영역으로구성됩니다. 그림 2: 블록의구조 (1) 블록헤더의사이즈 블록헤더 블록헤더의사이즈는아래의계산식보다구할수있다. INITRANS 는테이블

28 파라미터이다. 헤더의사이즈 = 90 + (INITRANS - 1) 데이터저장 (2) 예비영역의사이즈 예비영역 예비영역의사이즈는이하의계산식보다구할수있다. PCTFREE는테이블파라미터이다. PCTFREE는단위가 % 이므로, 계산때는소수로고쳐서계산한다. 예비영역의사이즈 = CEIL(( 테이블저장전의테이블스페이스의블록사이즈 - 헤더의사이즈 ) PCTFREE) (3) 데이터저장부분의사이즈데이터저장부분의사이즈는다음의계산식으로구할수있다. 데이터저장부의사이즈 = 테이블저장전의테이블스페이스의블록사이즈- 헤더의사이즈-예비영역의사이즈 이를개선해서 TRUNC( 데이터저장부의사이즈 평균레코드길이 ) 가 1 개의블록에들어가는레코드수가된다. 평균레코드길이가데이터저장부보다긴경우의산정방법 평균레코드길이가데이터저장부분보다긴경우, 당연히레코드는복수의블록에걸쳐저장된다. 이상태를로우체인현상이라부른다. 로우체인현상이발생하는평균레코드길이의경우의테이블용량의산정은, CEIL( 평균레코드길이 데이터저장부분의길이 ) 산정레코드수 블록사이즈가된다. 데이터저장부분의계산시에는, PCTFREE는실제의지정에관련되지않고 0으로서계산해주면된다. 또, 이산정방법은레코드의사이즈의격차가적은경우는유효하지만, 불규칙한경우는실제의레코드의저장순서에실제저장사이즈가큰폭으로영향을받기때문에, 산정값이크게나올가능성이있다. LOB 테이블의산정방법 LOB 테이블의산정은아래의순서와같다. ( 순서 1) CHUNK 의값을블록사이즈의배수값에반올림한다

29 LOB 테이블의파라미터 CHUNK는 LOB의 I/O사이즈가된다. 하지만, CHUNK의사이즈가블록사이즈의배수가아닌경우는, CHUNK의사이즈를초과한다. 제일작은블록사이즈의배수의값이실제의 I/O사이즈가된다. 예를들면 CHUNK가 5 KB, 블록사이즈가 4 KB의경우는, 실 I/O사이즈는 8 KB가되지만. 이경우딕셔너리상에 5 KB는아니고 8 KB가되게된다. ( 순서 2) 평균 LOB 길이를순서 1 의배수값에반올림한다 LOB 의데이터는순서 1 으로요구한 CHUNK 사이즈의배수값에내림하여저장된다. 예를들면평균 LOB 길 이가 28 KB, CHUNK 의사이즈가 8 KB 의경우는 32 KB 가된다. ( 순서 3) LOB 저장영역의사이즈를구한다. LOB 저장공간의사이즈는아래의계산식으로구할수있다. LOB 저장공간의사이즈 = 순서 2 의값 예상레코드수 ( 순서 4) RETENTION(PCTVERSION) 영역을구한다. LOB 테이블에대해서갱신을실행했을경우, 갱신전데이터는 UNDO 세그먼트 (segment) 가아니고, LOB 테이블자신에게저장된다. 따라서, 산정시에이부분을예측할필요가있다. 자동 UNDO 관리를이용하고있는경우는파라미터 RETENTION, 수동 UNDO 관리를이용하고있는경우는파라미터 PCTVERSION로지정한영역이 LOB의 UNDO를위하여확보된다. 시스템중에트랜잭션 (transaction) 이최대한발생할수있는변경전 LOB 데이터가저장가능한크기로예상한다. 잘모르는경우는순서 3에서구한용량의 20% 정도로산정한다. ( 순서 5) 순서 3 과순서 4 의합계가 LOB 테이블의용량이된다. 제 2 장테이블의산정예제 레코드가한개의블록에들어가는길이의경우의산정예 SCOTT schema 에있는 EMP 테이블을예로산정을실제로해보자. 예상건수는 10,000 건으로한다.EMP 테이블

30 의구조는다음과같다. SQL> desc emp 컬럼명 NULL 유무데이터타입 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO NOT NULL NUMBER(4) VARCHAR2(10) VARCHAR2(9) NUMBER(4) DATE NUMBER(7,2) NUMBER(7,2) NUMBER(2) (1) 평균레코드길이를구하자 표 3: 각열의길이의계산예 렬명데이터형렬데이터가길이열헤더가길이합계렬장 EMPNO NUMBER(4) 1 + CEIL (4 / 2) = 3 바이트 1 바이트 4 바이트 ENAME VARCHAR2(10) CEIL (10 * 0.7) = 7 바이트 1 바이트 8 바이트 JOB VARCHAR2(9) CEIL (9 * 0.7) = 7 바이트 1 바이트 8 바이트 MGR NUMBER(4) 1 + CEIL (4 / 2) = 3 바이트 1 바이트 4 바이트 HIREDATE DATE 7 바이트 1 바이트 8 바이트 SAL NUMBER(7,2) 1 + CEIL (7 / 2) = 5 바이트 1 바이트 6 바이트 COMM NUMBER(7,2) 1 + CEIL (7 / 2) = 5 바이트 1 바이트 6 바이트 DEPTNO NUMBER(2) 1 + CEIL (2 / 2) = 3 바이트 1 바이트 3 바이트 레코드길이소계 47 바이트 레코드길이 ( 열헤더 3 바이트를가산 ) 50 바이트 (2) 한개블록에들어가는레코드수를구하자 필요한테이블파라미터는모두디폴트 (INITRANS:1, PCTFREE:10) 으로한다. 블록사이즈는 8 KB(8,192 바이

31 트 ) 로한다. 표 4: 블록의빈영역을구하자 요구하는대상 계산내용 블록헤더의사이 즈 86 + (INITRANS - 1) = 90 + (1-1) = 90 바이트 예비영역의사이 즈 CEIL(( 테이블저장부의테이블스페이스의블록사이즈 - 헤더의사이즈 ) PCTFREE) = CEIL((8,192-90) * 0.1) = 811 바이트 데이터저장영역의 사이즈 테이블저장전의테이블스페이스블록사이즈 - 헤더의사이즈 - 예비영역의사이즈 = 8, = 7,291 바이트 한개블록중에서실제로데이터를저장할수있는영역이 7,291 바이트가구해졌으므로, 이것을 (1) 에서요 구한평균레코드길이 50 바이트로나누는 ( 소수점은버림 ) 것과한개블록에 146 건들어가는것을알수있다. (3) 테이블용량을구하자 (2) 에서부터테이블용량은예상레코드수 (2) 의값 블록사이즈가된다. 실제로계산하면, CEIL(10,000 / 146) * 8,192 = 565,248바이트 = 552킬로바이트가된다. 레코드가한개블록에들어가지않는길이의경우의산정 블록사이즈 8 KB 에대해서 VARACHAR2(3000) 의 3 개열의레코드가 10,000 건삽입되는테이블의용량을추측 한다. 데이터는어느열도항상 3,000 바이트들어오는것으로가정하자. (1) 평균레코드길이를구하자 레코드길이는 (3, ( 열헤더 )) * 3( 열 ) + 3( 레코드헤더 ) = 9,012 바이트가된다. (2) 데이터저장부분의사이즈를구한다 레코드가블록에들어간다면, 7,291 바이트가된다.

32 (3) 테이블용량을구하자테이블용량의계산방법은 CEIL( 평균레코드길이 데이터저장부분의길이 ) 예상레코드수 블록사이즈이므로, CEIL(9,012 / 7,291) * 10,000 * 8,192 = 163,840,000바이트 = 약 164 메가바이트가됩니다. LOB 테이블의견적 블록사이즈 8 KB 에대해서평균 1 MB 의 LOB 를 1000 건저장하는 LOB 테이블의산정예이다. RETENTION 로 필요한영역은 LOB 저장영역의 20%, DISABLE STORAGE IN ROW 지정으로 1000 건모두가 LOB 테이블에저장 되는것으로가정하자. CHUNK 는 3 KB 로한다. (1) CHUNK 의값을블록사이즈의배수에반올림한다 CHUNK 가 3 KB 로블록사이즈가 8 KB 이므로, 8 KB 가된다 (2) 평균 LOB 길이를 (1) 의배수에반올림한다 1 MB 는 8 KB 의배수이므로, 1 MB 그대로이다. (3) LOB 저장영역의사이즈를구하자 LOB 저장영역의사이즈는 LOB 저장영역 = (2) 의값 예상레코드수이므로, 1,048,576 * 1,000 = 1,048,576,000바이트 = 1,000 메가바이트가됩니다. (4) RETENTION 영역을구해서더한테이블용량으로하자. RETENTION 영역은 LOB 저장영역의 20% 이므로, (3) 의값을 1.2 배가되었다 1,200 메가바이트 (MB) 가산정 결과가된다. 제 3 장열의나열순서에대한지침

33 열 ( 컬럼 ) 의나열순서의기본지침 열의나열순서는기본적으로관리가하기쉬운것을염두에두어설계한다. 예를들면아래와같은점에유의 하자. 주키 (PK) 를처음에정의하자 관련성이있는열 ( 예 : 성과이름, 부코드와과코드등 ) 은연속적으로정의한다 자주이용되는열을앞쪽에배치하자 레코드길이을짧게하는테크닉 레코드길이을짧게할수있으면있을수록, 블록에저장할수있는레코드수가많아진다. 그리고, 캐쉬의히트 율또한높아지고, 용량의절약도가능하게된다. (1) 가변길이데이터형의이용 예를들면우편번호와같이자리수가정해져있는데이터를저장하는열을제외하고, VARCHAR2 와같은가 변길이을이용하도록하자. 정의된길이에대해실데이터가짧으면짧을수록용량의낭비를막을수있다. (2) NULL값이저장되기쉬운열을뒤쪽에정의하자 NULL가되는것이많은열은정리해서뒤로배치하면, 열헤더가생략되기때문에레코드길이를짧게할수있다. ( 그림 ) NULL값의컬럼을뒤로정의한결과행의길이가짧아졌다. 제 4 장 CREATE TABLE 의파라미터의설정

34 본장은로컬관리테이블스페이스를이용하고있는것을전제로진행한다. 로컬관리와딕셔너리관리로의미가다른파라미터 이하의파라미터는로컬관리와딕셔너리관리로의미가다르다. INITIAL NEXT PCTINCREASE MAXEXTENTS 표 5 에서차이를설명하고있다. 로컬관리테이블스페이스에있어파라미터의의미가크게다른것은, 크기를 지정하는파라미터 (INITIAL/NEXT) 가, extent 의사이즈를나타내는것이아니라, 초기에확보하는총테이블용 량이되는것이다. 표 5: 로컬관리와딕셔너리관리로의미가달라지는파라미터 파라미터딕셔내리관리로컬관리 INITIAL 초기에확보하는 extent 의사이즈. 예를들 면 100 MB 의 INITIAL 를지정하면, 1 개의 초기에확보하는 extent 의총사이즈. 예를들면 1 MB 의 UNIFORM 으로지정된테이블스페이 NEXT 두번째의 extent 의사이즈. 예를들면 70 MB 의 NEXT 를지정하면, 두번째의 extent MINEXTENTS 가 2 개이상일때에 INITIAL 와 NEXT 의합계의사이즈가되도록하나이상의 PCTINCREASE 세번째이후의 extent 를확보할때의, 전에 할당받은 extent 사이즈로부터의증분. 디 MINEXTENTS 가 3 개이상일때에, 딕셔너리 관리와같은계산방법으로하나이상의 extent

35 의 extent 의 40% 증가로 196 MB 가된다 = 536 개의 1 MB 의 extent 를확보된다. MAXEXTENTS 지정한수보다많은 extent 를작성할수 없게된다. 의미는딕셔내리관리와같지만, 지정해도무시 되어항상 UNLIMITED 가된다. 테이블작성시로지정을고려해야할파라미터 로컬관리테이블스페이스에있어서는, 항상지정해야할파라미터는기본적으로 INITIAL 만이라고생각하는 것이일반적이지만. 물론이것은틀린말이아니다. 상당히처리량이많은경우를제외하고는어느정도다른 파라미터를고려하지않아도상관이없다 INITIAL 는가능한한예상레코드수를수용할만한 크기를지정한다. 로컬관리테이블스페이스에서 extent 의수가많은것자체는퍼포먼스에별로영향을주지않지만, extent 의확장의경우딕셔너리관리테 이블스페이스정도는아니지만, 어느정도의부하가발생한다. 경우에의해지정을고려하는파라미터 (1) NEXT/MINEXTENTS/PCTINCREASE 대규모테이블의경우, extent를복수의데이터파일에분산시켜 I/O성능의향상을도모하는케이스가있다. 이와같은경우에이파라미터사용을검토한다. 그때 PCTINCREASE는디폴트의 0인편이영역계산이하기쉽다. (2) FREELIST GROUPS 디폴트로 1 인그대로두어도상관없다. RAC 환경에서는노드수에맞춘값을기본하자. (3) FREELISTS 디폴트의 1 그대로두어도상관없다. 대량삽입의트랜잭션 (transaction) 이동시에발생하는경우는값을 늘리는것을검토하자. (4) PCTFREE 초기레코드사이즈로부터최종레코드사이즈가길어질수있는비율을설정하는것이제일효율적인영역관리를할수있다. 예를들면 INSERT시의레코드의평균사이즈가 100바이트로, 갱신을거듭해최종적으로평균 130바이트가되는것이면 30(%) 을지정하면좋다. 레코드사이즈의증가율을알수없는경우는, 디폴트의 10으로운용해, 재편성시에값을조정한다. 읽기전용의테이블이라면 0으로해도상관

36 없다. (5) PCTUSED 기본적으로디폴트의 40으로상관하지않습니다.PCTUSED의값을비싸게하면블록의재이용이해져서싸지므로영역을효율적으로이용할수있습니다만, FreeList에블록이많이등록되기쉽기때문에갱신계의퍼포먼스가떨어집니다. 낮게하면퍼포먼스는오르지만영역의이용효율은떨어진다. 퍼포먼스를우선시키는경우는보다낮게, 영역의이용효율을우선시키는경우는보다조금높게설정한다. 또, 풀테이블스캔의퍼포먼스를높이고싶은경우는데이터를채워저장하는것이좋기때문에높은값으로설정한다. 덧붙여 PCTFREE 와 PCTUSED 의값의합계는 100 을넘을수없기때문에, 이범위에서조정하도록한 다. (6) INITRANS 디폴트의 1 인채로상관하지없지만복수의트랜잭션 (transaction) 으로동시에같은블록에의갱신이빈번 한경우는, 값을늘리는것을검토하자 (7) LOGGING/NOLOGGING 테이블에대한처리로 NOLOGGING 옵션이효과가있는것은아래의작업에서이다. 이러한처리의속도를올리고싶은경우는 NOLOGGING의지정을검토하자. 다만, REDO 로그에처리내용이기록되지않기때문에, 이러한처리를실시한다음은백업을하는것을권고한다. 다이렉트 로드 (SQL*Loader) 다이렉트 로드 인서트 (APPEND 힌트포함의 INSERT SELECT) CREATE TABLE... AS SELECT ALTER TABLE... MOVE PARTITION ALTER TABLE... SPLIT PARTITION LOB 테이블에저장되는 NOCACHE NOLOGGING 모드의 LOB의 INSERT, UPDATE 및 DELETE 제 4 부 : 인덱스의설계 제 1 장인덱스용량의견적 여기서설명하고있는산정방법은, 앞에서와같이자료 영역사이즈의견적방법 의방법을약간간략화해서, 계산 하기쉽게한방법이다. 또, 지침을내는것을우선해서, 플랫폼이나버젼에의한차이를시작으로세세한요소를생략

37 하고있으므로, 여기서설명하고있는산정방법은완벽하게정확하지는않다. B-Tree 인덱스의산정방법개요 인덱스의산정순서는대략적으로아래의순서가된다. ( 순서 1) 1개의레코드의평균길이을구한다 ( 순서 2) 1개의블록에들어가는레코드수를구한다 ( 순서 3) CEIL( 예상레코드수 1.05 순서 2의값 ) 블록사이즈가인덱스용량이된다 ( 순서 4) 빈테이블에대해서인덱스를작성하는경우는순서 2의값 1.3 이인덱스용량이된다 순서 4가필요하게되는이유는, 순서 3까지방법으로구할수있는용량이이미예상건수의레코드가저장되고있는테이블에대해서인덱스를작성했을경우의용량이기때문이다. 빈테이블과인덱스를작성하고나서예상건수의레코드를삽입했을경우, 어떠한값을가지는레코드가어떠한차례로삽입될지를사전에알지못한다. 그때문에, 레코드가이미존재하고있는상태로부터인덱스를작성했을경우에비하면, 블록내에서의레코드의저장효율이떨어져버린다. 덧붙여곱해지는수 1.3은필자의경험에의하는것이라는것을말해둔다. 저장되는데이터의특성이나차례로따라더욱여유공간을갖는것이좋은케이스가될지도모르지만. 1.1 정도의값이들어가는케이스도있다. 1 개레코드의평균길이의구하는방법 인덱스의레코드는대략적으로는그림 1 과같은이미지로저장된다. 그림 1:B-Tree 인덱스의레코드의구조 레코드 헤더 ROWID 오버 헤드 열헤더열데이터열헤더열데이터열헤더열데이터 (1) 레코드헤더의사이즈 레코드헤더의사이즈는 3 바이트입니다. (2) ROWID 의사이즈 Oracle9i 의본래의 ROWID 의사이즈는 10 바이트지만, 색인레코드중의 ROWID 는 Oracle7 때의 6 바이트의포맷으 로저장된다. 다만, 글로벌파티션 인덱스의경우는 10 바이트가된다.

38 (3) 오버헤드의사이즈 (127 바이트이하의열수 1) + (128 바이트이상의열수 2) + 4 바이트가됩니다. 인덱스의경우는 1 바이트가 더필요하다. (4) 열헤더의사이즈 대응하는열의데이터길이가 250 바이트이하의경우는 1 바이트, 251 바이트이상의경우는 3 바이트이다. (5) 열데이터의사이즈 데이터형에따라서다르다. 주요데이터형에대해서는앞서설명한테이타형의표를참조하자. 주의점으로서 (1)+(4)+(5) 의합계 ( 복합인덱스의경우는전인덱스대상열의 (4)+(5) 가대상 ) 가 9 바이트에못미친경우 는 9 바이트로반올림해준다. 1 블록에들어가는레코드수의구하는방법 DB 블록의구조는대략적으로는그림 2 와같다. 인덱스에서도테이블과다르지않지만. 블록은블록헤더와데이터저장부, 그리고인덱스파라미터 PCTFREE 의지정으로확 보한예비영역으로구성된다. 그림 2: 블록의구조 블록헤더 (1) 블록헤더의사이즈 블록헤더의사이즈는아래의계산식보다구할수있다.INITRANS 는인덱스파라미 터이다. 헤더의사이즈 = INITRANS 데이터저장부 예비영역 (2) 예비영역의사이즈

39 (3) 데이터저장부분의사이즈데이터저장부분의사이즈는아래의계산식으로부터구할수있다. 데이터저장부분의사이즈 = 인덱스저장테이블스페이스의블록사이즈-헤더의사이즈-예비영역의사이즈 이상의값으로부터 TRUNC( 데이터저장부분의사이즈 평균레코드길이 ) 가 1 개블록에들어가는레코드수가된다. TRUNC 는소수를버리는함수이다. LOB 인덱스의산정방법 Oracle9i에서 LOB 인덱스는 LOB 테이블을생성했을때에 LOB 테이블과같은테이블스페이스에 Oracle이내부적으로생성한다. 따라서자동으로생성되기때문에용량을간과하기쉽지만, LOB 인덱스의산정도정확하게하도록한다. LOB 인덱스의용량은 LOB 테이블의최저5% 의크기를 Oracle이자동으로확보한다. 그러므로 LOB 인덱스의용량은 LOB 테이블의용량의 5% 로추측하면된다. 비트맵 인덱스의산정방법 비트맵 인덱스의산정에대해서는, 실제로들어오는데이터의내용이나, 데이터압축의상황에따라실용량이좌우되 기때문에, 사전에추측하는게힘들다. 단지, 같은열에대해서작성한 B-Tree 인덱스보다는작아지므로, 큰견적이 되어버려서 B-Tree 인덱스의산정법을이용하자. 제 2 장인덱스의산정예 B-Tree 인덱스의산정법 SCOTT schema 에있는 EMP 테이블을예로, ENAME 열에 B-Tree 인덱스를작성했을경우의산정을실제로해보 자. 예상건수는 10,000 건으로한다. EMP 테이블의정의는아래와같다.

40 이므로, SQL> desc emp 이름 NULL? 형 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO NOT NULL NUMBER(4) VARCHAR2(10) VARCHAR2(9) NUMBER(4) DATE NUMBER(7,2) NUMBER(7,2) NUMBER(2) (1) 평균레코드길이를구하자이번은 ENAME열의평균열의길이가 7바이트로가정해계산한다. 이것에열헤더사이즈 1바이트를더한 8바이트가열의평균길이가된다. 레코드헤더의사이즈를더하면 9바이트가넘기때문에, 8바이트의길이로계산을계속하자. 이것에레코드헤더 3바이트, ROWID6 바이트, 오버헤드 6바이트 ( , 최초의 1바이트는열의오버헤드, 4는고정치, 마지막 1은인덱스의몫이다 ) 을더한 23바이트가평균레코드길이가된다. (2) 1 개의블록에들어가는레코드수를구하자 필요한인덱스파라미터는모두디폴트 (INITRANS:2, PCTFREE:10) 으로한다. 블록사이즈는 8 KB(8,192 바이트 ) 로한다 표 1: 블록의빈공간을구하자 요구하는대상 계산내용 블록헤더의사이 즈 INITRANS = = 144 바이트 예비영역의사이 즈 CEIL(( 인덱스저장공간의테이블스페이스의블록사이즈 - 헤더의사이즈 ) PCTFREE) = CEIL((8, ) * 0.1) = 805 바이트 데이터저장부분의 사이즈 인덱스저장공간의테이블스페이스의블록사이즈 - 헤더의사이즈 - 예비영역의사이즈 = 8, = 7,243 바이트 1 개블록중에서실제로데이터를저장할수있는영역이 7,243 바이트가구해졌으므로, 이것을 (1) 에서구한평 균레코드길이 23 바이트로나누면 ( 소수점은버린다 ) 1 블록에 314 건들어가는것을알수있다. (3) 인덱스용량을구하자 인덱스용량의계산방법은 CEIL( 예상레코드수 1 블록에들어가는레코드수 ) 블록사이즈

41 제 5 부 : 영역감시 제 1 장영역감시의대상 영역감시라고하는것은그목적과수단에따라서여러가지가존재한다. 본장에서는그중에서도이른바 단 편화 라고하는키워드에주목해서, 단편화의발생을감시한다고하는관점에서각종영역감시의대상 방법에대 해설명하기로한다. 감시를실시하고문제를찾아낸후, 그문제를해결하는방법에대해서는 6 부에서설명하기 로한다. 이번장은 SQL 가많이실행되고있고, 특별한기술이없는한 SYSTEM 유저로실행한다. 다른유저로 실행하는경우, 검색대상의딕셔너리나권한등을부여해주세요 단편화란? Oracle 에있어서의단편화란, Oracle 의물리영역이어떠한형태로불연속이되어있는상태를말한다. 단편화가 발생하면, 발생의정도로따라주로이하와같은영향이나온다. 영역이효율적으로이용되지않고, 실데이터량에비해디스크용량을많이차지한다 I/O 시간이걸려, 퍼포먼스가떨어진다 한마디로단편화라고하여, Oracle 의경우 DB 의물리구조가계층구조가되어있어, 계층에따라나타나는단편 화의현상에차이가있습니다. 표 1 으로 DB 의물리구조에대응한단편화현상을정리했다. 다음장이후에각각 의단편화현상에대하고설명을할것이다. 표 1:Oracle 의물리구조와단편화 계층 단편화현상 데이터파일 ( 테이블스페이스 ) 파일레벨의단편화 데이터파일레벨의미사용영역의발생 수위가높은하이워터마크 세그먼트 (segment) 세그먼트 (segment) 레벨의미사용영역의발생 계층이깊은인덱스 extent 불연속의 extent 행이행 블록 행 chaining 블록내의미사용영역의발생

42 제 2 장데이터파일 ( 테이블스페이스 ) 레벨의단편화 파일레벨의단편화 아무리블록레벨이나세그먼트 (segment) 레벨이라고한세세한레벨로단편화를해소해도, 데이터파일의레벨로단편화가발생하고있으면의미가없다. 특히하나의디스크에데이터베이스가들어가, 초기의물리설계의시스템에대해서빈번한단편화가발생해, 시스템의퍼포먼스를떨어뜨릴가능성이있다. 또, 테이블스페이스의설정을자동확장으로지정으로하고있었을경우, 파일레벨의단편화가일어나기쉬워진다. 파일레벨의단편 화의상황은 OS 레벨과관련있으므로, 예를들면 Windows 이면 defrag( 조각모음 ) 기능을이용해조사하면된 다. 하이워터마크란? 이의미대로, Oracle 에있어서의 HWM 는과거에데이터가저장된것이있는제일높은 ( 마지막 ) 위치를나타내는 지표가된다. 하이워터마크의개념은데이터파일과세그먼트 (segment) 에존재합니다. 그림 1: 하이워터마크의개념 데이터파일레벨의미사용영역의발생 ~HWM 이후의미사용영역 ~ 데이터파일내의미사용영역은, HWM 이후의미사용영역및 HWM 이전의세그먼트 (segment) 간의미사용영 역으로부터구성이된다. HWM 이후의미사용영역은데이터의검색이나갱신에는영향을주지않지만, 저장하 고있는데이터용량에비교해데이터파일의사이즈가크기때문에, 실데이터량에비교해백업 restore 에시 간을더필요로하게된다. 그한편, 장래의데이터파일의확장을막기위해서사전확보하고있는케이스도있으므로, 통틀어 HWM 이후의미사용영역이큰것이나쁜것이라고는말할수없다. 장래의데이터의증가예상과의균형으로사이즈의조정을실시해주길바란다. 어떤 A 테이블스페이스에존재하는데이터파일의 HWM 이후의미사용영역의합계에관한정보는이하의 SQL으로파악이가능하다. < 테이블스페이스의데이터파일마다 HWM 이후의미사용영역의사이즈를구하자 >

43 select sumdf.file_name 데이터파일명 ", to_char(sumdf.total_bytes, 'FM999,999,999,990') 테이블스페이스의사이즈 ", to_char(sumfs.free_bytes, 'FM999,999,999,990') "HWM 이후의미사용공간사이즈 " from (select df.file_id, df.file_name, sum(df.bytes) total_bytes from dba_data_files df where df.tablespace_name = upper('&tsname') group by df.file_id, df.file_name) sumdf left outer join (select fs.file_id, fs.bytes free_bytes from (select fs2.file_id, fs2.bytes, fs2.block_id, max(fs2.block_id) over (partition by fs2.file_id) max_block from dba_free_space fs2 where fs2.tablespace_name = upper('&tsname')) fs where fs.block_id = fs.max_block) sumfs on (sumdf.file_id = sumfs.file_id); tsname 에값을입력해주세요 : USERS3 구 6: where df.tablespace_name = upper('&&tsname') 신 6: where df.tablespace_name = upper('users3') 구 12: where fs2.tablespace_name = upper('&&tsname')) fs 신 12: where fs2.tablespace_name = upper('users3')) fs 데이터파일명 테이블스페이스의사이즈 HWM 이후의미사용영역의사이즈 D: ORACLE ORADATA TEST1 USERS03.DBF 786,432,000 34,504,704 D: ORACLE ORADATA TEST1 USERS03_2.ORA 134,217, ,464,064 tsname 에는테이블스페이스명을입력해한다. DBA_FREE_SAPCE 딕셔너리의각데이터파일마다블록 ID 가 제일큰빈공간을추출하고있다. 덧붙여서, 어떤 A 테이블스페이스영역전체의미사용공간을구하는 SQL 도구해보았다.. <특정테이블스페이스의미사용공간의총사이즈를구하는 SQL 문> select to_char(sumdf.total_bytes, 'FM999,999,999,990') 테이블스페이스의사이즈 ", to_char(sumdf.total_bytes - sumfs.free_bytes, 'FM999,999,999,990') " 사용중인영역의사이즈 ", to_char(sumfs.free_bytes, 'FM999,999,999,990') " 미사용영역의사이즈 ",

44 to_char((nvl(sumfs.free_bytes, 0) / sumdf.total_bytes) * 100, 'FM990.99') '% 사용율 " from (select df.tablespace_name, sum(df.bytes) total_bytes from dba_data_files df group by df.tablespace_name) sumdf left outer join (select fs.tablespace_name, sum(fs.bytes) free_bytes from dba_free_space fs group by fs.tablespace_name) sumfs on (sumdf.tablespace_name = sumfs.tablespace_name) where sumdf.tablespace_name = upper('&tsname'); 데이터파일레벨의미사용영역의발생 ~HWM 이전의세그먼트 (segment) 간의미사용영역 ~ 데이터파일내의미사용영역은, HWM 이후의미사용영역및 HWM 이전의세그먼트 (segment) 간의미사용영역으로부터구성된다. 여기에서는후자에대해설명하고있다. 세그먼트 (segment) 간의미사용영역은물리단위에서는 extent가된다. 로컬관리테이블스페이스에서, 특히 UNIFORM 사이즈지정의경우는이러한영역도효율적으로이용되지만, AUTOALLOCATE 지정의경우는미사용인채남을가능성이있다. HWM 이전의세그먼트 (segment) 간에어느정도의미사용 extent가존재하는지에대해서는아래의 SQL문으로구할수있다. <HWM 이전의세그먼트 (segment) 간의미사용영역을구하는 SQL 문> select sumdf.file_name 데이터파일명 ", to_char(sumfs.free_bytes, 'FM999,999,999,990') " 미사용영역의사이즈 " from (select df.file_id, df.file_name from dba_data_files df where df.tablespace_name = upper('pos_data')) sumdf left outer join (select fs.file_id, fs.bytes free_bytes from (select fs2.file_id, fs2.bytes, fs2.block_id, max(fs2.block_id) over (partition by fs2.file_id) max_block from dba_free_space fs2 where fs2.tablespace_name = upper('pos_data')) fs where fs.block_id <> fs.max_block) sumfs on (sumdf.file_id = sumfs.file_id); 제 3 장세그먼트 (segment) 레벨의단편화

45 위치가비싼하이워터마크 HWM 에대한속성중의하나는 HWM 는자동에서는결코내리지않는다고하는점이다, 예를들면전체레코드 를 DELETE 문으로삭제했다고해도, HWM 의위치는그대로이다. 그림 2:DELETE 로움직이지않는하이워터마크 세그먼트 (segment) 레벨의 HWM 는주로 풀테이블스캔 다이렉트 로드 / 다이렉트 로드 인서트 에영향을준다. (1) HWM의풀스캔에대한영향테이블이나인덱스의풀스캔을실행하는경우, 실제의스캔범위는테이블이나인덱스전체가아니고, HWM 의위치까지를스캔한다. 이것에의해, 그림 3의윗부분과같이, 실제로용량을확보하고있는사이즈에비교해데이터량이적은경우의처리시간을단축하고있다. 그러나, HWM가자동으로내리지는않는다. 그때문에, 일단많이데이터가들어가있는상태로부터대량삭제가있으면, 그림 3의밑부분과같이, 실제로는데이터가들어가있지않음에도불구하고 HWM의위치까지스캔해버려, 실데이터량에비해더많은검색시간이걸려버린다. 그림 3: 하이워터마크의풀스캔시의영향

46 (2) HWM의다이렉트처리에대한영향다이렉트 로드나다이렉트 로드 인서트는 INSERT문에의해데이터를삽입하는것이아니라, 먼저블록에저장된포맷이미지를작성해, 그블록이미지를직접쓴다. 그때문에조금이라도데이터가들어가있는블록에는쓸수가없다. HWM 이후의블록은빈것으로간주되고있기때문에, 다이렉트처리는그림 4의상부와같이 HWM 이후의블록에데이터를쓴다. 따라서이러한처리에의해서다이렉트처리는퍼포먼스를확보하고있다. 덧붙여서패러렐 다이렉트 로드때는 HWM 이후의 extent로부터쓴다. 만약 HWM 이전의영역에빈곳이많은경우, 그림 4의하부와같이세그먼트 (segment) 내에큰빈공간이생기게된다. 만약이테이블에다이렉트처리에의한데이터삽입밖에없는경우, 이빈공간은사용하지없는채남아버린다. 그림 4: 하이워터마크의다이렉트처리에의영향 (3) HWM의위치를아는방법 HWM의위치를알려면 DBMS_SPACE 패키지에있는 UNUSED_SPACE 프로시저를이용한다. 이프로시저를이용하기위해서는, ANALYZE 혹은 ANALYZE ANY 시스템권한이필요하다. 다만, 실제로 ANALYZE를실시하는것은아니기때문에, RBO 로운용하고있는시스템에서도이용가능하다. 또, 빈공간관리를 FREELIST가아닌자동세그먼트 (segment) 관리 (ASSM) 를이용하고있는경우는, UNUSED_SPACE 프로시저는아닌 SPACE_USAGE프로시저를이용하지않으면잘못된결과가나와버린다.

47 다음에 UNUSED_SPACE 프로시저의이용예를이용해 HWM 의위치를구해보자. SQL> set serveroutput on SQL> declare 2 v_total_blocks number; 3 v_total_bytes number; 4 v_unused_blocks number; 5 v_unused_bytes number; 6 v_last_used_extent_file_id number; 7 v_last_used_extent_block_id number; 8 v_last_used_block number; 9 begin 10 dbms_space.unused_space(upper('&uname'), upper('&sename'), '&stype', 11 v_total_blocks, v_total_bytes, v_unused_blocks, v_unused_bytes, 12 v_last_used_extent_file_id, v_last_used_extent_block_id, v_last_used_block); 13 dbms_output.put_line('hwm가있는데이터파일의 ID :' 14 to_char(v_last_used_extent_file_id, '9,999,990')); 15 dbms_output.put_line('hwm가있는 extent의시작블록 ID:' 16 to_char(v_last_used_extent_block_id, '9,999,990')); 17 dbms_output.put_line('hwm가있는블록의위치 :' 18 to_char(v_last_used_block, '9,999,990')); 19 end; 20 / uname 에값을입력해주세요 : SCOTT sename 에값을입력해주세요 : CUSTOMERS stype 에값을입력해주세요 : TABLE 구 10: dbms_space.unused_space(upper('&uname'), upper('&sename'), '&stype', 신 10: dbms_space.unused_space(upper('scott'), upper('customers'), 'TABLE', HWM 가있는데이터파일의 ID : 11 HWM가있는 extent의시작블록 ID : 1,033 HWM 가있는블록의위치 : 6 PL/SQL 프로시저가정상적으로완료했습니다 위의 SQL 스크립트의실행에서, uname 에는세그먼트 (segment) 소유자명을, sename 에는세그먼트 (segment) 명을,

48 stype 에는세그먼트 (segment) 의타입 (TABLE/TABLE PARTITION/TABLE SUBPARTITION/INDEX/INDEX PARTITION/INDEX SUBPARTITION/CLUSTER/LOB 의어느쪽이든 ) 을입력한다. SQL 스크립트의실행결과를설명하면, HWM가있는데이터파일의 ID 는, 프로시저의 V_LAST_USED_EXTENT_FILE_ID파라미터의값이된다.DBA_DATA_FILES 딕셔너리의 FILE_ID열이 V_LAST_USED_EXTENT_FILE_ID파라미터와일치하는데이터파일중에 HWM가존재하는것을나타내고있다. HWM가있는 extent의시작블록 ID 는, 프로시저의 V_LAST_USED_EXTENT_BLOCK_ID파라미터의값이된다.???_EXTENTS(??? 는DBA/ALL/USER) 딕셔너리의 FILE_ID열이 V_LAST_USED_EXTENT_FILE_ID와같고, 한편 BLOCK_ID열이 V_LAST_USED_EXTENT_BLOCK_ID와일치하는익스텐트중에 HWM가존재하는것을나타내고있다. HWM가있는블록의위치 는프로시저의 V_LAST_USED_BLOCK 파라미터의값이된다. 그림 5: 하이워터마크의위치 세그먼트 (segment) 레벨의미사용영역의발생 ~HWM 이후의미사용영역 ~ HWM 이후의미사용영역은다음과같은방법으로파악이가능합니다. (1) DBMS_SPACE.UNUSED_SPACE 프로시저를이용한다바로전에기술된 DBMS_SPACE.UNUSED_SPACE 프로시저를이용하는것으로세그먼트 (segment) 의 HWM 이후의미사용영역의크기를계산할수있다. <HWM 이후의미사용영역을구하는실행문 (1)> SQL> set serveroutput on SQL> declare 2 v_total_blocks number; 3 v_total_bytes number; 4 v_unused_blocks number; 5 v_unused_bytes number; 6 v_last_used_extent_file_id number; 7 v_last_used_extent_block_id number;

49 8 v_last_used_block number; 9 begin 10 dbms_space.unused_space(upper('&uname'), upper('&sename'), '&stype', 11 v_total_blocks, v_total_bytes, v_unused_blocks, v_unused_bytes, 12 v_last_used_extent_file_id, v_last_used_extent_block_id, v_last_used_block); 13 dbms_output.put_line(' 현세그먼트 (segment) 용량 :' 14 to_char(v_total_bytes, '999,999,999,990') ' 아르바이트 '); 15 dbms_output.put_line(' 소비용량 :' 16 to_char(v_total_bytes - v_unused_bytes, '999,999,999,990') ' 아르바이트 '); 17 dbms_output.put_line(' 나머지용량 :' 18 to_char(v_unused_bytes, '999,999,999,990') ' 아르바이트 '); 19 dbms_output.put_line(' 소비율 : ' 20 to_char((v_total_bytes - v_unused_bytes) / v_total_bytes * 100, '990.99') ' %'); 21 end; 22 / uname 에값을입력해주세요 : SCOTT sename 에값을입력해주세요 : CUSTOMERS stype 에값을입력해주세요 : TABLE 구 10: dbms_space.unused_space(upper('&uname'), upper('&sename'), '&stype', 신 10: dbms_space.unused_space(upper('scott'), upper('customers'), 'TABLE', 현세그먼트 (segment) 용량 : 9,437,184 바이트 소비용량 : 8,593,408 바이트 나머지용량 : 843,776 바이트 소비율 : % PL/SQL 프로시저가정상적으로완료했습니다. 위의 SQL 의실행에서, uname 에는세그먼트 (segment) 소유자명을, sname 에는세그먼트 (segment) 명을, stype 에는 세그먼트 (segment) 의타입 (TABLE/TABLE PARTITION/TABLE SUBPARTITION/INDEX/INDEX PARTITION/INDEX SUBPARTITION/CLUSTER/LOB) 을입력한다. (2) ANALYZE 실행후딕셔너리를참조한다.

50 테이블의경우는 ANALYZE 를실행한후의 XXX_TABLES 딕셔너리의 BLOCKS 와 EMPTY_BLOCKS 의값을 검색하는것으로 HWM 이후의미사용영역의크기가구할수있다.BLOCKS 는세그먼트 (segment) 내의사용 이끝난블록수, EMPTY_BLOCKS 는세그먼트 (segment) 내의미사용블록수 (HWM 이후 ) 를나타낸다. <HWM 이후의미사용영역을구하는실행문 (2)> SQL> analyze table scott.customers compute statistics; 테이블이분석되었습니다. SQL> select to_char((blocks + empty_blocks) * 8192, 'FM999,999,999,990') as " 테이블용량 ", 2 to_char(empty_blocks * 8192, 'FM999,999,999,990') as " 나머지용량 " 3 from dba_tables where owner = '&uname' and table_name = '&tname'; uname 에값을입력해주세요 : SCOTT tname 에값을입력해주세요 : CUSTOMERS 구 3: from dba_tables where owner = '&uname' and table_name = '&tname' 신 3: from dba_tables where owner = 'SCOTT' and table_name = 'CUSTOMERS' 테이블용량나머지용량 ,428, ,776 상기는테이블의사용량에관해서 SQL 의실행에임하여, uname 에는세그먼트 (segment) 소유자명을, tname 에는 테이블명을입력한다. 세그먼트 (segment) 레벨의미사용영역의발생 ~HWM 이전의미사용영역 ~ HWM 이전의미사용영역의영향은, 본장의처음에설명한 HWM 의설명을참조하자. 계산방법은, 테이블만의 방식이지만, 아래와같이 SQL 으로산출할수있다. <HWM 이전의미사용영역을구하자 > SQL> analyze table scott.customers compute statistics; 테이블이분석되었습니다.

51 SQL> select to_char(avg_space * blocks, 'FM999,999,999,999') " 빈영역 " 2 from dba_tables where owner = '&uname' and table_name = '&tname'; uname 에값을입력해주세요 : SCOTT tname 에값을입력해주세요 : CUSTOMERS 구 2: from dba_tables where owner = '&uname' and table_name = '&tname' 신 2: from dba_tables where owner = 'SCOTT' and table_name = 'CUSTOMERS' 빈영역 ,264 위의테이블의사용량에관해서 SQL 의실행에대해, uname 에는세그먼트 (segment) 소유자명을, tname 에는테이 블명을입력한다. 계층이깊은인덱스 B*Tree 인덱스는계층구조가되어있습니다. 이계층이깊으면검색에시간이걸리게된다. 계층의깊이는, 인덱스를 ANALYZE 한뒤, INDEX_STATS 딕셔너리의 HEIGHT열, 이것이없다면 xxx_indexes 딕셔너리의 BLEVEL 컬럼으로알수있다. 이러한컬럼의값이 4 이상일경우는, 인덱스를이용한검색의퍼포먼스에영향을주기때문에재구성을해주어야한다. 참고로 INDEX_STATS 딕셔너리를검색하는경우는, VALIDATE STRUCTURE 옵션으로 ANALYZE를실행할필요가있다. 제 4 장 extent 레벨의단편화 불연속의 extent 어떤 A 세그먼트 (segment) 를구성하는 extent가연속해서확보되어있지않아도퍼포먼스에거의영향을주지않는다. 단지, 불연속의 extent간의타세그먼트 (segment) 의 extent가미사용 extent가되었을경우, 특히딕셔너리관리테이블스페이스에서세그먼트 (segment) 마다개별의 INITIAL/NEXT/PCTINCREASE를지정해있는경우는공간의낭비가발생하기쉽다. 로컬관리의경우는이러한일이일어나기힘들고, 이를신경쓰지않아도된다. 이를검시하고싶은경우는아래와같은스크립트로검사를할수있다. <extent 의연속여부를조사한다 > select ext2.extent_id "extent ID",

52 ext2.file_id 파일ID", ext2.block_id 개시블록ID", ext2.blocks " 블록수 ", case when ext2.extent_id = 0 then 처음의 EXTENT입니다 when ext2.old_fid <> ext2.file_id then 데이터파일이다릅니다 when ext2.old_blk_id <> ext2.block_id then 불연속의 EXTENT입니다 else ' 연속하는 EXTENT입니다 end 단편화상황 from (select ext1.extent_id, ext1.file_id, ext1.block_id, ext1.blocks, lag(ext1.file_id, 1) over (order by ext1.extent_id) old_fid, lag(ext1.block_id, 1) over (order by ext1.extent_id) + lag(ext1.blocks, 1) over (order by ext1.extent_id) old_blk_id from dba_extents ext1 where ext1.owner = '&uname' and ext1.segment_name = = '&sname') ext2; uname 에값을입력해주세요 : SCOTT sname 에값을입력해주세요 : C3 구 13: where ext1.owner = '&uname' and ext1.segment_name = '&sname') ext2 신 13: where ext1.owner = 'SCOTT' and ext1.segment_name = 'C3') ext2 extent ID 파일 ID 개시블록 ID 블록수단편화상황 선두의 extent 입니다 데이터파일이다릅니다 데이터파일이다릅니다 불연속의 extent 입니다 연속하는 extent 입니다 10 행이선택되었습니다. 제 5 장블록레벨의단편화

53 행이행 (ROW MIGRATION) 블록에공간을차지하고있는레코드에대해서갱신을했을때에, PCTFREE로확보한영역을가지고있어도원래존재하고있던블록에들어가지않을경도의크기라면 Oracle는해당레코드를다른블록에저장한다. 이때, 원래의블록에새로운저장영역의포인터를남긴다. 이러한상태를행이행 ( 로우마이그레이션 ) 이라고부른다. 행이행이발생하면해당레코드는본래 1 블록에들어가는길이에서만나도 2 블록에걸쳐서저장된다. 즉, 이레코드에액세스하기위해서는 2개의블록을읽어들일필요가있는것이다. 행이행이발생하고있는레코드에의액세스가많으면읽기 I/O의증가나캐쉬히트율의저하가발생하게된다. 그림 6: 행이행 (ROW MIGRATION) 행체인 (ROW CHANING) 행체인현상은행이행과같이레코드가복수블록에걸치는현상이지만, 행체인은 블록에저장가능한사이즈 이상의레코드가복수블록에건너저장되는현상이다. 물리적으로큰레코드를넣으려하고있는이상어쩔수 없는현상이다, 빈번히발생한다면행이행과같은영향이있다.

54 행이행 행체인을조사하자 행이행과행체인은같은방법으로파악할수있다. 테이블을 ANALYZE 한후, XXX_TABLES 딕셔너리의 CHAIN_CNT 열에행이행또는행체인하고있는레코드수가나타난다. 즉, 행이행과행체인는따로파악할 수없다. 블록레벨의미사용영역의발생 세그먼트 (segment) 용량에비교해저장가능한데이터량이적은경우는, 블록의내용이효율적으로이용되어있지않은경우를생각할수있다. 예를들면데이터저장가능영역이 3000바이트에대해서레코드사이즈가평균 2000바이트의경우, 단순계산이라면 1 블록에대해 1000바이트의낭비가발생하게된다. 테이블이라면 DELETE 비교해 PCTUSED의값이낮은경우에블록레벨의미사용영역이발생하기쉬워진다. 인덱스의경우는 DELETE 가많은경우, 인덱스대상열의값이오름차순이아니고랜덤이나내림차순에 INSERT 되는경우, 인덱스대상열에대해서갱신이발생하는것이많은경우에블록레벨의미사용영역이발생하기쉬워진다. 산정수치에대해서실제의용량 (HWM까지의용량 ) 이너무큰경우는재편성을검토해하자. 제 6 부 : 단편화의해결 제 1 장단편화해결의개요 본장에서는단편화의해결방법에대해설명하기로한다. 단편화의해소의작업은가능한한발생하지않는 것이 DBA 에있어서도좋다고생각되지만, 해당의단편화가발생되는것을방지하는사전의대책에대해서도 설명하기로한다. 단편화해결의개요 단편화의해결방법은, 단편화현상에의해어느정도는다르지만, 공통의방법으로해결할수있는것도있다. 우선은단편화현상마다해결방법을리스트로정리해보았다.

55 계층단편화현상해결방법 ( 재편성방법 ) 해설 장 파일레벨의단편화 OS 커멘드에의한 defrag 제2 장 데이터파일 ( 테이블스페 이스 ) 데이터파일레벨의미사용영역의발생 테이블스페이스레벨의재편성데이터파일의축소 제2 장제2 장 위치가높은하이워터마크 세그먼트 (segment) 레벨의재구성 제3 장 세그먼트 (segment) 세그먼트 (segment) 레벨의미사용영역의발생 extent의잘라서버림세그먼트 (segment) 레벨의재구 제3 장제3 성 장 계층이깊은인덱스 세그먼트 (segment) 레벨의재구성 제3 장 세그먼트 (segment) 레벨의재구 제 3 extent 불연속의 extent 성표영역레벨의재구성 장제2 장 세그먼트 (segment) 레벨의재구 제 3 행이행 성행이행하고있는레코드만의 장제4 블록 행체인 재구성블록사이즈변경해재구성 장제4 장 블록내의미사용영역의발생 세그먼트 (segment) 레벨의재구성 제3 장 제 2 장데이터파일 ( 겉 ( 표 ) 영역 ) 레벨의단편화대책

56 OS 커멘드에의한 degrg( 조각모음 ) UNIX나 Linux등에서는파일의단편화는별로신경쓸필요는없다. Windows계 OS에서도심하지않는정도의단편화는퍼포먼스에의영향은거의없다. 단지, 시스템구축후한번도 defrag를실시했던적이없는것이면한번쯤실시하는것이좋을것이다. Windows계 OS상에서 Oracle를구축하는경우는 degrg( 조각모음 ) 툴로파일레벨의단편화를해소한다. 예를들면 Windows2000계의 OS의경우라면 시작버튼 프로그램 보조프로그램 시스템도구 디스크조각모음 로기동할수있습니다. 덧붙여 defrag 툴등으로최적화를실시하는경우는인스턴스는정지해두어야한다. 테이블스페이스영역레벨의재구성 Oracle9i 으로부터있는테이블스페이스에저장되는테이블을통째로 Export/Import 할수있게되었다. 이기 능을사용하면, 어떤 A 테이블스페이스전체를정리해서재구성하는것이가능하다. 다음의과정이재구성의 순서가된다. (1) Export를실시 Export시에 TABLESPACE 옵션에테이블스페이스명을지정하면, 지정한테이블스페이스영역에존재하는오브젝트가 Export 됩니다. EXP_FULL_DATABASE role을소유하고있는유저로실시한다. c: > exp system/manager tablespace=test01 file=c: temp test.dmp log=c: temp testexp.log (2) 오브젝트의삭제 & 재작성테이블스페이스의파라미터를변경하고싶은경우는테이블스페이스를삭제 재구성합니다. 겉 ( 표 ) 영역의파라미터를하지않는경우에서도겉 ( 표 ) 영역을삭제 재작성하는것이편합니다.TABLESPACE 옵션을지정한 Export로작성한덤프파일에는 CREATE TABLESPACE문은포함되지않으므로, 겉 ( 표 ) 영역은먼저작성해둘필요가있습니다. 만약 Export시와다른파라미터로테이블이나인덱스를작성하고싶은경우는, 하늘의테이블이나인덱스를새로운파라미터의값으로사전에작성해주세요. SQL> drop tablespace test01 including contents and datafiles; SQL> create tablespace test01 datafile...; (3) Import 를실시 Import 시는 TABLESPACE 옵션은불필요합니다. 만약 (2) 으로하늘의세그먼트 (segment) 를작성한경우는 IGNORE 옵션을 Y 로지정해주세요.IMP_FULL_DATABASE 롤을소유하고있는유저로실시한다.

57 c: > imp system/manager tablespace=test file=c: temp test.dmp log=c: temp testimp.log 데이터파일의축소 초기에확보한데이터파일의용량에비교해실제로데이터가들어오지않으면미사용영역으로서경우에따라서는많은디스크용량을소비한다. 데이터가들어올가능성이없는경우등은미사용영역을잘라버리는것도가능하다. 데이터파일을축소하는경우는, 이하의 SQL로실시한다. SQL> alter database datafile 'c: temp test.dbf' resize 100m; 데이터파일명및축소후의사이즈를지정한다. 다만, 잘라버릴수있는것은 HWM( 하이워터마크 ) 이후의영역만이다. HWM에대해서는제5부제 2장을참조하자. 만일 HWM 이전에단편화된미사용영역이있어도 축소의대상으로는되지않는다. 축소라고하는것보다는뒷부분의미사용영역의잘라버리는 처리가된다. 덧 붙여서축소뿐만이아니라확장도같은구문으로가능하다. 파일레벨의단편화의방지책 파일레벨의단편화를막기위해서는이하와같은방책을채택하면유효하다 (1) 데이타베이스작성전에 defrag( 조각모음 ) 를실행한다데이타베이스작성전에조각모음을하지않은상태고신규디스크가아니라면그후에작성하는파일은단편화할가능성이높다. 테이블스페이스를배치할예정의디스크 ( 드라이브, 파티션 ) 에대해서는, 신규디스크가아니라면사전에 defrag( 조각모음 ) 를실행합시다. (2) 최대한전용디스크에, 없다면전용파티션에데이터파일을배치하자데이타베이스를구성하지않는파일군과같은드라이브 ( 파티션 ) 에데이터파일을배치하면단편화하기쉽습니다. 소규모시스템으로디스크가하나라고했을경우에서도, 새롭게파티션을작성하고, 거기에는데이타베이스와무관한파일을두지않게하자. (3) 사전의산정을제대로실시한다 다른레벨의단편화의예방책이기도이지만, 사전에세그먼트 (segment) 나데이터파일의견적을실시해필 요분을확보해두면, 막상예상이빗나가단편화가발생해도추측하지않고적당하게확보했을경우보다단

58 편화의영향을억제할수있다. (4) 데이터파일의자동확장에의지하지말자데이터파일이자동확장하면, 그드라이브 ( 파티션 ) 에해당파일밖에없는경우이외에는단편화가발생할수있다. 최대한사전산정을하고, 필요충분한파일용량을사전에확보해주자. ( 제2부제 2장의마지막항참조 ). 제 3 장세그먼트 (segment) 레벨의단편화대책 세그먼트 (segment) 레벨의재구성 : 개요 개별의세그먼트 (segment) 의재구성방법은몇개가존재한다. 테이블, 인덱스의 Export/Import 테이블의이동 (MOVE) 인덱스의재구성 (REBUILD) 테이블, 인덱스의 Export/Import 테이블과그테이블에부수적인 인덱스를정리해재구성하는경우에유용하다. 복수의테이블이나인덱스를 정리해실시하는것도가능하다. 그러나재구성중에해당의테이블에액세스할수없다. 다음의순서로실행 한다. 제 2 장의테이블스페이스레벨의재구성과순서는거의같아서, 상세한실행로그는생략한다. (1) Export 를실시하자 재구성을목적으로 Export 를실시할때의주의점으로서는, 순서 2로테이블을재작성하는경우는, 속해있는인덱스나제약이나트리거등도 Export 해둔다. 별도의작성 SQL가있으면그것을재실행해도괜찮지만 ( 이쪽이재구성이빨리끝나는케이스도있다 ), 정리해서 Export/Import 하는것이쉽다. 로컬관리테이블스페이스의경우, COMPRESS 옵션은의미가없기때문에, 지정은불필요하다. 예 ) c: > exp scott/tiger tables=(emp, dept) file=c: temp test.dmp log=c: temp testexp.log