튜닝수업 (20161126)

SQL> set autot on
SQL> select * from scott.emp;

위 조회결과가 실행되면 중간에 실행계획에 대하여 아래처럼 나옴.

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=14 Bytes=12
          18)

   1    0   TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Bytes
          =1218)

   
set autot 옵션에 아래 선택하여 조회결과를 조정이 가능
 1-1 on 
 1-2 trace
 2-1 exp
 2-2 stat
 
 - HINT (Page.138)
 옵티마이저에게 처리방법/순서에 대해 요청 넣을때
 꼭 SELECT/INERT/UPDATE 키워드 바로 뒤에 와야댐.
 오타검증이 안되므로 반드시 오타확인(아니면 무시당함)
 말도 안되는 내용을 적으면 무시
 조인방법/순서, 인덱스컬럼 등 명시
 EX)
 SELECT /*+ 힌트 */
 FROM TABLE1
 WHERE COL1 IN ( SELECT /*+ 힌트 */
     FROM ~
     WHERE ~ );

-----------------------------------------------------------------------------------------
chapter1. 인덱스를 활용한 성능개선

1. 대용량DB 조회에 풀스캔으로 조회시 조정이 필요
2. 인덱스가 필요
 2-1)인덱스 컬럼과 ROWID를 추출
 2-2)추출된 데이터 정렬
 2-3)B-TREE 구조로 관리하면서 DISK에 저장
 2-4)실행계획에 보면 INDEX RANGE SCAN 이라고 보임
3. 단점
 - DISK 사용량 증가
 - 테이블에 신규 데이터가 생성되면 인덱스정보 갱신
 - 인덱스는 SQL문형태, 연산자에 따라 사용 못하는 경우도 존재
4. 인덱스가 걸려있는 컬럼은 별도로 ORDER BY 가 없어도 해당 컬럼으로 정렬한다.
5. 복합인덱스는 먼저 입력된 인덱스로 1차 정렬
6. LIKE 연산자는 문자만(숫자는 FULL SCAN됨)
7. 조건선행컬럼이 INDEX 컬럼인지가 중요
8. **컬럼 조건 조회시 대상값이 문자인지,숫자인지에 따라서 반드시 문자인경우 따옴표를 붙이고, 숫자인경우는 그냥 쓰도록 습관화
9. INDEX를 탈 수 있도록 쿼리문 변경이 필수
10. BETWEEN 보다 IN 문장으로 변경이 필요(복합인덱스 인경우에만)
   -> 조회되는 값 수를 조금더 줄여서 시간 단축
  

인덱스 컬럼 조건사용시
 A. =, IN : POINT(점) 조건
 
 B. >,<, LIKE, BETWEEN : LINE(선분) 조건

실행계획 인덱스 관련부분
 1. INDEX UNIQUE SCAN : PK컬럼, UNIQUE 컬럼을 = 조건 사용시에만 나옴
 2. INDEX RANGE SCAN : 인덱스 정보 2개이상 SCAN시 (하지만 주의 필요)
 3. INDEX FULL SCAN (MIN,MAX) : MIN/MAX값 인덱스 정보 1개만 참조
 4. (안좋음)INDEX FAST FULL SCAN  : SQL변경 또는 다른 인덱스 생성유도해서 INDEX RANGE SCAN으로 변경하도록
    (안좋음)INDEX FULL SCAN       : SQL변경 또는 다른 인덱스 생성유도해서 INDEX RANGE SCAN으로 변경하도록 

변환기본 우선순위
 - 문자 -> 날짜
 - 문자 -> 숫자
 예외) LIKE 비교시엔 문자타입으로 변형

인덱스 사용 X
 - !=, <>
 - LIKE '%문자'
 - NOT IN
 
-- 다수의 숫자 IN으로 넣고싶을때 참고
SELECT LEVEL
FROM DUAL
CONNECT BY LEVEL < 11;

여러 TABLE 이용 작업 수행시
 1. 조인
 2. WHERE절 서브쿼리
 3. SELECT절 서브쿼리
 4. 사용자 정의함수 : 복잡합 업무 적용된 경우에만 함수 이용
 5. 집합연산자(INTERSECT,MINUS,UNION) : 대용량 TABLE처리시 서브쿼리 OR 조인문장으로 변경해야함.
 
 A. 조인 / WHERE절 서브쿼리 이용 : DB에서 자동변형
 B. 조인 / SELECT절 서브쿼리 이용 : DB자동변경 대상 아님
         사용자가 선택하여 SQL문 작성
         
조인방법(*)                / 순위 변경 검토
 - netsted loop               - 힌트로 순서 / 방법 변경함
 - sort merge      - ordered -> from 절에 지정한 순서
 - hash        - leading(emp dept)
         - leading( dept emp)
        
 - nested loop
  두 테이블의 비교컬럼에 인덱스 1개라도 있어야 nested loop으로 처리가능
  한개 table 자료 먼저 읽어서 조인컬럼에 해당되는 반대편 table자료를 하나씩 찾아가서 연결함
  인덱스 없으면 full table scan 해야함.
  반드시 인덱스 있어야함.
   -> 반복횟수는 먼저 읽는 table에 따라서 선정됨
  데이터양이 작은거부터 테이블 읽도록 (driving table)
  많은양의 데이터라도 실제 소량의 데이터만 볼경우에는 괜찮음
  양쪽 테이블이 index가 있어도 시스템에서는 어떤게 적은 테이블인지 모르므로, hint로 그걸 알려줘야함.
  튜닝포인트 : 어느 테이블 먼저 읽어서처리하는지 선정이 중요
               적절한 인덱스 사용중인지 검토
 
 - sort merge
  각각 테이블 스스로 정렬
  정렬한 값을  서로 정렬한 값과 비교
  정렬시에 속도가 많이 느림
  두 테이블의 건수차이가 많이나는게 안좋음
  **각각의 프로세스로 병렬작업 가능시 효과 좋음
  튜닝포인트 : 정렬 작업 빨리 끝나도록 SORT_AREA_SIZE 조정
 
 - hash 조인방식
   한개 테이블 선정해서 MEMORY에 테이블 자료 저장
   (= HASH 파티션 구축)
   값이 작은 테이블이 선정이 되도록
   대용량 조인시에도 적절한 처리시간 보장 가능
   CPU COST발생
   조인시 = 기준으로만 조인가능
   튜닝포인트 : Build Table(memory에 저장할 table) 선정이 중요
  
    WHERE -> GROUP BY -> HAVING -> ORDER BY