3.2.2. 데이터 연결을 위한 실행계획

 

EXPLAIN PLAN(실행계획) 이란?

  SQL문의 엑세스 경로를 확인하고 튜닝할 수 있도록 SQL문을 분석하고 해석하여 실행계획을 수립한 후 실행계획을 PLAN_TABLE에 저장하도록 해주는 명령 이다.

  SQL Trace 없이 사용 할 수 있다.

  ORACLE_HOME/rdbms/admin/utlxplan.sql실행 하여 PLAN_TABLE을 생성한다.

  statement_id컬럼에 인덱스를 생성해주는것이 수행속도를 향상시켜주고 id값이 중복되는 것을 막을 수 있다.

--문법------------------------------------------------------

EXPLAIN PLAN set statement_id = 'identifier' INTO tablename

FOR <sql statement>

------------------------------------------------------------

 

. Plan_table 생성

  Explain plan sql에 포함해서 수행하면 옵티마이저가 실행 계획까지만 수립하여 plan_table에 저장 한다.

  table을 생성하기 위한 script $ORACLE_HOME/rdbms/admin/utlxplan.sql 이다.

SQL> @C:\oracle\ora81\rdbms\admin\utlxplan.sql

. Index 생성

  테이블 생성 후 수행 속도 향상과 동일한 statement_id가 생성되는 것을 방지하기 위해 index를 생성 한다.

  SQL> CREATE UNIQUE INDEX plan_index ON PLAN_TABLE(statement_id,id);

. SQL 문 사용

  FOR 문장 다음에 확인하고자 하는 sql문을 입력 실행한다.

 

SQL> EXPLAIN PLAN SET STATEMENT_ID='a1' FOR

     SELECT /*+ index(emp emp_pk) */ * FROM emp WHERE empno > 0;

   

 

. PLAN_TABLE SELECT 하는 SQL 문을 실행

 

SQL> SELECT LPAD(operation,LENGTH(operation)+ 2*(LEVEL-1)) ||DECODE(id,0,'cost  estimate:' ||

       DECODE(position,'0','N/A',position),null) || ' ' ||options || DECODE(object_name,null,null,':') ||

       RPAD(object_owner, LENGTH(object_name)+1,',') || object_name ||

       DECODE (object_type,'UNIQUE' ,'(U) ','NON_UNIQUE','(NU)',null) ||

       DECODE(object_instance,null,null,'('||object_instance||')')  "Explain Plan"

       FROM PLAN_TABLE

       START WITH ID= 0 and STATEMENT_ID = '&&id'

       CONNECT by prior ID=PARENT_ID and STATEMENT_ID='&&id'

 

-- a1을 입력하면 아래와 같은 실행계획을 볼 수 있다.

 Explain Plan

-----------------------------------------------

SELECT STATEMENTcost  estimate:1

  TABLE ACCESS BY INDEX ROWID:TESTEMP(1)

    INDEX RANGE SCAN:TEST,,,EMP_PK   

   

PLAN_TABLE 컬럼 설명

컬 럼 명

설 명

STATEMENT_ID

EXPLAIN PLAN문에서 사용자가 지정한 제목

TIMESTAMP

실행계획이 수립된 날짜와 시간

REMARKS

사용자가 부여한 주석(COMMENT)

OPERATION

아래 표에 자세히 설명 되어 있습니다.

OPTIONS

아래 표에 자세히 설명 되어 있습니다.

OBJECT_NODE

사용한 데이터베이스 링크

OBJECT_OWNER

해당 라인의 오브젝트를 생성한 사용자 그룹

OBJECT_NAME

테이블이나 인덱스, 클러스터등의 이름

OBJECT_INSTANCE

SQL FROM절에 기술된 오브젝트를 좌에서 우로 부여한 번호

OBJECT_TYPE

오브젝트의 종류( non-unique index)

ID

수립된 각 실행단계에 붙여진 일련번호

PARENT_ID

해당 ID의 부모가 가진 ID

POSITION

같은 부모 ID를 가지고 있는 자식 ID간의 처리 순서

OTHER

다른 필요한 텍스트를 지정하기 위한 필트

