[오라클 DB 최적의 물리적 설계 및 SQL 튜닝 방법론] 튜닝기법 세미나 [오라클 DB 최적의 물리적 설계 및 SQL 튜닝 방법론] PLAN 정보기술 Consultant : 주 종 면 jina6678@yahoo.co.kr 011-864-1858

- 메리놀 병원/동의의료원 DB 컨설팅 강사 : 주 종 면 E-MAIL : jina6678@yahoo.co.kr 경 력 저 서 다음 <OracleZone> 카페 운영진 한국 데이터베이스 진흥센터 <오라클의 모든 것> 카페시샵 현. PLAN 정보기술 대표 컨설턴트 전. Computer4You Co.(미국 Ohio주 소재) 데이터베이스분야 컨설턴트 전. 오라클 교육센터 공인강사 및 컨설턴트 전. 프랑스 쌍고방 그룹 한국 베트로텍스 정보관리팀 데이터베이스 관리자 전. 삼미그룹 삼미전산(주) 시스템사업부 프로그래머 전. 육군 26사단 전산실 프로그래머 경 력 저 서 - 메리놀 병원/동의의료원 DB 컨설팅 - 성창기업 DB 컨설팅 - 농협/하나은행/동남은행 DB 컨설팅 - 일본 전자계산소 자산관리 시스템 개발 - 프랑스 쌍고방그룹 Vetrotex Korea Co. 생산/공정/근태/회계관리 시스템 개발 - 제일제당(주) 생산/물류관리 시스템 분석/설계/개발 - 밀양대학교 학사/행정관리 시스템 분석/설계/개발 - 한국 중공업 인사관리 시스템 개발(분석/설계) - 우경철강(주) MIS 시스템(분석/설계) - 육군 저축관리 시스템 개발 - 공군 물류관리 시스템 개발 - 메리놀병원 홈페이지 개발 - 중고 자동차매매조합 홈페이지 개발 - 외 공장자동화 시스템 개발 - 도서출판- 2004년 출간에정 “오라클 10g 장애와 복구” 대림출판사 2003년 출간 "오라클 데이터베이스 모델링“ 프리렉 출판사 "오라클 SQL 튜닝 & 서버 튜닝” 프리렉 출판사 "오라클 9i SQL & PL/SQL” 프리렉 출판사 2002년 출간 "클릭하세요 오라클 9i“ 대림 출판사 2000년 출간 "클릭하세요 오라클 8i“ 대림 출판사 1999년 출간 "지나와 함께하는 오라클 8“ 대림 출판사 1998년 출간 "ORACLE-Unleashed“ 대림 출판사 1997년 출간 "SQL-Unleashed“ 대림 출판사 월간 마이크로 소프트웨어 특집기사 월간 마이크로 소프트웨어 DB 프로그래밍 독점연재 월간 PC 자격증 "OCP" 분야 독점연재

- 목 차 - 1) 테이블 스페이스의 설계 2. SQL 튜닝 방법론 1. 테이블과 인덱스의 물리적 설계 2) 블록 크기의 설계 - 목 차 - 1. 테이블과 인덱스의 물리적 설계 1) 테이블 스페이스의 설계 2) 블록 크기의 설계 3) 테이블의 물리적 설계 4) INITIAL, NEXT의 크기 계산 5) 인덱스의 크기 설계 2. SQL 튜닝 방법론 1) 옵 티 마 이 저 2) SQL 튜닝 Road-Map

테이블과 인덱스의 물리적 설계

1) 테이블 스페이스의 설계

논리적/물리적 저장구조 Database Tablespace File Segment Extent Block Logical Physical Block

1) Data-Dictionary Type 2) Locally Management Type Tablespace의 종류 CREATE TABLESPACE [tablespace명] DATAFILE ‘[디렉토리와 file명]’ SIZE [크기] EXTENT MANAGEMENT DICTIONARY DEFAULT STORAGE( INITIAL [크기] NEXT [크기] MINEXTENTS [n] MAXEXTENTS [n] PCTINCREASE [n]); 1) Data-Dictionary Type CREATE TABLESPACE [tablespace명] DATAFILE ‘[디렉토리와 file명]’ SIZE [크기] EXTENT MANAGEMENT LOCAL [UNIFORM SIZE [크기]] [AUTOALLOCATE]; 2) Locally Management Type

