데지덤

  1. 데이터관련 직무와 자격
    1. 데이터베이스 직무

    2. 데이터베이스 자격

  2. 데이터관련 학습방법
    1. 데이터베이스 개론 학습

    2. DBMS 학습

    3. 읽어볼만한 DB책

  3. 최신동향과 유명한 Things
    1. DB최신동향

    2. 데이터로 유명한 Things

  4. 데이터베이스 개념
    1. 데이터베이스 개념

    2. DBMS

    3. 데이터베이스 개발과운영

  5. 데이터베이스 설계(1/2)
    1. 데이터표준

    2. 데이터모델링

    3. 데이터모델 디자인패턴

  6. 데이터베이스 설계(2/2)
    1. 프로세스및상관모델링

    2. 정규화

    3. 반(역)정규화

    4. DB물리설계

  7. 인덱싱과 DB프로그래밍
    1. 인덱스와 해싱

    2. 관계연산

    3. DB언어

    4. SQL

    5. 데이터베이스 미들웨어

  8. 데이터베이스 운영
    1. 트랜잭션

    2. 병렬처리

    3. 데이터베이스 복구

    4. 데이터베이스 성능

    5. 병행제어(동시성제어)

  9. 분석계 및 빅데이터기술
    1. 데이터웨어하우스

    2. 데이터마이닝

    3. 빅데이터기술

  10. 데이터거버넌스
    1. 데이터거버넌스

    2. 데이터베이스 감리/진단

  11. 데이터베이스 종류와 보안
    1. 데이터베이스 종류

    2. 데이터베이스 보안

  12. DBMS
    1. 오라클

    2. SQL Server

    3. DB2

    4. Sybase

    5. Altibase

SQL실행계획

개념
- 옵티마이저에 의해 생성된 SQL문에 사용되는 Table 및 Index의 접근경로. - 동일한 SQL문은 많은 수의 실행계획을 가질 수 있으며, 결과는 동일하지만 소요되는 시간과 자원의 양은 서로 다름. - 성능에 중요한 영향을 미치는 요소(튜닝)

1. SQL의 성능 진단을 위한 도구 실행계획의 개요

  가. 실행계획(Execution Plan)의 정의

    옵티마이저에 의해 생성된 SQL문에 사용되는 Table 및 Index의 접근경로.

    동일한 SQL문은 많은 수의 실행계획을 가질 수 있으며, 결과는 동일하지만 소요되는 시간과 자원의 양은 서로 다름.

    성능에 중요한 영향을 미치는 요소(튜닝)

 

  나. 실행계획에 영향을 미치는 요소

 

 

2. 실행계획(Execution Plan)의 유형 및 상세

  가. 실행계획의 유형

구분

유형

OPTION

설명

데이터접근(Access Method)

테이블

TABLE ACCESS

BY INDEX ROWID

Record가 Index를 거쳐 접근되는 경우

FULL

Table을 직접 접근해 모든 자료를 가져오는 경우

BY USER ROWID

사용자가 직접 Record의 Rowid를 지정해 접근하는 경우

인덱스

INDEX

 

RANGE SCAN

순차적으로 Index가 Scan되는 경우

UNIQUE SCAN

Index로부터 단 1개의 Rowid만을 가져오는 경우(Where절에서 'EQUAL(=) 상수'를 받을 때)

비트맵인덱스BITMAP INDEX

SINGLE VALUE

Bitmap Index 컬럼에 ‘=‘로 접근하는 경우

데이터연결(Join Operation)

내포조인 NESTED LOOPS

 

한쪽의 Row set로 부터 다른 쪽의 Row set를 순차적,반복적으로 Access하는 경우

병합조인

MERGE JOIN

OUTER

Outer Join을 Merge Join 방식으로 수행하는 경우

해쉬조인

HASH JOIN

SEMI

Semi Join을 Hash Join 방식으로 수행하는 경우

Set Operation

UNION

 

2개 이상의 Row set을 서로 결합한 후 중복 값은 삭제하는 경우

MINUS

 

앞의 Row set중 나중의 Row set의 값들은 삭제하는 경우

 

  나. NESTED  LOOP  JOIN :

    1) 두 개의 Row Set 중 첫 번째 Row Set의 내용을 1개 읽은 후 그것을 두 번째 Row Set에서 검색

    2) 주어진 조건을 만족하거나 첫 번째 Row Set의 내용이 소진될 때까지 반복 수행

    3) NESTED LOOP JOIN의 특징

