Oracle 실행 계획 보기. 개발 DB와 운영 DB의 쿼리 속도가 차이나는 이유
문제
동일한 쿼리를 개발 DB와 운영 DB에 실행했는데, 쿼리의 실행 속도가 개발은 10초, 운영은 0.1초 나왔다.
원인을 찾기 위해 개발 DB와 운영 DB에서 실행계획을 살펴보기로 했다.
원인
개발 DB에서 Data Grip으로 실행 계획 보기
개발 DB는 Data Grip이라는 DB프로그램을 사용하는데, 마우스 오른쪽을 클릭하고, Explain Plan이나 Explain Plan (Raw)를 클릭하면 콘솔창에 실행계획이 출력된다.
아래는 Explain Plan (Raw)를 선택했을 때 콘솔창에 나온 결과이다.
모자이크 된 부분은 사용한 인덱스 키가 나온다.
Operation에 Index Fast Full Scan과 Rows가 2449, 2668로 나온 것을 보아 개발 DB에서는 Full Scan이 일어났다.
Data Grip에서는 Execute Plan을 누르고 아래 버튼을 누르면 GUI로도 실행 계획을 볼 수 있다.
운영 DB에서 명령어로 실행계획 보기
운영 DB는 Data Grip을 사용할 수 없고, 실행 계획 GUI 없어서, 직접 명령어로 실행계획을 봐야 했다.
아래 쿼리를 실행하면 PLAN_TABLE이라는 테이블에 쿼리의 실행계획이 저장된다.
EXPLAIN PLAN
SET STATEMENT_ID = '저장될 실행계획 Data에 부여할 ID'
FOR
실행할 Select 쿼리
-- Example
EXPLAIN PLAN
SET STATEMENT_ID = 'REAL_PLAN_TEST'
FOR
SELECT * FROM TABLE_A
INNER JOIN TALBE_B ON TABLE_B.A_ID = TABLE_A.ID
아래 쿼리를 실행하면 콘솔 창에 실행 계획이 출력된다.
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','저장될 실행계획 Data에 부여할 ID','ALL'))
-- Example
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE','REAL_PLAN_TEST','ALL'))
운영 DB는 Operaion에 Full Scan이 없는 것을 보아, 쿼리 실행 시 Full Scan이 일어나지 않았다.
실행계획을 분석함으로써 개발 DB는 Full Scan이 일어나서 10초가 걸리고, 운영 DB는 그렇지 않아 0.1초가 걸린 것을 알 수 있었다.
왜 개발 DB는 Full Scan이 일어났을까?
개발 DB에서 Full Scan은 총 2개의 테이블에서 일어났다. Full Scan 가능성은 3가지로 보았다.
- 운영 DB에 적용한 Index가 개발 DB에는 적용되어 있지 않다.
- 개발 DB 테이블의 Index를 살펴보니, 운영 DB에는 있지만 개발 DB에는 없는 것을 확인해 적용해주었다. 하지만 문제가 해결되지 않았다.
- 운영 DB의 데이터 양과 개발 DB의 데이터 양이 다르다.
- 데이터 양을 살펴보았다. Full Scan이 일어난 테이블은 운영 DB의 경우 데이터가 5배 정도 개발 DB보다 많았다.
- 개발 DB에 데이터 양이 현저히 적어서 Full Scan이 일어났을 수 있단 생각이 들었다. 하지만 개발 DB에 더미 데이터를 넣어서 확인할 수 없어서 정확히 체크는 하지 못했다.
- 쿼리가 잘못되거나, 데이터가 어딘가 잘못되었다.
- 문제가 되었던 쿼리의 경우, 테이블끼리 Join을 On절이 아닌 Where절에서 하고 있었다.
- Where에서 Join하는 것을 On 절로 수정하였다.
해결
테이블 Join을 Where절에서 On절에서 하는 것으로 변경하니 개발 DB와 운영 DB의 실행계획이 동일해졌다.