오라클 쿼리 튜닝 관련 질문드립니다.

Question

안녕하세요 &#x1f596;쿼리 튜닝 문제 하나 질문드립니다.스칼라 서브쿼리 튜닝 문제이구요.SELECT ... ,(SELECT CASE WHEN A.일할계산여부 &#61; 'Y' THEN NVL(A.총청구건수, 0) - NVL(A.청구횟수, 0) ELSE B.할부개월수 - NVL(A.청구횟수, 0) END FROM 서비스별할부 A, 할부계획 B WHERE A.서비스계약번호 &#61; MV.서비스계약번호 AND A.할부상태코드 &#61; 'XR' -- AND B.할부계획ID(&#43;) &#61; A.할부계획ID (튜닝) AND B.할부계획ID(&#43;) &#61; (CASE WHEN A.일할계산여부 &#61; 'Y' THEN NULL ELSE A.할부계획ID END) -- (튜닝) AND ROWNUM <&#61; 1) AS 청구횟수, ... FROM (...) MV WHERE ...메인쿼리 집합(MV)의 건수 37,243번 만큼 서브쿼리가 실행되었습니다.튜닝 전 서비스별할부_X1와 서비스별할부 테이블의 Starts가 37,243이고, 할부계획_PK와 할부계획 테이블의 Starts가 28,953이었습니다.서브쿼리의 CASE문에 의해서 서비스별할부 테이블과 할부계획 테이블과의 조인은 줄어들 것으로 생각합니다.제가 궁금한 것을 튜닝을 했을 때 서비스별할부 관련 인덱스의 스캔이 줄어드느냐 입니다.메인 쿼리와 테이블 A의 조인은 동일하게 이루어질 것으로 생각되는데요.좋은 답변 기다리겠습니다.

crnmst · Accepted Answer

