[오라클] SQL 튜닝 시작하기

SQL 튜닝은 "SQL + 튜닝"입니다. 즉, SQL 튜닝이란 튜닝 대상이 되는 SQL을 이해하고, SQL이 가진 정보(테이블/인덱스/컬럼의 정보 및 업무적 성격 등)를 치밀하게 분석하여 얻어지는 결과라고 생각합니다. SQL 튜닝을 시작하기 위해서는 SQL에 대한 이해가 선행되어야 한다고 생각합니다. 왜냐하면, SQL의 작성형태에 따라 다양한 성능 문제가 발생되기 때문입니다.

 

SQL 튜닝의 시작은 SQL의 의미(작성 의도)를 제대로 파악하는 것입니다. SQL의 의미를 정확히 파악하지 못한다면, 원본 SQL에서 추출하고자 했던 결과 집합이 아닌 다른 집합을 추출하게 될지도 모릅니다. 이러한 개선안은 개선안이라고 할 수 없습니다. 즉, 원본 SQL의 작성 의도를 제대로 파악하지 않고, 단순히 I/O 발생량을 줄인다거나 수행시간을 단축하기 위해 힌트를 남발하는 것은 상당히 위험한 일입니다. 그러므로 SQL의 의미를 파악하는 것이 성능 문제를 유발하는 SQL에 대한 개선의 시작이라 말할 수 있습니다.

 

SELECT *
FROM 	(
	SELECT /*+ INDEX_DESC(A IDX_MOX_SENDDATE) */
		a.*,
		ROWNUM as rnum
	FROM   tbs_mbox a
	WHERE userid = :b1
	  AND status = :b2
	  AND ROWNUM <= :b3
	)
WHERE rnum >= :b4;

SELECT STATEMENT - CHOOSE- COST ESTIMATE:3
VIEW
	COUNT STOPKEY
	TABLE ACCESS BY INDEX ROWID :imsi.tbs_mbox
	INDEX RANGE SCAN DESCENDING :imsi.idx_mbox_senddate

 

해당 SQL은 메일 프로그램에서 사용되며, 동시 간대(특히, 업무 시작 시간, 09:00)에 다수의 다용자가 사용하는 특성이 있습니다. 뿐만 아니라, 데이터를 추출하는 과정에서 발생하는 경합으로 인해 DB 서버 전반적인 성능 저하를 유발하는 악성 SQL입니다.

 

해당 SQL은 INDEX_DESC 힌트와 ROWNUM 조건으로 인해 COUNT STOPKEY의 실행계획이 수립되었습니다. 따라서 전체 데이터를 모두 처리하지 않고, RONUM, 조건에 해당하는 일부의 데이터만 처리할 것으로 예상됩니다. 즉, 실행계획 상으로는 SQL의 문제점을 찾기가 쉽지 않습니다. 따라서 인덱스 구성과 힌트 사용이 적절한지에 대한 검토가 필요합니다.

 

INDEX_NAME          COLUMN LIST
-----------------   -----------------
idx_mbox_status     userid, status
idx_mbox_senddate   userid, senddate

 

인덱스 구성만 보면, 해당 SQL은 IDX_MBOX_SENDDATE 인덱스보다는 USER_ID, STATUS 컬럼으로 구성된 IDX_MBOX_STATUS 인덱스를 사용하는 것이 유리해 보입니다.

 

그런데 조건 절에 적합한 인덱스 구성만 확인하고, 해당 SQL의 의미를 파악하지 않은 채 성능 개선을 하려고 한다면, 잘못된 개선안을 도출할 가능성이 높습니다.

 

INDEX_DESC 힌트의 인덱스 명만을 변경하는 것으로 결론을 도출한다면, 아주 치명적인 실수를 저지를 수 있습니다. 왜냐하면, SQL의 작성 의도와는 전혀 다른 데이터가 추출될 수도 있기 때문입니다. 물론, 경우에 따라 USERID, STATUS로 구성된 인덱스를 이용하는 것이 올바른 방법일 수도 있습니다. 그러나 이와 같은 개선안은 "USER_ID, SENDDATE 컬럼으로 정렬이 필요없다"는 전제 조건을 반족해야 합니다. 이러한 업무적인 내용은 개발 담당자 또는 업무 운영자에게 문의할 필요가 있으며, 그렇지 못한 경우라면 SQL 자체에서 작성 의도를 찾아봐야 합니다. 그렇다면, 해당 SQL의 작성 의도를 파악해보겠습니다.

 

- ROWNUM을 사용하였으므로 PAGINATION QUERY일 가능성이 높다.

- INDEX_DESC 힌트는 ORDER BY DESC를 대체하려는 것으로 보인다.

- 인덱스 컬럼 정보와 힌트를 고려해 보았을 때, 의미사응로는 "ORDER BY USER_ID DESC, SEND_DATE DESC"가 내포된 것으로 보인다.

 

앞에서 파악한 SQL의 의미를 통해, 추출하고자 하는 데이ㅏ터는 조건에 만족하는 "최근 발송된" ROWNUM <= :B3개의 메일 중에서 RNUM >= :B4인 메일이라는 것을 알 수 있습니다. 즉, INDEX_DESC 힌트에 내포된 의미를 풀어보면 다음과 같습니다.

 

SELECT x.*
FROM (
	SELECT a.*,
		ROWNUM AS RNUM
	FROM (
		SELECT a.*
		FROM tbs_mbox a
		WHERE userid = :b1
		  AND status = :b2
		ORDER BY a.userid DESC,
			a.senddate DESC
	      ) a
	WHERE ROWNUM <= :b3
     ) x
WHERE
	x.rnum >= :b4

 

재작성한 SQL을 보면, 원본 SQL에서 INDEX_DESC 힌트의 인덱스 명만을 변경하는 것은 올바른 튜닝 방법이 아니라는 것을 알 수 있습니다. 왜냐하면, INDEX_DESC 힌트에 사용된 IDX_MBOX_SENDDATE 인덱스는 조건에 대한 처리뿐만 아니라 ORDER BY DESC  역할도 수행하기 때문입니다. 따라서 SQL의 의미를 파악한 후에 도출될 수 있는 개선안 중의 하나는 IDX_MBOX_SENDDATE 인덱스에 STATUS 컬럼을 추가하여 재 생성하는 것입니다. 이와 같이 SQL 튜닝을 위해서 가장 필요한 것은 SQL을 정확하게 이해하는 것입니다. SQL의 의미를 파악해야만 올바른 SQL 개선안이 도출될 뿐만 아니라, 때로는 SQL의 의미 파악 자체가 SQL 개선으로 이어지는 경우도 있기 때문입니다.

 

개발자나 DBA 들 중의 일부는 SQL 튜닝의 세계에 뛰어드는 것을 매우 어려워합니다. 왜냐하면, "SQL 튜닝"이란 용어 자체가 친근하지 않을 뿐 아니라 SQL 튜닝의 시작점을 정하기도 쉽지 않기 때문입니다. SQL의 의미를 해석하는 것으로부터, 이미 SQL 튜닝이 시작됩니다.