[Oracle] 실행 계획(Execution plan)과 hint사용의 필요성

스프링 개발을 하면서 이제 페이징 처리 단계에 다다랐는데,

페이징 처리는 SQL쿼리 작성에 대해 신중히 생각해볼 필요성이 있다.

 

일반적으로 우리는 게시물 양이 많은 경우 페이지를 나누게 된다. 그리고 화면에 보여주는 과정에서 최신 등록 순으로 가져오는 것을 선호한다. 그럼 먼저 전체 데이터를 역순으로 정렬해서 조회해야 하는데, 간단히 SQL쿼리를

select * from tbl_board order by bno desc;

이런식으로 작성하면 우리가 원하는 결과를 보여줄 수는 있을 것이다.

 

그러나 order by의 경우 데이터의 양이 많을 수록 많은 리소스를 소모한다.

사용자가 정렬이 필요한 데이터를 요청하게 된다면 정렬된 결과를 볼 때까지 오랜 시간을 기다려야 하고, 특히 여러명의 사용자가 동시에 요청을 하게 되었을 때 연결 가능한 커넥션의 개수가 점점 줄어서 서비스가 멈추는 상황을 초래할 수 있다.

 

그러면 이 경우에서는 order by 작업을 가급적 지양하는 것이 좋다는 것을 알았는데, 이를 다른 방식으로 어떻게 처리해야할까?

이 방법에 대해 고민해보자🤷‍♀️

 

---

 

실행 계획

오라클의 페이징 처리를 제대로 이해하기 위해서는 실행계획(execution plan)에 대해 알아야 한다.

실행계획이란 'SQL을 데이터베이스에서 어떻게 처리할 것인가?'에 대한 것이다. 즉, SQL이 실행되는데 필요한 cost를 계산하고 그 cost를 통해 어떤 방식으로 실행하는 것이 가장 좋은지를 판단한다. SQL이 데이터베이스에 전달되면 데이터베이스는 여러 단계를 거쳐서 해당 SQL을 어떤 순서와 방식으로 처리할 것인지 계획을 세우게 된다.

 

데이터베이스에서 전달된 SQL문은 파싱 > 최적화 > 실행 과정을 거쳐서 처리된다.

SQL파싱 : SQL구문에 오류가 있는지, SQL을 실행해야 하는 대상 객체가 존재하는지를 검사

SQL최적화 : SQL이 실행되는데 필요한 cost를 계산, 실행 계획 작성

SQL실행 : 실행 계획을 바탕으로 메모리상에서 데이터를 읽거나 물리적인 공간에서 데이터를 로딩하는 등의 작업을 함

 

우리가 이번에 초점을 맞출 부분은 sql최적화 과정 중 실행 계획에 대한 내용이다.

 

실행 계획에 대한 이해를 위해 아래의 작업으로 테스트를 진행해보겠다.

먼저 조회 성능의 차이를 확인하기 위해서는 대량의 데이터가 필요하기 때문에 재귀복사를 통해서 데이터의 개수를 쭉쭉 늘린다.

insert info tbl_board (bno, title, content, writer)
(select seq_board.nextval, title, content, writer from tbl_board);

데이터 입력 후에는 커밋을 실행해서 제대로 반영이 되도록 한다. 

 

 

조회 성능 테스트를 위해 bno에 1을 더한 값을 역순으로 정렬하는 쿼리를 실행해보자.

select * from tbl_board order by bno+1 desc;

 

처음 실행해보면 결과가 나오기까지 2.481초가 걸렸다. 

반복적으로 몇 번 실행하면 데이터베이스가 메모리상에 보관하는 데이터를 가져오는 상황이 되어 걸리는 시간이 감소한다.

 

방금의 쿼리에서 실행계획을 살펴보면 

TBL_BOARD를 FULL로 조사했고 바깥쪽으로 가면 SORT가 일어난 것을 확인할 수 있다.

음? 뭔소리지? 싶지만 일단 다음 과정을 살펴보자.

 

아까 쿼리에서 왜 bno가 아니라 bno+1로 order by했지? 라고 생각했을텐데,

다시 +1를 빼고 bno로만 정렬작업을 진행하면 다음과 같이 0.007초밖에 안걸리고 결과가 뜬다.

단순히 +1을 하는 쉬운 연산과정임에도 불구하고 성능에서는 차이가 발생하게 된다.

select * from tbl_board order by bno desc;

 

기존의 SQL은 TBL_BOARD 테이블 전체를 스캔했지만, 이 쿼리의 실행 계획을 살펴보면 TBL_BOARD를 전체 스캔한 것이 아니라 PK값을 이용해서 동작하는 것을 확인할 수 있다. SORT작업이 없어지고 INDEX를 이용하였다. 

 

+1의 차이는 인덱스의 유무에 대해 테스트하기 위함이었다. 그럼 눈으로 확인했듯이, PK값이 있으면 자동으로 인덱스 조회를 한다는 것을 알았다.

인덱스는 이미 정렬된 구조로 존재하기 때문에 별도로 SORT작업이 필요가 없고, 그래서 order by를 사용하는 것보다 훨씬 효율적이다. 특히 대량의 데이터를 처리해야 하는 경우에 그 필요성이 부각된다.

 

위의 이미지에서 TABLE ACCESS에 BY INDEX ROWID라는 옵션을 확인할 수 있는데, ROWID는 인덱스와 실제 테이블을 연결하는 고리이다. ROWID는 데이터베이스 내의 주소값과 같아서 아래의 이미지처럼 인덱스가 테이블 데이터의 주소값을 저장하고 있는 것이다.

 

 

인덱스가 가지고 있는 ROWID를 이용하였을 때 훨씬 조회 속도가 빠르기 때문에 가능하면 SORT를 안하는 방식으로 조회 작업을 하는 것이 좋다는 것을 테스트를 통해 확인해보았다. 

그러면 실행계획을 SORT를 안하는 방식으로 개발자가 원하는 의도로 변경할 수 있게 작업을 하고싶다.는 말이고,

이것을 가능하게 하는 것이 oracle의 hint이다.

 

---

 

Hint

Oracle은 select문을 전달할 때 힌트라는 것을 사용할 수 있다.

힌트는 지시구문이며 sql과 함께 사용한다. 그래서 개발자가 원하는 의도대로 실행 계획을 변경할 수 있게 하여 직접 최적의 실행 경로를 작성해 주는 것이다.

(힌트 구문은 에러가 나도 전혀 sql실행에 영향을 주지 않는다. 무시해버림)

 

👇 자세한 힌트 구문은 아래의 블로그에서 자세히 설명해주셨다.

https://devuna.tistory.com/35

[SQL 튜닝] 오라클 힌트(hint)의 개념/ 사용법/자주쓰이는힌트 정리

 

 

사용법 : /*+ 힌트내용 */

 

select * from tbl_board order by bno desc;

다음의 쿼리를 order by를 사용하지 않도록 힌트를 사용한 쿼리로 변경하게 되면

select /*+ INDEX_DESC (tbl_board tbl_board_pk)*/ * from tbl_board;

 

위의 쿼리와 같다. 둘은 동일한 결과를 반환하지만 위의 쿼리는 order by를 사용하였고 아래 쿼리는 인덱스를 사용하였기 때문에 조회 성능에 차이가 발생하게 된다.

이처럼 같은 결과를 반환하는 쿼리이더라도 더 좋은 성능을 기대할 수 있게 최적의 경로를 설정해주는 것을 SQL튜닝이라고 한다.