OPERATION의 종류와 OPTIONS에 대한 설명

OPERATION(기능)

OPTIONS(옵션)

설 명

AGGREGATE

GROUP BY

그룹함수를 사용하여 하나의 로우가 추출되도록 하는 처리(버전 7에서만 표시됨)

AND-EQUAL

 

인덱스 머지를 이용하는 경우

CONNECT BY

 

CONNECT BY를 사용하여 트리 구조로 전개

CONCATENATION

 

단위 액세스에서 추출한 로우들의 합집합을 생성

COUNTING

 

테이블의 로우스를 센다

FILTER

 

선택된 로우에 대해서 다른 집합에 대응되는 로우가 있다면 제거하는 작업

FIRST ROW

 

조회 로우 중에 첫번째 로우만 추출한다.

FOR UPDATE

 

선택된 로우에 LOCK을 지정한다.

INDEX

INQUE

UNIQUE인덱스를 사용한다. (단 한개의 로우 추출)

RANGE SCAN

NON-UNIQUE한 인덱스를 사용한다.(한 개 이상의 로우)

RANGE SCAN
DESCENDING

RANGE SCAN하고 동일하지만 역순으로 로우를 추출한다.

NTERSECTION

 

교집합의 로우를 추출한다.

MERGE JOIN

 

먼저 자신이 조건만으로 액세스한 후 각각을 SORT하여 MERGE해 가는 조인

OUTER

위와 동일하지만 outer join을 사용한다

MINUS

 

MINUS 함수를 사용한다.

NESTED LOOPS

 

먼저 어떤 드라이빙 테이블의 로우를 액세스한 후 그 결과를 이용해 다른 테이블을 연결하는 조인

OUTER

위와 동일하지만 outer join을 사용한다.

PROJECTION

 

내부적인 처리의 일종

REMOTE

 

다른 분산 데이터베이스에 있는 오브젝트를 추출하기 위해 DATABASE LINK를 사용하는 경우

SEQUENCE

 

시퀀스를 액세스 한다.

SORT

UNIQUE

같은 로우를 제거하기 위한 SORT

GROUP BY

액세스 결과를 GROUP BY 하기 위한 SORT

JOIN

MERGE JOIN을 하기 위한 SORT

ORDER BY

ORDER BY를 위한 SORT

TABLE ACCESS

FULL

전체 테이블을 스캔한다.

CLUSTER

CLUSTER를 액세스 한다.

HASH

키값에 대한 해쉬 알고리즘을 사용(버전 7에서만)

BY ROWID

ROWID를 이용하여 테이블을 추출한다.

UNION

 

두 집합의 합집합을 구한다.(중복없음) 항상 전체 범위 처리를 한다.

UNION ALL

 

두 집합의 합집합을 구한다.(중복가능) UNION과는 다르게 부분범위 처리를 한다.

VIEW

 

어떤 처리에 의해 생성되는 가상의 집합에서 추출한다.(주로 서브쿼리에 의해 수행된 결과)

 

 

* 주요 내용은 조인, 서브쿼리 여러 개의 테이블이 연결될 실행계획에 대한 설명

  (일반 조인연산) Nested Loop Join, Sort Merge Join, Hash Join

  (특별 조인연산) Semi Join, Cartesian Join, Outer Join, Index Join

 

* 조인을 위한 힌트절

/*+ use_nl(table_name) */                 -> Nested Loop

   /*+ USE_HASH (table_name) */     -> 각 테이블간 HASH JOIN이 일어나도록 유도 합니다.
/*+ USE_MERGE (table_name) */         ->
지정된 테이블들의 조인이 SORT-MERGE형식으로 일어나도록 유도 합니다.

 

3.2.2.1. Nested Loop Join (내포 조인, 중첩루프 조인)

- 기본적인 조인 형태이다.

- 프로그램 구조

  Nested Loops

    { (outer loop) - 선행테이블(Driving Table)

       (inner loop) - 후행테이블(Driven Table)

       (process join)

     }

