Transcript oracle_db_물리튜닝

WWW.DBGUIDE.NET
오라클 DB 최적의 물리적 설계 및
튜닝기법 세미나
[오라클 DB 최적의 물리적 설계 및 SQL 튜닝 방법론]
PLAN 정보기술
Consultant : 주 종 면
[email protected]
011-864-1858
WWW.DBGUIDE.NET
강사 : 주 종 면 E-MAIL : [email protected]
다음 <OracleZone> 카페 운영진
한국 데이터베이스 진흥센터 <오라클의 모든 것> 카페시샵
현. PLAN 정보기술 대표 컨설턴트
전. Computer4You Co.(미국 Ohio주 소재) 데이터베이스분야 컨설턴트
전. 오라클 교육센터 공인강사 및 컨설턴트
전. 프랑스 쌍고방 그룹 한국 베트로텍스 정보관리팀 데이터베이스 관리자
전. 삼미그룹 삼미전산(주) 시스템사업부 프로그래머
경 력
전. 육군 26사단 전산실 프로그래머
저 서
- 메리놀 병원/동의의료원 DB 컨설팅
- 도서출판-
- 성창기업 DB 컨설팅
2004년 출간에정 “오라클 10g 장애와 복구”
대림출판사
- 농협/하나은행/동남은행 DB 컨설팅
2003년 출간 "오라클 데이터베이스 모델링“ 프리렉 출판사
- 일본 전자계산소 자산관리 시스템 개발
"오라클 SQL 튜닝 & 서버 튜닝” 프리렉 출판사
- 프랑스 쌍고방그룹 Vetrotex Korea Co.
"오라클 9i SQL & PL/SQL”
프리렉 출판사
2002년 출간 "클릭하세요 오라클 9i“
대림 출판사
- 제일제당(주) 생산/물류관리 시스템 분석/설계/개발
2000년 출간 "클릭하세요 오라클 8i“
대림 출판사
- 밀양대학교 학사/행정관리 시스템 분석/설계/개발
1999년 출간 "지나와 함께하는 오라클 8“
대림 출판사
- 한국 중공업 인사관리 시스템 개발(분석/설계)
1998년 출간 "ORACLE-Unleashed“
대림 출판사
- 우경철강(주) MIS 시스템(분석/설계)
1997년 출간 "SQL-Unleashed“
대림 출판사
생산/공정/근태/회계관리 시스템 개발
- 육군 저축관리 시스템 개발
- 공군 물류관리 시스템 개발
월간 마이크로 소프트웨어 특집기사
- 메리놀병원 홈페이지 개발
월간 마이크로 소프트웨어 DB 프로그래밍 독점연재
- 중고 자동차매매조합 홈페이지 개발
월간 PC 자격증 "OCP" 분야 독점연재
- 외 공장자동화 시스템 개발
WWW.DBGUIDE.NET
-목
차-
1. 테이블과 인덱스의 물리적 설계
1) 테이블 스페이스의 설계
2) 블록 크기의 설계
3) 테이블의 물리적 설계
4) INITIAL, NEXT의 크기 계산
5) 인덱스의 크기 설계
2. SQL 튜닝 방법론
1) 옵 티 마 이 저
2) SQL 튜닝 Road-Map
WWW.DBGUIDE.NET
1. 테이블과 인덱스의
물리적 설계
WWW.DBGUIDE.NET
1) 테이블 스페이스의 설계
WWW.DBGUIDE.NET
논리적/물리적 저장구조
Database
Tablespace
File
Segment
Extent
Logical
Physical
Block
WWW.DBGUIDE.NET
Tablespace의 종류
1) Data-Dictionary Type
CREATE TABLESPACE [tablespace명]
DATAFILE ‘[디렉토리와 file명]’ SIZE [크기]
EXTENT MANAGEMENT DICTIONARY
DEFAULT STORAGE( INITIAL
[크기]
NEXT
[크기]
MINEXTENTS [n]
MAXEXTENTS [n]
PCTINCREASE [n]);
2) Locally Management Type
CREATE TABLESPACE [tablespace명]
DATAFILE ‘[디렉토리와 file명]’ SIZE [크기]
EXTENT MANAGEMENT LOCAL [UNIFORM SIZE [크기]]
[AUTOALLOCATE];
WWW.DBGUIDE.NET
테이블스페이스의 비교
Data Dictionary 타입
Locally Management 타입
오라클 8i 버전까지 기본 테이블스페이스
오라클 9i 버전부터 기본 테이블스페이스
데이터 익스텐트와 롤백 세그멘트의 익스텐
트 정보를 SYSTEM 데이터 파일에 갱신
데이터 익스텐트와 롤백 세그멘트의 익스텐
트 정보를 자신의 데이터 파일에 갱신
INITIAL,NEXT,PCTINCREASE,FREELIST
PCTFREE를 제외한 파라메터는 오라클 서버
,PCTFREE,PCTUSED를 사용자가 직접 정의 에 의해 자동 할당
해당 객체에 맞는 최적의 물리적 구조를 설계
할 수 있다.
오라클 서버 구조에 대한 이해를 못하더라도
쉽게 데이터베이스를 구축할 수 있다.
주로 검색이 이루어지는 D/W 환경에 가장
적합한 물리적 구조 설계가 가능하다.
많은 사용자가 동시에 입력, 수정, 삭제, 조회
하는 OLTP 환경에서 빠른 성능이 요구되는
DB의 물리적 구조 설계에 적합하다.
WWW.DBGUIDE.NET
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 단위로 블록을 할당한다.
WWW.DBGUIDE.NET
Tablespace와 Table과의 관계
1) Data-Dictionary Type
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);
2) Locally Management Type
Create tablespace insa
Datafile ‘d:\data\insa1.dbf’ size 500m
Extent Management Local
Uniform size 1m;
Cteate table emp
(no char(3), name varchar2(10))
Tablespace insa
Storage(initial 10 M
next 10 M
minextents 1
maxextents 121
pctincrease 50);
Cteate table emp
(no char(3), name varchar2(10))
Tablespace insa
Storage(initial 1 M
next 1 M
minextents 1
maxextents UNLIMITED
pctincrease 0);
WWW.DBGUIDE.NET
2) 블록 크기의 설계
WWW.DBGUIDE.NET
설치방법(v10g)
WWW.DBGUIDE.NET
블록설계와 성능문제
db_block_size
(2048byte)
db_block_size
(2048byte)
aaa002
aaa001
db_block_size
(4096 byte)
aaa001
1 주종면 서울
1 주종면 서울
51 유관순 서울
………………………
…..
……..
……..
……….………………
……..
……..
50 홍길동 부산
100 주영현 부산
작은 블록
100 주영현 부산
큰 블록
적은 행이 저장되기 때문에 한 개 블록에 대한 사용
자들의 검색이 큰 블록의 경우보다 적기 때문에 경
합이 적게 발생합니다.
많은 사용자가 동시에 검색하는 경우 많은 행이 하
나의 블록에 저장되어 있기 때문에 집중적인 경합
현상이 발생합니다.
WHERE 조건에 의한 RANDOM 검색에 탁월한
성능개선이 기대됩니다.
테이블 전체 스캔의 경우에 탁월한 성능개선이
기대됩니다.
하나의 블록에 적은 수의 행이 저장되기 때문에
불필요한 블록헤드가 많이 발생합니다.
하나의 블록의 많은 행을 저장할 수 있기 때문에
작은 블록의 경우보다 오버헤드가 적게 발생합니다.
인덱스 스캔 시 보다 많은 블록을 읽을 수 도 있기
때문에 성능이 저하될 수도 있습니다.
보다 충분한 메모리 공간을 확보할 수 있기 때문에
성능개선이 기대됩니다.
WWW.DBGUIDE.NET
다중 블록 테이블스페이스
판매업무
(OLTP)
관리회계 업무
(Data Warehouse)
SALES_SMALL
(Block_Size 4k)
SALES1.DBF
I_SALES_SMALL
(Block_Size 4k)
I_SALES1.DBF
ACC_LARGE
(Block_Size 16k)
ACC1.DBF
ACC2.DBF
I_ACC_LARGE
(Block_Size 16k)
I_ACC1.DBF
WWW.DBGUIDE.NET
다중 블록 테이블스페이스
1) 판매 업무
CREATE TABLESPACE sales
DATAFILE ‘c:\oracle\oradata\ora90\sales1.dbf’ SIZE 100M
BLOCKSIZE 4K ;
2) 관리회계 업무
CREATE TABLESPACE acc
DATAFILE ‘c:\oracle\oradata\ora90\acc1.dbf’ SIZE 100M
BLOCKSIZE 16K
* 반드시, init.ora 파일에 DB_nK_CACHE_SIZE 파라메터를 정의해야 합니다.
WWW.DBGUIDE.NET
3) 테이블의 물리적 설계
WWW.DBGUIDE.NET
테이블의 물리적 설계
CREATE TABLE s_emp
(id
NUMBER(7)
last_name
VARCHAR2(25)
first_name
VARCHAR2(25),
userid
VARCHAR2(8)
start_date
DATE,
comments
VARCHAR2(255),
manager_id
NUMBER(7),
title
VARCHAR2(25),
dept_id
NUMBER(7)
CONSTRAINT s_emp_id_nn
NOT NULL,
CONSTRAINT s_emp_last_name_nn NOT NULL,
CONSTRAINT s_emp_userid
NOT NULL,
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
MINEXTENTS
1
PCTINCREASE 50)
PCTFREE 10
PCTUSED
40
NEXT
MAXEXTENTS
FREELIST
10K
UNLIMITED
1 ;
WWW.DBGUIDE.NET
Table 설계 시 주의사항
1) 저장될 테이블스페이스를 반드시 지정하라.
(TABLE-Level, USER-Level, SERVER-Level)
2) 블록 영역을 위해 INITIAL, NEXT, PCTFREE, PCTUSED 파라메터를
충분히 고려하라.(Fragmentation 제거, 공간 관리, 성능향상)
3) 테이블의 PARTITION 여부와 CLUSTER 여부를 결정하라.
WWW.DBGUIDE.NET
4) INITIAL, NEXT의 크기 계산
WWW.DBGUIDE.NET
블록 구조
블록헤드 영역 : 블록주소, 태이블 디렉토리,
행 디렉토리, 트랜잭션 슬롯
DB_BLOCK_SIZE
2048 Byte
aaa001
Table Directory : 클러스트 생성시 관련 테이블
정보를 저장
Row Directory : 블록 내에 저장된 Row에 대한
정보를 저장(4 byte 소요)
행 데이터 영역 : 행 데이터를 저장할 수
있는 빈 공간(행수 * 2)
WWW.DBGUIDE.NET
테이블의 크기 계산
Step-1
요구되는 블록 헤드의 총 크기를 계산한다.
Step-2
하나의 블록 당 사용 가능한 데이터 영역의 크기를 계산한다.
Step-3
한 개 행의 평균 길이를 계산한다.
Step-4
한 개 행의 전체 평균 크기를 계산한다.
Step-5
한 개 블록에 저장할 수 있는 평균 행의 수를 계산한다.
Step-6
하나의 테이블에 요구되는 INITIAL의 크기를 계산한다.
WWW.DBGUIDE.NET
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))
WWW.DBGUIDE.NET
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
WWW.DBGUIDE.NET
Step-3 한 개 행의 평균 길이를 계산한다.
* 한 개 행의 평균길이
CHAR 타입
= 정의된 길이
VARCHAR2 타입 = 정의된 길이
DATE 타입
= 7 Byte
NUMBER 타입
= ( 정수 값의 길이 / 2) + 1
CREATE TABLE s_item
(ord_id
item_id
product_id
price
quantity
quantity_shipped
NUMBER(7),
NUMBER(7),
NUMBER(7),
NUMBER(11, 2),
NUMBER(9),
NUMBER(9))






