실전 데이터모델링 & 데이터베이스 설계와 구축 (11. 데이터베이스 튜닝 )

실전 데이터모델링 & 데이터베이스 설계와 구축 (11.데이터베이스 튜닝)

11. 데이터베이스 튜닝

▶데이터베이스 튜닝이란… 데이터베이스 튜닝이란? • 데이터베이스의 활용 성능을 최상/최적으로 만들기 데이터베이스 튜닝의 구분 • 데이터베이스 설계 튜닝 -데이터베이스 설계 단계에서 성능을 고려하여 설계 • 데이터베이스 환경 -성능을 고려하여 메모리나 블록 크기 등을 지정 • SQL 문장 튜닝 -성능을 고려하여 SQL 문장을 작성

▶데이터베이스 튜닝의 목적 데이터베이스 튜닝의 목적 • 업무적인 환경과 시스템적 환경에 적합한 데이터베이스 파라미터를 설정한다. • 데이터베이스에 접근하는 SQL 문장은 가능한 한 디스크 블록에 최소로 접근하도록 한다. • 디스크 블록에서 한번 읽은 데이터는 가능하면 메모리 영역에 보관한다. • 모든 사용자의 SQL 문장은 공유 가능하도록 명명 표준을 준수하여 작성한다. • 잠금이 최소가 되도록 한다.

▶데이터베이스 설계 단계에서 튜닝하기 - 1 • 데이터 정합성을 유지할 수 있는 대책을 마련하고, 성능을 위해 필요하다면 테이블, 컬럼, 관계에 대해 반정규화를 적용 • 대용량 테이블의 경우 필요한 데이터에 대해서는 파티셔닝을 이용하여 테이블 분할을 검토 • 이력을 관리해야 하는 테이블에 대해서는 필요하다면 시작과 종료나 현재 진행 상태 등을 명확하게 명시하여 SQL 문장의 실행 성능을 보장 • 테이블 접근 유형에 따라 전체 스캔 방식과 B 트리 인덱스, 비트맵 인덱스, 클러스터링과 해싱 적용 등을 고려 • 테이블이 조회 작업이 주로 이루어지는지, 입력, 수정, 삭제 작업이 주로 이루어지는지를 고려하여 적당한 인덱스와 인덱스의 수를 지정 • 분산 데이터베이스를 적용했을 경우 원격 데이터베이스를 이용할 때 성능 저하가 예상된다면 스냅샷을 이용한 복제 테이블 생성 등을 고려 • 공통적으로 관리하는 데이터에 대한 접근이 빈번하다면 어플리케이션의 메모리에 상주시키고 함수를 사용하여 코드 변환을 하도록 유도

▶데이터베이스 설계 단계에서 튜닝하기 - 2 • PK는 일반적으로 지정된 순서를 복합 컬럼 인덱스를 지정하는 규칙에 따라 나열 • FK에 대해서는 가급적 인덱스를 생성하여 전체 스캔이 발생하는 경우와 불필요하게 발생하는 잠금을 피함 • SYSTEM 테이블 스페이스에는 데이터를 관리하는 딕셔너리 정보만 포함하고, 일반 오브젝트는 저장하지 않도록 함 • 테이블을 위한 테이블 스페이스와 인덱스를 위한 테이블 스페이스를 분리 • 롤백 세그먼트에 대한 경합을 피하기 위해 롤백 세그먼트를 여러 개로 구성 • 자주 수정되거나 변경 또는 삭제되는 데이터는 별도의 테이블 스페이스를 만들어 생성

▶복합 컬럼 인덱스 지정 규칙 복합 컬럼 인덱스 지정 규칙 • 항상 조회 조건으로 사용하는 컬럼은 앞쪽으로… • WHERE절의 조건에서 ‘=‘로 비교되는 컬럼이 있으면 앞쪽으로… • 데이터의 분포도가 낮은 컬럼이 앞쪽으로… • 정렬이 자주 발생하는 컬럼이 앞쪽으로…