- 순차적(부분범위처리가능)
- 종속적(먼저 처리되는 테이블의 처리범위에 따라 처리량 결정)
- 랜덤 액세스 위주
- 연결고리 상태에 따라 영향이 큼
- 주로 좁은 범위 처리에 유리
- Join 방법중 첫번째 row를 받는 시간이 가장 빠르다.
- index의 효율이 좋다면 가장 최적의 성능을 발휘하는 Join Method이다.

 

=> 조인 조건에 해당되는 컬럼에 인덱스가 없을 경우 Nested Loop에서는 Full 스캔하게 되어 엄청난 오버헤드를 가지게 됨. 이 점을 해결하기 위해  단 두번의 Scan으로 처리가 가능한  소트머지 조인이나 해시 조인이 생겨나게 된것

 
- 실행계획 분석

SQL> select employee_id, first_name, salary, department_id
  2  from emp
  3  where department_id  in (
  4  select  department_id
  5  from dept
  6  where department_name like 'C%');

Execution Plan
----------------------------------------------------------
Plan hash value: 230627304

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    39 |  3159 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |    39 |  3159 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |   107 |  5457 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| DEPT |     4 |   120 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

 

SQL> Create index emp_deptid_idx on emp(department_id);

SQL> select employee_id, first_name, salary, department_id
  2  from emp
  3  where department_id  in (
  4  select  department_id
  5  from dept
  6  where department_name like 'C%');

Execution Plan
----------------------------------------------------------
Plan hash value: 3728445527

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |    39 |  3159 |     5  (20)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP            |    10 |   510 |     1   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |                |    39 |  3159 |     5  (20)| 00:00:01 |
|   3 |    SORT UNIQUE              |                |     4 |   120 |     3   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL       | DEPT           |     4 |   120 |     3   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN         | EMP_DEPTID_IDX |    10 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

 

=> 4번 서브쿼리 테이블 DEPT  스캔

=>3번 소팅

=>5번 메인쿼리의 연결조건에 대한 인덱스 스캔

=>2번 서브결과와 메인결과를 Nested loop로 조인(다:다)

=>1번 메인쿼리의 select절의 값을 스캔하기 위한 access

=>0번 최종 select절로 가져온 것.

 

 

3.2.2.2. Sort Merge Join(정렬병합조인)

- Nested Loop 조인에서 적절한 인덱스가 존재하지 않아서 조인의 대상 범위가 넓을 때는 랜덤 액세스로 인해 비효율적인 점을 개선하기 위한 방법이다.

- 두 집합을 스캔하여 정렬한 후 머지한다.

- 대용량을 처리해야하는 경우는 정렬의 부담이 크기 때문에 이 방법보다는 해시조인 방법을 사용한다.

- 조인 연결고리 연산자가 ‘=’이 아닌 경우(LIKE, BETWEEN, 부등호)일 경우 Nested Loop 조인 보다 유리하며 해시조인은 이 경우 사용할 수 없다.

- 정렬된 집합이 준비되어있거나 인덱스를 이용하여 정렬이 가능할 때 유리하다.

- Sort Merge 조인의 실행계획 예(한쪽을 인덱스 정렬)

- 조인이 되는 각각의 테이블 자료를 스캔방식으로 읽어 들이거나 인덱스를 사용하여 메모리로 읽어 들임. 읽혀진 두 테이블의 조인 집합은 연결 고리 칼럼에 대하여 각각 정렬을 수행한 후 조인 작업이 수행됨. 정렬을 수행하기 위하여 SORT_AREA_SIZE 크기 만큼 메모리를 할당 받아 사용하게 되며, 메모리가 부족하다면 temporary tablespace 를 이용하여 정렬을 수행하게 된다.

 

튜닝 포인트
-SORT를 얼마나 효과적으로 하느냐
, SORT_AREA_SIZE를 적절한 크기로 지정하는 것

 
 
SQL> select /*+ORDERED USE_MERGE(emp) */ employee_id, first_name, salary, depa
ment_id
  2  from emp
  3  where department_id  in (
  4  select  department_id
  5  from dept
  6  where department_name like 'C%');
