[완료] /*modifySchedule.base.selectContentList */

AS-IS Total query runtime: 3 secs 65 msec. TO-BE Total query runtime: 465 msec.   개선효과 92.15%

 

AS-IS Plan NODE 

->  Index Scan using pk_pt_la_album_info on imcsuser.pt_la_album_info aif  (cost=0.42..0.52 rows=1 width=111) (actual time=0.003..0.003 rows=0 loops=519621)" "                                                                          Output: aif.album_id, aif.album_name, aif.album_date, aif.series_id, aif.series_name, aif.series_no, aif.onair_date, aif.onair_date_display, aif.release_date, aif.watch_pctv_yn, aif.watch_2ndtv_yn, aif.encoding_format, aif.high_quality_type, aif.pre_mapping_yn, aif.update_date, aif.event_yn, aif.vod_type" "                                                                          Index Cond: (((aif.album_id)::text = (sam.album_id)::text) AND ((aif.album_id)::text >= 'M'::text) AND ((aif.album_id)::text < 'N'::text))" "                                                                          Filter: (((aif.album_id)::text ~~ 'M%'::text) AND (COALESCE(NULLIF((aif.vod_type)::text, ''::text), 'M'::text) <> 'D'::text) AND (upper(replace((aif.album_name)::text, ' '::text, ''::text)) ~~ '%646재배포-220818-S17955%'::text))" "                                                                          Rows Removed by Filter: 1" "                                                                          Buffers: shared hit=2078482"

→ Loop 519621 돌면서 17GB Block 을 비효율적으로 읽었음.

 

TO-BE Plan NODE 

->  Parallel Seq Scan on imcsuser.pt_la_album_info aif  (cost=0.00..34462.71 rows=40 width=111) (actual time=184.937..185.611 rows=0 loops=3)" "                                                                                      Output: aif.album_id, aif.album_name, aif.series_id, aif.series_name, aif.series_no, aif.album_date" "                                                                                      Filter: (((aif.album_id)::text ~~ 'M%'::text) AND (COALESCE(NULLIF((aif.vod_type)::text, ''::text), 'M'::text) <> 'D'::text) AND (upper(replace((aif.album_name)::text, ' '::text, ''::text)) ~~ '%646재배포-220818-S17955%'::text))" "                                                                                      Rows Removed by Filter: 338653" "                                                                                      Buffers: shared hit=23880" "                                                                                      Worker 0:  actual time=183.111..183.112 rows=0 loops=1" "                                                                                        Buffers: shared hit=7944" "                                                                                      Worker 1:  actual time=183.555..185.575 rows=1 loops=1" "                                                                                        Buffers: shared hit=7944"

→ driving table 로 hint 로 유도 후 패러럴로 seq scan 하여 loops=3 만 수행 하였다.

 

* 발췌

Note  Oracle 의 경우 갑작스런 플랜의 변경을 방지 하고자 많은 사이트에서 자동 통계정보 수집 기능을 비활성화 하고 사용하고 있습니다. 기본적인 통계정보만을 수집하고 그외 추가로 통계정보를 수집 하지 않고 기존의 통계정보를 그대로 유지하여 사용하는 사이트나 시스템도 많은 편 입니다. 그래서 Oracle 에서는 SQL 레벨에서의 Hint 가 주로 많이 사용하여 플랜을 보정하고 고정 하여 사용 하고 있습니다.

 

* 튜닝 쿼리 ( hint  로 해결 ) -> PG 는 plan stroe 가 없기에 hint 로 고정해서 사용 하기 보다는, Optimizer 에게 맡기는걸 권장 ??
--> 운영 하다보니 hint 로 되는 쿼리도 있지만 안되는 쿼리도 많았음;;; ( aurora PG ver 13 )