테이블스페이스의 비교 Data Dictionary 타입 Locally Management 타입 데이터 익스텐트와 롤백 세그멘트의 익스텐 트 정보를 SYSTEM 데이터 파일에 갱신 트 정보를 자신의 데이터 파일에 갱신 INITIAL,NEXT,PCTINCREASE,FREELIST ,PCTFREE,PCTUSED를 사용자가 직접 정의 PCTFREE를 제외한 파라메터는 오라클 서버 에 의해 자동 할당 해당 객체에 맞는 최적의 물리적 구조를 설계 할 수 있다. 오라클 서버 구조에 대한 이해를 못하더라도 쉽게 데이터베이스를 구축할 수 있다. 주로 검색이 이루어지는 D/W 환경에 가장 적합한 물리적 구조 설계가 가능하다. 많은 사용자가 동시에 입력, 수정, 삭제, 조회 하는 OLTP 환경에서 빠른 성능이 요구되는 DB의 물리적 구조 설계에 적합하다.

ASSM CREATE TABLESPACE [tablespace명] DATAFILE ‘[디렉토리와 file명]’ SIZE [크기] SEGMENT SPACE MANAGEMENT [ MANUAL | AUTO ]; 1) 오라클 9i 버전부터 사용가능 하다. 2) Free-List, FreeList-Group, PCTUSED는 오라클 서버에 의해 자동 관리 된다. 3) Locally Managed Tablespace 타입에서 사용 가능하며, LOCAL UNIFORM절을 사용하면 5의 배수단위로 블록을 할당하며, LOCAL AUTOALLOCATE절을 사용하면 DB_BLOCK_SIZE가 16K 이상인 경우 1M 단위로 블록을 할당한다.

Tablespace와 Table과의 관계 Create tablespace insa Datafile ‘d:\data\insa1.dbf’ size 500m Extent Management Dictionary Default Storage(initial 10 M next 10 M minextents 1 maxextents 121 pctincrease 50); 1) Data-Dictionary Type Cteate table emp (no char(3), name varchar2(10)) Tablespace insa Storage(initial 10 M next 10 M minextents 1 maxextents 121 pctincrease 50); Create tablespace insa Datafile ‘d:\data\insa1.dbf’ size 500m Extent Management Local Uniform size 1m; 2) Locally Management Type Cteate table emp (no char(3), name varchar2(10)) Tablespace insa Storage(initial 1 M next 1 M minextents 1 maxextents UNLIMITED pctincrease 0);

2) 블록 크기의 설계

설치방법(v10g)

블록설계와 성능문제 aaa001 db_block_size (4096 byte) aaa001 aaa002 작은 블록 1 주종면 서울 ………………………….. ……….……………… 100 주영현 부산 aaa001 db_block_size (2048byte) 1 주종면 서울 …….. 50 홍길동 부산 aaa002 51 유관순 서울 100 주영현 부산 작은 블록 적은 행이 저장되기 때문에 한 개 블록에 대한 사용 자들의 검색이 큰 블록의 경우보다 적기 때문에 경 합이 적게 발생합니다. WHERE 조건에 의한 RANDOM 검색에 탁월한 성능개선이 기대됩니다. 하나의 블록에 적은 수의 행이 저장되기 때문에 불필요한 블록헤드가 많이 발생합니다. 인덱스 스캔 시 보다 많은 블록을 읽을 수 도 있기 때문에 성능이 저하될 수도 있습니다. 큰 블록 하나의 블록의 많은 행을 저장할 수 있기 때문에 작은 블록의 경우보다 오버헤드가 적게 발생합니다. 테이블 전체 스캔의 경우에 탁월한 성능개선이 기대됩니다. 많은 사용자가 동시에 검색하는 경우 많은 행이 하 나의 블록에 저장되어 있기 때문에 집중적인 경합 현상이 발생합니다. 보다 충분한 메모리 공간을 확보할 수 있기 때문에 성능개선이 기대됩니다.

다중 블록 테이블스페이스 판 매 업 무 (OLTP) SALES_SMALL (Block_Size 4k) I_SALES_SMALL SALES1.DBF I_SALES_SMALL I_SALES1.DBF ACC_LARGE (Block_Size 16k) ACC1.DBF ACC2.DBF I_ACC_LARGE I_ACC1.DBF 관리회계 업무 (Data Warehouse)

다중 블록 테이블스페이스 CREATE TABLESPACE sales DATAFILE ‘c:\oracle\oradata\ora90\sales1.dbf’ SIZE 100M BLOCKSIZE 4K ; 1) 판매 업무 CREATE TABLESPACE acc DATAFILE ‘c:\oracle\oradata\ora90\acc1.dbf’ SIZE 100M BLOCKSIZE 16K 2) 관리회계 업무 * 반드시, init.ora 파일에 DB_nK_CACHE_SIZE 파라메터를 정의해야 합니다.

3) 테이블의 물리적 설계

