오라클 데이터베이스 성능 튜닝

테이블 구성시 성능 관련 참고 사항 테이블 설계시 주의사항 설계단계에서 이후 비즈니스의 가변성 등을 예측하고 충분한 분석을 통해 정규화 작업을 수행함 논리 설계를 마치고 물리 설계시에 각 벤더사의 DBMS에 최적화된 물리설계를 통해 성능의 잇점을 최대화 함

테이블 구성시 성능 관련 참고 사항 정규화(Normalization) 정규화란 정규화는 눈리적 데이터 모델을 일관성이 있고 중복을 제거하여 보다 안정성을 갖는 바람직한 자료구조로 개선시켜 나아가는 과정을 말함 정규화의 장점 새로운 요구 사항의 발견과 적용이 절차적, 단계적으로 추가하기에 용이함

테이블 구성시 성능 관련 참고 사항 정규화(Normalization) 정규화의 종류 1차 정규화 – 복구의 속성 값을 갖는 속성을 분리 2차 정규화 – 주식별자에 종속적이지 않은 속성의 분리, 부분 종속(Partial Dependency) 속성을 분리 3차 정규화 – 속성에 종속적인 속성의 분리, 이전 속성(Transitive Dependency) 속성을 분리 보이스-코드 정규화 – 다수의 주식별자 분리 4차 정규화 – 다가 종속(Multi-Valued Dependency) 속성분리 5차 정규화 – 결합 종속(Join Dependency)일 경우는 두개 이상의 N개로 분리

테이블 구성시 성능 관련 참고 사항 물리 설계시의 주의사항 스토리지에 대한 충분한 고려 스토리지 경합에 대한 고려 효과적인 관리, 백업, 복구에 대한 고려

테이블 구성시 성능 관련 참고 사항 데이터 타입 종류 CHAR VARCHAR2 NUMBER LONG LOB DATE TIMESTAMP 등

테이블 구성시 성능 관련 참고 사항 CHAR 타입 고정길이 문자타입의 데이터 타입 4000 byte 문자이외는 공백으로 입력됨 불필요한 저장 공간을 사용할 수 있음 비교시에 공백에 대한 처리를 해야 함

테이블 구성시 성능 관련 참고 사항 VARCHAR2 타입 가변길이 문자티입의 데이터 타입 4000 byte 저장 공간을 절약할 수 있음 DML 등의 데이터 변경 시에 기타 추가 작업에 의한 성능 이슈에 대한 고려 필요

테이블 구성시 성능 관련 참고 사항 LONG 타입 대용량 파일 및 텍스트 저장을 위한 데이터 타입 2GB 하나의 테이블에 하나의 컬럼만 정의 가능 인덱스 생성 불가 특수한 조회 방법을 이용

테이블 구성시 성능 관련 참고 사항 LOB 타입 LONG 타입의 확장된 기능으로 추가된 데이터 타입 4GB

테이블 구성시 성능 관련 참고 사항 Row Migration 현상 현재 데이터가 저장되어 있는 Data Block이 꽉 차게 되면..? 만약 꽉 찬 Data Block에 Update가 발생을 한다면..? 위와 같은 경우에 Row Migration 현상이 발생하게 된다

테이블 구성시 성능 관련 참고 사항 Row Migration 현상 Row Data Row Data Point 정보 Pctfree Pctfree Pctfree Pctfree insert or update update Row Data Block에 저장 공간을 다 사용했을 때 Update로 Row 길이가 늘어나면 블록에 저장할 공간의 부족한 현상이 발생함 길이가 늘어난 Row를 다른 빈 Block에 저장해야 함 빈 Block에 저장하고 이전 공간에 신규 저장한 곳의 위치 정보를 남겨 둠 데이터베이스가 데이터를 검색하기 위해 두 개의 Block을 읽어야 함

테이블 구성시 성능 관련 참고 사항 Row Chaining 현상 Row를 처음 insert 할 경우 Row 데이터가 단일 Block에 들어가지 않는 경우 마치 Chain과 같이 서로 연결된 Data Block에 Row를 저장 함 대개 LONG, LONG RAW 등의 데이터 타입의 열을 포함하는 Row 같이 크기가 큰 Row에 대한 insert시 발생함 Row Chaining의 경우 불가피한 상황임 데이터베이스가 Row 데이터를 읽을 때 두 개 이상의 Block 접근해야 하기 때문에 I/O 성능의 감소를 초래함

