SQL문- 실행계획/ INDEX(UNIQUE, FULL, RANGE, MIN/MAX, 컬럼 가공, FAST)

실행계획

- SQL이 실행되어 데이터를 처리하고자 할 때 옵티마이저에서 수립되는 SQL 실행 방법론

SQL의 비효율적인 부분 확인이 가능하며 쿼리의 성능 향상을 위한 튜닝포인트 도출 가능.

 

실행계획 읽기

 - 테이블 액세스 프로세스와 그 테이블의 인덱스를 액세스하는 프로세스 하나의 단위
  - 여러 문장 중에서 들여쓰기가 많이 되어 있는 문장이 먼저 실행
 - 들여쓰기가 적은(한 레벨위의) 상위 프로세스에 종속
 - 들여쓰기가 같은 동일 레벨이라면 위에 있는(먼저 나오는) 문장이 먼저 실행
 - 하위 노드를 가진 노드의 경우에는 하위 노드가 먼저 실행

 

 

사용 방법 : SELECT 문 앞에 EXPLAIN PLAN FOR이라고 적으면 됨.

 

단점을 뽑자면 EXPLAIN PLAN은 한번에 하나의 쿼리문 실행계획만 확인 가능하고 별도의 SELECT문을 작성해야 함.

또한 데이터를 읽지 않아 소요 시간을 추정하거나 데이터 관련 I/O정보를 확인할 수 없음.

(그래서 사용빈도는 낮다고 함)

 

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY를 통해 실행계획(예측ver.)을 도출해냄.

---

DBMS_XPLAN.DISPLAY_CURSOR 사용

 

- 튜닝 진행 시 SQL의 플랜 정보를 확인하기 위해 DBMS_XPLAN 패키지 사용해야 할때

 

사용방법 : GRANT SELECT ON V_$SESSION TO  유저명;

                 GRANT SELECT ON V_$SQL_PLAN_STATISTICS_ALL TO 유저명;

                 GRANT SELECT ON V_$SQL_PLAN TO 유저명;

                 GRANT SELECT ANY DICTIONARY TO   유저명;

---

통계 캡처레벨(STATISTICS_LEVEL)

● BASIC : MMON이 DB통계정보를 캡처하지 않기 때문에 AWR 통계 및 METRIC이 계산되지 않고, ASMM을 사용할 수 없으며 ADVISOR 기능이 비활성화 된다.

 

● TYPICAL : 일부 통계만 수집. 일반적으로 오라클 데이터베이스 동작을 모니터하는데 필요한 사항

 

● ALL : 가능한 모든 통계 캡처. 별도의 SQL 진단 정보가 필요한 특이한 경우 외에는 사용하지 않는 게 좋음.

 

사용방법ALTER SESSION SET STATISTICS_LEVEL = BASIC;

ALTER SESSION SET STATISTICS_LEVEL = TYPICAL;

ALTER SESSION SET STATISTICS_LEVEL = ALL;

 

 

위의 CURSOR 구문은 직전 쿼리 실행 PLAN을 확인할 수 있음.

---

INDEX UNIQUE SCAN

- 고유 인덱스가 정의된 컬럼이 조건절에서 '='로 비교되는 경우

- 그 외의 경우는 전부 INDEX RANGE SCAN 발생

- 조건 검색이 단일인 경우 사용

사용예시

 

 

 

FULL TABLE SCAN

    테이블에 할당된 첫번째 블록부터 HWM 아래의 모든 블록을 읽음
    1회의 I/O에 대해서 여러 개의 블록을 읽음

 

INDEX RANGE SCAN

    INDEX UNIQUE SCAN을 제외한 모든 INDEX SCAN을 INDEX RANGE SCAN 
    고유 인덱스가 정의된 컬럼이 조건절에서 '='비교연산자를 제외한 모든 연산자로 비교되는 경우
    비고유 인덱스가 정의된 컬럼이 조건절에 기술되는 경우

    필요한 범위까지만 탐색

 

INDEX RANGE SCAN DESCENDING

 

        INDEX RANGE SCAN에서는 조건절의 범위를 검색할 때, 기본적으로 최소 경계값부터 검색을 시작하여

        최대 경계값에서 검색을 종료(즉, 최대값을 구할 때 사용)

        만약, 최대 경계값에서 검색을 시작해 최소 경계값에서 검색을 종료해야 하는 경우에 사용

 

 

INDEX RANGE SCAN(MIN/MAX)

  DESCENDING 으로 언급한 최대값 찾기에서 결합 인덱스를 정의

 

---

INDEX컬럼 가공

- 인덱스가 정의된 컬럼을 가공하면 인덱스를 활용할 수 없음

 

 

* 별도로 팁이 있다면 테이블 대부분의 데이터를 찾을 때는 RANGE SCAN 보다는 FULL TABLE SCAN 방식이 유리함.

 

 

INDEX FAST FULL SCAN

- INDEX FULL SCAN보다 빠름

- 바로 Multi Block Read가 가능하기 때문인데 데이터 출력 순서를 보장하지는 않음