▶데이터베이스 환경 튜닝 • 공유 풀 튜닝(shared_pool tuning) • 데이터베이스 버퍼 캐시 튜닝(database buffer cache tuning) • 리두 로그 버퍼 튜닝(redo log buffer tuning) • 디스크 I/O 튜닝(disk I/O tuning) • 로우 마이그레이션(row migration) & 로우 체이닝(row chaining)

▶공유 풀 튜닝(shared_pool tuning) • 라이브러리 캐시 튜닝 SQL문, PL/SQL문을 실행하면 실행된 문장을 파싱하여 메모리 영역에 LRU 알고리즘에 따라 보관하였다가 동일한 문장이 다시 실행되면 다시 파싱하지 않아서 성능이 향상 • 데이터 딕셔너리 캐시 튜닝 각종 환경 정보와 오브젝트 생성 정보 등이 저장된 공간이고 데이터 딕셔너리 캐시를 위해 별도로 할당되지 않으며, SHARED_POOL_SIZE 파라미터에 의해 할당

▶라이브러리 캐시 튜닝 - 1 라이브러리 캐시 튜닝에 필요한 뷰 • v$librarycache: 라이브러리 캐시 정보 • v$db_object_cache: 객체(패키지, 뷰, 프로시저, 함수, 트리거 등)의 캐시된 정보 • v$sqlarea: shared pool에 캐시되어 있는 SQL문의 정보 • v$sqltext: SQL문 전체 텍스트와 관련된 정보 • v$sql_plan: 캐시된 커서의 실제 실행 계획 정보 • v$shared_pool_reserved: 공유 풀 내에 단편화 되지 않은 대용량 메모리 예약 풀 및 공간 정보

▶라이브러리 캐시 튜닝 - 2 • SQL 문장 재사용 비율  gethitratio(v$librarycache): 90% 이상 SELECT GETS, GETHITRATIO, PINS, PINHITRATIO, RELOADS, INVALIDATIONS FROM V$LIBRARYCACHE WHERE NAMESPACE = ‘SQL AREA’; • 재파싱 비율  reload/pins(v$libararychache): 1% 이하 -reload: 디스크의 데이터를 메모리로 적재하는 횟수 -pins: 실행 횟수 -테이블 구조 변경과 같은 객체 변경에 의한 invalidation으로 리로드가 발생할 가능 성이 있음 SELECT (SUM(RELOADS)/SUM(PINS))*100 “MISS RATE” FROM V$LIBARARYCACHE; • 라이브러리 캐시 HIT율 향상 방안 -init<SID>.ora 파일에서 SHARED_POOL_SIZE 증가 시킴 -SQL 문장에 대한 명명 표준을 준수하여 동일한 문장에 대해 재파싱이 이루어지지 않게 함 -상수를 사용하기 보다는 바인딩 변수를 사용

▶딕셔너리 캐시 튜닝 딕셔너리 캐시 튜닝에 필요한 뷰 • v$rowcache • 딕셔너리 캐시의 MISS 비율  getmisses(cache miss수)/gets(항목에 대한 접근 수) : 15% 이하 -15% 이상인 경우 SHARED_POOL_SIZE를 증가시킴 SELECT TO_CHAR(TRUNC(SUM(GETMISSES)/SUM(GETS)*100, 5), 0.9999.99) || ‘% (LESS THAN 15%) ‘MISS RATE’ FROM V$ROWCACHE;

▶데이터베이스 버퍼 캐시 튜닝 – 1 • 데이터베이스 버퍼 캐시 튜닝 -SQL 문장을 실행하면 오라클 데이터베이스는 디스크에서 데이터를 읽어 이를 메모리, 즉 데이터베이스 버퍼 캐시에 저장 -자주 사용되는 데이터를 버퍼 캐시에 저장할 수 있도록 환경 설정 -데이터베이스 버퍼 캐시의 크기 조절은 DB_BLOCK_BUFFER 파라미터를 변경

