데이터베이스 질문과 답변
안녕하세요 이번에 근태관련 쿼리를 만들다가 이상한 현상을 발견하여 질문드립니다.
여러 신청서의 일자 및 시간을 조회하여 해당 근무인원의 일자별 근태현황을 보여주는 쿼리인데요
함수 바깥에서 직접 실행하는 경우에는 속도가 빠른데 함수로 만들어서 처리하면 속도가 느려지더라구요
그래서 원인을 찾다가 WHERE절에 기간 체크하는 부분에서
SELECT A.* FROM TABLE_RQST A WHERE A.RQST_DATE BETWEEN V_FROM_DATE AND V_TO_DATE --AND A.RQST_DATE BETWEEN '19000101' AND '99991231'
주석처리된 부분처럼 명시적인 값을 넣어주니까 속도가 매우 개선되더라구요
해당쿼리가 몇 개 있는데 한 군데라도 저렇게 명시적인 값 비교를 추가해주면 전체속도가 개선됩니다.
그런데 마냥 저렇게 처리해두기에는 원인도 못 찾겠고 불안해서
혹시 관련 키워드나 원인을 아시는 분이 있다면 의견을 구하고 싶습니다.
-- 직접 수행할 때도 바인드 변수로 수행해서 직접 줄때와 실행계획 비교해 보세요. SELECT a.* FROM table_rqst a WHERE a.rqst_date BETWEEN :v_from_date AND :v_to_date ;
계속 검색해본 결과 정확히 일치하는 상황이 있어 링크 남깁니다, 답변은 크게 도움이 되지 않는 것 같지만요
https://forums.oracle.com/ords/apexds/post/pl-sql-speed-issues-when-using-a-variable-1494
https://stackoverflow.com/questions/72068511/query-execution-is-slow-in-pl-sql-anonymous-block
위의 케이스도 동일한 케이스라 생각하여 XPLAN 출력해본 결과 SQL_ID는 다르고 큰 차이는 없었습니다
read값은 여러 번 실행해보니 안 보이더라구요
FN_BINDING이 명시적 비교절을 추가하지 않은 함수
FN_LITERAL이 명시적 비교절을 추가한 함수입니다
------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:14.17 | 115K| | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:14.17 | 115K| | 2 | COLLECTION ITERATOR PICKLER FETCH| FN_BINDING | 1 | 106K| 53196 |00:00:14.17 | 115K| ------------------------------------------------------------------------------------------------------------ --------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | --------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:14.24 | 115K| 217 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:14.24 | 115K| 217 | | 2 | COLLECTION ITERATOR PICKLER FETCH| FN_LITERAL | 1 | 106K| 53196 |00:00:14.24 | 115K| 217 | ---------------------------------------------------------------------------------------------------------------------
바인드 변수로 직접 수행한 결과는 직접 줄 때와 똑같았습니다.
아마 함수 안에 있을 때 생기는 문제 같습니다
함수에서 파이프라인을 반환하고 있기는 합니다만 이건 관련 없을 것 같은데 영문을 모르겠네요,,
리터럴 변수비교를 추가하고 안하고의 경우 데이터의 양만큼 시간 차이가 나는 것 같은데
키워드를 찾아서 계속 찾아본 결과 프로시저나 함수안에 들어가는 경우
WHERE 절에 변수가 사용되는 경우에 발생하는 옵티마이저 문제 같네요
되게 옛날부터 있었던 문제이고, 딱히 왜 발생하는지 해결방법은 뭔지 찾지는 못 했습니다.
몇몇 의견에는 parameter sniffing 문제가 계속 언급되긴 하네요
) 버튼을 클릭하여 작성 하시면 됩니다.