[Oracle] Select 튜닝 순서

1. 적절한 인덱스를 사용하여 Block I/O를 최소화 한다.

인덱스의 사용이 상황에 따라서 좋거나 나쁠 수 있지만, 조인이 없는 경우는 적절한 인덱스를 사용하는 것만으로도 상당한 효과를 볼 수 있습니다. Driving 집합의 Block I/O를 줄이기 위하여 최적화된 인덱스가 없다면 생성하고, 있다면 그것을 사용하면 된다. 최적의 Access Path를 만드는 것이 좋다.

 

2. 조인 방법과 조인 순서를 최적화 한다.

조인수가 적은 SQL에 Hash Join이나 Sort Merge Join이 발견되면 Nested Loop Join으로 변경하는 것이 더 유리한지 검토해야 한다.

Nested Loop 조인에서 가장 중요한 것은 조인 순서이다. From절에 테이블(집합)이 두 개라면 후행집합의 관점에서는 적절한 인덱스만 존재한다면 그것으로 충분하다. 만약 From절에 테이블(집합)이 세 개 이상이라면 조인순서를 변경할 수 있는지에 대한 두 가지 원리를 사용하면 좋다. 아무리 조인할 집합이 많다고 하더라도 이 두 가지의 원리는 동일하게 적용될 수 있다. 두 가지 원리를 이용할 때 필요하다면 Leading 힌트를 사용해야 한다.

첫번째, 후행집합에 적절한 인덱스가 없는 경우에 조인순서를 바꾸면, 최적의 인덱스를 사용할 수 있는 경우가 많다. 예컨대, 튜닝전의 조인순서가 A → B → C라고 하면, 중간 집합인 B에 적절한 인덱스가 없고 오히려 C에 적절한 인덱스가 존재하는 경우가 있다. 이럴 때는 B에 인덱스를 무작정 생성하지 말고, 조인순서를 A → C → B로 바꿀 수 있는지, 바꾸는 것이 더 효율적인지 검증하면 좋다. 조인 순서만 바꿔주어도 일량이 획기적으로 줄어드는 경우가 많다. 만약 조인순서를 바꿀 수 없거나, C를 중간집합으로 하는 것이 비효율적이라면, B를 중간집합으로 유지하고 적절한 인덱스를 사용해야 한다.

두번째, 조인되는 집합 중 특정 인덱스에서 Block I/O가 증가하는 경우에 조인순서의 변경을 검토하면 된다. 예를 들어, 튜닝전에 조인순서가 A → B → C라고 하고, 집합 B에서 Block I/O량이 증가하면 A → C → B로 바꾸면 일량이 줄어드는 경우가 많다. C를 먼저 조인(Filter)하여 선행집합(B의 입장에서는 C가 선행이다)의 건수를 줄이고 B에 조인하면 성능이 향상된다.

 

3. Table Access(Random Access)를 최소화하라.

1번과 2번으로 최적화 했다면 Random Access도 자동으로 많이 줄어들었을 것이다. 하지만 여전히 성능이 만족스럽지 못하다면 Random Access 횟수를 줄일 수 있는지 확인 해 봐야한다.

Random Access의 부하를 줄이는 방법은 네가지이다.

1. 테이블의 종류를 변경하는 방법입니다. IOT나 클러스터를 이용하면 Clustering Factor가 극단적으로 좋아집니다. 또한 파티션을 이용하면 같은 범위의 데이터를 밀집시킬 수 있다.
2. 효율적인 인덱스를 사용하거나 조인방법과 순서를 조정하여 Table Access를 최소화하는 방법입니다. 이 방법은 1번과 2번에서 이미 설명되었다.
3. 인덱스에 칼럼을 추가하여 Table Access를 방지하는 방법입니다. 예를 들어 Select절의 특정 칼럼 때문에 테이블이 액세스 된다면, 인덱스의 마지막에 그 컬럼을 추가하면 된다.
4. 인덱스만 액세스하고 테이블로의 액세스는 모든 조인을 끝내고 마지막에 시도하여 Random Access의 횟수를 줄이는 방법이다.

 

4. Sort나 Hash 작업을 최소화한다.

1~3번을 모두 진행하였어도 Order by나 Groub by에 의해 성능히 저하될 수 있다. 특히 조회문이 길어질 경우 sort는 치명적이다.

인덱스가 sort 되어 있다는 특성을 이용하면 order by 작업을 대신할 수 있다. Group By도 sort가 발생하는데 group by 단위와 인덱스의 컬럼이 동일하다면 sort는 발생하지 않는다. 최적의 인덱스를 사용하면 Access Path를 개선하는 효과뿐만 아니라 Sort의 부하도 없어진다.

 

5. 한 블록은 한 번만 Scan하고 끝낸다.

같은 데이터를 반복적으로 Scan하는 SQL이 의외로 많이 존재한다. 대표적인 경우로 Union All로 분리되었지만 실제로는 그럴 필요가 없는 경우가 있다.

 

6. 페이징 처리

부분범위 처리를 해야한다. 조회화면이라면 몇 십만건이 되는 결과를 모두 볼수 없게된다. 따라서 볼 수 있는 단위로 끊어서 출력하게 된다면 부하를 감소시킬 수 있게 된다.

 

7. SQL을 검증하라

튜닝을 하였어도 답이 틀리다면 하지 못하니만 못하게 된다. 따라서 튜닝 후에 답이 옳은지 항상 검증하는게 좋다.

 

생각날 때마다 보기 편하게 정리하여 올렸습니다. 원본출처는 Science of Database 블로그 SQL 튜닝방법론 이니 자세한 내용이 궁금하시면 참고 하시면 좋을 것 같습니다.