8. 친절한 SQL 튜닝

NL조인이란?

인덱스를 이용한 조인방식으로 반복문을 통하여 현재 블록의 조건에 따라 데이터를 읽어 오는 방식.

NL조인 실행계획 제어

SELECT /*+ ordered use_nl(c) */
       e.사원명
      ,c.고객명
      ,c.전화번호
      
  FROM 사원 e
     , 고객 c
  
  WHERE e.입사일자 >= '19960101'
    AND c.관리사원번호 = e.사원번호

위 쿼리문과 같이 ordered 힌트를 통하여 DBMS의 옵티마이저에게 고객 테이블을 NL조인을 이용하도록 지시할 수 있다.

세 개 이상 테이블을 조인할 때는 힌트를 아래처럼 사용한다.

SELECT /*+ ordered use_nl(B) use_nl(C) use_hash(D) */
  FROM A, B, C, D
  WHERE ..........

B, C는 NL조인을 이용하고 D는 HASH 조인을 이용하도록 지시하는 힌트이다.

• ordered 대신 leanging 힌트를 사용할 수도 있다.

올바른 조인 메소드 선택

온라인 트랜잭션 처리(OLTP) 시스템에서 튜닝할 때는 일차적으로 NL 조인부터 고려하는 것이 올바른 순서이다. 성능이 느리다면, NL 조인 튜닝 포인트에 따라 각 단계의 수행 일량을 분석해서 과도한 랜덤 액세스가 발생하는 지점을 우선 파악해야한다. 조인 순서를 변경해서 랜덤 액세스 발생량을 줄일 수 있는지, 더 효과적인 다른 인덱스가 있는지 등을 검토한다. 여러 방안을 검토한 결과 NL 조인으로 결코 좋은 성능을 내기 어렵다고 판단한다면 소트머지 조인이나 해시 조인을 검토한다.

NL 조인 특징 요약

• NL 조인은 랜덤 액세스 조인 방식
• 레코드 하나를 읽으려고 블록을 통째로 읽는 랜덤 액세스 방식은 설령 메모리 버퍼에서 빠르게 읽더라도 비효율이 존재 ==> 대용량 데이터를 읽을 시에 비효율적
• 한 레코드를 순차적으로 읽어서 join 조건절을 읽음 ==> Index를 통하여 부분적으로 읽는 것이 매우 중요

NL 조인 확장 메커니즘

버전이 올라가면서 오라클은 두 가지 기능을 제공하기 시작함

• 테이블 Prefetch
  : 인덱스를 이용해 테이블을 액세스하다가 디스크 I/O가 필요해지면, 이어서 곧 읽게 될 블록까지 미리 읽어서 버퍼캐시에 적재하는 기능
• 배치 I/O 기능
  : 디스크 I/O를 미뤄뒀다가 일정 블록량 만큼 쌓이면 한꺼번에 처리하는 기능
  ==> I/O를 즉각적으로 수행하는 것이 아니기 때문에 데이터의 순서가 바뀔수가 있음.
  이를 해결하기 위해서는 ORDER BY를 명시해주거나 no nlj batching(테이블 Alias) 힌트를 추가해주면 된다.