Download
1 / 66
660 likes | 1.09k Views
오라클 DB 최적의 물리적 설계 및 튜닝기법 세미나 [ 오라클 DB 최적의 물리적 설계 및 SQL 튜닝 방법론 ]. PLAN 정보기술 Consultant : 주 종 면 jina6678@yahoo.co.kr 011-864-1858. 강사 : 주 종 면 E-MAIL : jina6678@yahoo.co.kr 다음 <OracleZone> 카페 운영진 한국 데이터베이스 진흥센터 < 오라클의 모든 것 > 카페시샵
E N D
오라클 DB 최적의 물리적 설계 및 튜닝기법 세미나 [오라클 DB 최적의 물리적 설계 및 SQL 튜닝 방법론] PLAN 정보기술 Consultant : 주 종 면 jina6678@yahoo.co.kr 011-864-1858
강사 : 주 종 면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. 테이블과 인덱스의 물리적 설계 1) 테이블 스페이스의 설계 2) 블록 크기의 설계 3) 테이블의 물리적 설계 4) INITIAL, NEXT의 크기 계산 5) 인덱스의 크기 설계 2. SQL 튜닝 방법론 1) 옵 티 마 이 저 2) SQL 튜닝 Road-Map
테이블과 인덱스의 • 물리적 설계
논리적/물리적 저장구조 Database Tablespace File Segment Extent Logical Physical Block
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]; Tablespace의 종류
Data Dictionary 타입 Locally Management 타입 오라클 8i 버전까지 기본 테이블스페이스 오라클 9i 버전부터 기본 테이블스페이스 데이터 익스텐트와 롤백 세그멘트의 익스텐 트 정보를 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 단위로 블록을 할당한다.
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); Cteate table emp (no char(3), name varchar2(10)) Tablespace insa Cteate table emp (no char(3), name varchar2(10)) Tablespace insa 2) Locally Management Type Create tablespace insa Datafile ‘d:\data\insa1.dbf’ size 500m Extent Management Local Uniform size 1m; Tablespace와 Table과의 관계 Storage(initial 10 M next 10 M minextents 1 maxextents 121 pctincrease 50); Storage(initial 1 M next 1 M minextents 1 maxextents UNLIMITED pctincrease 0);
db_block_size (4096 byte) db_block_size (2048byte) aaa001 1 주종면 서울 …….. …….. 50 홍길동 부산 aaa001 1 주종면 서울 ………………………….. ……….……………… 100 주영현 부산 작은 블록 큰 블록 적은 행이 저장되기 때문에 한 개 블록에 대한 사용 자들의 검색이 큰 블록의 경우보다 적기 때문에 경 합이 적게 발생합니다. 많은 사용자가 동시에 검색하는 경우 많은 행이 하 나의 블록에 저장되어 있기 때문에 집중적인 경합 현상이 발생합니다. db_block_size (2048byte) aaa002 WHERE 조건에 의한 RANDOM 검색에 탁월한 성능개선이 기대됩니다. 테이블 전체 스캔의 경우에 탁월한 성능개선이 기대됩니다. 51 유관순 서울 …….. …….. 100 주영현 부산 하나의 블록의 많은 행을 저장할 수 있기 때문에 작은 블록의 경우보다 오버헤드가 적게 발생합니다. 하나의 블록에 적은 수의 행이 저장되기 때문에 불필요한 블록헤드가 많이 발생합니다. 인덱스 스캔 시 보다 많은 블록을 읽을 수 도 있기 때문에 성능이 저하될 수도 있습니다. 보다 충분한 메모리 공간을 확보할 수 있기 때문에 성능개선이 기대됩니다. 블록설계와 성능문제
판 매 업 무 (OLTP) SALES_SMALL (Block_Size 4k) SALES1.DBF I_SALES_SMALL (Block_Size 4k) I_SALES1.DBF 관리회계 업무 (Data Warehouse) ACC_LARGE (Block_Size 16k) ACC1.DBF ACC2.DBF I_ACC_LARGE (Block_Size 16k) I_ACC1.DBF 다중 블록 테이블스페이스
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 파라메터를 정의해야 합니다. 다중 블록 테이블스페이스
테이블 크기의 물리적 설계 테이블의 물리적 설계 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여부를 결정하라.
DB_BLOCK_SIZE 2048 Byte aaa001 블록 구조 블록헤드 영역 : 블록주소, 태이블 디렉토리, 행 디렉토리, 트랜잭션 슬롯 Table Directory : 클러스트 생성시 관련 테이블 정보를 저장 Row Directory : 블록 내에 저장된 Row에 대한 정보를 저장(4 byte 소요) 행 데이터 영역 : 행 데이터를 저장할 수 있는 빈 공간(행수 * 2)
Step-2 하나의 블록 당 사용 가능한 데이터 영역의 크기를 계산한다. Step-1 요구되는 블록 헤드의 총 크기를 계산한다. Step-3 한 개 행의 평균 길이를 계산한다. Step-4 한 개 행의 전체 평균 크기를 계산한다. Step-5 한 개 블록에 저장할 수 있는 평균 행의 수를 계산한다. Step-6 하나의 테이블에 요구되는 INITIAL의 크기를 계산한다. 테이블의 크기 계산
* 전체 소요되는 블록헤드의 크기 = (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 수 Step-1 요구되는 블록 헤드의 총 크기를 계산한다. (예) INITRANS = 1인 경우 전체 블록헤드의 크기 = (57 + (23 * 1)) + (4 + (2 * R)) = 80 + ( 4 + (2 * R))
* 블록 당 사용 가능한 데이터 영역의 크기 = ( 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 % (테이블 생성시 기본값) Step-2 하나의 블록 당 사용 가능한 데이터 영역의 크기를 계산한다. (예) BLOCK SIZE가 2048 Byte, PCTFREE=10인 경우 데이터 영역의 크기 = (2048 – ( 80 + (4 + 2R)) - ((2048 – 80) * (10 / 100)) = (1768 – 2R) Byte
* 한 개 행의 평균길이 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 Step-3 한 개 행의 평균 길이를 계산한다. (예) 한 개 행의 평균길이 = 5 + 5 + 5 + 6 + 6 + 6 = 33 Byte
* 전체 행의 평균 크기 = 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의 결과 Step-4한개 행의 전체 평균 크기를 계산한다. (예) s_item 테이블에서 250 byte 이하 컬럼 수 = 6, 250 byte 이상 컬럼 수 = 0, 한 개 행의 평균길이 = 33 byte 인 경우 전체 행의 평균 크기 = ( 3 + ( 1 * 6) + (3 * 0) + 33) = 42 Byte
* 한 개 블록에 저장하는 평균 행의 수 = Step-2 / Step-4 1) Step-2 = 한 개 블록에 저장할 수 있는 평균 데이터 영역의 크기 2) Step-4 = 전체 행의 평균 크기 Step-5한 개 블록에 저장할 수 있는 평균 행의 수를 계산한다. (예) 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의 크기를 계산한다. 1) 테이블을 생성하기 위해 요구되는 블록 수 = 전체 행 수 / Step-5 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
EMP aaa001 aaa002 aaa003 aaa004 aaa005 1주종면110 2주영현132 ……. …… ……. ……. …… ……. ……. …… ……. ……. …… ……. Initial 10 k aaa016 aaa017 aaa018 aaa019 aaa020 ……. …… ……. 1주종면110 2주영현132 ……. …… ……. ……. …… ……. ……. …… ……. ……. …… ……. Next 10 k aaa006 aaa007 aaa008 aaa009 aaa010 aaa011 aaa012 aaa013 aaa014 aaa015 Next 1주종면110 2주영현132 ……. …… ……. ……. …… ……. ……. …… ……. ……. …… ……. 1주종면110 2주영현132 ……. …… ……. ……. …… ……. ……. …… ……. ……. …… ……. 20 k 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);
테이블의 물리적 설계 결과 테이블 설계의 결과 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;
Step-2 하나의 블록 당 사용 가능한 데이터 영역의 크기를 계산한다. Step-1 요구되는 블록 헤드의 총 크기를 계산한다. Step-3 인덱스 컬럼의 평균 길이를 계산한다. Step-4 전체 행의 평균 인덱스 크기를 계산한다. Step-5 하나의 테이블에 요구되는 INITIAL의 크기를 계산한다. 인덱스의 크기 계산
* 전체 소요되는 블록헤드의 크기 = (Fixed Header + Variable Transaction Header) 1) Fixed Header = 113 바이트(기본적으로 요구되는 블록헤드의 크기) 2) Variable Transaction Header = INITRANS 파라메터의 수 마다 23 바이트 Step-1 요구되는 블록 헤드의 총 크기를 계산한다. (예) INITRANS = 2(기본값) 인 경우 전체 블록헤드의 크기 = ( 113 + (23 * 2)) = 159 Bytes
* 블록 당 사용 가능한 데이터 영역의 크기 = ( Block Size - [Step-1의 결과] ) * ( 1 - PCTFREE / 100 ) 1) Block Size = 2048 바이트 (db_block_size 파라메터에 의해 결정) 2) PCTFREE = 10 % (테이블 생성시 기본값) Step-2 하나의 블록 당 사용 가능한 데이터 영역의 크기를 계산한다. (예) BLOCK SIZE가 2048 Byte, PCTFREE=10인 경우 데이터 영역의 크기 = ((2048 – 159) * (1 - 10 / 100)) = 1700.1 Byte
* 인덱스로 생성된 컬럼의 평균길이 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 Step-3인덱스 컬럼의 평균 길이를 계산한다. (예) 한 개 행의 평균길이 = 22 Byte
* 전체 행의 평균 크기 = 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의 결과 Step-4전체 행의 평균 인덱스 크기를 계산한다. (예) 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; 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; 인덱스 생성 스크립트
정규화 및 역정규화 테이블의 파티션닝 여부 로컬/글로벌 인덱스 데이터-디자인튜닝 디자인 튜닝 App-PG의 구조설계 응용PG설계의 튜닝 테이블의 저장구조 Foreign-Key 및 제약조건의여부 논리적구조의 튜닝 인덱스사용 결정 인덱스 타입분석 클러스터의 활용 SQL 튜닝 실행경로의 튜닝 SQL 문장의 튜닝 옵티마이저 활용 조인기법의 활용 메모리 구조의 튜닝 버퍼캐시 영역,공유-풀 영역,로그-풀 영역 서버 튜닝 I/O와 물리적구조 Disk I-O, 저장구조의 물리적 설계분석 자원에 대한 경합 Sort공간,Lock 경합 Undo 공간 System 튜닝 Network 튜닝 OS와 Network의 경합 튜닝 Method
준비단계 (Step-1) 분석/튜닝단계 (Step-2) 결과단계 (Step-3) 튜닝후 자료수집/분석 튜닝결과 평가 산출물 작성 의문점/문제점 분석 고객 Interview 튜닝대상 적용 고객 Interview 튜닝 Methodology 고객 Interview 디자인 튜닝 자료수집 및 분석 (Check-List 작성) SQL 튜닝 튜닝계획 수립 서버 튜닝 고객 Interview System/Network 튜닝
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 Key To Tuning Tuning Method CPU Memory I/O Network S/W Design/Architecture Network-Traffic
OPTIMIZER 처리방법 처리순서 우선순위 SQL해석 실행계획 작성 Full Table Scan 참조 참조 COL$ OBJ$ IND$ TAB$ VIEW$ DATA Dictionary 옵티마이저 개발자 SELECT * FROM emp WHERE deptno=10;
비용기반 공식기반 옵티마이저의 종류
Step-1 테이블과 인덱스의 구조를 최적화 한다. Step-2 SQL 문장을 최적화 한다. Step-3 인덱스 타입을 최적화 한다. SQL 튜닝 절차
ORA-01467(01652) 에러가 발생했나요 ? Road-1 (Sort 공간의 구조분석) Y N Road 2-1 (테이블에 대한 구조분석) SELECT문의 성능이 저하되고 있습니까? Road-3 (SQL튜닝) Y Road 2-2 (인덱스에 대한 구조분석) ORA-1555/1562/1560/1628 에러가 발생했나요 ? Road-4 (UNDO 공간의 구조분석) Y N DML문의 성능이 저하되고 있습니까? Road-5 (Extent의 동적 할당현상) Road-5-1 (사용하지 않는 인덱스의제거) Y SQL 튜닝 RoadMap SELECT문 DML문
<조치1> 사용자의 SQL문에서 필요한 컬럼 만을 분류대상으로 설정한다. SELECT * FROM emp ORDER BY ename; SELECT empno, ename FROM emp ORDER BY ename; Road 1 Sorting 공간의 튜닝 <원인> CREATE INDEX, ORDER BY, GROUP BY, DISTINCT, DECODE, UNION, INTERSECT, MINUS와 같은 문장을 통해 대용량 데이터를 분류(Sorting)할 때 Temporary 테이블스페이 스가 부족할 때 발생하는 에러입니다.
<조치3> Temporary 테이블스페이스의 공간을 추가로 늘려주어야 합니다. SQL> ALTER TABLESPACE tempADD DATAFILE ‘temp02.dbf’ SIZE 500M; 또는 SQL> ALTER DATABASE DATAFILE ‘temp01.dbf’RESIZE 800M; <조치4> SORT_AREA_SIZE의 값을 추가로 늘려주어야 합니다. SORT_AREA_SIZE = 1000000; • <조치2> • 사용자의 SQL문에서 ORDER BY 절에 사용되는 컬럼에 대해 INDEX를 설정한다. • SELECT * FROM emp ORDER BY ename; • CREATE INDEX I_emp_ename ON emp(ename); SELECT empno, ename FROM emp;
<조치1> ANALYZE 작업을 수행한 후 전체공간의 30%이상에서 발생하면 테이블을 재구성한다. SQL> EXECUTEdbms_redefinition.start_redef_table( ~~~~~ ) Road 2-1테이블에 대한 구조분석 <원인1> Row-Chaining 및 Row-Migration 현상이 발생하면 불필요한 블록에 대한 읽기 작업이 발생하기 때문에 성능저하 현상이 발생합니다. (과다한 VARCHAR2 타입의 사용문제)