7
7
7
9
9
9
/
/
/
/
/
/
2
2
2
2
2
2
+
+
+
+
+
+
1
1
1
1
1
1
=
=
=
=
=
=
5
5
5
6
6
6
(예) 한 개 행의 평균길이 = 5 + 5 + 5 + 6 + 6 + 6 = 33 Byte
WWW.DBGUIDE.NET
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
WWW.DBGUIDE.NET
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)
WWW.DBGUIDE.NET
Step-6 하나의 테이블에 요구되는 INITIAL의 크기를 계산한다.
1) 테이블을 생성하기 위해 요구되는 블록 수 = 전체 행 수 / Step-5
2) 테이블을 생성하기 위해 요구되는 INITIAL의 Byte 크기
= 요구되는 블록 수 * 2048 (db_block_size의 값)
(예) Step-5의 결과가 40, 테이블에 10,000개 행이 저장되어 있는 경우
1) 요구되는 블록 수
= 10000 / 40
2) 요구되는 INITIAL의 크기 = 250 * 2048
= 512,000 byte
= 512 KB
= 250 blocks
WWW.DBGUIDE.NET
Pctincrease 파라메터
* Next = next + (next * pctincrease / 100)
Cteate table emp
(no char(3), name varchar2(10))
Storage(initial
10 k
next
10 k
minextents 1
maxextents 121
pctincrease 50);
= 10 + (10 * 50 / 100)
= 10 + ( 10 * 1 / 2)
= 10 + 5
= 15 k
 7.5 block (15000 byte / 2048 byte)
 10 block (5의 배수단위로 증가)