테이블의 물리적 설계 테이블 크기의 물리적 설계 CREATE TABLE s_emp (id NUMBER(7) CONSTRAINT s_emp_id_nn NOT NULL, last_name VARCHAR2(25) CONSTRAINT s_emp_last_name_nn NOT NULL, first_name VARCHAR2(25), userid VARCHAR2(8) CONSTRAINT s_emp_userid NOT NULL, start_date DATE, comments VARCHAR2(255), manager_id NUMBER(7), title VARCHAR2(25), dept_id NUMBER(7) CONSTRAINT s_emp_dept_id_fk References s_dept(id), salary NUMBER(11, 2), commission_pct NUMBER(4, 2), CONSTRAINT s_emp_id_pk PRIMARY KEY (id), CONSTRAINT s_emp_userid_uk UNIQUE (userid), CONSTRAINT s_emp_commission_pct_ck CHECK (commission_pct IN (10, 12.5, 15, 17.5, 20))) TABLESPACE SALES STORAGE (INITIAL 10K NEXT 10K MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 50) PCTFREE 10 PCTUSED 40 FREELIST 1 ; 테이블 크기의 물리적 설계

Table 설계 시 주의사항 1) 저장될 테이블스페이스를 반드시 지정하라. (TABLE-Level, USER-Level, SERVER-Level) 2) 블록 영역을 위해 INITIAL, NEXT, PCTFREE, PCTUSED 파라메터를 충분히 고려하라.(Fragmentation 제거, 공간 관리, 성능향상) 3) 테이블의 PARTITION 여부와 CLUSTER 여부를 결정하라.

4) INITIAL, NEXT의 크기 계산

블록 구조 aaa001 블록헤드 영역 : 블록주소, 태이블 디렉토리, 행 디렉토리, 트랜잭션 슬롯 DB_BLOCK_SIZE 2048 Byte Table Directory : 클러스트 생성시 관련 테이블 정보를 저장 Row Directory : 블록 내에 저장된 Row에 대한 정보를 저장(4 byte 소요) 행 데이터 영역 : 행 데이터를 저장할 수 있는 빈 공간(행수 * 2)

테이블의 크기 계산 요구되는 블록 헤드의 총 크기를 계산한다. Step-1 하나의 블록 당 사용 가능한 데이터 영역의 크기를 계산한다. Step-2 한 개 행의 평균 길이를 계산한다. Step-3 한 개 행의 전체 평균 크기를 계산한다. Step-4 한 개 블록에 저장할 수 있는 평균 행의 수를 계산한다. Step-5 하나의 테이블에 요구되는 INITIAL의 크기를 계산한다. Step-6

Step-1 요구되는 블록 헤드의 총 크기를 계산한다. * 전체 소요되는 블록헤드의 크기 = (Fixed Header + Variable Transaction Header) + (Table Directory + Row Directory) 1) Fixed Header = 57 바이트(기본적으로 요구되는 블록헤드의 크기) 2) Variable Transaction Header = INITRANS 파라메터의 수 마다 23 바이트 3) Table Directory = 4 바이트(기본값) 4) Row Directory = 2 x 한 개 블록에 저장되는 Row 수 (예) INITRANS = 1인 경우 전체 블록헤드의 크기 = (57 + (23 * 1)) + (4 + (2 * R)) = 80 + ( 4 + (2 * R))

Step-2 하나의 블록 당 사용 가능한 데이터 영역의 크기를 계산한다. * 블록 당 사용 가능한 데이터 영역의 크기 = ( Block_Size - [Step-1의 결과] ) - ( (Block_Size - (Fixed Header + Variable Transaction Header)) * ( PCTFREE / 100 )) 1) Block_Size = 2048 바이트 (db_block_size 파라메터에 의해 결정) 2) Fixed Header = 57 바이트(기본적으로 요구되는 블록헤드의 크기) 3) Variable Transaction Header = INITRANS 파라메터의 수 마다 23 바이트 4) PCTFREE = 10 % (테이블 생성시 기본값) (예) BLOCK SIZE가 2048 Byte, PCTFREE=10인 경우 데이터 영역의 크기 = (2048 – ( 80 + (4 + 2R)) - ((2048 – 80) * (10 / 100)) = (1768 – 2R) Byte

(예) 한 개 행의 평균길이 = 5 + 5 + 5 + 6 + 6 + 6 = 33 Byte Step-3 한 개 행의 평균 길이를 계산한다. * 한 개 행의 평균길이 CHAR 타입 = 정의된 길이 VARCHAR2 타입 = 정의된 길이 DATE 타입 = 7 Byte NUMBER 타입 = ( 정수 값의 길이 / 2) + 1 CREATE TABLE s_item (ord_id NUMBER(7),  7 / 2 + 1 = 5 item_id NUMBER(7),  7 / 2 + 1 = 5 product_id NUMBER(7),  7 / 2 + 1 = 5 price NUMBER(11, 2),  9 / 2 + 1 = 6 quantity NUMBER(9),  9 / 2 + 1 = 6 quantity_shipped NUMBER(9))  9 / 2 + 1 = 6 (예) 한 개 행의 평균길이 = 5 + 5 + 5 + 6 + 6 + 6 = 33 Byte

Step-4 한개 행의 전체 평균 크기를 계산한다. * 전체 행의 평균 크기 = 1행 당 오버헤드 + F + V + D 1) 1행 당 오버헤드 = 3 Byte (기본값) 2) F = 해당 컬럼이 250 Byte 이하면 1 Byte x 컬럼 수 3) V = 해당 컬럼이 250 Byte 이상이면 3 Byte x 컬럼 수 4) D = Step-3의 결과 (예) s_item 테이블에서 250 byte 이하 컬럼 수 = 6, 250 byte 이상 컬럼 수 = 0, 한 개 행의 평균길이 = 33 byte 인 경우 전체 행의 평균 크기 = ( 3 + ( 1 * 6) + (3 * 0) + 33) = 42 Byte

