대용량 데이터 베이스 솔루션 I
Ch1. 액세스 효율의 향상
> 어떤 처리를 위해 여러개의 sql을 나열하는것보다 , 사용하는 SQL의수를줄여 처리한는 것이 좋다.
1. 인덱스의 활용
> 인덱스는 생성시킨 컬럼들과 논리적인주소(ROWID)로 구성된다.
INDEX KEY : ROWID >>> 실제 테이블 로우데이터
2. 인덱스의 적용원칙
인덱스가 정요되지 않는 경우
> 인덱스 컬럼은 비교되기 전에 변형이 일어나면 인덱스를 사용할수없다. 이러한 성질을 역이용하여 액세스경로를 조정하기도 한다.
> where SUBSTR(JOB,0,4) = 'SALE' >>> WHERE JOB LIKE 'SALE%' (인덱스 이용)
> where job||dept ='SALE10' (결합인덱스의경우) >>> where job='SALE' and dept='10'
% 서로 다른 타입을 비교할때 적용불가 > where name(char) = 2 의경우 캐릭터타입이 숫자형으로변환된다.
> 부정형 연산자 로 기술한 경우도 직접 비교해야할 갑시 존재하지 않으므로 논리적으로 오류가 발생한다.
> NOT EXIST 문을 사용해 서브 쿼리로 넣으면 긍정형이되어 인덱스를 사용할수있게된다.
>> EXIST문은 Boolean이므로 만족되는조건이 나타나면 수행을 멈추므로 빠르다.
> 인덱스 컬럼이 NULL 로 비교되면 사용하지 않는다. (인덱스는 null은 저장하지 않는다.)
> null값에 대한 일관성있는 테이블 설계사 필요
>> 어디서는 null인값이 다른곳에서는 '0'이나 ""의 형식으로 쓰일때
> 옵티마이저가 어런저런이유로 안쓸수도 있다.
% 인덱스의 강제 사용제한 내부 함수
> RTRIM(col_name) >>> col_name의 인덱스 가 있더라도 사용되지 않는다.
>>>> 인덱스 머지 실행계획이 수립되었을때 사용하여 원하는 Access Plan 을 유도해준다.
<<예를들면 분포도가 좋은컬럼을 차별선택
3. 인덱스의 선정
> 인덱스 대상 테이블의 선정
>> 테이블의크기가 작은것(5,6block)의경우는 인덱스를 생성하지 않아도 무관하나 조인시에쓰므로 생성해준다.
>> 너무많은 인덱스는 데이터의 수정입력삭제등이 일어나면 부하가 가해지므로 자제한다.
> 인덱스 컬럼의 선정
>> 인덱스의 분포도와 손익분기점
>>> 인덱스 컬럼의 분포도는 10~15%을 넘지 말아야한다.
>>> 분포도가 일정기준을 넘는값을 엑세스 하는경우는 전체테이블을 스캔하는것다 효율이 나빠진다.
>>>인덱스가 여러개일경우 대부분은 가장좋은 하나의인덱스만 사용하는것이 좋다. (인덱스머지가 발생하여효율이 내려간다)
% 인덱스 머지와 결합인덱스의 차이점
인덱스 머지 : 서로다른 몇 개의 인덱스를 rowid별로 머지 정열한다.
결합인덱스 : 각 인덱스 컬럼값과 rowid로 정렬되어있다 (=인덱스머지의 성공결과와 같다 )
> 결합인덱스의 첫번째 컬럼을 사용안하면 인덱스는 사용되지 않는다.
>결합된 컬럼의 순서,개수 에 큰영향을 받는다.
>> 상위컬럼의 인덱스 검색결과가 후위컬럼의 조건이 된다,
> 인덱스 선정 절차
1. 해당 테이블의 가능한 모든 액세스 형태의 수집
1. 반복 수행되는 엑세스 형태를 찾는다.
2. 분포도가 아주 양호한 컬럼들을 발췌하여 액세느 유형을 조사한다.
3. 자주 넓은 범위의 조건이 부여되는 경우를 찾는다. >>> 부분범위처리로 변형해준다.
4. 조건에 자주 사용되는 중요컬럼들의 엑세스 유형을 조사한다.
5. 자주 결합되어 사용되는 컬럼들의 조합형태,정렬순서를 찾는다.
6. 역순정렬하는 경우를 찾는다.
7. 일련번호를 부여하는 경우를 찾는다.
8.통계자료 추출을위한 액세스 유형을 조사한다.
2. 대상컬럼 선정및 그 분포도의 조사
3. 반복 수행되는 액세스 경로(CRITICAL ACCESS PATh)의 해결
4. 클러스터링의 검토 >>> 인덱스와는 반대로 분포도가 넓을수록 유리하다.
5. 인덱스 컬럼의 조합 및 순서의 결정
6. 시험 생성및 테스트
7. 수정이 필요한 애플리케이션 조사 및 수정
8. 일괄 적용
>>>>SQL
:컴럼값의 종류를 추출한다.
SELECT COUNT(DISTINCT COL1) ,…..
FROM TABLE_NAME
WHERE CONDITIONS.
:컬럼별로 평균 ,초대,최소 로우수를 구해보자
SELECT AVG(COUNT(*)), MAX(COUNT(*)), MIN(COUNT(*))
FROM TABLE_NAME
WHERE COLUMNS IS NOT NULL
GROUP BY COLUMNS
: 만약 평균로우수에 비해 최대 로우수가 크다면.
SELECT COLUMNS ,COUNT(*)
FROM TABLE_NAME
WHERE COLUMNS IS NOT NULL
GROUP BY COLUMNS
HAVING COUNT(*) > AVERAGE_ROWS <두번째 sql문 AVG의 값
2. 조인의 최적화
1. 조인효율 향상의 원리
> Nested Lop의경우 처리범위가 좁은 범위를 먼저 처리 할수록 조인 효율이 좋아진다.
>> 즉 조인 성공률(조인에성공해서반환된 로우수) 가 적은쪽의 조인이 우선시되어야 한다.
> 연결고리 상태
>> 연결고리 정상
>>> where절에 사용되는 조인컬럼 에 모두 인덱스가 정의 되어 있을때 어느 방향으로 조인이 수행되어도 인덱스를 사용한다.
>>> 연결고리가 정상일때는 먼저 처리범위를 줄여주는 테이블을 먼저 처리하면 수행속도가 향상된다.
>>연결고리 이상
>>> TAB1 에 인덱스가 존재하고 TAB2에 인덱스가 존재하지 않을경우
TAB1-> TAB2 방향의 조인은 TAB1의 로우수만큼 TAB2를 전체 스캔하므로 엄청 느리다.
반대로 TAB2 -> TAB1 방향의 조인은 "연결고리 정상" 의 상황일때와 동일하다.
>>> 연결고리 이상의 발생 원인
조인되는 컬럼이 1:1로 대응되지 않을경우
데이터 타입의 차이에 의해서도 발생(내부적으로 형변환을 하므로 사용제한이 걸림)
>> 양쪽 연결고리 이상
>>> 양쪽 연결고리가 이상일떄는 Nested Loop 조인의 경우는 엄청난 전체 테이블 스캔이 발생하므로
연결고리의 상태에 영향을 받지 않는 Sort Merge 조인 방식으로 처리된다.
> 조인의 튜닝
>> 조인시의 Access Path 선택
>>> where x.A1 = y.B1
and z.C1 = y.B2
and x.A2 = '10'
and y.B2 like 'b%'
>>>> 최조의 엑세는 패스의 경우에는 y.B1, y.B2등의 변수는 액세는 패스의 조건이될수 없으므로
x.A2='10'의 경우처럼 상수값을 가진 조건을 먼저 액세스하여 가장 먼저 나오는 로우로
다른 컬럼들의 값을 상수값으로 만든다.
>>>> 액세스 가능한 조건이 아무것도 없으면 전체테이블을 랜던액세스한다.
>> 조인 튜닝의 단계
1. Nested Loop 조인이 유리한지 Sort Merge조인이 유리한지 를 먼저 판단한다.
2. 연결고리의 상태를 확인한다. (이상이 있다면 추가 수정 여부를 결정한다)
3. 연결고리를 제외한 컬럼들의 조건에 사용된 연산자와 인덱스의 상태를 비교하여 가장 처리 범위를 줄여줄수있는
조건을 찾는다.
4. 현제의 인덱스가 우리가 찾은 최소의 범위를 액세스 할수 있도록 구성되어있는지 확인한다.
(부분범위처리로의 유도가 가능한가 를 조사)
> Nested Loop Join & Sort Merge Join
>> Nested Loop Join 은 어떤 태이블의 처리범위를 하나씩 엑세스 하면서 그 추출된 값으로 연결할 테이블을 조인하는 방식이다.
특징
>>> 순차적으로 처리된다.(로우들의 순차적 수행, 테이블간의 연결도 순차적)
>>> 먼저 액세스 되는 테이블의 처리범위에 의해 처리량이 결정된다.
>>> 나중처리되는테이블은 앞서 처리된 값을 받아 액세는 된다.(스스로범위를줄이지못하고,앞의상수결과값에의해정해진다).
>>> 주로 랜덤 액세스 방식으로 처리된다.
>>> 컬럼에 있는 모든 인덱스가 사용되지 않는다.
>>> 연결고리가되는 인덱스의상태,방향성에 의해 성능이 결정된다.
>>> 연결작업 수행후 마지막으로 체크 되는 조건은 부분범위처리를 하는 경우에는 조건의 범위가 넓을수록 없다면 더 좋다.
사용기준
>>> 부분범위처리를 하는경우에 유리하다.
>>> 조인되는 어느한쪽이 상대방 테이블의 어느결과를 받아 처리범위를 줄일수있을때 사용한다.
>>> 주로 처리량이 적은경우(혹은부분범위처리)에 사용 한다 <<< 주로 랜덤엑세는 방식으로 테이블을 접근하기 때문.
>>> 가능한한 연결고리 이상 상태를 만들지 않는다.
>>> 순차적으로처리 되기때문에 어떤 테이블을 먼저 액세스 하는가가 중요하므로 최적의 순서가 나오게 설계해야한다.
>>> 부분범위처리의경우에는 운반단위의 크기가 수행속도에 영향을미친다(운반단위가작으면 FETCH의횟수가 늘어난다.)
>>> 테이블의처리범위가 넓거나 연결 테이블의 랜덤엑세스 양이 많으면 Sort Merge 조인보다 불리해지는경우가 많다.
>> Sort Merge Join
>>> Sort Merge Join 이란 양쪽 테이블의처리범위를 각각 엑세스 하여 정렬한 결과를 차례로 스캔해가면서 연결고리
의 조건으로 머지 해가는 방식을 말한다.
특징
>>> 동시적으로 처리된다.(테이블 각자가따로엑세스를 행한다)
>>> 각 테이블은 타 테이블에서 어떤 상수값도 제공받지 않는다.
>>> 항상 전체 범위처리를 한다.
>>> 자신의 처리범위를 줄이기위해서는 인덱스를 랜덤엑세스 하고 머지는 스캔방식으로처리한다.
>>> 모든 인덱스가 사용되지 않는다. (연결고리 커럼은 사용하지 않는다.)
>>> 조인의 방향과 무관( 선행테이블이 없기때문이다.)
>>> 자신의 처리범위를 줄이기 위한 인덱스는 주로 가장 좋은 한가지 인덱스만 사용되어진다.
사용기준
>>> 전체범위처리를 하는경우 주로 유리하다.
>>> 상대방테이블에서 어떤 상수값을 제공받지 않더라도, 처리범위를 줄인수 있는경우 유리하다.
>>> 주로 처리량이 많은경우 (항상 전체처리 범위를 해야하는 경우) 유리 (스캔방식이므로 많은양의 랜덤엑세스를 줄일수 있다.)
>>> 연결고리와관계없으므로 연결고리를위한 인덱스생성이 필요없다.
>>> 스스로 자신의 처리범위를 줄이는것이 관건이므로 효율적인 인덱스 구성이 필요하다.
>>> 전체범위처리를 하므로 운반단위의 크기가 수행속도에 영향이없다, 적당히 좀큰편의 운반단위가 좋다.
>>> 처리할 데이터 양이 적은 온라인 애플리케이션에서는 Nested Loop Join이 유리한경우가 많으로 함부로 사용불가.
>>> 옵티마이져 목표가 ALL_ROWS인경우에는 자주 소트 머지 조인으로 실행되므로 주의한다.
% 소트머지 조인에서 가장 중요한것은 정렬작업이 얼마나 최적화 되어있느냐이다
메모리 내부의 정렬영역의 크기가 작다면 대형 배치 어플리케이션의 경우 밤에 일시정렬영역을증가시킨후 실행 되돌리는것도 좋다.
3. 클러스터링의 활용
> 클러스터의 구조 및 특징
>> 클러스터는 테이블의 상위개념으로 하나의 테이블은 하나의 클러스터에만 포함되며, 클러스터링이란 어떤 정해진 컬럼값을 기준으로
동일한 값을 갑지는 여러 로우를 엑세스 할때 랜덤엑세스를 줄임으로오효율을 개선시키는 방법이다,
>>> 단일 테이블 클러스팅
>>>> 하나의 클러스터에 하나의 테이블
>> 클러스터란 한번 인덱스를 액세스하여 여러건의 테이블 로우를 스캔방식으로 액세스 할수있는것일뿐이다. 클러스터링은
분포도가 오히려 넓어야 유리해지므로 인덱스의추약점을 해결해줄수있다. 단 지정되 위치를 찾아가 저장되어야 하므로 검색을 제외한
모든경우에 부하를 일으킨다.
>>> 다중 테이블 클러스터링
지정된 클러스터에 두개 이상의 테이블을 생성시키는 것을 말한다.
클러스터 테이블의 사용법은 변하지 않는다,
% 클러스터의 부하
> 입력시의 부하 클러스터링 테이블은 각로우의 값에따라 저장위치가 다르므로 최악의경우 각각의 레코드가 서로 다른 블록에 존재할수도있다.
이경우 부하는 커진다.
> 수정시의부하 수정작업에 따른 부하는 증가하지 않으나. 클러스터 팩터가 나쁘게 되 클러스터 체인이 발생하게 된다,
>삭제시의 부하 부하는 없거나 적다 단 테이블은 DROP 은 내부적으로 DELETE를 실행한다 이는 클러스터내에 다중 테이블이 있을경우
클러스터의입장에서는 그중의 레코드가 삭제되는것 처럼 보이기때문이다, 이때는 아주큰 롤백 세그먼트가 필요해진다.
% 롤백의증가 SET TRANSACTION USE ROLLBACK SEGMENT large_rollback_segment_name; <<Commit 이나 Rollback후
% 클러스터의 삭제
DROP CLUSTER cluster_name INCLUDING TABLES CASCADE CONSTRAINTS;
TRUNCATE CLUSTER cluster_name REUSE STORAGE;
> 클러스터의 선정 절차
>> 6블럭 이상의 테이블에서 선택한다.
>> 다량의 범위를 빈번하게 엑세스 하는경우라면 검토해 보아야한다(5배8배속도향상) << 특히 BETWEEN 같은 범위처리의 경우
>> 후보클러스터 컬럼의 효율성 진단.
>>> 엑세스 범위와 분포도(넓은범위가좋다)
>> 생성후의 수행속도진단 ( 어느정도의 로우수등에서 최적의 속도를 낼수 있는지)
>> 인덱스와 의 종합적인 판단후에 결정해야 한다.
> 클러스터링의 사용 판단기준
>> 넓은 범위의 처리를 스캔방식으로 유도하기에는 단일테이블 클러스터링
여러테이블의조인을 향상 시키는데는 다중 테이블 클러스터링
> 클러스터링 테이블의 체인
>> 데이터의 변경이 여유공간(Free Space)보다 크면 체인이 발생하게 된다.
>>> 많은체인의발생은 원하는데이타를얻어내기 위한 여러 개의 블럭 엑세스를하기때문에 수행속도가 저하된다.
>> 데이터 변경보다 로우수의 증가가 체인의 생성과 직결된다.
> 클러스터링 사이즈
n << 클러스터 키 = (20 + 컬럼길이)
∑ (테이블 평균 로우길이* 클러스터 키당 평균 로우수) << 로우 Dictionary = (2 * 블럭내 평균 로우수)
table=1
1. 먼저 ANALYZE명령을 사용하여 틀러스터링테이블의 통계정보를 생성한다.
% ANALYZE TABLE clustering_table_name COMPUTE STATISTICS
2. 테이블 평균 로우길이의 산정
% SELECT AVG_ROW_LEN
FROM USER_TABLES
WHERE TABLE_NAME ='clustering_table_name'
3. 클러스터 키당 평균 로우 수의 산정
% SELECT AVG(COUNT(*))
FROM clustering_table_name
GROUP BY clustering_key_columns
4. 블록내 평균 로우수의 산정
% SELECT NUM_ROWs / (BLOCK - NVL(EMPTY_BLOCK,0))
FROM USER_TABLES
WHERE TABLE_NAME ='clustering_table_name'
% 간단 한 clustering size 산출 방법
cluster size = Σ(테이블 평균 로우길이 * 클러스터 키당 평균 로우수))*1.1
> 클러스터링 생성 예제
A 테이블의 B 컬럼을 키로 하여 클러스터링하고자 할때
1. ANALYZE 명령을 사용하여 클러스터링할 테이블의 통계정보를 추출한다.
% ANALYZE TABLE A COMPUTE STATISTICS
2. 테이블 평균 로우길이의 산정
% SELECT AVG_ROW_LEN
FROM USER_TABLES
WHERE TABLE_NAME ='A'
3. 클러스터 키당 평균 로우 수의 산정
% SELECT AVG(COUNT(*))
FROM A
GROUP BY B
4. 블록내 평균 로우수의 산정
% SELECT NUM_ROWs / (BLOCK - NVL(EMPTY_BLOCK,0))
FROM USER_TABLES
WHERE TABLE_NAME ='A'
5. 클러스터및 클러스터 인덱스를 생성한다.
% CREATE CLUSTER A# (B char(6))
PCTFREE 10 PCTUSED 60 SIZE 8600;
% CREATE INDEX A#x ON CLUSTER A#
PCTFREE 5 STORAGE (INITIAL 20K NEXT 10K);
6. 기존 테이블의 명칭을 바꾼다.
RENAME A TO A- ;
ALTER TABLE A RENAME TO A-;
7. 클러스터내에 테이블을 선언한다. 이때 기존의 D이 끝에 반드시 클러스터를 지정해야한다.
% CREATE TABLE A (
B CHAR(6)
……
) CLUSTER A#(B);
8. 생성된 테이블내에 데이터를 저장시킨다.
% INSERT INTO A
SELECT * FROM A- WHERE B >= '940101';
9. 기존 테이블을 삭제시키고 다른 인덱스를 추가 시킨다.
% DROP TABLE A-;
CREATE INDEX ………………….;
> 클러스터 사용을 위한 조치
1. 클러스터 키 컬러을 첫번째로 하는 인덱스를 생성 시키지 말것 (클러스터보다인덱스가실행될수가있다가능성)
2. 클러스터를 사용하고자 한다면 액세스경로를 고정시킨다.
또는 /*+ CLUSTER(A) */ 힌트를 사용하거나 RTRIM(B) ='11200' 으로 액세스경로를 고정시킨다.
4. 부분범위처리 (Partial Range Scan)
>SQL 의수행도중 정해진 운반단위만큼 데이터가 모아지면 처리를 일시멈추고 값을 일단 반환한다.
>> 그룹함수(SUM,MAX,COUNT)등과 GROUP BY 절 ORDER BY 절이 들어가 있거나 SQL 실행계획에 SORT나 FILTER가 들어가 있으면 전체범위
처리를 한다 단 ORDER BY 의 경우에는 옴티마이저의 선택인덱스와 ORDER BY절의 컬럼이 같다면 정렬은 무시되고 부분 범위처리가된다,
>부분범위처리에서 액세스를 주관하는 조거능 ㄴ범위가 적을수록 일량이 줄어들고 그렇지 않은 조건은 오히려 대상 범위가 넓을수록 일량이 줄어든다.
>> 이유는 액세스 주관조건의 경우 인덱스를 사용하여 B*TREE방식으로 찾기 때문이고 나머지 조건들은 처리 범위가 넓을수록 운반단위를
쉽게 채울수 있으므로 빨라진다는것이다,
>부분범위처리로의유도.
>> 인덱스나 클러스터를 이용한 SORT의대체
>>> WHERE ORD_DEPT >'' 등의 있지도 않은 조건을 실행하여 액세스 주관컬럼을 변형 시킨다.
오라클 힌트등과 같이 사용하여 힌트에 액세스 주관을 시킬떄 사용된다,
>> 인덱스만으로 액세스
>>> 인덱스는 첫번쨰 로우만 랱덤액세스 하고 그후로는 스캔방식으로 접근한다.
>>> 테이블 액세스는 항상 랜덤 엑세스 이므로 수행 속도의 큰 영향을 준다.
>>>> 인덱스만 실행되는 수행계획 수립
1. 사용된 모든 컬럼이 하나의 인덱스에 결합되어있거나.
2. 인덱스머지 실행계획이 수립된다면 머지되는 두개의 인덱스 내에 모든 컬럼들이 속해야 한다.
>> MAX의처리
>>> 오라클 힌트를 이용해서 인덱스 주관처리를하고, ROWNUM=1을 이용해서 최고 값을 MAX값을 추출한다.
>>> 액세스를 주관하는 컬럼과 MAX를 구하고자 하는 컬럼만 결합인덱스로 되어있으면 다른 추가적인 WHERE 절 조건
들은 영향을 미치지 못한다,
>> EXISTS의활용
>>>존재확인은 설사 그 결과가 하나이든 만개이든 아무런 상관이 없으므로 부분범위처리가 필수이다.
>>> EXISTS 는 진행중인 쿼리를 중간에서 정지(STOP KEY)역할을 해준다.
>> ROWNUM의활용
>>>ROWNUM은 1부터 생성되므로 BETWEEN 5 and 10 은 있을수없다 . <-왜냐하면 그 조건을 만족하지 않으므로 영원히 1이 나오지 않기 때문이다.
>>> ROWNUM 은 ORDER BY 보다 먼저 실행되므로 정렬시 주의가 필요하다.
>> SQL의 이원화
>>> 처리량의 범위가 넓은 많은 테이블을 직접 참조하지 않고 좁고 적은 테이블을 우선 처리하여 조건 이 되는 상수 절을 가져올수있는
SQL 로 나누어 처리한다.
5. 다중 처리(array Processing)의 활용
> 다중처리란 어떤 어퍼레이션이 실행될때 한번 DBMS를 호출 할때마다 여러건을 처리하도록하여 DBMS의 호출 횟수를 줄임으로서
시스템 오버헤드를 감소시키는것이다.
> 부분 범위처리에서 설명된 운반단위가 바로 한번의 DBMS 호출에서 처리되는 다중처리의 단위가 된다,
> 여러건을 한꺼번에 처리하지만 사용방법은 한껀을 처리 할때와 같다.
>유형별 다중 처리 방법
>> SQL*PLUS 와 SQL*LOADER
>>> SQL*PLUS
정해진 범위를 하나의 SQL 로 처리 하지 못하는처리라면 한번에 얼마 만큼의 운반단위로 처리했는냐에 따라
수행속도가 큰 영향을 준다.
% 운반단위 크기의 변경
> SQL>SET ARRAYSIZE 100
>>SQL 처리를 위한 기본 버퍼의 크기는 6000바이트로 가로는 로우의 평균길이 세로는 운반단위의 크기 로 생각하면 된다,
단 이버퍼는 쿨력되는 로우를 담는 버퍼 이므로 SELECT 를 수행시킬 때만 해당된다,
>>> SQL*LOADER
DIRECT_PATH'를 사용하지 않는다면 기본적으로 64개의 레코드를 다중처리하며 인서트 하고 커밋을 수행한다,
ROWS= , BINDSIZE=를 사용하여 수행속도를 향상시킬수 있다.
> 온라인형 개발툴
>> 이 설명은 현재 SQL*FROM 이란 툴에 대한 설명이 중점이므로 나중에 추가하기로한다.
No comments:
Post a Comment