구분

설명

순차적

Driving Table의 처리범위에 있는 각각의 ROW들이 순차적으로 수행될 뿐 아니라 테이블간의 연결도 순차적

선행적

먼저 액세스되는 테이블의 처리범위에 의해 처리량이 결정

종속적

나중에 처리되는 테이블은 앞서 처리된 값을 받아 액세스.즉 값을 받아서 처리범위가 결정

Random Access

Driving Table의 인덱스 액세스는 첫 번째 ROW만 Random Access이고,나머지는 Scan, 연결작업은 Random Access.

선택적

연결되는 방향에 따라 사용되는 인덱스들이 달라질 수 있음

연결고리중요, 방향성

연결고리의 인덱스 유무에 따라 액세스 방향 및 수행속도에 많은 차이가 있음

부분범위처리 가능

연결작업 수행 후 Check 되는 조건으로 부분범위처리를 하는 경우에는 조건의 범위가 넓거나 없다면 오히려 빨라짐

 

    4) NESTED LOOP JOIN의 사용

- 부분범위처리를 하는 경우

ㆍ Nested Loop Join은 주로 전체가 아닌 부분범위를 처리하는 경우에 유리.

- Join 되는 테이블이 상호의존적인 경우

ㆍ Join 되는 어느 한 쪽이 상대방 테이블에서 추출된 결과를 받아야 처리범위를 줄일 수 있는 상태라면 항상 유리. 하지만, Driving Table의 처리가 많거나 연결 테이블의 Random Access 량이 많을 경우에는 Sort/Merge Join 보다 불리

- 처리량이 적은 경우

ㆍRandom Access를 많이 하므로 On-Line 애플리케이션처럼 처리량이 적은 경우에 유리

- Driving Table 의 선택이 관건

ㆍ어느 테이블이 먼저 ACCESS 되는가가 수행속도에 큰 영향.

 

  다. SORT MERGE JOIN

    1) 첫 번째 Table을 읽고 Join Key를 중심으로 Sort, 두 번째Table을 읽고 Join Key를 중심으로 Sort 후 각각의 값을 비교해 일치하는 자료를 Return함

    2) 처리 건수와 상관없이 Join의 대상이 되는 Table(또는 Index) 모두를 읽어 Sort 해야 함.

- SORT MERGE JOIN의 특징

동시적 : 각각의 테이블이 자신의 처리범위를 액세스하여 정렬.

독립적 : 각 테이블은 다른 테이블에서 어떠한 상수값도 제공 받지않고 주어진 상수값에 의해서만 범위를 줄임

전체범위처리 : 부분범위처리를 할 수 없음.

Scan방식 : 자신의 처리범위를 줄이기 위해 인덱스를 사용하는 경우만 Random Access이고,Merge작업은 Scan방식

선택적 : 연결고리가 되는 칼럼은 인덱스를 사용하지 않음

무방향성 : Join의 방향과는 무관

    3) SORT MERGE JOIN의 사용

- 전체범위처리를 하는 경우

ㆍSort/Merge Join은 주로 부분이 아닌 전체범위를 처리하는 경우에 유리

- EQUI-JOIN에서만 가능

- Join되는 테이블이 상호독립적인 경우

ㆍ 상대방 테이블에서 어떤 상수값을 받지 않고도 처리범위를 줄일 수 있는 상태에서 유리. 상수값을 받아 줄여진 범위가 30%이상이면 Sort/Merge Join이 유리

- 처리량이 많은 경우

ㆍRandom Access를 하지 않으므로 전체범위처리에 유리

- 효과적인 인덱스 구성이 관건

ㆍ자신의 처리범위를 어떻게 줄이느냐가 관건이므로 효과적인 인덱스구성 중요

 

  라. HASH JOIN

    1) 첫 번째 Table을 읽어 Join Key를 대상으로 Hash Area상에 Hash Table을 구성함

    2) 두 번째 Table을 읽으면서Join Key에 대한 Hash Value를 구해 Hash Area상의 Hash Value와 비교

    3) 주어진 조건을 만족하거나 두 번째 Table의 내용을 모두 읽을 때까지 수행

- HASH JOIN의 특징

독립적 : 각 테이블은 다른 테이블에서 어떠한 상수값도 제공 받지 않고 주어진 상수값에 의해서만 범위를 줄임

전체범위처리 : 부분범위처리를 할 수 없음

선택적 : 연결고리가 되는 칼럼은 인덱스를 사용하지 않음