Step-5 한 개 블록에 저장할 수 있는 평균 행의 수를 계산한다. * 한 개 블록에 저장하는 평균 행의 수 = Step-2 / Step-4 1) Step-2 = 한 개 블록에 저장할 수 있는 평균 데이터 영역의 크기 2) Step-4 = 전체 행의 평균 크기 (예) Step-2의 결과는 (1768 – 2R), Step-4의 결과는 42 Byte인 경우 한 개 블록의 평균 행수 R = ( 1768 – 2R ) / 42 42 R = ( 1768 – 2R ) 44 R = 1768 R = 40 (Row / 1-Block)

Step-6 하나의 테이블에 요구되는 INITIAL의 크기를 계산한다. 2) 테이블을 생성하기 위해 요구되는 INITIAL의 Byte 크기 = 요구되는 블록 수 * 2048 (db_block_size의 값) (예) Step-5의 결과가 40, 테이블에 10,000개 행이 저장되어 있는 경우 1) 요구되는 블록 수 = 10000 / 40 = 250 blocks 2) 요구되는 INITIAL의 크기 = 250 * 2048 = 512,000 byte = 512 KB

Pctincrease 파라메터 * Next = next + (next * pctincrease / 100) = 10 + (10 * 50 / 100) = 10 + ( 10 * 1 / 2) = 10 + 5 = 15 k  7.5 block (15000 byte / 2048 byte)  10 block (5의 배수단위로 증가) = 20 k Cteate table emp (no char(3), name varchar2(10)) Storage(initial 10 k next 10 k minextents 1 maxextents 121 pctincrease 50); aaa001 aaa002 aaa003 aaa004 aaa005 EMP 1주종면110 2주영현132 ……. …… 10 k Initial aaa016 aaa017 aaa018 aaa019 aaa020 ……. …… 1주종면110 2주영현132 10 k Next aaa006 aaa007 aaa008 aaa009 aaa010 1주종면110 2주영현132 ……. …… aaa011 aaa012 aaa013 aaa014 aaa015 20 k Next

테이블 설계의 결과 CREATE TABLE s_item (item_no NUMBER(7) CONSTRAINT dept_id_nn NOT NULL, product_id VARCHAR2(25) CONSTRAINT dept_name_nn NOT NULL, ord_id NUMBER(7) CONSTRAINT dept_id_pk PRIMARY KEY (id)), price NUMBER(7), quantity NUMBER(7), TABLESPACE SALES STORAGE ( INITIAL 512 K NEXT 512 K PCTINCREASE 50) PCTFREE 10 PCTUSED 40; 테이블의 물리적 설계 결과

5) 인덱스의 크기 설계

인덱스의 크기 계산 요구되는 블록 헤드의 총 크기를 계산한다. Step-1 하나의 블록 당 사용 가능한 데이터 영역의 크기를 계산한다. Step-2 인덱스 컬럼의 평균 길이를 계산한다. Step-3 전체 행의 평균 인덱스 크기를 계산한다. Step-4 하나의 테이블에 요구되는 INITIAL의 크기를 계산한다. Step-5

Step-1 요구되는 블록 헤드의 총 크기를 계산한다. * 전체 소요되는 블록헤드의 크기 = (Fixed Header + Variable Transaction Header) 1) Fixed Header = 113 바이트(기본적으로 요구되는 블록헤드의 크기) 2) Variable Transaction Header = INITRANS 파라메터의 수 마다 23 바이트 (예) INITRANS = 2(기본값) 인 경우 전체 블록헤드의 크기 = ( 113 + (23 * 2)) = 159 Bytes

Step-2 하나의 블록 당 사용 가능한 데이터 영역의 크기를 계산한다. * 블록 당 사용 가능한 데이터 영역의 크기 = ( Block Size - [Step-1의 결과] ) * ( 1 - PCTFREE / 100 ) 1) Block Size = 2048 바이트 (db_block_size 파라메터에 의해 결정) 2) PCTFREE = 10 % (테이블 생성시 기본값) (예) BLOCK SIZE가 2048 Byte, PCTFREE=10인 경우 데이터 영역의 크기 = ((2048 – 159) * (1 - 10 / 100)) = 1700.1 Byte