Execution Plan
----------------------------------------------------------
Plan hash value: 1183202798
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    36 |  1224 |     9  (34)| 00:00:01 |
|   1 |  MERGE JOIN          |      |    36 |  1224 |     9  (34)| 00:00:01 |
|   2 |   SORT JOIN          |      |     2 |    32 |     5  (40)| 00:00:01 |
|   3 |    SORT UNIQUE       |      |     4 |    64 |     3   (0)| 00:00:01 |
|*  4 |     TABLE ACCESS FULL| DEPT |     4 |    64 |     3   (0)| 00:00:01 |
|*  5 |   SORT JOIN          |      |   107 |  1926 |     4  (25)| 00:00:01 |
|   6 |    TABLE ACCESS FULL | EMP  |   107 |  1926 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

 

- 동시적(무조건 전체범위처리). 전체 데이터를 리턴받는 시간이 Nested-Loop보다 빠르다.
- 추가적인 sort메모리 비용이 필요하다. 메모리 공간이 부족하다면 temp tablespace를 사용하게 됨.
- Sort 메모리에 위치하는 대상은 Join key뿐만 아니라 select list도 포함하므로 불필요한 select list는 제거해야 한다.
- Sort를 요구하지 않는 경우는 Hash Join이 유리하다.
- 독립적(자기의 처리범위만으로 처리량 결정)
- 스캔(Scan)액세스 위주
- 연결고리 상태에 영향이 없음
- 주로 넓은범위 처리에 유리

 

장단점
단점 - 두 결과 집합의 크기가 많이 차이나는 경우에는 SORT Merge Join이 비효율적
어느 한쪽이라도 정렬 작업이 종료되지 않으면 조인이 시작될 수 없으므로
두 테이블 조인 집합의 크기가 많이 차이가 난다면 한쪽에 '대기' 상태가 발생하여
비효율적으로 처리가 된다. 이렇게 크기가 비슷하지 않은 집합의 조인을 위해서
HASH 조인을 사용할 수 있다

 

3.2.2.3. Hash Join(해시 조인)

- 해싱함수를 이용한 조인, 동등조인(=)일 경우만 사용 가능

- Hash Join의 실행계획 예(인덱스 처리된 스캔과 전체테이블 스캔)

 

해쉬조인은 대용량 처리의 선결조건인 랜덤과 정렬에 대한 부담을 해결할 수 있는 대안으로서 등장

* 해쉬함수 : 데이터의 컬럼에 있는 상수값을 입력으로 받아 '위치값'을 리턴하는 것으로 이해하면 됨.
* 해싱 : 하나의 문자열을 원래의 것을 대신하는, 보다 짧은 길이의 값이나 키로 변환하는 것.


Hash Join Method만을 이용하는 것보다는 Parallel Processing을 이용한 Hash Join은 대용량 데이터를 처리하기 위한 최적의 Solution을 제공해주고 있다.

 
<특징>
- CBO(Cost based optimization)에서만 가능하며, CPU Power에 의존적이다.
- Hash Join은 equal만 가능
- Hash Join은 두개의 Join Table중 small rowset을 가지고 hash_area_size에 지정된 메모리 내에서 hash table을 만든다.
- Hash table을 만든 이후부터는 Nested-Loop의 장점인 순차적인 처리 형태로 수행된다.
Hash Join은 Nested-Loop 와 Sort Merge의 장점을가지고 있다.

- 기존에 미리 생성되어 있는 인덱스를 전혀 사용하지 않는다.
먼저 수행되는 집합이 어느 것인지 확인할 필요가 있다.
옵티마이저가 해쉬조인을 너무 과신한 나머지 소량의 데이터의 경우에도 모두 해쉬조인으로 처리하려는 경향이 많으므로 실전에서는 이러한 면을 반드시 주의깊게 살펴봐야 할 것이다.

- 초대형 조인이라면 반드시 해쉬조인을 적용해야 한다고 생각할 수 있다.
대용량의 데이터를 조인한다면 상당히 큰 해쉬영역을 필요로 하므로 수많은 프로세스가 공동으로 사용하는 귀중한 메모리를 함부로 차지한다는 것은 시스템 전체 시각에서 볼 때는 커다란 오버헤드가 될 수 있다. 온라인처리에서 함부로 해쉬조인을 적용하는 것은 문제를 발생시킬 소지가 충분히 있다.

 