= 20 k
EMP
aaa001 aaa002 aaa003 aaa004 aaa005
1주종면110
2주영현132
…….
……
…….
…….
……
…….
…….
……
…….
…….
……
…….
10 k
Initial
10 k
Next
aaa016 aaa017 aaa018 aaa019 aaa020
…….
……
…….
1주종면110
2주영현132
…….
……
…….
…….
……
…….
…….
……
…….
…….
……
…….
aaa006 aaa007 aaa008 aaa009 aaa010
1주종면110
2주영현132
…….
……
…….
…….
……
…….
…….
……
…….
…….
……
…….
aaa011 aaa012 aaa013 aaa014 aaa015
1주종면110
2주영현132
…….
……
…….
…….
……
…….
…….
……
…….
…….
……
…….
Next
20 k
WWW.DBGUIDE.NET
테이블 설계의 결과
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
NEXT
PCTINCREASE
PCTFREE 10
PCTUSED 40;
테이블의 물리적 설계 결과
512 K
512 K
50)
WWW.DBGUIDE.NET
5) 인덱스의 크기 설계
WWW.DBGUIDE.NET
인덱스의 크기 계산
Step-1
요구되는 블록 헤드의 총 크기를 계산한다.
Step-2
하나의 블록 당 사용 가능한 데이터 영역의 크기를 계산한다.
Step-3
인덱스 컬럼의 평균 길이를 계산한다.
Step-4
전체 행의 평균 인덱스 크기를 계산한다.
Step-5
하나의 테이블에 요구되는 INITIAL의 크기를 계산한다.
WWW.DBGUIDE.NET
Step-1 요구되는 블록 헤드의 총 크기를 계산한다.
* 전체 소요되는 블록헤드의 크기
=
(Fixed Header + Variable Transaction Header)
1) Fixed Header = 113 바이트(기본적으로 요구되는 블록헤드의 크기)
2) Variable Transaction Header = INITRANS 파라메터의 수 마다 23 바이트
(예) INITRANS = 2(기본값) 인 경우
전체 블록헤드의 크기 = ( 113 + (23 * 2))
= 159 Bytes
WWW.DBGUIDE.NET
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
WWW.DBGUIDE.NET
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 Byte
 결과 :