Step-3 인덱스 컬럼의 평균 길이를 계산한다. * 인덱스로 생성된 컬럼의 평균길이 Not Null인 컬럼의 평균길이 = AVG( VSIZE(인덱스 컬럼명)) Null 컬럼의 평균길이 = 1 * 예를 들어, Not Null인 2개 컬럼으로 복합 인덱스가 생성된 테이블에서 인덱스의 평균길이는 CREATE INDEX I_big_emp_deptno_empno ON big_emp (deptno, empno);  SELECT AVG ( NVL ( VSIZE (deptno), 1)) + AVG ( NVL ( VSIZE (empno), 1)) FROM big_emp ;  결과 : 22 Byets (예) 한 개 행의 평균길이 = 22 Byte

Step-4 전체 행의 평균 인덱스 크기를 계산한다. * 전체 행의 평균 크기 = 1행 당 오버헤드 + ROWID + F + V + D 1) 1행 당 오버헤드 = 2 Byte (기본값) 2) ROWID = 6 Byte 3) F = 해당 컬럼이 128 Byte 이하면 1 Byte x 컬럼 수 4) V = 해당 컬럼이 128 Byte 이상이면 3 Byte x 컬럼 수 5) D = Step-3의 결과 (예) s_item 테이블에서 NUMBER(11) 컬럼으로 구성된 인덱스 크기는 전체 행의 평균 크기 = ( 2 + 6 + (1 * 1) + (3 * 0) + 22 = 31 Byte

Step-5 하나의 인덱스에 요구되는 INITIAL의 크기를 계산한다. 1) 한 블록에 저장할 수 있는 인덱스 수 a = Step-2 / Step-4 2) 인덱스를 생성하기 위해 요구되는 INITIAL의 Byte 크기 = ( 1.05 * ( Not null인 행수 / a )) * 2048 (예) Step-2의 결과가 1700.1 byte, Step-4의 결과가 31 Byte인 경우 1) 한 블록에 저장할 수 있는 인덱스 수 = 1700 / 31 = 54 개 2) 요구되는 INITIAL의 크기 = (1.05 * (10000 / 54)) * 2048 = 398,222 Bytes = 398 Kbytes

인덱스 생성 스크립트 $ vi summit2.sql CREATE TABLE s_region (id NUMBER(7) CONSTRAINT s_region_id_nn NOT NULL, name VARCHAR2(50) CONSTRAINT s_region_name_nn NOT NULL, CONSTRAINT s_region_id_pk PRIMARY KEY (id) USING INDEX PCTFREE 20 TABLESPACE i_sales STORAGE (INITIAL 10K NEXT 10K), CONSTRAINT s_region_name_uk UNIQUE (name)) TABLESPACE SALES STORAGE (INITIAL 10K NEXT 10K PCTINCREASE 0) PCTFREE 10 PCTUSED 40; $ vi summit2.sql CREATE TABLE s_dept (id NUMBER(7) CONSTRAINT s_dept_id_nn NOT NULL, name VARCHAR2(25) CONSTRAINT s_dept_name_nn NOT NULL, region_id NUMBER(7), CONSTRAINT s_dept_id_pk PRIMARY KEY (id) USING INDEX PCTFREE 20 TABLESPACE i_sales STORAGE (INITIAL 10K NEXT 10K)) TABLESPACE SALES STORAGE (INITIAL 10K NEXT 10K PCTINCREASE 50) PCTFREE 10 PCTUSED 40;

2. SQL 튜닝 방법론

튜닝 Method 디자인 튜닝 데이터-디자인튜닝 응용PG설계의 튜닝 정규화 및 역정규화 테이블의 파티션닝 여부 로컬/글로벌 인덱스 App-PG의 구조설계 SQL 튜닝 논리적구조의 튜닝 SQL 문장의 튜닝 실행경로의 튜닝 테이블의 저장구조 Foreign-Key 및 제약조건의여부 인덱스사용 결정 인덱스 타입분석 클러스터의 활용 옵티마이저 활용 조인기법의 활용 서버 튜닝 메모리 구조의 튜닝 I/O와 물리적구조 자원에 대한 경합 버퍼캐시 영역,공유-풀 영역,로그-풀 영역 Disk I-O, 저장구조의 물리적 설계분석 Sort공간,Lock 경합 Undo 공간 OS와 Network의 경합 System 튜닝 Network 튜닝

튜닝 Methodology 준비단계 (Step-1) 분석/튜닝단계 (Step-2) 결과단계 (Step-3) 고객 Interview 디자인 튜닝 튜닝후 자료수집/분석 산출물 작성 고객 Interview 튜닝결과 평가 자료수집 및 분석 (Check-List 작성) SQL 튜닝 튜닝계획 수립 서버 튜닝 고객 Interview System/Network 튜닝 의문점/문제점 분석 고객 Interview 튜닝대상 적용