SORT 안함 : SORT를 하지 않으므로 SORT MERGE JOIN 보다 좋은 성능을 내며,작은 table  

과 큰 table 의 join시에 유리

 

- HASH JOIN의 사용

전체범위처리를 하는 경우 : HASH Join은 주로 부분이 아닌 전체범위를 처리하는 경우에 유리

EQUI-JOIN에서만 가능

처리량이 많은 경우 : Random Access를 하지 않으므로 전체범위처리에 유리.

효과적인 인덱스 구성이 관건 : 자신의 처리범위를 어떻게 줄이느냐가 관건이므로 효과적인 인덱스 구성 중요. 작은 table 과 큰 table의 join시에 유리.

 

  마. 실행계획의 사례

    제일 상단의 부분에서 시작해 가장 오른쪽에 있는Join Operation의 바로 아래에 있는 Table(또는 Index) 먼저 수행

    2개 이상의 Table일 경우 Join Operation에 따라 바로 아래에 있는 Table(또는 Index)에 Access

    Index가 있는 경우 Index를 먼저 Access한 후 Table Access

Select  emp.ename

,dept.dname

From  emp, dept

Where  emp.deptno = dept.deptno

and   emp.ename like 'A%'

SELECT STATEMENT : ALL_ROWS

NESTED LOOPS

①TABLE ACCESS (FULL) : STD01.EMP[O]

②TABLE ACCESS (BY INDEX ROWID) : STD01.DEPT[O]

③INDEX (UNIQUE SCAN) : STD01.DEPT_PRIMARY_KEY[O](DEPTNO:1)

 

3. 실행계획(Execution plan)의 제어

  가. 힌트(hint)의 활용

    -힌트 : 옵티마이저가 항상 최적화된 실행계획을 수립하는 것은 아니므로 사용자가 힌트를 사용하여 원하는 실행계획으로 유도

    -힌트의 사용 형식 :

 

  나. 힌트(hint)의 종류

    1)INDEX Access Operation 관련 HINT

HINT

내용

INDEX

INDEX를 순차적으로 스캔

INDEX_ASC

INDEX를 내림차순으로 스캔

INDEX_DESC

INDEX를 오름차순으로 스캔.

    2)JOIN Access Operation 관련 HINT

HINT

내용

USE_NL

옵티마이저가 NESTED LOOP JOIN을 사용하도록 유도

먼저 특정 테이블의 ROW에 액세스하고 그 값에 해당하는 다른 테이블의 ROW를 찾는 작업을 해당범위까지 실행 조인

USE_NL_WITH_INDEX

INDEX를 사용해서 NESTED LOOP JOIN을 사용하도록 유도

USE_MERGE

옵티마이저가 SORT MERGE JOIN을 사용하도록 유도. 먼저 각각의 TABLE의 처리범위를 스캔하여 SORT한 후, 서로 MERGE하면서 JOING하는 방식

USE_HASH

옵티마이저가 HASH_JOIN을 사용하도록 유도

    3)JOIN시 DRIVING 순서 결정 HINT

HINT

내용

ORDERED

FROM절에 명시된 테이블의 순서대로 DRIVING

LEADING

파라미터에 명시된 테이블의 순서대로 JOIN

    4)옵티마이저 모드 변경 HINT

HINT

내용

CHOOSE

Access되는 테이블의 통계치 존재여부에 따라 옵티마이저가 RBO와 CBO중 하나를 선택 가능

ALL_ROWS

전체 RESOURCE 소비를 최소화 시키기 위함

FIRST_ROWS

주로 On-Line환경일 때 빠른 응답시간을 얻기 위함

RULE

RULE Based approach를 하도록 유도

 

4. 실행계획(Execution plan)을 통한 성과 및 제약사항

  가. Explain Plan을 통한 성과

    의도하지 않은 Full scans 을 피할 수 있다.

    100건을 조회하기 위해 백만 건을 스캔 하는 Unselective range scans 을 피할 수 있다.

    Late Predicate filters

    처리범위를 증가시키는 잘못된 조인순서를 피할 수 있다. Wrong join order

    처리범위를 줄이기 위해 조인 전에 데이터를 필터할 수 있다. Late filter operations

  나. Explain Plan의 제약사항

    바인드 변수에 대해서는 정확한 실제 계획을 보여주지 못한다.

    암묵적인 형 변환에 대해서도 정확한 예측을 하지 못한다.

댓글