[Oracle] 실행계획(Explain Plan)

개발자/Oracle | 2015.08.09 22:01

[일반적인 해석 순서]

- 실행계획 상 가장 안쪽으로 들여쓰기된 문장

- 각 문장은 들여쓰기가 한단계 '덜' 한 부모 문장에 종속

- 동일 레벨에서는 위쪽 문장

- 인덱스 경우하여 테이블 액세스 하는 두개의 실행계획 줌장의 경우에는 부모문장과 동일한 하나의 문장으로 취급 (가장 안쪽 들여쓰기 된 문장이 먼저 수행된다는 룰을 따르지 않음)

 

[중첩 루프 조인(NESTED LOOPS JOIN)]

- 실행계획 상 NESTED LOOPS 바로 밑에 나온 문장이 드라이빙 테이블에 대한 액세스, 그 아래 문장이 비드라이븡 테이블(PROBED/INNER TABLE) 액세스를 의미

- 드라이빙 테이블을 시작점으로 풀어나가야함.

- 9i 부터 보여지는 실행계획이 달라지긴 했지만, 내부 동작방식은 동일.

 

NESTED LOOPS

TABLE ACCESS(FULL) OF 'DEPT'

TABLE ACCESS(FULL) OF 'EMP'

: 피해야할 실행 계획,

  - 두 테이블의 크기가 매우 작거나,

  - 조인 시 두테이블의 조인 컬럼에 대한 인덱스가 없는 상태에서 인위적인 힌트 구문을 이용해서 중첩 루프 조인을 강제적으로 일어나게 할때

 

[로우 연산 (ROW OPERATIONS)과 집합 연산(SET OPERATIONS)]

- 로우연산

  : 단순 테이블 풀스캔 처럼 한번에 하나의 로우만을 대상으로 처리되는 연산/오퍼레이션.

  : 사용자는 마지막 로우가 페치되기까지 기다리지 않아도, 먼저 추출되는 로우값을 볼 수 있는 경우. (스캔되는 대로 바로 로우들을 계속 추출하여 보여줄 수 있음)

- 집합연산

  : 단 하나의 로우가 아닌 로우들의 집합을 대상으로 실행되는 오퍼레이션.

  : 모든 로우들이 페치되어 처리될 때까지, 해당 결과를 볼 수 없는 연산.

    SELECT JOB, SUM(SALARY) FROM EMPLOYEE GROUP BY JOB;

 

[SQL 연산]

- AND-EQUAL (로우연산)

  : 인덱스들에 의해 반환되는 값들(ROWID)을 병합해서, 공통적인 값만을 반환하는 연산.

 

SELECT * FROM EMP WHERE JOB = 'ANALYST' AND DEPTNO=20;

TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

AND-EQUAL

INDEX (RANGE SCAN) OF 'EMP$JOB$IDX' (NON-UNIQUE)

INDEX (RANGE SCAN) OF 'EMP$DEPTNO$ID' (NON-UNIQUE)

 

- CONCATENATION (로우연산)

  : 반환된 로우들을 합산하는 역할을 하는 단순한 로우 연산

 

SELECT * FROM EMP WHERE JOB = 'CLERK' AND DEPTNO IN (10,20);

SELECT * FROM EMP WHERE (JOB = 'CLERK' AND DEPNO = 10) OR (JOB = 'CLERK' AND DEPTNO = 20);

# 위의 두 쿼리는 실행계획이 동일하기 때문에, 성능도 동일하다.

 

CONCATENATION

TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

AND-EQUAL

INDEX (RANGE SCAN) OF 'EMP$DEPTNO$ID' (NON-UNIQUE)

INDEX (RANGE SCAN) OF 'EMP$JOB$IDX' (NON-UNIQUE)

TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

AND-EQUAL

INDEX (RANGE SCAN) OF 'EMP$DEPTNO$ID' (NON-UNIQUE)

INDEX (RANGE SCAN) OF 'EMP$JOB$IDX' (NON-UNIQUE)

 

- CONNECT BY

  : 재귀

  : 계층형 쿼리가 실행되는 경우, 성능을 제대로 내기 위해서는, START WITH 과 CONNECT BY 절이 인덱스를 사용하여 풀릴 수 있도록 하는 것이 키포인트다.

 

SELECT LEVEL, PARENT, THIS FROM CATEGORY

START WITH PARENT='COMPUTER' CONNECT BY PARENT = PRIOR THIS;

 

CONNECT BY

INDEX (RANGE SCAN) OF 'CATEGORY$PARENT$IDX' (NON-UNIQUE)

TABLE ACCESS (BY USER ROWID) OF 'CATEGORY'

TABLE ACCESS (BY INDEX ROWID) OF 'CATEGORY'

INDEX (RANGE SCAN) OF 'CATEGORY$PARENT$IDX' (NON-UNIQUE)

 