Key To Tuning CPU Memory I/O Network S/W Tuning Method Design/Architecture DML SQL or Structure Query SQL or Structure Buffer Cache Area Shared Pool Area Sort Area(Temp Segment) Physical Datafile I/O Logfile I/O Archiver-File I/O Undo Segments Locking Network-Traffic

1) 옵 티 마 이 저

옵티마이저 Full Table Scan 개발자 OPTIMIZER SELECT * FROM emp SQL해석 처리방법 처리순서 우선순위 개발자 OPTIMIZER SQL해석 실행계획 작성 SELECT * FROM emp WHERE deptno=10; COL$ IND$ OBJ$ TAB$ VIEW$ DATA Dictionary 참조

옵티마이저의 종류 공식기반 비용기반

2) SQL 튜닝 Road-Map

SQL 튜닝 절차 테이블과 인덱스의 구조를 최적화 한다. Step-1 SQL 문장을 최적화 한다. Step-2 인덱스 타입을 최적화 한다. Step-3

SQL 튜닝 RoadMap SELECT문 DML문 ORA-01467(01652) 에러가 Road-1 발생했나요 ? Y N (Sort 공간의 구조분석) N SELECT문의 성능이 저하되고 있습니까? Y Road 2-1 (테이블에 대한 구조분석) Road-3 (SQL튜닝) Road 2-2 (인덱스에 대한 구조분석) ORA-1555/1562/1560/1628 에러가 발생했나요 ? DML문 Y Road-4 (UNDO 공간의 구조분석) N DML문의 성능이 저하되고 있습니까? Y Road-5 (Extent의 동적 할당현상) Road-5-1 (사용하지 않는 인덱스의제거)

Road 1 Sorting 공간의 튜닝 <원인> CREATE INDEX, ORDER BY, GROUP BY, DISTINCT, DECODE, UNION, INTERSECT, MINUS와 같은 문장을 통해 대용량 데이터를 분류(Sorting)할 때 Temporary 테이블스페이 스가 부족할 때 발생하는 에러입니다. <조치1> 사용자의 SQL문에서 필요한 컬럼 만을 분류대상으로 설정한다. SELECT * FROM emp ORDER BY ename;  SELECT empno, ename FROM emp ORDER BY ename;

<조치2> 사용자의 SQL문에서 ORDER BY 절에 사용되는 컬럼에 대해 INDEX를 설정한다. SELECT * FROM emp ORDER BY ename; CREATE INDEX I_emp_ename ON emp(ename); SELECT empno, ename FROM emp; <조치3> Temporary 테이블스페이스의 공간을 추가로 늘려주어야 합니다. SQL> ALTER TABLESPACE temp ADD DATAFILE ‘temp02.dbf’ SIZE 500M; 또는 SQL> ALTER DATABASE DATAFILE ‘temp01.dbf’ RESIZE 800M; <조치4> SORT_AREA_SIZE의 값을 추가로 늘려주어야 합니다. SORT_AREA_SIZE = 1000000;

Road 2-1 테이블에 대한 구조분석 <원인1> Row-Chaining 및 Row-Migration 현상이 발생하면 불필요한 블록에 대한 읽기 작업이 발생하기 때문에 성능저하 현상이 발생합니다. (과다한 VARCHAR2 타입의 사용문제) <조치1> ANALYZE 작업을 수행한 후 전체공간의 30%이상에서 발생하면 테이블을 재구성한다. SQL> EXECUTE dbms_redefinition.start_redef_table( ~~~~~ )