22 Byets
WWW.DBGUIDE.NET
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
WWW.DBGUIDE.NET
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
WWW.DBGUIDE.NET
인덱스 생성 스크립트
$ 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;
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;
WWW.DBGUIDE.NET
2. SQL 튜닝 방법론
WWW.DBGUIDE.NET
튜닝 Method
데이터-디자인튜닝
디자인 튜닝
정규화 및 역정규화
테이블의 파티션닝 여부
로컬/글로벌 인덱스
App-PG의 구조설계
응용PG설계의 튜닝
논리적구조의 튜닝
SQL 튜닝
실행경로의 튜닝
SQL 문장의 튜닝
테이블의 저장구조
Foreign-Key 및
제약조건의여부
인덱스사용 결정
인덱스 타입분석
클러스터의 활용
옵티마이저 활용
조인기법의 활용
메모리 구조의 튜닝
서버 튜닝
I/O와 물리적구조
자원에 대한 경합
System 튜닝
Network 튜닝
버퍼캐시 영역,공유-풀
영역,로그-풀 영역
Disk I-O, 저장구조의
물리적 설계분석
Sort공간,Lock 경합
Undo 공간
OS와 Network의 경합
WWW.DBGUIDE.NET
튜닝 Methodology
준비단계
(Step-1)
분석/튜닝단계
(Step-2)
결과단계
(Step-3)
고객 Interview
디자인 튜닝
튜닝후 자료수집/분석
자료수집 및 분석
(Check-List 작성)
SQL 튜닝
튜닝결과 평가
튜닝계획 수립
서버 튜닝
산출물 작성
고객 Interview
System/Network 튜닝
고객 Interview
의문점/문제점 분석
튜닝대상 적용
고객 Interview
WWW.DBGUIDE.NET
Key To Tuning
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
CPU
Memory
I/O
Network
S/W
WWW.DBGUIDE.NET
1) 옵 티 마 이 저
WWW.DBGUIDE.NET
옵티마이저
처리방법
개발자
처리순서
OPTIMIZER
우선순위
SELECT *
FROM emp
WHERE deptno=10;
SQL
실행계획
작성
해석
Full
Table
Scan
참
조
COL$
참
조
OBJ$
IND$
TAB$
VIEW$
DATA Dictionary
WWW.DBGUIDE.NET
옵티마이저의 종류
공식기반
비용기반
WWW.DBGUIDE.NET
2) SQL 튜닝 Road-Map
WWW.DBGUIDE.NET
SQL 튜닝 절차
Step-1 테이블과 인덱스의 구조를 최적화 한다.
Step-2 SQL 문장을 최적화 한다.
Step-3 인덱스 타입을 최적화 한다.
WWW.DBGUIDE.NET
SQL 튜닝 RoadMap
SELECT문
ORA-01467(01652)
에러가
발생했나요 ?
Road-1
Y
(Sort 공간의
구조분석)
N
SELECT문의 성능이
저하되고 있습니까?
Road 2-1
Y
(테이블에 대한
구조분석)
Road-3
(SQL튜닝)
Road 2-2
(인덱스에 대한
구조분석)
DML문
ORA-1555/1562/1560/1628
Y
Road-4
(UNDO 공간의
구조분석)
에러가
발생했나요 ?
N
DML문의 성능이
저하되고 있습니까?
Y
Road-5
Road-5-1
(Extent의 동적
할당현상)
(사용하지 않는
인덱스의제거)
WWW.DBGUIDE.NET
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;
WWW.DBGUIDE.NET
<조치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;
WWW.DBGUIDE.NET
Road 2-1
테이블에 대한 구조분석
<원인1>
Row-Chaining 및 Row-Migration 현상이 발생하면 불필요한 블록에 대한 읽기 작업이
발생하기 때문에 성능저하 현상이 발생합니다. (과다한 VARCHAR2 타입의 사용문제)
<조치1>
ANALYZE 작업을 수행한 후 전체공간의 30%이상에서 발생하면 테이블을 재구성한다.
SQL> EXECUTE dbms_redefinition.start_redef_table( ~~~~~ )
WWW.DBGUIDE.NET
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
(Cost=19 Card=69
Card=69 Bytes=2484)
0 TABLE ACCESS (FULL) OF 'BIG_EMP' (Cost=19
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
(Cost=44 Card=5
Card=69Bytes=180)
WWW.DBGUIDE.NET
<조치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;
WWW.DBGUIDE.NET
<원인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;
WWW.DBGUIDE.NET
<조치2>
해당 테이블이 저장되는 테이블스페이스를 Locally Management 테이블스페이스로
생성하십시오. Data-Dictionary 타입의 테이블스페이스는 테이블의 모든 익스텐트 정보를
SYSTEM 테이블스페이스에 저장하기 때문에 성능이 저하될 수 있습니다.
CREATE TABLESPSCE sample
DATAFILE ‘/disk1/sample1.dbf’ SIZE 500m
EXTENT MANAGEMENT LOCAL
UNIFORM SIZE 10m;
WWW.DBGUIDE.NET
<조치3>
대용량 데이터가 저장되는 컬럼(VARCHAR2, LONG, LONG RAW)이 있는 테이블을
수직 파티션 또는 수평 파티션 테이블로 분리하십시오.
VARCHAR2와 LONG/LONG RAW 컬럼은 검색할 때 불필요한 메모리와 디스크 I-O를
유발시키기 때문에 성능저하 현상을 유발하게 됩니다.
CREATE TABLE emp
CREATE TABLE emp_pic
(empno
(empno number, pic LONG RAW);
number, ename varchar2(15)) ;
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);
WWW.DBGUIDE.NET
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;
또는
WWW.DBGUIDE.NET
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
(Cost=11 Card=10584 Bytes=294)
2 1
INDEX SCAN I_EMP_DEPTNO
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
(Cost=53 Card=10584 Bytes=294)
2
1
INDEX SCAN
I_EMP_DEPTNO
WWW.DBGUIDE.NET
<원인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;
WWW.DBGUIDE.NET
Road 3
SQL문의 분석
Explain Plan 분석
(Optimizer 결정)
Y
Full Table Scan ?
N
인덱스를 사용하지
못하는 경우인가 ?
Y
Road 3-1
(SQL튜닝)
N
Index Scan ?
Y
대상 컬럼의
분포도가 나쁜가 ?
Y
Road 3-2
(Full-Table
Scan의 검토)
N
Road 3-3
(Index 타입의
검토)
WWW.DBGUIDE.NET
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 연산자를 사용할 때 와일드 카드(%)의 사용에 주의하십시오.
WWW.DBGUIDE.NET
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
WWW.DBGUIDE.NET
<조치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;
WWW.DBGUIDE.NET
Road 3-3
인덱스 타입의 검토
INDEX Type의 검토
결합 index를
사용하는가?
Y
여러 개의 컬럼 중
가장 분포도가 좋은
컬럼을 기준으로 생성
하였는가 ?
N
2개 이상의 테이블이
조인되는가 ?
Y
N
Road3-3-2
(인덱스타입
의 결정)
Y
분포도가 좋은
테이블을 기준 테이블로
설정하였는가?
Y
Instance 튜닝으로!
N
Road3-3-3
(조인순서의
결정)
N
Road3-3-1
(인덱스선행
컬럼의 결정)
WWW.DBGUIDE.NET
Road 3-3-2
인덱스 타입의 결정
<원인>
검색하고자 하는 컬럼의 데이터 속성 및 크기에 따라 적절한 인덱스를 사용하면 보다 성능
향상을 기대할 수 있습니다.
일반적인 테이블 정보에 대한 검색 시 사용된다.
B*Tree Index
대용량 데이터베이스 환경에서 나쁜 분포도에 대한 컬럼
검색 시 사용된다.
BitMap Index
테이블의 인덱스 컬럼에 대해 삭제가 빈번하게 발생할 때
사용된다.
Reverse Index
최신 행 정보를 우선시 조회할 때 사용 된다.
검색조건에서 계산공식을 사용하는 경우 결과치를 인덱스로
생성할 수 있다.
Primary Key를 이용하여 Text 컬럼의 정보를 검색할 때
사용된다.
Descending Index
Function-Based Index
Index Organization Index
WWW.DBGUIDE.NET
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
~~~~ ;
WWW.DBGUIDE.NET
감사합니다!
질문 Q&A