실행계획

만약 SQL문이 속도에 문제를 나타내고 있다면, 무엇보다도 실행계획을 확인해서 분석을 통해 원인을 규명해야한다. 여기서 실행계획이 무엇이냐면 사용자가 SQL을 실행하여 데이터를 추출하려고 할 때, 옵티마이저가 수립하는 작업절차를 뜻한다.

실행계획 확인 방법

< 1. EXPLAIN PLAN >

• SQL에 대한 실행계획만을 확인할 수 있음
• 명령을 사용할 때 데이터를 처리하지 않음

• FOR다음에 적혀있는 SELECT문이 튜닝할 대상이라는 가정이 된다. 명령문을 통해서 동작을 시키게되면 데이터를 실제 읽어들이지 않는다. -> 이로 인해, USER들에게 끼치는 영향이 없다.

• 저장해둔 결과를 조회하는 차원에서 위의 SELECT문을 작성해야한다.

< 2. SET AUTOTRACE >

• EXPLAIN PLAN 명령과는 다르게 달리 한번의 명령으로, 여러 개의 SQL에 대한 실행계획을 바로 볼 수 있음

• 다양하게 옵션을 사용할 수 있어서 여러 가지의 정보를 선택적으로 확인할 수 있음
  

• 데이터 추출을 실제로 하게된다. 추출결과를 화면에 나타내게된다. 또한 실행계획을 자동으로 나타내게 된다. I/O관련정보도 제공!

명령 옵션

• SET AUTOTRACE ON EXPLAIN : SELECT를 동작시켰을 때 출력되는 결과를 나타내고 그 밑에 실행계획까지만을 나타내는 것(통계정보 생략)
• SET AUTOTRAACE ON STATISTICS : ON이란 옵션에 의해서 SELECT를 돌렸을 때 출력되는 결과물을 화면에 DISPLAY 시키고 실행계획을 생략하고
  I/O 관련정보를 선택적으로 보여준다.
• SET AUTOTRACE TRACEONLY : ON과 달리 SELECT를 동작시켰을 때, 출력된 결과물을 일일이 DISPLAY 시키지 않는다. 실행계획과 I/O정보까지 제공한다.
• SET AUTOTRACE TRACEONLY EXPLAIN : EXPLAIN PLAN과 비슷하게 데이터를 처리하지 않는다. 단지, 그 SQL만이 소유하는 실행계획만을 추출해준다라고 보는것이 맞다. 정말 큰 DATA를 처리할수 밖에 없는 SQL이 된다면 데이터 처리과정없이 실행계획만 하고자한다했을 때, 이 명령어를 써라!(부하X)
  -SET AUTOTRACE TRACEONLY STATISTICS : I/O관련정보만을 보여준다.
  -SET AUTOTRACE OFF - SET AUTOTRACE에 따른 정보를 활용하지 않겠다 했을때 이 명령어!

< 3. 실행계획 분석 >

SQL에 대한 실행분석

• 맨 왼쪽은 각 작업간의 ID가 부여되어 있다. 각 작업을 구분하기 위한 것이다. 전반적 실행계획은 들여쓰기로 구분이 되어있다. 1번밑에 2번과 5번이 동일한만큼 들어가있는것을 알 수 있다. 1번과 연관된 작업이 2번과 5번이란 뜻이다! 그리고 2번과 5번중에 2번이 먼저 행해지는 작업이다. 들여쓰기가 같은 경우에는 위에 것이 먼저 실행되는 순서이다.

• 위 실행계획을 그림으로 나타내었을 때!
• 3번이 가장 먼저 실행되었다.
• EMP 테이블로부터 아무런 조건 없이 FULL TIMESCAN을 통해 데이터를 읽어내는 것이 첫번째 작업이다. 그 테이블이 SELECT문을 보면 JOIN을 하고 있다. JOIN을 시켜나갈 때 상대쪽의 TABLE이 갖고 있는 연결 컬럼의 INDEX에 대해서 연결을 시도하고 있다. 그것이 4번이다. EMP에서 읽어서 인덱스를 연결지을때 방식이 NESTED LOOPS이다.
• 여기서 인덱스의 설계상 문제라던가 테이블의 설계상 문제는 파악할 수가 없다.

nested loop : 중첩된 조인 방법으로 두개의 테이블을 조인할 때 한건, 한건씩 순차적으로 반복해서 조인하는 방법

Index Unique Scan : 수직적 탐색으로만 데이터를 찾는 스캔 방식입니다. Unique 인덱스를 '=' 조건으로 탐색하는 경우에 동작합니다. Unique 인덱스가 존재하는 컬럼은 중복 값이 존재하지 않습니다. 따라서 동일('=')하다는 조건으로 검색할 때, 데이터를 한 건 찾고 나서는 더 이상 탐색할 필요가 없습니다