SQL> ANALYZE TABLE big_emp COMPUTE STATISTICS; SQL> SELECT * FROM BIG_EMP; Execution Plan --------------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=19 Card=69 Bytes=2484) 1 0 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=19 Card=69 Bytes=2484) (Cost=19 Card=69 SQL> UPDATE big_emp SET ename = '1234567890123456789012345678901234567890', job = '123456789'; SQL> ANALYZE TABLE BIG_EMP COMPUTE STATISTICS; SQL> SELECT * FROM BIG_EMP; Execution Plan --------------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=44 Card=69 Bytes=180) 1 0 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=44 Card=5 Bytes=180) (Cost=44 Card=69

<조치2> EXPORT로 해당 테이블을 백업한 후 삭제하고 다시 IMPORT 한다. (Export시 extents compress 옵션을 반드시 yes로 설정해야함) $ EXP scott/tiger COMPRESS=yes <조치3> 해당 테이블에서 Row-Chaining 및 Row-Migration 을 유발한 행만 삭제 후 재 입력한다. SQL> ANALYZE TABLE emp LIST CHAINED ROWS into chained_rows; SQL> CREATE emp_temp as select * from emp where rowid in (select rowid from chained_rows); SQL> DELETE emp WHERE rowid in (select rowid from chained_rows); SQL> INSERT INTO emp select * from emp_temp;

<원인2> 테이블의 구조적 설계에 문제가 발생하면 성능이 저하될 수 있습니다. <조치1> 해당 테이블이 SYSTEM 테이블스페이스에 저장되어 있는지 확인하십시오. SYSTEM 테이 블스페이스는 자료사전 테이블이 저장되는 공간이므로 집중적인 Disk I-O가 발생하는 공간입니다. SELECT owner, segment_name, tablespace_name FROM dba_segments WHERE tablespace_name = ‘SYSTEM’ and owner = ‘SCOTT’; ALTER TABLE emp MOVE TABLESPACE users;

<조치2> 해당 테이블이 저장되는 테이블스페이스를 Locally Management 테이블스페이스로 생성하십시오. Data-Dictionary 타입의 테이블스페이스는 테이블의 모든 익스텐트 정보를 SYSTEM 테이블스페이스에 저장하기 때문에 성능이 저하될 수 있습니다. CREATE TABLESPSCE sample DATAFILE ‘/disk1/sample1.dbf’ SIZE 500m EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10m;

<조치3> 대용량 데이터가 저장되는 컬럼(VARCHAR2, LONG, LONG RAW)이 있는 테이블을 수직 파티션 또는 수평 파티션 테이블로 분리하십시오. VARCHAR2와 LONG/LONG RAW 컬럼은 검색할 때 불필요한 메모리와 디스크 I-O를 유발시키기 때문에 성능저하 현상을 유발하게 됩니다. CREATE TABLE emp CREATE TABLE emp_pic (empno number, ename varchar2(15)) ; (empno number, pic LONG RAW); CREATE TABLE jeon(idate date, no char(2), name v2(20), qty number) Partition By Range(idate) (Partition t1 values less than(to_date(‘2000’)) Tablespace chul1999, Partition t2 values less than(to_date(‘2001’)) Tablespace chul2000, Partition t3 values less than(MAXVALUE) Tablespace chul2001);

Road 2-2 인덱스에 대한 구조분석 <원인1> 테이블에 대한 입력과 삭제 작업이 빈번하게 발생하는 테이블은 해당 테이블의 밸런싱이 깨지는 현상으로 인해 검색할 때 좋은 성능이 보장되지 않습니다. <조치1> ANALYZE 작업을 수행한 후 인덱스 밸런싱이 30%이상 깨지면 인덱스를 재구성한다. SQL> ANALYZE INDEX I_emp_no VALIDATE STRUCTURE; SQL> SELECT (DEL_LF_ROWS_LEN/LF_ROWS_LEN)*100 ‘Balance’ FROM index_stats; SQL> ALTER INDEX I_emp_no REBUILD; 또는 SQL> ALTER INDEX I_emp_no COALESCE;

SQL> CREATE INDEX i_emp_deptno ON BIG_EMP(deptno); SQL> ANALYZE INDEX i_emp_deptno COMPUTE STATISTICS; SQL> SELECT /*+index(big_emp i_emp_deptno)*/ * FROM BIG_EMP WHERE DEPTNO = 10; Execution Plan --------------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=11 Card=10584 Bytes=294) 1 0 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=11 Card=10584 Bytes=294) 2 1 INDEX SCAN I_EMP_DEPTNO (Cost=11 SQL> DELETE FROM big_emp WHERE empno > 1 and empno < 25000; SQL> ANALYZE INDEX i_emp_deptno COMPUTE STATISTICS; SQL> SELECT /*+index(big_emp i_emp_deptno)*/ * FROM BIG_EMP WHERE DEPTNO = 10; Execution Plan --------------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=53 Card=10584 Bytes=294) 1 0 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=11 Card=10584 Bytes=294) 2 1 INDEX SCAN I_EMP_DEPTNO (Cost=53

<원인2> 인덱스의 구조적 설계에 문제가 발생하면 성능이 저하될 수 있습니다. <조치1> 해당 인덱스가 SYSTEM 테이블스페이스에 저장되어 있는지 확인하십시오. SYSTEM 테이 블스페이스는 자료사전 테이블이 저장되는 공간이므로 집중적인 Disk I-O가 발생하는 공간입니다. SELECT owner, segment_name, tablespace_name FROM dba_segments WHERE tablespace_name = ‘SYSTEM’ and owner = ‘SCOTT’; ALTER INDEX i_emp REBUILD TABLESPACE i_sample;

Explain Plan 분석 (Optimizer 결정) Road 3 SQL문의 분석 Road 3-1 (SQL튜닝) Y Full Table Scan ? 인덱스를 사용하지 못하는 경우인가 ? Y Explain Plan 분석 (Optimizer 결정) Index Scan ? N 대상 컬럼의 분포도가 나쁜가 ? N Road 3-2 (Full-Table Scan의 검토) Y Y Road 3-3 (Index 타입의 검토) N

Road 3-1 SQL문의 튜닝 <원인> 사용자의 실수로 INDEX SCAN되어야 할 SQL문이 FULL TABLE SCAN되는 경우가 빈번 하게 발생합니다. INDEX SCAN을 하도록 SQL문을 조정하십시오. <조치1> 1) 인덱스가 생성된 컬럼을 부정조건(!=, <>, not 등)으로 비교하지 마십시오. 2) IS NULL 조건으로 컬럼을 비교하지 마십시오. 인덱스는 NULL값을 포함하지 않습니다. 3) 해당 컬럼을 표현식, 함수 등으로 변형시키지 마십시오. 4) 해당 컬럼의 데이터 타입에 맞는 조건으로 검색하십시오. 5) 같은 테이블에 있는 다른 컬럼과 비교하지 마십시오. 6) LIKE 연산자를 사용할 때 와일드 카드(%)의 사용에 주의하십시오.