▶데이터베이스 버퍼 캐시 튜닝 – 2 • 데이터베이스 버퍼 캐시의 재사용 비율 • HITS = 100 * (1 – (PHYSICAL READS/(DB_BLOCK_GETS + CONSTRAINT_GETS))) -PHYSICAL READS: 사용자가 SQL문을 실행했을 때 디스크로부터 읽은 테이블의 블록 수 -DB_BLOCK_GETS: 데이터베이스 버퍼 캐시 영역의 데이터 변경 후 데이터를 읽는 블록 수 -CONSTRAINT_GETS: 변경 작업 후 ROLLBACK 문 실행 시 변경 전 데이터로 복구하기 위해 이전 데이터를 저장해 주는 메모리 영역으로부터 읽은 블록 수 • 90% 이상인 경우 아주 좋은 경우 • 80% 이하면 데이터베이스 버퍼 캐시 크기를 증가시킴 SELECT TO_CHAR(TRUNC(SUM(GETMISSES)/SUM(GETS)*100, 5), 0.9999.99) || ‘% (LESS THAN 15%) ‘MISS RATE’ FROM V$ROWCACHE;

▶리두로그 버퍼 튜닝 – 1 • 리두로그 버퍼 튜닝 -모든 SQL 문장을 실행할 때 이용하는 메모리 영역이므로 작업하는 동안은 이 메모리 공간을 활용 -데이터에 대해 수정이 발생하면 잠금을 설정하듯이 메모리 영역에서 다른 프로세스가 이용하지 못하도록 독점하는 방법으로 래치(Latch)를 할당 받아 작업 -대기율을 분석하여 오랜 시간 동안 대기하고 있거나 잦은 대기 상태가 되는 경우 리두로그 버퍼의 크기를 조정

▶리두로그 버퍼 튜닝 – 2 • 리두 할당 대기율 SELECT A.NAME, (B.MISSES/B.GETS)*100, WAIT_RATIO FROM V$LATCHNAME A, V$LATCH B WHERE A. NAME IN (‘리두 ALLOCATION’) AND A.LATCH#=B.LATCH#; • WAIT_RATIO가 1% 이상이 나오면 리두로그가 발생할 때 경합이 발생하는 경우이다. • 리두로그 버퍼를 획득하기 위해 기다린 횟수 SELECT NAME, VALUE FROM V$SYSSTAT WHERE NAME = ‘리두 LOG SPACE REQUESTS’; • 리두로그 버퍼를 얻기 위해 기다린 횟수를 정기적으로 점검하여 만약 횟수가 지속적으로 증가하거나 비이상적으로 증가했다면 리두로그 버퍼의 크기 증가시킨다.

▶디스크 I/O 튜닝 - 1 • 특정 디스크에 있는 데이터 파일에 I/O가 급증하면 성능 저하의 요인이 되므로 I/O 현황을 분석하여 집중된 I/O를 분산시켜야 한다. • 데이터 파일에 대한 I/O 현황 • 물리적인 I/O 작업이 특정 디스크에 집중으로 발생한다면 해당 데이터 파일을 다른 디스크로 옮기거나 테이블 스페이스를 여유가 있는 데이터 파일에 생성해야 한다. -DATA01.DBF 파일에 물리적 I/O가 집중되어 있으므로 데이터 파일에 대한 I/O의 분산을 고려

▶디스크 I/O 튜닝 - 2 • 데이터 파일에 대한 I/O 현황을 전체에 대한 비율

▶디스크 I/O 튜닝 - 3 데이터베이스의 디스크 I/O를 줄이기 위한 설계 방법 • 시스템 테이블 스페이스를 별도로 생성 • 임시 테이블 스페이스는 테이블과 인덱스의 테이블 스페이스와 분리하여 생성 • 롤백 세그먼트와 온라인 리두로그 파일은 분리하여 생성 • 온라인 리두로그 파일과 아카이브 리두로그 파일은 분리하여 생성 • 롤백 세그먼트와 테이블과 인덱스 테이블 스페이스는 분리하여 생성 • 리두로그 파일은 지속적으로 I/O가 발생하므로 I/O가 가장 적은 디스크에 배치 • 테이블과 인덱스 테이블 스페이스 중 I/O가 많은 테이블 스페이스는 별도의 디스크에 배치

