쿼리 조회 시간이 너무 오래걸립니다.

Question

현재 쿼리는 아래와 같습니다.SELECT *FROM ( SELECT ROWNUM AS RW , A_.* FROM ( SELECT COUNT(*) OVER() TOTAL_CNT , D.STAT_CD , CM_GETCODENM('GM0100', D.STAT_CD ) AS STAT_NM , A.ASSET_MGMT_NO , A.ACNTS_SE_CD , CM_GETCODENM('CM0030', A.ACNTS_SE_CD ) AS ACNTS_SE_NM , A.DEPT_CD , CM_GETDEPTNM(A.DEPT_CD) AS DEPT_NM , A.HAVE_SE_CD , CM_GETCODENM('GM0010', A.HAVE_SE_CD ) AS HAVE_SE_NM , A.ASSET_CL_NO || '-' || A.ASSET_STND_NO AS ASSET_CL_STND_NO , A.ASSET_CL_NO , B.ASSET_CL_KO_NM , A.ASSET_STND_NO , B.ASSET_STND_KO_NM , A.BUY_DT , A.ACQ_DT , A.BUY_UPRICE , A.ASSET_STAT_CD , CM_GETCODENM('GM0020', A.ASSET_STAT_CD ) AS ASSET_STAT_NM , A.PRT_ISSU_DATE , A.RMK , A.USR_ID , A.MNGR_ID , A.USE_AT , DECODE(A.USE_AT, 'Y', '사용', '미사용') AS USE_NM , A.MTHRGDS_ASSET_MGMT_NO , C.DUSE_STAT_CD , CM_GETCODENM('GM0050', C.DUSE_STAT_CD ) AS DUSE_STAT_NM , DUSE_JUDGM_DT , C.DUSE_JUDGM_SE_CD , CM_GETCODENM('GM0030', C.DUSE_JUDGM_SE_CD ) AS DUSE_JUDGM_SE_NM , C.DUSE_JUDGM_ASSET_STAT_CD , CM_GETCODENM('GM0020', C.DUSE_JUDGM_ASSET_STAT_CD ) AS DUSE_JUDGM_ASSET_STAT_NM , DUSE_JUDGM_ASSET_CSTDY_LOC , DUSE_JUDGM_RSON , B.USE_YYCNT , NVL(E.MTHRGDS_ASSET_MGMT_CNT, 0) AS MTHRGDS_ASSET_MGMT_CNT , C.DUSE_DISP_DT , C.DUSE_DISP_SE_CD , CM_GETCODENM('GM0030', C.DUSE_DISP_SE_CD ) AS DUSE_DISP_SE_NM , C.DUSE_DISP_RSON , TO_CHAR(ADD_MONTHS(TO_DATE(A.BUY_DT, 'YYYYMMDD'), NVL( B.USE_YYCNT, 0)* 12)-1, 'YYYYMMDD') AS USE_YYCNT_OVER_DT , A.BLDG_CD , F.BLDG_NM , A.STAIR_CD , F.STAIR_NM , A.LOC_CD , F.LOC_NM , CM_GETUSERNM(A.USR_ID) AS USR_NM FROM TA_ASSET_ACQ_DTL A , ( SELECT B1.ASSET_CL_NO, B1.ASSET_CL_KO_NM , B1.USE_YYCNT , B1.TAG_KIND_CD , B2.ASSET_STND_NO , B2.ASSET_STND_KO_NM , B2.UNIT_CD FROM TA_ASSET_CL_CD B1 , TA_ASSET_STND_CD B2 WHERE B1.ASSET_CL_NO &#61; B2.ASSET_CL_NO(&#43;) AND B1.ASSET_CL_NO > ' ' AND B2.ASSET_STND_NO > ' ' ) B , ( SELECT A.* FROM TA_ASSET_DUSE A , ( SELECT ASSET_MGMT_NO , MAX(DUSE_SEQ) AS DUSE_SEQ FROM TA_ASSET_DUSE GROUP BY ASSET_MGMT_NO ) B WHERE A.ASSET_MGMT_NO &#61; B.ASSET_MGMT_NO AND A.DUSE_SEQ &#61; B.DUSE_SEQ ) C , ( SELECT CASE WHEN A.STAT_CD &#61; '21' OR A.STAT_CD &#61; '23' THEN '21' WHEN A.STAT_CD &#61; '31' OR A.STAT_CD &#61; '32' THEN A.STAT_CD WHEN A.STAT_CD &#61; '41' THEN '41' WHEN A.STAT_CD &#61; '51' THEN '51' ELSE '11' END AS STAT_CD , A.ASSET_MGMT_NO FROM TA_ASSET_ACQ_DTL_HIST A , ( SELECT ASSET_MGMT_NO , MAX(HIST_SEQ) AS HIST_SEQ FROM TA_ASSET_ACQ_DTL_HIST GROUP BY ASSET_MGMT_NO ) B WHERE A.ASSET_MGMT_NO &#61; B.ASSET_MGMT_NO AND A.HIST_SEQ &#61; B.HIST_SEQ ) D , ( SELECT MTHRGDS_ASSET_MGMT_NO , COUNT(*) AS MTHRGDS_ASSET_MGMT_CNT FROM TA_ASSET_ACQ_DTL G WHERE G.MTHRGDS_ASSET_MGMT_NO IS NOT NULL AND G.ASSET_MGMT_NO > ' ' AND G.USE_AT &#61; 'Y' AND ( G.MTHRGDS_ASSET_MGMT_NO IN ( SELECT MTHRGDS_ASSET_MGMT_NO FROM TA_ASSET_ACQ_DTL WHERE ASSET_MGMT_NO IN (G.ASSET_MGMT_NO) ) ) GROUP BY MTHRGDS_ASSET_MGMT_NO ) E , ( SELECT LOC_NM , cm_getcodenm('CM0310', BLDG_CD) AS BLDG_NM , cm_getcodenm('CM0320', STAIR_CD) AS STAIR_NM , BLDG_CD , STAIR_CD , LOC_CD FROM TA_USE_LOC_CD WHERE USE_AT &#61; 'Y' ) F WHERE A.ASSET_CL_NO &#61; B.ASSET_CL_NO AND A.ASSET_STND_NO &#61; B.ASSET_STND_NO AND A.ASSET_MGMT_NO &#61; C.ASSET_MGMT_NO(&#43;) AND A.ASSET_MGMT_NO &#61; D.ASSET_MGMT_NO(&#43;) AND A.ASSET_MGMT_NO &#61; E.MTHRGDS_ASSET_MGMT_NO(&#43;) AND A.BLDG_CD &#61; F.BLDG_CD(&#43;) AND A.STAIR_CD &#61; F.STAIR_CD(&#43;) AND A.LOC_CD &#61; F.LOC_CD(&#43;) AND NVL(C.DUSE_STAT_CD,0) NOT IN ('20') AND A.DEPT_CD IN (( SELECT DEPT_CD FROM VA_DEPT START WITH DEPT_CD &#61; '00' CONNECT BY PRIOR DEPT_CD &#61; UP_DEPT_CD )) ORDER BY ASSET_MGMT_NO DESC ) A_ WHERE ROWNUM <&#61; to_NUMBER(1) * to_NUMBER(300) )WHERE RW >&#61; to_NUMBER(1) * to_NUMBER(300) - (to_NUMBER(300) - 1)그리고 실행 계획은 아래와 같습니다.비슷한 조건의 다른 쿼리들은 1s 이내로 조회가 되는데무엇이 문제인지 300건 조회하는데도 35초~1분 내외로 시간이 소요되네요 조회 시간 개선을 위해 수정해야할 부분이 어디일까요..?

n = n + 1 · Answer

잘은 모르지만 MAX가 눈에 밟히네요

MySpace · Answer

ㅎㅎㅎ 실행계획도 보시며 해보세요

장독깨기 · Answer

SELECT mthrgds_asset_mgmt_no, Count(*) AS mthrgds_asset_mgmt_cnt FROM ta_asset_acq_dtl g WHERE g.mthrgds_asset_mgmt_no IS NOT NULL AND g.asset_mgmt_no > ' ' AND g.use_at = 'Y' AND ( g.mthrgds_asset_mgmt_no IN ( SELECT mthrgds_asset_mgmt_no FROM ta_asset_acq_dtl WHERE asset_mgmt_no IN ( g.asset_mgmt_no )) ) GROUP BY mthrgds_asset_mgmt_no) e이 부분 한 번 살펴보세요. 내부 서브 쿼리가 외부 쿼리를 참조하는데요,이런 상관관계는 성능 문제가 생깁니다.

꿈은없고요놀고싶습니다 · Answer

저도 쿼리를 감으로 짜는 수준이라 실행계획같은걸 잘 볼줄은 모르지만, 몇가지 경험을 기반으로 느린 이유를 유추해 보자면...서브쿼리를 남발하면 성능에 좋지 않으니 with 절 같은것들로 미리 묶어서 사용하는게 좋습니다.쿼리는 적은 데이터로 조인을 걸어야 빠릅니다. WHERE절을 잘 써서 조인하는 데이터의 양을 최대한 줄여보세요. 100*100보다 10*100이 훨씬 빠른건 당연합니다.SELECT 절에 함수가 있는것 같은데 단건조회는 괜찮지만, 리스트에 사용할 경우 심각한 성능저하를 유발할 수 있습니다. 최대한 제거 하시는게 좋습니다.혹시 불필요한 형변환이 일어나는 조건절이 있나 살펴보세요. 제가 알기로 형변환이 발생하면 인덱스를 못타는 걸로 알고 있습니다.혹시 불필요한 레프트 조인이 있다면 머리를 잘 써서 이너조인으로 바꾸시는게 좋습니다.이거 외에도 필요한 부분에 인덱스를 적절하게 걸어주는 것도 성능에 도움이 됩니다.이도저도 안되면 필요한 데이터만 들어있는 백업(?)테이블을 하나 만드는 것도 방법입니다. 몇분마다 데이터를 증분해서 백업테이블에 넣어주고, 실제 조회할때는 그 백업테이블의 데이터만 가져오면 되니 데이터가 몇백만 건이든 속도 이슈가 없습니다. 근데 저 정도 쿼리에 이 방법은 좀 사치 같긴 합니다.

라떼개발자 · Answer

서브쿼리가 되게 많네요 그리고 * 도…챗gpt에게 맡겨봅니다.———————————————-쿼리의 복잡도를 줄이고 성능을 개선하기 위해, 다음 사항을 고려해 볼 수 있습니다:1. *인덱스*: 적절한 컬럼에 인덱스를 적용하여 데이터 접근 속도를 높이십시오. 특히 TA_ASSET_ACQ_DTL, TA_ASSET_CL_CD, TA_ASSET_STND_CD 등의 테이블에 대한 인덱스가 있는지 확인하십시오.2. *서브쿼리 최적화*: 중복된 서브쿼리나 불필요한 연산이 없는지 확인하고, 인덱스 스캔이 필요한 경우 메인 쿼리에 합칠 수 있는지 고려하십시오.3. *JOINS*: 조인 조건을 최적화하여, 최대한 필요한 데이터만 가져오도록 조정하십시오.4. *불필요한 함수 호출 제거*: 일부 함수 호출을 제거하고, 필요한 경우 미리 데이터를 준비해 두십시오.다음은 튜닝된 쿼리의 예시입니다:```sqlWITH FILTERED_DATA AS ( SELECT A.*, B.*, C.*, D.STAT_CD, E.MTHRGDS_ASSET_MGMT_CNT, F.BLDG_NM, F.STAIR_NM, F.LOC_NM FROM TA_ASSET_ACQ_DTL A LEFT JOIN ( SELECT B1.ASSET_CL_NO, B1.ASSET_CL_KO_NM, B1.USE_YYCNT, B2.ASSET_STND_NO, B2.ASSET_STND_KO_NM FROM TA_ASSET_CL_CD B1 JOIN TA_ASSET_STND_CD B2 ON B1.ASSET_CL_NO = B2.ASSET_CL_NO ) B ON A.ASSET_CL_NO = B.ASSET_CL_NO AND A.ASSET_STND_NO = B.ASSET_STND_NO LEFT JOIN ( SELECT ASSET_MGMT_NO, MAX(DUSE_SEQ) AS DUSE_SEQ FROM TA_ASSET_DUSE GROUP BY ASSET_MGMT_NO ) C1 ON A.ASSET_MGMT_NO = C1.ASSET_MGMT_NO LEFT JOIN TA_ASSET_DUSE C ON C.ASSET_MGMT_NO = C1.ASSET_MGMT_NO AND C.DUSE_SEQ = C1.DUSE_SEQ LEFT JOIN ( SELECT ASSET_MGMT_NO, MAX(HIST_SEQ) AS HIST_SEQ FROM TA_ASSET_ACQ_DTL_HIST GROUP BY ASSET_MGMT_NO ) D1 ON A.ASSET_MGMT_NO = D1.ASSET_MGMT_NO LEFT JOIN TA_ASSET_ACQ_DTL_HIST D ON D.ASSET_MGMT_NO = D1.ASSET_MGMT_NO AND D.HIST_SEQ = D1.HIST_SEQ LEFT JOIN ( SELECT MTHRGDS_ASSET_MGMT_NO, COUNT(*) AS MTHRGDS_ASSET_MGMT_CNT FROM TA_ASSET_ACQ_DTL WHERE G.MTHRGDS_ASSET_MGMT_NO IS NOT NULL GROUP BY MTHRGDS_ASSET_MGMT_NO ) E ON A.ASSET_MGMT_NO = E.MTHRGDS_ASSET_MGMT_NO LEFT JOIN TA_USE_LOC_CD F ON A.BLDG_CD = F.BLDG_CD AND A.STAIR_CD = F.STAIR_CD AND A.LOC_CD = F.LOC_CD WHERE NVL(C.DUSE_STAT_CD, '0') NOT IN ('20') AND A.DEPT_CD IN (SELECT DEPT_CD FROM VA_DEPT START WITH DEPT_CD = '00' CONNECT BY PRIOR DEPT_CD = UP_DEPT_CD))SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY ASSET_MGMT_NO DESC) AS RW, FILTERED_DATA.* FROM FILTERED_DATA)WHERE RW BETWEEN TO_NUMBER(1) TO_NUMBER(300) - (TO_NUMBER(300) - 1) AND TO_NUMBER(1) TO_NUMBER(300)```튜닝된 쿼리는 더 나은 인덱싱과 필터링을 사용하여 서브쿼리 구조를 간소화하고, 데이터베이스 성능을 향상하도록 설계되었습니다.

쿼리 조회 시간이 너무 오래걸립니다.

5개의 답변

#select 목록