- COUNT

  : ROWNUM 모조 컬럼(PSEUDO COLUMN)이 WHERE 절이 아닌 SELECT 문 안에 위치할 때 나타나는 로우 연산.

 

SELECT ROWNUM, ENAME FROM EMP;

 

COUNT

TABLE ACCESS (FULL) OF 'EMP'

 

# 주요 모조 컬럼 : CURRVAL, NEXTVAL, LEVEL, ROWID, ROWNUM

 

- COUNT STOPKEY

  : ROWNUM 모조 컬럼이 WHERE 절 안에서 사용되어 조건자 구실을 할 때 나타는 로우 연산

SELECT * FROM EMP WHERE ROWNUM < 3;

COUNT (STOPKEY)

TABLE ACCESS (FULL) OF 'EMP'

 

- FILTER

  : 필터링이 일어나고 있음을 알려주는 SQL 로우 연산으로 WHERE 절 조건에서 인덱스를 사용하지 못할 때 나타난다.

SELECT * FROM EMP A WHERE SAL = (SELECT MIN(SAL) FROM EMP B WHERE B.DEPTNO = A.DEPTNO);

FILTER

TABLE ACCESS (FULL) OF 'EMP'

SORT (AGGREGATE)

TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

INDEX (RANGE SCAN) OF 'EMP$DEPTNO$ID' (NON-UNIQUE) 

 

 

 

 

- INDEX RAGE SCAN

  : 인덱스를 일정 범위의 값들로 제한을 두고 스캔을 행하는 로우연산.

  : 인덱스는 UNIQUE 이거나 NON-UNIQUE이든 상관없다

  : WHERE 절에 인덱스 구성 컬럼이 '<' 나 '>' 와 같은 범위제한자에 이용되거나 , BETWEEN 또는 LIKE 'A%' 와 같은 조건절에 이용될 때 수립된다.

  : 결합 인덱스 (COMPOSITE INDEX) 라면, 범위 제한자에 사용되는 컬럼은 인덱스 구성 컬럼 중, 선두 컬럼이여야 한다.

  : 조건에 만족하는, 첫번째의 로우를 인덱스 블럭에서 찾은 후 조건을 만족하는 로우를 찾을 때까지 인덱스 블럭을 연속적으로 계속 스캔해 나가는 방식이다.

  # 인덱스 스캔은 테이블 스캔과 달리 싱글 블럭(SINGLE BLOCK) 스캔이다. 즉, 테이블 스캔과 달리 멀티 블럭이 아닌 한 블럭씩 스캔을 진행하게 된다.

 

SELECT * FROM EMP WHERE DEPTNO > 10;

TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

INDEX (RANGE SCAN) OF 'EMP$DEPTNO$IDX' (NON-UNIQUE)

 

DEPNO의 오름차순으로 정렬되어 나오는 것을 알 수 있다.

 

# 인덱스의 속성

  : 특정 컬럼에 대해 인덱스 생성 시 자동으로 그 컬럼이 오름차순으로 정렬된 형태의 인덱스가 생성된다.

    그러므로, 인덱스를 경유하게 되면, 그 컬럼 값들은 자동으로 오름차순으로 정렬형태로 추출되게 된다.

  : 그러나 INDEX_FFS 와 같은 힌트 구문을 이용하여 INDEX FULL SCAN을 행해서 얻어진 결과 셋의 경우에는

    사용 인덱스 컬럼을 기준으로 볼 때 정렬되지 않은 결과를 보여 줄 수 있으므로 주의해야한다.

  : INDEX FULL SCAN 시에는 일반적인 인덱스의 스캔시 일어나느 싱글 블럭 스캔이 아닌 멀티 블록 스캔이 일어나며,

    일반 인덱스 스캔과 달리 병렬로 인덱스 스캔을 수행할 수 있다.

 

- INDEX RANGE SCAN DESCENDING

  : WHERE DEPTNO > 10 조건을 만족하는 첫번째 로우를 EMP$DEPTNO$IDX 인덱스에서 찾은 후, 역순으로 인덱스 블록을 스캔해 나간다.

  : 이 때 추출되는 로우들은 DEPTNO 컬럼을 기준으로 볼때 자동으로 역정렬 되어 있음을 알 수 있다.

SELECT /*+ INDEX_DESC(E EMP$DEPTNO$IDX) */ FROM EMP E WHERE DEPTNO > 10;

TABLE ACCESS (BY INDEX ROWID) OF 'EMP'

INDEX (RANGE SCAN DESCENDING) OF 'EMP$DEPTNO$IDX' (NON-UNIQUE)

 

SSS

 

 

 

'개발자 > Oracle' 카테고리의 다른 글

[Oracle] 실행계획(Explain Plan)  (0) 2015.08.09
[Oracle] 실행계획(Explain plan/Execution plan)  (0) 2015.08.02

댓글을 달아 주세요