▶로우 마이그레이션 로우 마이그레이션(Row Migration) • 데이터 블록에서 수정이 발생하면 수정된 데이터를 해당 데이터 블록에 저장하지 못하고, 다른 블록의 빈 공간을 찾아 데이터를 저장하는 방식 • PCTFREE가 낮게 설정되어 있는 경우에 많이 발생 • 테이블을 분석하면 DBA_TABLES에서 마이그레이션이 발생한 횟수를 조회 • 테이블의 로우에 비해 마이그레이션된 발생 횟수가 많다면 테이블을 생성할 때의 PCTFREE를 증가시켜 테이블을 재생성하고 데이터를 이동시킨다. ANALYZE TABLE { TABLE | CLUSTER } NAME COMPUTE STATISTICS; SELECT NUM_ROWS, CHAIN_CNT FROM DBA_TABLES WHERE TABLE_NAME = ‘TABLE_NAME’;

▶로우 체이닝 로우 체이닝(Row Chaining) • 행의 길이기 너무 길어서 데이터 블록 하나에 데이터가 모두 저장되지 않고, 두 개 이상의 블록에 걸쳐 하나의 행이 저장되어 있는 형태 • 데이터베이스를 생성할 때 지정하는 DB_BLOCK_SIZE를 너무 작게 지정한 경우 • 데이터 타입이 LOB 타입과 같이 대용량 타입일 경우 • 오라클에서 제공하는 UTLCHAIN.SQL 파일 실행 -문장을 실행하면 CHAINDEC_ROWS라는 테이블이 생성, 분석 작업을 수행할 경우 체이닝에 대한 관련된 데이터가 테이블에 저장 • 많은 로우 체이닝이 발생하였다면 행을 다를 테이블에 복사하고, 원래 테이블에서 체이닝이 발생한 행을 삭제한 다음 다시 원위치키기는 작업을 한다. ANALYZE { TABLE | CLUSTER } NAME LIST CHAINED ROWS INTO CHAINED ROWS; SELECT COUNT(*) FROM CHAINED_ROWS WHERE TABLE_NAME = UPPER(‘NAME’);

▶SQL 문장 튜닝 – 1 SQL 문장 작성할 때 꼭 알고 있어야 할 사항 - 1 • 접근 경로에 대한 우선 순위를 알고 있어야 한다. • 옵티마이저 모드가 비용 기반 모드인지 규칙 기반 모드인지 알고 있어야 한다. • 숫자 형식은 숫자 형식의 타입으로 문자 형식은 문자 형식의 타입으로 비교한다. • WHERE 절 안에 비교하는 조건에 인덱스를 이용해야 할 경우 인덱스 컬럼을 기술한다. • 여러 개의 컬럼이 인덱스 하나로 지정된 경우 앞쪽에 있는 컬럼을 모두 지정하여 인덱스 범위를 조회할 수 있게 한다. • 인덱스를 이용하고자 하는 컬럼을 변형하지 않는다. • 인덱스 컬럼에 NULL 값을 사용하지 않는다.

▶SQL 문장 튜닝 – 2 SQL 문장 작성할 때 꼭 알고 있어야 할 사항 - 2 • 부정형으로 지정할 경우에는 인덱스를 사용하지 않는다. 가능하면 부정형을 사용하지 않는다. • OR를 사용하면 인덱스를 이용하지 않을 수 있다. • 불필요하게 DUAL 테이블을 이용하지 않는다. • WHERE 조건절에 걸린 인덱스가 15% 이상 반환하리라 예상되면 인덱스 스캔을 피한다. • 불필요하게 DBMS에서 제공하는 함수(SUM, SIGN, DECODE, NVL 등)를 사용하지 않는다. • 인덱스가 걸려있는 컬럼에 대해 LIKE 형식으로 비교하는 경우에는 반드시 뒤쪽에 비교자(%)가 위치해야 하며, 앞쪽에 위치할 경우에는 인덱스를 이용하지 않는다. • 힌트를 적절하게 사용한다.

▶SQL 문장 튜닝 – 3 접근 경로에 대한 우선 순위를 알고 있어야 한다.