테이블 구성시 성능 관련 참고 사항 PCTFREE Block에 데이터를 insert 시 pctfree에 정해진 %만큼의 여유공간은 쓰지 않겠다를 의미함 변경 데이터를 위해 Block의 pctfree로 정해진 %의 양 만큼 확보함을 의미 Row Migration 현상을 줄일 수 있음 데이터의 수정이 빈번한 경우 10 정도로 수정이 거의 없을 경우 0로 설정해서 사용함

테이블 구성시 성능 관련 참고 사항 PCTUSED Block의 재사용을 위해 기준으로 정해둔 Block 사용율 %를 의미함 최초 Block이 Free 상태에서 사용이 됨. PCTFREE를 제외한 부분이 다 사용이 되고 난 후 Block의 상태는 Used로 변경되고 상태가 Used Block에는 데이터의 삽입이 불가함 상태가 다시 Free로 변경되어야 Insert가 가능한데 이때 Block의 사용율이 PCTUSED 이하로 떨어져야 Free 상태로 변경됨

테이블 구성시 성능 관련 참고 사항 PCTUSED Row Caining과 Row Migration 현상을 줄일 수 있음 삭제가 거의 발생하지 않을 경우에는 90정도로 큰 값을 설정하고 수정작업이 자주 발생하면서 Row Size가 증가할 경우에는 40정도로 낮은 값을 설정해도 됨 적정하지 않은 값을 설정하게 되면 Storage를 낭비하게 됨

테이블 구성시 성능 관련 참고 사항 ASSM(Automatic Segment Space Management) FS1 FS2 PCTFREE insert / update delete Local Management 방식으로 관리되는 Tablespace에 Segment에 사용 가능 PCTUSED, FREELIST, FREELIST GROUP 등의 옵션이 무시됨 Segment에 대해서 오라클 DB 서버가 알아서 자동으로 관리하여 줌

테이블 구성시 성능 관련 참고 사항 ASSM(Automatic Segment Space Management) Row Caining과 Row Migration 현상을 줄일 수 있음 삭제가 거의 발생하지 않을 경우에는 90정도로 큰 값을 설정하고 수정작업이 자주 발생하면서 Row Size가 증가할 경우에는 40정도로 낮은 값을 설정해도 됨 적정하지 않은 값을 설정하게 되면 Storage를 낭비하게 됨

테이블 구성시 성능 관련 참고 사항 테이블 Reorgnization Row Caining과 Row Migration 현상이 심하면 SQL 성능에 영향을 미침 테이블에 Fragmentation이 많을 경우 SQL 성능에 영향을 미침 빈번한 update, delete가 많은 테이블은 Row Chaining과 Row Migration 현상이 많이 발결될 확률이 높으며 테이블의 Fragmentation이 발생할 확률이 높음 위 사항들에 대해서 해결을 하기 위한 테이블을 재구성하는 작업을 Reorgnization 작업이라고 함

테이블 구성시 성능 관련 참고 사항 테이블 Reorgnization exp 툴로 테이블을 백업한 후 DROP하고 다시 테이블을 생성한 후 imp 툴로 백업 데이터를 넣어줌 CTAS(Create Table ~ AS ~)로 테이블 재구성 ALTER TABLE ~ SHRINK SPACE COMPACT; 문장을 실행 Row Migration, Row Chaining 현상이 발생한 Row를 찾아 Delete 후 insert 해 줌

테이블 구성시 성능 관련 참고 사항 Extent 동적 할당 Extent 공간에 데이터가 저장되다가 공간이 부족하여 새로운 Extent를 늘리는 현상을 말함 동적 할당 작업이 발생하는 순간 할당 작업으로 평상시 보다 성능이 약간 떨어질 수 있음 ALTER TABLE ~ ALLOCATE EXTENT; 문장을 사용하여 Extent를 미리 할당하여 성능 저하 현상 방지

테이블 구성시 성능 관련 참고 사항 Index 설계 시 주의 사항 테이블에 대한 Access 패턴에 대해 가능한 정확한 분석이 필요함 데이터의 선택도, 분포도가 좋은 컬럼이 대상일수록 효과가 좋음 Index도 저장 공간을 사용하기 때문에 이에 대한 고려가 있어야 함

테이블 구성시 성능 관련 참고 사항 Index의 종류 B*Tree Index Reverse Index Bitmap Index Index-Organized Table Function-Based Index