<수행방법>
1. Index Scan을 통해 제공받은 ROWID를 통해 테이블에 Random Access한다.
2. 추출한 테이블의 컬럼값에 Hash Function을 적용한 후 Hash값을 제공받아 Hash Table을 구성.
3. 2번에서 추출한 Hash Table을 PGA에 구성하게 된다. 이때 hash_area_size로 정의된 Parameter의 값만큼 구성
4. 조인하기 위한 테이블과 hash table을 조인하여 성공 여부를 규명

 

 

3.2.2.4. Semi Join(세미 조인)

- 조인의 정의에서 자연조인에서 한쪽 릴레이션의 속성은 제거되고 다른 한쪽의 속성만 나타나는 조인연산으로 주로 서브질의에 의한 조인을 말한다.

SQL> select employee_id, first_name, salary, department_id
  2  from emp
  3  where department_id  in (
  4  select  department_id
  5  from dept
  6  where department_name like 'C%');

Execution Plan
----------------------------------------------------------
Plan hash value: 230627304

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    39 |  3159 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |    39 |  3159 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |   107 |  5457 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| DEPT |     4 |   120 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

 

 

3.2.2.5 Cartesian Join(카티젼조인)

- 조인되는 2 테이블간에 공통 속성이 없어서 조인연산이 카티젼곱으로 계산되는 경우이다.

- (실행계획의 예)

MERGE JOIN(CARTESIAN)

VIEW

SORT(GROUP BY)

TABLE ACCESS (FULL) OF table1

SORT(JOIN)

INDEX (RANGE SCAN) OF tableindex (UNIQUE)

- 카티젼 조인이 나타나는 경우

(1) 2개의 테이블을 합하기 위하여 고의적으로 카티젼조인을 할 경우

(2) 스타조인을 하기 위하여 디멘전 테이블을 조인하는 경우

(3) 3개 이상 테이블 조인시 조인 순서 잘못으로 나타난다.

 

3.2.2.6. Outer Join(아우터 조인)

- 아우터조인은 조인에 참여하지 못하는 테이블도 결과에 포함시킬 경우 사용하는 조인이다.

1 Nested Loop 아우터조인

- 아우터 조인의 아우터루프가 결정되기 때문에 조인시 신중해야 한다.

- 힌트는 USE_NL(table1, table2)와 동일하지만 실행계획에는 NESTED LOOPS(OUTER)로 나타난다.

2 Hash 아우터조인

- Nested Loop 조인으로는 용량이 많아 부담되는 경우 사용한다.

- 내측 조인 집합이 해시테이블로 생성되어 연결된다.

- (실행계획 예)

HASH(GROUP BY)

HASH JOIN(OUTER)

TABLE ACCESS (FULL) OF table1 (outer table)

INDEX(RANGE SCAN) OF tableindex (inner table)

- 조인뷰와 조인인라인뷰와 아우터조인 수행 - 뷰의 병합이나 조건절 진입이 허용되지 않고 뷰의 전체 집합이 독립적으로 수행된 결과와 아우터조인을 수행한다(실행 예 참조)

3 SORT Merge 아우터조인

- Nested Loop 조인으로 문제가 있을 때 사용

4 전체 아우터조인

- 양쪽 데이블 모두에 아우터 조인을 실행할 경우

- 실행계획의 예

(주의) : (+) 연산자를 사용하여 아우터조인시 주의할 점 - 내측테이블에 조건을 기술할 경우(p230 참조)

 

SQL> select employee_id, first_name, salary, d.department_id,d.department_name
  2  from emp e
  3  left outer join dept d
  4  on e.department_id  = d.department_id;

Execution Plan
----------------------------------------------------------
Plan hash value: 3387915970

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   107 |  3638 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |   107 |  3638 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |   107 |  1926 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| DEPT |    27 |   432 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+))

 

3.2.2.7 인덱스조인

- 인덱스간의 해시조인을 통하여 액세스하는 방법이다.