▶SQL 문장 튜닝 – 4 옵티마이저 모드가 비용 기반 모드인지 규칙 기반 모드인지 알고 있어야 한다. • 비용 기반 모드(Cost-Based Mode) -ANALYZE를 이용하여 생성한 통계 정보를 활용하여 가장 빠른 접근 경로가 무엇인지를 결정하여 실행 계획을 수립 -모든 SQL에 대해서 실행 계획을 완벽하게 수립하지는 못하므로 원하지 않는 성능이 종종 나오는 경우가 발생 -접근 경로에 대한 우선 순위와 데이터의 분포도를 고려하여 SQL 문장 튜닝 • 규칙 기반 모드(Rule-Based Mode) -ANALYZE를 이용하여 생성한 통계 정보를 활용하지 않고, 오라클 데이터베이스에서 정한 접근 경로에 대한 우선 순위에 근거하여 실행 계획을 선택 -실행 계획의 명시로 인해 SQL 문장의 튜닝이 용이

▶SQL 문장 튜닝 – 5 숫자 형식은 숫자 형식의 타입으로, 문자 형식은 문자 형식의 타입으로 비교한다. • WHERE 절에 기술된 비교 형식을 일치 시키지 않으면 비록 변환에 문제가 없는 형식이라 할지라도 DBMS 내부적으로 한 번 더 작업이 발생하게 되므로 성능에 영향을 줌 • Ex) 숫자 형식 DEPTNO DEPTNO=’10’ (X)  DEPTNO=10 문자 형식 DEPTNM DEPTNM=영업부 (X)  DEPTNM=‘영업부’

▶SQL 문장 튜닝 – 6 WHERE절의 비교 조건에 인덱스를 이용해야 할 경우 인덱스 컬럼을 기술한다. • 실제 적절한 인덱스와 비교할 수 있음에도 불구하고 WHERE 조건절에 기술하지 않는 경우가 많다. • 데이터를 조회할 테이블의 인덱스가 무엇인지 항상 확인해야 한다. • 여러 개의 컬럼이 인덱스 하나로 지정된 경우 WHERE 절에 모두 기술한다. • 테이블에 있는 인덱스와 인덱스 컬럼을 조회하는 SQL문 BREAK ON OWNER ON TABLE_NAME ON INDEX_NAME COL OWNER FORMAT A15 COL INDEX_NAME FORMAT A20 COL COLUMN_NAME FORMAT A20 COL P FORMAT 999 SELECT TABLE_NAME, INDEX_NAME, COLUMN_POSITION P, COLUMN_NAME FROM USER_IND_COLUMNS ORDER BY TABLE_NAME, INDEX_NAME, COLUMN_POSITION;

▶SQL 문장 튜닝 – 7 여러 개의 컬럼이 하나의 인덱스로 지정된 복합 컬럼 인덱스인 경우 WHERE절에 모든 컬럼을 기술한다. • 여러 개의 컬럼이 하나의 인덱스로 지정된 경우 WHERE절에 모두 기술 • 인덱스 (BRANCH_NO, ORDER_DATE, ORDER_CD) SELECT ORDER_NO FROM ORDER WHERE ORDER_DATE = ‘2002-07-01’ AND ORDER_CD = ‘01’; • 적절한 인덱스를 활용하지 않으므로 성능이 저하 SELECT ORDER_NO FROM ORDER WHERE BRANCH_NO = ‘1111’ AND ORDER_DATE = ‘2002-07-01’ AND ORDER_CD = ‘01’; • 실제 프로젝트의 경우 FROM 절에 테이블이 5개 이상 지정되는 경도 있고, 테이블 하나에 여러 개의 인덱스가 걸려있는 경우도 많으므로 인덱스 컬럼을 모두 기술하지 않아 성능을 저하시키는 경우가 많음

▶SQL 문장 튜닝 – 8 여러 개의 컬럼이 인덱스 하나로 지정된 경우 앞쪽에 있는 컬럼을 모두 지정하여 인덱스 범위를 조회할 수 있게 한다. • 복합 컬럼 인덱스를 설정할 때 자주 사용하는 컬럼을 맨 앞쪽에 놓고, 이용 순서대로 설계해야 함 • 비록 뒤에 오는 컬럼에 대한 데이터가 입력되지 않더라고 앞쪽에 위치한 컬럼에 대해서는 정확하게 기술해야 함 • -인덱스에 대한 범위 조회(Range Scan)이 가능하게 되어 성능 향상 • 단 앞쪽에 위치한 컬럼에 값이 누락되면 인덱스는 쓰이지 않음

