SQL 튜닝이 됬는지는 실행시간으로 비교하는 것이 아니다.

오늘 사내 지라를 보다가 다른 팀원이 처리한 데이터 조회가 오래걸린다는 사용자 문의를 보고 포스팅합니다.

 

이슈

해당 지라는 담당자가 SQL과 Function, Table DDL 정보를 넘겨 튜닝을 했고, 그 결과는 아래와 같다고 기재되어있었습니다.

	AS-IS	TO-BE
실행시간	5sec	0.5sec

 

해당 표를 봤을 때 튜닝 이후에 속도가 무려 10배나 빨라졌지만, 운영에 반영되었을 때 해당 문제가 해결되지 않을 수 있습니다.

 

 

무엇이 문제인가?

튜닝이 되었는지는 이전 쿼리와 튜닝 쿼리의 실행시간으로 비교하는 것이 아닙니다.

SQL 튜닝을 하면 실행시간은 빨라져야하는것아닌가? 라고 생각하는게 결론적으로 맞는 얘기이지만 비교하는 대상이 '시간'이 되면 안된다는 뜻입니다.

 

현재 사내 DB인 Oracle을 기준으로(다른 DB도 동작방식은 비슷하다고 알고있습니다.) SQL을 실행하면 하드디스크에 있는 데이터 파일을 DB 버퍼캐시(메모리)에 올린 뒤에 사용자에게 데이터를 반환합니다.

 

 

 

그리고 이후 동일한 데이터를 요청하는 SQL이 온다면 캐싱되어있는 데이터를 전달하기 때문에 만약 비교를 이전 쿼리 실행 이후 튜닝 쿼리를 실행한 시간을 비교한다면 실제로 튜닝이 잘 되지 않았음에도 불구하고 디스크에 있는 데이터가 아닌 메모리에 있는 데이터를 읽어오기 때문에 실행시간이 빨라질 수 있다는 점입니다.

 

그럼 어떤걸 비교해야하는가 ?

SQL 튜닝은 모든 데이터를 물리적으로(하드디스크) 읽어온다고 가정하고 전체적인 일량이 줄어드는 것을 확인해야합니다.

이는 실행계획을 통해서 확인할 수 있는데 가급적 예상 실행계획이 아닌 실제로 쿼리를 실행하고, 해당 쿼리의 실행계획을 통해서 전체적인 일량이 줄었는지 확인해야합니다.

 

Oracle에서는 아래와 같은 도구를 통해서 확인할 수 있고, 쿼리를 통해 가장 확인하기 쉬운 것은 1번 입니다.

 

1. DBMS_XPLAN.DISPLAY_CURSOR

SELECT /*+ GATHER_PLAN_STATISTICS */
       *
FROM employees
WHERE employee_id = 100;

SELECT *
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL, NULL, 'ALLSTATS LAST')); 
-- 해당 함수의 파라미터를 NULL, NULL로 넣는 것은 가장 최근에 실행된 쿼리에 대한 실행계획을 호출합니다

2. AUTOTRACE

3. SQL Trace