안녕하세요!질문해주신 스칼라 서브쿼리 튜닝과 관련된 사항을 상세히 살펴보겠습니다.현재의 쿼리는 MV 집합의 각 행마다 서브쿼리를 실행하여 계산을 수행하고 있습니다. 이 서브쿼리는 서비스별할부(A) 테이블과 할부계획(B) 테이블을 조인하고 있습니다. 튜닝의 목표는 서브쿼리가 실행되는 횟수를 줄이고, 필요 없는 인덱스 스캔을 최소화하는 것입니다.튜닝 전:sql코드 복사SELECT ... ,(SELECT CASE WHEN A.일할계산여부 = 'Y' THEN NVL(A.총청구건수, 0) - NVL(A.청구횟수, 0) ELSE B.할부개월수 - NVL(A.청구횟수, 0) END FROM 서비스별할부 A, 할부계획 B WHERE A.서비스계약번호 = MV.서비스계약번호 AND A.할부상태코드 = 'XR' AND B.할부계획ID(+) = A.할부계획ID AND ROWNUM <= 1) AS 청구횟수, ... FROM (...) MV WHERE ...튜닝 후:sql코드 복사SELECT ... ,(SELECT CASE WHEN A.일할계산여부 = 'Y' THEN NVL(A.총청구건수, 0) - NVL(A.청구횟수, 0) ELSE B.할부개월수 - NVL(A.청구횟수, 0) END FROM 서비스별할부 A, 할부계획 B WHERE A.서비스계약번호 = MV.서비스계약번호 AND A.할부상태코드 = 'XR' AND B.할부계획ID(+) = (CASE WHEN A.일할계산여부 = 'Y' THEN NULL ELSE A.할부계획ID END) AND ROWNUM <= 1) AS 청구횟수, ... FROM (...) MV WHERE ...튜닝의 핵심 사항조인의 조건 변경:튜닝 전에는 AND B.할부계획ID(+) = A.할부계획ID 조건이 사용되었습니다.튜닝 후에는 AND B.할부계획ID(+) = (CASE WHEN A.일할계산여부 = 'Y' THEN NULL ELSE A.할부계획ID END) 조건으로 변경되었습니다. 이는 A.일할계산여부가 'Y'일 때 조인 조건을 무효화하여 불필요한 조인을 피하려는 것입니다.성능 개선 기대 효과불필요한 조인 제거:A.일할계산여부가 'Y'인 경우, B.할부계획ID가 NULL로 설정되어 B 테이블과의 조인이 발생하지 않습니다. 이는 B 테이블에 대한 불필요한 스캔을 줄이는 효과를 가져올 수 있습니다.인덱스 스캔 감소:A.일할계산여부가 'Y'인 경우, B 테이블에 대한 인덱스 스캔이 줄어듭니다. 이는 전체 쿼리 수행 시간에 긍정적인 영향을 미칠 수 있습니다.인덱스 스캔 감소 여부메인 쿼리와 A 테이블의 조인은 동일하게 유지되기 때문에 A 테이블의 스캔 횟수는 크게 변하지 않을 수 있습니다. 하지만 서브쿼리 내에서 B 테이블과의 조인 조건이 변경되었기 때문에, B 테이블에 대한 인덱스 스캔 횟수는 감소할 수 있습니다.이를 확인하기 위해서는 실제 실행 계획(EXPLAIN PLAN)과 수행 통계(V$SQLSTATS)를 비교해 보는 것이 좋습니다. 튜닝 전후의 실행 계획을 확인하여 인덱스 스캔 횟수와 각 단계별 수행 시간을 비교하면 성능 개선 여부를 명확하게 알 수 있습니다.추가적인 튜닝 방법서브쿼리 대신 조인 사용: 서브쿼리를 메인 쿼리에 조인으로 바꾸는 것도 한 가지 방법입니다. 이 방식은 종종 성능을 개선할 수 있습니다.인덱스 최적화: 서비스별할부와 할부계획 테이블에 적절한 인덱스를 추가하여 조인 성능을 최적화할 수 있습니다.아래는 서브쿼리를 조인으로 변경한 예시입니다:sql코드 복사SELECT MV.*, CASE WHEN A.일할계산여부 = 'Y' THEN NVL(A.총청구건수, 0) - NVL(A.청구횟수, 0) ELSE B.할부개월수 - NVL(A.청구횟수, 0) END AS 청구횟수 FROM (...) MV LEFT JOIN 서비스별할부 A ON A.서비스계약번호 = MV.서비스계약번호 AND A.할부상태코드 = 'XR' LEFT JOIN 할부계획 B ON B.할부계획ID = CASE WHEN A.일할계산여부 = 'Y' THEN NULL ELSE A.할부계획ID END WHERE ...이와 같이 서브쿼리를 조인으로 변경하면 Oracle이 조인 전략을 최적화하는 데 도움이 될 수 있습니다.도움이 되셨기를 바랍니다! 추가 질문이 있으시면 언제든지 문의해주세요.

Clickin · Answer

조인이 줄어들 리가 없을텐데요. 조인 연산에 조건절 더 걸어놓으셨으니 조인에 드는 비용만 더 커집니다.filter 단계에서 일할계산여부를 전부 확인해야하니까요저라면 어차피 서브쿼리 쓰기로 했으니 서브쿼리로 끝장을 보는 게 좋다고 보네요SELECT ... ,(SELECT CASE WHEN A.일할계산여부 = 'Y' THEN NVL(A.총청구건수, 0) - NVL(A.청구횟수, 0) ELSE (SELECT B.할부개월수 FROM 할부계획 B WHERE B.할부계획ID = A.할부계획ID AND rownum <= 1 -- prevent occassional multiple row ) - NVL(A.청구횟수, 0) END FROM 서비스별할부 A WHERE A.서비스계약번호 = MV.서비스계약번호 AND A.할부상태코드 = 'XR' AND rownum <= 1) AS 청구횟수, ... FROM (...) MV WHERE ...

MSSQL 튜너 · Answer

쿼리가 뭘 할려고 하는지 모르겠네요~모델링 부터 신경써야 성능이 나옵니다~실무에서 쓰는 쿼리 맞나요?

오라클 쿼리 튜닝 관련 질문드립니다.

3개의 답변

기술 목록