▶SQL 문장 튜닝 – 9 인덱스를 이용하고자 하는 컬럼에 대해 변형을 하지 않는다. • 인덱스를 지정한 컬럼이 모두 WHERE절에서 비교되어 있음에도 불구하고, 인덱스를 이용하지 못하게 인덱스가 변형되는 경우가 있다. • ITEMCD가 VARCHAR2(4)고, 인덱스가 설정되어 있다 SELECT ITEMNM, QTY FROM ITEM WHERE SUBSTR(ITEMCD, 1, 2) = ‘CA’; -SUBSTR을 이용하여 ITEMCD의 문자열을 잘라버리면 인덱스 영역에 존재하는 컬럼과 비교하지 않고, 바로 테이블에 존재하는 데이터 영역에서 비교하므로 풀 테이블 스캔이 발생  SELECT ITEMNM, QTY FROM ITEM WHERE ITEMCD LIKE ‘CA%’; -LIKE 연산자를 사용하여 동일한 기능의 SQL문 수행하면서 인덱스 영역에서 인덱스를 이용하여 비교

▶SQL 문장 튜닝 – 10 인덱스 컬럼에 NULL/NOT NULL 값을 사용하지 않는다. • ITEMCD가 VARCHAR2(4), 인덱스가 설정되어 있음 SELECT ITEMNM, QTY FROM ITEM WHERE ITEMCD IS NULL; - ITEMCD에 인덱스를 지정했는데, 값이 지정되지 않은 것을 조회하면 풀 테이블 스캔이 발생 • 인덱스를 꼭 설정할 필요가 있는 컬럼에 대해서는 테이블 생성할 때 기본 값 설정을 고려해야 한다. SELECT ITEMNM, QTY FROM ITEM WHERE ITEMCD = ‘0000’; -ITEMCD 컬럼의 기본 값이 ‘0000’으로 설정

▶SQL 문장 튜닝 – 11 부정형으로 지정할 경우에는 인덱스를 사용하지 않는다. 가능하면 부정형을 사용하지 않는다. • 부정형: !=, <>, NOT IN, NOT EXIST 등 • 부정형 비교는 인덱스 분포도를 고려해야 하며, 분포도가 15% 이상이면 혹시 다른 비교 조건이 존재하는지 확인하여야 한다. 그렇지 않으면 풀 테이블 스캔할 수 밖에 없다. • ITEMCD의 CA01 값이 90%를 차지, 나머지 데이터가 10% 정도의 분포도를 보인다고 가정 SELECT ITEMNM, QTY FROM ITEM WHERE ITEMCD != ‘CA01’;  SELECT ITEMNM, QTY FROM ITEM WHERE ITEMCD IN (‘CA02’, ‘CA03’, ‘BA01’);

