1. 실행계획

1. 개념
   • 사용자가 SQL을 실행하여 데이터를 추출하려고 할 때 옵티마이저가 수립하는 작업절차
   • 실행계획은 SQL 처리를 위한 실행 절차와 방법을 표현한 것
   • 실행계획은 조인 방법, 조인 순서, 액세스 기법 등이 표현
   • 실행계획이 다르다고 실행 결과가 달라 질 수는 없음
   • CBO의 실행계획에는 단계별 예상 비용 및 건수 등이 표현됌
2. SQL 처리 흐름도
   • SQL문의 처리 절차를 시각적으로 표현한 것
   • 인덱스 스캔 및 전체 테이블 스캔 등의 액세스 기법을 표현 가능
   • 때문에 성능적인 면도 표현 가능
   • 인덱스 범위 스캔은 결과 건수만큼 반환
   • 결과가 없으면 한 건도 반환하지 않을 수 있음
3. 실행계획을 통해 알 수 있는 정보
   • 액세스 기법
   • 질의 처리 예상 비용
   • 조인 순서
   • 실제 처리 건수는 알 수가 없음(단순 계획이므로)
4. 실행계획 확인 방법

   1. Explain plan

      1. SQL에 대한 생행계획만을 확인할 뿐 데이터를 처리를 하지 않음
      2. 때문에 데이터베이스에 어떠한 부하도 주지 않음
      3. 튜닝하고자 하는 SQL이 다수일 경우 매번 명령을 수행시켜야하는 불편함이 존재
      4. 데이터를 처리하지 않기 때문에 I/O관련 시간도 측정 불가(SORTING 포함)

      • EXPLAIN PLAN 생성 방법

        EXPLAIN PLAN
        SET STATEMENT_ID = 'TEST1' INTO PLAN_TABLE
        FOR
        	SELECT /*+USE_NL(e d)*/
        	        e.name, e.deptno, d.dname
        	FROM emp e, dept d
        	WHERE e.deptno = d.deptno;
        

      • EXPLAIN PLAN 결과 확인 방법

        SELECT *
        FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE', 'TEST1', 'ALL'));
        

   2. Set autotrace

      1. EXPLAIN 명력과 달리 한번의 명령으로 여러개의 SQL에 대한 실행계획을 볼 수 있음
      2. 다양하게 옵션을 사용할 수 있어서 여러가지의 정보를 선택적으로 확인 가능
      3. SET AUTOTRACE는 옵션이 존재

         • SET AUTOTRACE ON EXPLAIN;

           출력결과와 실행계획까지만 나타냄 통계정보는 생략

         • SET AUTOTRACE ON STATISTICS;

           출력결과와 실행계획을 생략하고 I/O관련 정보를 선택적으로 보여줌

         • SET AUTOTRACE ON TRACEONLY;

           데이터가 상당히 클때 사용하는데 출력결과를 나타내지 않음

         • SET AUTOTRACE ON TRACEONLY EXPLAIN;

           데이터를 처리하지 않고 실행계획만을 보여줌(많이 사용함)

         • SET AUTOTRACE ON TRACEONLY STATISTICS;

           데이터를 처리하지 않고 I/O관련 정보를 보여줌

         • SET AUTOTRACE ON OFF;

           사용하지 않을때 사용

      • 기본 예제

        SET AUTOTRACE ON; 
        SELECT /*+USE_NL(e d)*/
                  e.name, e.deptno, d.dname
        FROM emp e, dept d
        WHERE e.deptno = d.deptno;
        

      • 옵션 사용 예제

        SET AUTOTRACE ON TRACEONLY EXPLAIN;
        SELECT /*+USE_NL(e d)*/
                  e.name, e.deptno, d.dname
        FROM emp e, dept d
        WHERE e.deptno = d.deptno;
        

        위 쿼리의 실행계획을 보면 다음과 같음

        

        들여쓰기에 따라 순서가 결정되기 때문에 3 -> 4 -> 2 -> 5 -> 1 -> 0 순으로 실행

2. 옵티마이저

1. 개념

   사용자가 질의한 SQL문에 대해 최적의 실행방법을 결정하는 역할

2. 옵티마이저 실행단계

   1. SQL 해석

   2. 실행계획 수립

   3. 실행

3. 종류별 특징

   1. 규칙기반 옵티마이저 (RBO, Rule Based Optimizer)
      • 규칙(우선순위)를 가지고 실행계획을 생성
      • 인덱스유무(유일, 비유일, 단일, 복합 인덱스)
      • 연산자(=, <, ...)
      • 객체(힙 테이블, 클러스터 테이블)의 세 종류를 이용하여 SQL문을 실행
      • 규칙 중 제일 낮은 우선 순위는 전체 테이블 스캔
      • 규칙 중 제일 높은 우선순위는 ROWID를 활용한 테이블 액세스 방식
   2. 비용기반 옵티마이저 (CBO, Cost Based Optimizer)
      1. 현재 대부분의 관계형 데이터베이스는 비용기반 옵티마이저만을 제공
      2. 규칙기반 옵티마이저의 단점을 극복하기 위하여 출현
      3. SQL문을 처리하는데 필요한 비용이 가장 적은 실행계획을 선택
      4. 테이블, 인덱스, 칼럼 등의 다양한 객체 통계정보와 시스템 통계정보등을 이용
      5. 비용기반 옵티마이저의 3가지 모듈 : 질의 변환기, 대안 계획 생성기, 비용 예측기 존재
      6. 테이블 및 인덱스 등의 통계 정보를 활용하여 SQL문을 실행하는데 소요될 처리시간 및 CPU, I/O 자원량등을 계산하여 가장 효율적인 것을 선택하는 옵티마이저
      7. 즉, 무턱대고 index 타는게 아니라는 말

3. 인덱스

1. 개념
   • 인덱스는 원하는 데이터를 쉽게 찾을 수 있도록 돕는 책의 찾아보기와 유사한 개념
2. 특징
   1. 기본 인덱스에 중복된 키 값들은 나타날 수 없음
   2. 기본 인덱스에 NULL 값이 나타날 수 없음
   3. 보조 인덱스에는 고유한 키 값들만 나타날 수 있는 것은 아님
   4. 자주 변경되는 속성은 인덱스로 지정하지 않는 것이 좋음
   5. 규칙기반 옵티마이저는 적절한 인덱스가 존재하면 항상 인덱스를 사용함
   6. 인덱스 범위 스캔은 결과가 없으면 한 건도 반환하지 않을 수 있음
   7. 인덱스의 목적은 조회 성능을 최적화 하는데 있음
   8. INSERT, UPDATE, DELETE 등의 DML 처리 성능을 저하시킬 수 있음
   9. B-TREE INDEX는 일치 및 범위 검색에 적절함
   10. 인덱스 액세스는 테이블 전체스캔보다 항상 유리한 것은 아님
   11. 범위 조건이 일치 조건보다 앞에 있으면 효율적일 수 없음
3. 종류별 특징
4. INDEX 생성 쿼리 작성 방법
5. 전체테이블 스캔과 인덱스 스캔의 차이점

4. JOIN기법

1. 개념
2. SEMI와 ANTI 차이