테이블 구성시 성능 관련 참고 사항 … B*Tree Index B*Tree Index Index : TAB_PK SELECT * FROM TAB WHERE COL1 = ‘caa’ Root Block(407323FC) lmc(407323404) ddd(407323405) ggg(407323406) ① Root Block Search ② Branch Block Search Branch Block(40732404) lmc(407323407) bbb(407323408) ccc(407323409) Branch Block(40732405) lmc(40732340A) eee(40732340B) fff(40732340C) Branch Block(40732406) lmc(40732340D) hhh(40732340E) iii(40732340F) ③ Leaf Block Search TAB1 Leaf Block(40732407) Null 4073248 abb(rowid) bab(rowid) Leaf Block(40732408) 40732407 40732409 bbc(rowid) caa(rowid) Leaf Block(40732409) 40732408 4073240A dde(407323408) ecd(407323409) … ④ Rowid를 통한 Table Access

테이블 구성시 성능 관련 참고 사항 Reverse Index B*Tree Index와 같은 방식이지만 실제 인덱스의 Leaf 노드에 저장되는 Value가 역순으로 바뀌어서 저장됨 Value를 역순으로 뒤집는 작업을 통해 인덱스의 Leaf 노드에 저장되는 데이터가 분산되는 효과가 있음 인덱스의 정렬순서와 일치하는 순서로 insert나 update 가 자주 발생할 경우 Block에 대한 경합이 발생하게 되나 Reverse를 사용할 경우 순차적인 insert나 update라도 인덱스의 여러 Leaf 노드에 분산이 됨

테이블 구성시 성능 관련 참고 사항 Reverse Index Leaf 노드의 Value가 역순으로 바뀌어서 정렬되어 저장되기 때문에 Range Scan을 사용할 수 없음

테이블 구성시 성능 관련 참고 사항 Bitmap Index

테이블 구성시 성능 관련 참고 사항 Bitmap Index 테이블에 Row가 많으나 인덱스 대상 컬럼의 Cardinality 가 낮을 경우 매우 유용함 (예로, 색상, 성별, 시(City), 나이, 직업, 상태 등등) 위 상황에서 특히나 SQL문의 Where 절에 OR 조건에 대한 수행에 매우 유용함 DML(update, delelte 등)문이 빈번하지 않은 경우에 유리함

테이블 구성시 성능 관련 참고 사항 Decending Index 일반적인 인덱스의 경우 Asc 옵션이 default 임 B*Tree 인덱스의 경우 Asc 옵션으로 기본 생성이 됨 내림차순의 데이터 조회가 빈번한 경우, 특히 최근 날짜에 대한 순으로 조회가 기본으로 여겨지는 SQL 문장에서 유용함 기본 구조는 B*Tree 인덱스의 구조와 동일하며 인덱스 대상 컬럼의 값을 기준으로 Desc 형태로 정렬된 구조임

테이블 구성시 성능 관련 참고 사항 Function-Based Index Leaf 노드의 저장되는 컬럼의 값이 함수가 적용이 되어 나온 값을 기준으로 함 오라클 8i 이후 버전부터 사용 가능함 B*Tree Index의 한계를 계선하기 위해서 발생함

테이블 구성시 성능 관련 참고 사항 Index-Organization Index(IOT) 인덱스와 테이블의 일체형 구조 반드시 Primary Key가 존재해야 함 인덱스와 테이블의 일체화된 구조로 저장 공간의 사용이 절약됨 데이터에 대한 접근이 기존의 경우 인덱스를 통해서 테이블에 접근하지만 IOT의 경우 한번의 접근으로 인덱스와 테이블에 동시 접근이 가능함 하지만 하나의 Leaf Node에 저장되는 Index entry 개수가 작아짐

Index Organization Index 테이블 구성시 성능 관련 참고 사항 Case별 Index 사용 Case Index type 일반적인 테이블 정보에 대한 검색시 사용 B*Tree Index 대용량 데이터베이스 환경에서 나쁜 분포도에 대한 컬럼 검색시 사용된다 Bitmap Index 테이블의 인덱스 검색에 대해 삭제가 빈번하게 발생할 때 사용된다 Reverse Index 최신 행 정보를 우선시 조회할 때 사용된다 Descending Index 검색조건에서 계산 공식을 사용하는 경우 결과치를 인덱스로 생성할 수 있다 Function-Based Index Primary Key를 이용하여 Text 컬럼의 정보를 검색할 때 사용된다 Index Organization Index