▶SQL 문장 튜닝 – 12 OR를 사용하면 인덱스를 이용하지 않을 수 있다. • ITEMCD가 인덱스가 설정되어 있고, ITEMNM에는 인덱스가 설정되어 있지 않다. SELECT ORDERDATE, QTY FROM ITEM WHERE ITEMCD = ‘CA01’ OR ITEMNM = ‘마우스’; -ITEMCD에 인덱스가 설정되어 있지만 ITEMNM의 인덱스가 존재하지 않으므로 이 SQL 문장은 전체 스캔을 유발한다. • 인덱스가 설정되어 있지 않은 컬럼에 대해 단독으로 비교되어야 하는지 고려, 만약 필요하면 인덱스 생성하거나 다른 비교 조건을 추가하여 인덱스를 이용하도록 유도한다. SELECT ORDERDATE, QTY FROM ITEM WHERE ORDERDATE BETWEEN TO_DATE(‘20020101’, ‘YYMMDD’) AND TO_DATE((‘20021231’, ‘YYMMDD’) AND (ITEMCD = ‘CA01’ OR ITEMNM = ‘마우스’);

▶SQL 문장 튜닝 – 13 불필요하게 DUAL 테이블을 이용하지 않는다. • 불필요하게 DUAL 테이블에서 데이터를 가져옴으로써 성능이 저하되므로 SYSDATE를 바로 이용하도록 수정 SELECT ORDERDATE, QTY FROM ITEM WHERE ORDERDATE > (SELECT SYSDATE – 7 FROM DUAL)  SELECT ORDERDATE, QTY FROM ITEM WHERE ORDERDATE > (SYSDATE – 7)

▶SQL 문장 튜닝 – 14 불필요하게 DBMS에서 제공하는 함수(SUM, SIGN, DECODE, NVL 등)를 사용하지 않는다. • DBMS에서 제공하는 함수는 내부적으로는 프로그램 로직을 수행한다. 따라서 DBMS에서 내부적인 작업이 많이 발생하면 할수록 실행 속도가 느려진다. SELECT ITEMNM, NVL(ITEMCD, ‘ ‘), NVL(QTY, 0) FROM ITEM WHERE ORDERDATE = TO_DATE(‘20020701’, ‘YYMMDD’); -테이블에 기본값을 지정하거나 NOT NULL을 지정하여 NVL 함수 사용을 피한다.

▶SQL 문장 튜닝 – 15 인덱스가 걸려있는 컬럼에 대해 LIKE 형식으로 비교하지 않는 경우에는 반드시 뒤쪽에 비교자(%)가 위치해야 하며, 앞쪽에 위치할 경우에는 인덱스를 사용하지 않는다. • ITEMCD는 VARCHAR(4)에 인덱스가 설정되어 있고, 맨 처음 코드 유형과 맨 뒤 코드 유형에 상관없이 중간에 두 개의 코드에 따라 값을 조회하는 경우 SELECT ITEMNM, QTY, ORDERDATE FROM ITEM WHERE ITEMCD LIKE ‘%BB%’; -앞쪽에 위치한 문자열 값을 한정할 수 없다면 ITEMCD의 인덱스를 사용할 수 없으며, 다른 조건을 추가하여 풀 테이블 스캔을 피한다.  SELECT ITEMNM, QTY, ORDERDATE FROM ITEM WHERE ITEMCD LIKE ‘ABB%’ AND ITEMCD LIKE ‘BBB%’ AND ITEMCD LIKE ‘CCC%’ …

▶SQL 문장 튜닝 – 16 힌트를 적절하게 사용한다. -힌트는 오라클의 옵티마이저에게 SQL 문장에 개발자가 원하는 방법으로 실행 계획을 수립하도록 명령을 내리는 방법 -SQL 문장에서 힌트를 지정하는 방법은 ‘/* + */’나 ‘--+’를 이용 최적화한 접근 목적을 위한 힌트 조인 순서를 위한 힌트 병행 수행을 위한 힌트

▶SQL 문장 튜닝 – 17 접근 방법을 위한 힌트

▶SQL 문장 튜닝 – 18 조인 방법을 위한 힌트 기타 부가적인 힌트

▶테이블에 일련번호(순번)을 증가시키는 방법 - 1 두 개의 문장으로 처리 SELECT MAX(주문일련번호)+1 INTO :V_주문일련번호 FROM 주문; INSERT INTO 주문(주문일련번호, COL2, COL3,…) VALUES (V_주문일련번호, ‘XXX’, ‘XXX’,…) MAX(컬럼)+1을 이용 -별도의 테이블을 구성하지 않고 입력하고자 하는 테이블에서 일련번호의 MAX 값을 읽어 처리하는 방법 -한 번 읽어서 처리하는 경우에는 단순하면서 효과적, 대용량 데이터 처리 시에는 적합하지 않은 방법 INSERT INTO 주문(주문일련번호, COL2, COL3,…) SELECT DECODE(MAX(주문일련번호), NULL, 0, MAX(주문일련번호)) + 1 주문 일련번호, :COL2값… FROM 주문

▶테이블에 일련번호(순번)을 증가시키는 방법 - 2 시퀀스(SEQUENCE) 이용 -DBMS에서 지정한 옵션에 따라 일률적으로 증가하는 시퀀스 테이블을 생성하고, 시퀀스에서 일련번호를 부여 받아 테이블에 입력 -시퀀스 테이블은 단순히 조회만 해도 순번이 증가하므로 사용자가 조금이라도 일련 번호에 의미를 두고자 한다면 사용하지 않는 것이 좋다. //시퀀스를 생성 CREATE SEQUENCE 주문_SEQ INCREMENT BY 1; //데이터 입력 INSERT INTO 주문(주문일련번호, COL2, COL3, …) VALUES (주문_SEQ.NEXTVAL, ‘XXX’, ‘XXX’, …)

▶테이블에 일련번호(순번)을 증가시키는 방법 - 3 순번 테이블 사용 -순번 테이블을 구성한다. -만약 주문 테이블의 PK가 지점코드+주문코드+일련번호 //신규 일련번호 SELECT 최종일련번호 + 1 INTO :V_주문일련번호 FROM 주문 WHERE 지점코드 = ’01’ AND 주문구분 = ‘인터넷’; //주문 테이블에 데이터 입력 INSERT INTO 주문(주문일련번호, COL2, COL3,…) VALUES (V_주문일련번호, ‘XXX’, ‘XXX’, …); //순번 테이블 수정 UPDATE 주문순번테이블 SET 최종일련번호 = :V_주문일련번호 WHERE 지점코드 = ’01’ AND 주문구분 = ‘인터넷’ -하나의 트랜잭션으로 처리되어야 정확한 데이터 관리가 가능

▶테이블에 일련번호(순번)을 증가시키는 방법 - 4 인덱스 역방향 정렬을 이용한 순번 -테이블에서 MAX 값을 읽어서 처리하기에 읽기 성능이 저하되고, 순번 테이블을 구성할 때 잠금이 예상되는 경우에는 인덱스를 이용하여 일련번호를 가져온다. INSERT INTO 주문(주문일련번호, COL2, COL3, …) SELECT /*+ INDEX_DESC (B i_01) */ <--인덱스가 i_01일 때 인덱스를 역정렬 FROM 주문 A, (SELECT 0 SER FROM DUAL) B WHERE A.주문일련번호 = B.SER(+) <--데이터가 하나도 없을 때 순번을 처리하기 위함 AND ROWNUM = 1; -인덱스 역방향 정렬을 이용하여 ROWNUM = 1에 해당하는 데이터를 조회하였으므로 성능도 좋고, 잠금으로 인한 기다림 현상도 나타나지 않음

▶갑작스런 대용량 테이블의 성능 저하 원인과 조치 방법 데이터베이스 시스템 운영 시 성능이 급격히 저하되거나 점진적을 성능이 저하되는 경우 가 발생한다. 이는 주로 인덱스에서 문제가 발생한 경우이다. -생성된 인덱스가 깨진 경우 -인덱스 레벨이 깊어진 경우 -인덱스 구조에 수정/삭제가 자주 발생한 결과 불필요한 인덱스로 인해 성능이 저하 • 인덱스에 대해 ANALYZE를 수행한다. ANALYZE INDEX 인덱스명 ESTIMATES STATISTICS; -대용량 데이터에 대해 COMPUTE로 ANALYZE를 실행하면 시간이 많이 소요되므로 EXTIMATES를 활용 • 인덱스 레벨을 검사한다. SELECT BLEVEL, LEAF_BLOCKS FROM USER_INDEXES WHERE INDEX_NAME=‘인덱스명’; -인덱스 레벨은 데이터베이스의 블록 크기과 인덱스의 크기에 따라 증가하므로 일률적으로 기준을 지정할 수 없다. • 인덱스를 대체한다. ALTER INDEX 인덱스명 REBUILD; • 인덱스를 대체하거나 재생성한 이후에도 성능이 개선되지 않으면 인덱스 레벨이 깊어지는 현상을 막기 위해 파티셔닝 기법을 사용하거 클러스터링 같은 방법 검토

실전 데이터모델링 & 데이터베이스 설계와 구축 (11. 데이터베이스 튜닝 )