/*+ Leading(aif asu) */ SELECT  /* modifySchedule.base.selectContentList */ CC.TOTAL_CNT,  CC.RNUM,  CC.CONTENT_ID,  CC.CONTENT_NAME,  CC.SERIES_NAME,  CC.SERIES_NO,  CC.ALBUM_DATE,  CC.GENRE_LARGE,  CC.GENRE_MID,  CC.GENRE_SMALL,  MAX(CP_NAME) CP_NAME  FROM ( SELECT    AA.TOTAL_CNT,   AA.RNUM,   AA.CONTENT_ID,   AA.CONTENT_NAME,   AA.SERIES_NAME,   AA.SERIES_NO,   AA.ALBUM_DATE,   AA.GENRE_LARGE,   AA.GENRE_MID,   AA.GENRE_SMALL,   (SELECT CP_NAME FROM IMCSUSER.PT_CD_CP_MST WHERE CP_ID = BB.CP_ID LIMIT 1) AS CP_NAME FROM (   SELECT COUNT(CONTENT_ID) OVER () AS TOTAL_CNT, ROW_NUMBER() OVER (ORDER BY A.ALBUM_DATE_EXIST DESC, A.ALBUM_DATE DESC) AS RNUM, A.CONTENT_ID, A.CONTENT_NAME, A.SERIES_NAME, A.SERIES_NO, A.ALBUM_DATE, A.GENRE_LARGE, A.GENRE_MID, A.GENRE_SMALL   FROM ( SELECT                                     AIF.ALBUM_ID AS CONTENT_ID,   AIF.ALBUM_NAME AS CONTENT_NAME,   AIF.SERIES_ID AS SERIES_ID,   AIF.SERIES_NAME AS SERIES_NAME,           MAX(SAM.ALBUM_GROUP_ID) AS SERIES_ID_IMCS,   MAX(SER_MST.ALBUM_GROUP_NM) AS SERIES_NAME_IMCS,       MAX(AIF.SERIES_NO) AS SERIES_NO,   CASE AIF.ALBUM_DATE  WHEN '' THEN  0  ELSE 1 END AS ALBUM_DATE_EXIST,   MAX(AIF.ALBUM_DATE) AS ALBUM_DATE,   ASU.GENRE_LARGE AS GENRE_LARGE,   ASU.GENRE_MID AS GENRE_MID,   ASU.GENRE_SMALL AS GENRE_SMALL,   (SELECT COUNT(1) FROM IMCSUSER.PT_LA_ALBUM_RELATION WHERE GROUP_TYPE = 'S' AND ALBUM_ID =  AIF.ALBUM_ID) AS SERIES_CNT FROM IMCSUSER.PT_LA_ALBUM_INFO AIF INNER JOIN IMCSUSER.PT_LA_ALBUM_SUB ASU ON AIF.ALBUM_ID = ASU.ALBUM_ID             INNER JOIN IMCSUSER.PT_LA_ALBUM_RELATION SAM ON AIF.ALBUM_ID = SAM.ALBUM_ID     INNER JOIN IMCSUSER.PT_LA_ALBUM_GROUP SER_MST ON SER_MST.ALBUM_GROUP_ID = SAM.ALBUM_GROUP_ID AND SER_MST.GROUP_TYPE = SAM.GROUP_TYPE AND SER_MST.GROUP_TYPE = 'S'   WHERE 1=1      AND AIF.ALBUM_ID LIKE 'M'||'%'   AND COALESCE(NULLIF(AIF.VOD_TYPE,''), 'M') != 'D'   AND UPPER(REPLACE(AIF.ALBUM_NAME,  ' ', '')) LIKE '%' || UPPER(REPLACE('646재배포-220818-S17955', ' ', '')) || '%'             AND ASU.GENRE_LARGE = '라이프'             AND ASU.GENRE_MID = '스포츠'             AND ASU.GENRE_SMALL = '프로야구'   GROUP BY AIF.ALBUM_ID, AIF.ALBUM_NAME, AIF.SERIES_ID, AIF.SERIES_NAME, AIF.SERIES_NO,              ASU.GENRE_LARGE, ASU.GENRE_MID, ASU.GENRE_SMALL, AIF.ALBUM_DATE   ) A   WHERE 1=1            AND A.SERIES_CNT   > 0        ) AA LEFT JOIN IMCSUSER.PT_LA_ALBUM_PLATFORM BB ON AA.CONTENT_ID = BB.ALBUM_ID WHERE 1=1   AND  AA.RNUM BETWEEN 0 + 1 AND 0 + 100   ) CC      GROUP BY  CC.TOTAL_CNT, CC.RNUM, CC.CONTENT_ID, CC.CONTENT_NAME, CC.SERIES_NAME, CC.SERIES_NO, CC.ALBUM_DATE,        CC.GENRE_LARGE, CC.GENRE_MID, CC.GENRE_SMALL      ORDER BY CC.RNUM ASC