Road 3-2 Full Table Scan의 성능향상 <원인> 분포도가 나쁜 컬럼에 대해서는 인덱스를 사용하지 않는 것이 인덱스를 사용하는 것 보다 좋은 성능이 기대될 수 있습니다. 오라클 사에서는 FULL TABLE SCAN시에 성능 향상을 위해 다음과 같은 기능을 제공합니다. <조치1> Init<DB명>.ora 파일에 DB_FILE_MULTIBLOCK_READ_COUNT 파라메터의 값을 높게 설정하면 FULL TABLE SCAN시에 한번에 지정된 블록 만큼씩 읽기 작업을 할 수 있습니다. DB_FILE_MULTIBLOCK_READ_COUNT = 32

<조치2> 오라클 사에서 제공하는 병렬질의(Parallel Query) 옵션을 사용하면 여러 개의 병렬 프로 세스를 통해 읽기작업을 수행할 수 있기 때문에 FULL TABLE SCAN시에 성능이 향상됨. SQL> CREATE TABLE emp (no number(5), ename varchar2(10)) PARALLEL(DEGREE 3); SQL> SELECT * FROM emp; <조치3> 이미 인덱스가 생성되어 있는 경우에는 임의로 인덱스를 삭제할 수 없으므로 HINT문을 사용하여 실행경로를 바꾸어준다. SQL> SELECT /*+ FULL(emp) PARALLEL(degree 3)*/ * FROM emp;

Road 3-3 인덱스 타입의 검토 INDEX Type의 검토 Instance 튜닝으로! Road3-3-1 N (인덱스선행 컬럼의 결정) N 결합 index를 사용하는가? 여러 개의 컬럼 중 가장 분포도가 좋은 컬럼을 기준으로 생성 하였는가 ? Y INDEX Type의 검토 2개 이상의 테이블이 조인되는가 ? N Y Road3-3-2 (인덱스타입 의 결정) N 분포도가 좋은 테이블을 기준 테이블로 설정하였는가? Y Road3-3-3 (조인순서의 결정) N Y Instance 튜닝으로!

Index Organization Index Road 3-3-2 인덱스 타입의 결정 <원인> 검색하고자 하는 컬럼의 데이터 속성 및 크기에 따라 적절한 인덱스를 사용하면 보다 성능 향상을 기대할 수 있습니다. B*Tree Index 일반적인 테이블 정보에 대한 검색 시 사용된다. 대용량 데이터베이스 환경에서 나쁜 분포도에 대한 컬럼 검색 시 사용된다. BitMap Index Reverse Index 테이블의 인덱스 컬럼에 대해 삭제가 빈번하게 발생할 때 사용된다. Descending Index 최신 행 정보를 우선시 조회할 때 사용 된다. Function-Based Index 검색조건에서 계산공식을 사용하는 경우 결과치를 인덱스로 생성할 수 있다. Index Organization Index Primary Key를 이용하여 Text 컬럼의 정보를 검색할 때 사용된다.

Road 3-3-3 조인 순서의 결정 <원인> 여러 개의 테이블에서 공통 컬럼을 기준으로 논리적 결합을 할 때 가장 범위가 좋은 테이블 을 먼저 검색 한 후 범위가 나쁜 테이블을 검색하는 것이 성능에 도움이 됩니다. <조치1> 예를 들어, A 테이블은 200행, B는 100, C는 10 행이 저장되어 있을 때 A,B,C 순으로 조인 하는 것 보다는 C, B, A 순으로 조인하는 것이 유리합니다. SQL> SELECT * FROM A, B, C WHERE ~~~~ ; SQL> SELECT * FROM C, B, A WHERE ~~~~ ;

감사합니다! 질 문 Q & A