[튜닝불가] /* categoryform.dr.getAlbumSeriesList */

Legacy DB 수행 시간 : 10분 정도 소요

 

MSA DB

– MylgDB STP DB 기준

AS-IS Query 수행 속도 : 3분

 

* 개선포인트 도출

 

* 개선 포인트

SET work_mem = '1000MB'; ( 5초 정도 수행 빨라짐) join imcsuser.PT_LA_ALBUM_INFO ALB_INFO on (AST_INFO.ALBUM_ID = ALB_INFO.ALBUM_ID and AST_INFO.SCREEN_TYPE = 'I' ) -- SCREEN_TYPE INDEX 없음 ( I / N ) join imcsuser.PT_LA_ALBUM_SUB ALB_SUB on (AST_INFO.ALBUM_ID = ALB_SUB.ALBUM_ID and ALB_INFO.ALBUM_ID = ALB_SUB.ALBUM_ID) join imcsuser.PT_LA_ALBUM_PLATFORM ALB_PLF on (AST_INFO.ALBUM_ID = ALB_PLF.ALBUM_ID and ALB_INFO.ALBUM_ID = ALB_PLF.ALBUM_ID and ALB_SUB.ALBUM_ID = ALB_PLF.ALBUM_ID and ALB_PLF.SCREEN_TYPE = 'I' ) -- SCREEN_TYPE INDEX 없음 ( I / N ) join imcsuser.PT_CD_CP_MST CP_MST on (ALB_PLF.CP_ID = CP_MST.CP_ID) -- imcsuser.PT_LA_ALBUM_PLATFORM (CP_ID 인덱스 없음) left outer join VODUSER.PT_VO_CATEGORY_MAP_UNITED CAT_MAP on (ALB_INFO.ALBUM_ID = CAT_MAP.VOD_CONTENTS_ID) left outer join VODUSER.PT_VO_CATEGORY_UNITED CAT on -- (ALB_INFO.ALBUM_ID = CAT_MAP.VOD_CONTENTS_ID -- 동일 조인 조건이 잘못걸려 있음 ( CAT_MAP.VOD_CATEGORY_ID = CAT.VOD_CATEGORY_ID and CAT.VOD_CATEGORY_GB in ('I20', 'I30', 'NSC')) -- VODUSER.PT_VO_CATEGORY_UNITED ( VOD_CATEGORY_GB 인덱스 없음) left outer join imcsuser.PT_LA_ALBUM_RELATION SER_MAP on (ALB_INFO.ALBUM_ID = SER_MAP.ALBUM_ID and SER_MAP.GROUP_TYPE = 'S') -- imcsuser.PT_LA_ALBUM_RELATION (imcsuser.PT_LA_ALBUM_RELATION 인덱스 GROUP_TYPE 생성) left outer join imcsuser.PT_LA_ALBUM_GROUP SER_MST on (SER_MST.ALBUM_GROUP_ID = SER_MAP.ALBUM_GROUP_ID and SER_MST.GROUP_TYPE = SER_MAP.GROUP_TYPE and SER_MST.GROUP_TYPE = 'S') -- imcsuser.PT_LA_ALBUM_GROUP (GROUP_TYPE 인덱스 생성) left outer join imcsuser.PT_PD_PACKAGE_DETAIL PD on (AST_INFO.ASSET_ID = PD.CONTENTS_ID) inner join IMCSUSER.PT_PD_PACKAGE_UNITED PP on ALB_MAIN.PRODUCT_ID = PP.IMCS_PRODUCT_ID where ALB_MAIN.PRODUCT_ID = PP.IMCS_PRODUCT_ID and PP.IMCS_PRODUCT_TYPE = '2' --조건이 같은게 걸려있음. and coalesce(nullif(PP.IMCS_NSC_PROD_KIND, ''), '0') = '0' and UPPER(PP.IMCS_PRODUCT_NAME) like '%' || UPPER('PPS')|| '%' -- IMCSUSER.PT_PD_PACKAGE_UNITED IMCS_PRODUCT_NAME index 생성 해야함!! -- and PP.imcs_product_type = '2' --조건이 같은게 걸려있음. /* and coalesce(nullif(PP.IMCS_NSC_PROD_KIND, ''), '0') = '0' */ ) ALB_MAIN_2 ) ALB_MAIN_3 --조건이 같은게 걸려있음. where

 

 

·       튜닝 불가

Inline view 5개로 감싸져 있으며, 메인 View 인 ALB_MAIN 내에서 들고 오는 row 수는 11개의 테이블에서 1,729,979 건을 들고와 group by 하여 115,792 건을 도출 및 sort 작업을 수행 한다.

group by 하는 시간이 총 2분52초 소요 되었다. ( 조건 1년치 AST_INFO.CREATE_DATE between '20210101' and '20211231' )

->  GroupAggregate  (cost=659114.18..826285.90 rows=799993 width=709) (actual time=7291.345..181430.719 rows=115792 loops=1)"

"                          Output: ast_info.create_date, ast_info.album_id, NULL::integer, NULL::text, regexp_replace(substr(array_to_string(array_agg(cat.vod_category_gb ORDER BY cat.vod_category_gb), ','::text), 2), '([^,]+)(,\1)+'::text, '\1'::text), regexp_replace(substr(array_to_string(array_agg(cat.vod_nsc_gb ORDER BY cat.vod_nsc_gb), ','::text), 2), '([^,]+)(,\1)+'::text, '\1'::text), alb_info.album_name, alb_info.onair_date, alb_info.series_no, (COALESCE(NULLIF((alb_info.pre_mapping_yn)::text, ''::text), 'N'::text)), alb_sub.player, alb_sub.director, alb_sub.genre_large, alb_sub.genre_mid, alb_sub.genre_small, ser_map.album_group_id, ser_mst.album_group_nm, max((cat_map.vod_viewing_flag)::text), max((cat_map.vod_qd_flag)::text), pd.product_id, NULL::text, NULL::text, NULL::text, NULL::text, (COALESCE(NULLIF(rtrim((alb_plf.maximum_viewing_length)::text), ''::text), '0'::text)), NULL::text, NULL::text, NULL::text, ast_info.suggested_price, (CASE WHEN ((COALESCE(cat_map.vod_contents_id, ''::character varying))::text = ''::text) THEN 'N'::text ELSE 'Y'::text END)"

"                          Group Key: ast_info.create_date, ast_info.album_id, ast_info.suggested_price, alb_info.album_name, alb_info.onair_date, alb_info.series_no, (COALESCE(NULLIF((alb_info.pre_mapping_yn)::text, ''::text), 'N'::text)), alb_sub.player, alb_sub.director, alb_sub.genre_large, alb_sub.genre_mid, alb_sub.genre_small, ser_map.album_group_id, ser_mst.album_group_nm, (CASE WHEN ((COALESCE(cat_map.vod_contents_id, ''::character varying))::text = ''::text) THEN 'N'::text ELSE 'Y'::text END), pd.product_id, (COALESCE(NULLIF(rtrim((alb_plf.maximum_viewing_length)::text), ''::text), '0'::text))"

"                          Buffers: shared hit=5453314, temp read=162046 written=164531"

 ->  Gather Merge  (cost=659114.18..752286.55 rows=799993 width=332) (actual time=7288.128..8773.305 rows=1729979 loops=1)"

 

·       원본 쿼리

select /* categoryform.dr.getAlbumSeriesList */ ALB_MAIN_4.ALB_SER_ID , ALB_MAIN_4.ALB_SER_NAME , ALB_MAIN_4.ALBUM_NO , ALB_MAIN_4.ALBUM_ID , ALB_MAIN_4.PRE_MAPPING_YN , ALB_MAIN_4.CREATE_DATE , ALB_MAIN_4.GENRE , ALB_MAIN_4.VIEWING_FLAG , ALB_MAIN_4.QD_FLAG , ALB_MAIN_4.I20_YN , ALB_MAIN_4.I30_YN , ALB_MAIN_4.NSC_YN , ALB_MAIN_4.UFLIX_YN , ALB_MAIN_4.REG_CNT , ALB_MAIN_4.ALB_SER_TYPE , ALB_MAIN_4.TERR_YN , ALB_MAIN_4.TERR_PERIOD , ALB_MAIN_4.TERR_ST_DATE , ALB_MAIN_4.TERR_ED_DATE , ALB_MAIN_4.LICENSING_WINDOW_START , ALB_MAIN_4.LICENSING_WINDOW_END , ALB_MAIN_4.SUPER_ID from ( select ALB_MAIN_3.PRODUCT_ID as ALB_SER_ID , ALB_MAIN_3.PRODUCT_NAME as ALB_SER_NAME , '' as ALBUM_NO , MAX(ALB_MAIN_3.ALBUM_ID) as ALBUM_ID , '' as PRE_MAPPING_YN , TO_CHAR(TO_DATE(MAX(ALB_MAIN_3.CREATE_DATE), 'YYYYMMDD'), 'YYYY-MM-DD') as CREATE_DATE , MAX(coalesce(nullif(ALB_MAIN_3.GENRE_LARGE, ''), '') || '/' || coalesce(nullif(ALB_MAIN_3.GENRE_MID, ''), '') || '/' || coalesce(nullif(ALB_MAIN_3.GENRE_SMALL, ''), '')) as GENRE , MAX(ALB_MAIN_3.VIEWING_FLAG) as VIEWING_FLAG , MAX(ALB_MAIN_3.QD_FLAG) as QD_FLAG , MAX(ALB_MAIN_3.I20_YN) as I20_YN , MAX(ALB_MAIN_3.I30_YN) as I30_YN , MAX(ALB_MAIN_3.NSC_YN) as NSC_YN , MAX(ALB_MAIN_3.UFLIX_YN) as UFLIX_YN , 1 as REG_CNT , 'PPS' as ALB_SER_TYPE , '' as TERR_YN , '' as TERR_PERIOD , '' as TERR_ST_DATE , '' as TERR_ED_DATE , '' as LICENSING_WINDOW_START , '' as LICENSING_WINDOW_END , '' as SUPER_ID from ( select ALB_MAIN_2.CREATE_DATE , ALB_MAIN_2.ALBUM_ID , ALB_MAIN_2.SUGGESTED_PRICE , ALB_MAIN_2.ALBUM_NAME , ALB_MAIN_2.ONAIR_DATE , ALB_MAIN_2.ALBUM_NO , ALB_MAIN_2.PRE_MAPPING_YN , ALB_MAIN_2.PLAYER , ALB_MAIN_2.DIRECTOR , ALB_MAIN_2.GENRE_LARGE , ALB_MAIN_2.GENRE_MID , ALB_MAIN_2.GENRE_SMALL , ALB_MAIN_2.CP_NAME , ALB_MAIN_2.SERIES_ID_IMCS , ALB_MAIN_2.SERIES_NAME , ALB_MAIN_2.VIEWING_FLAG , ALB_MAIN_2.QD_FLAG , ALB_MAIN_2.I20_YN , ALB_MAIN_2.I30_YN , ALB_MAIN_2.NSC_YN , ALB_MAIN_2.UFLIX_YN , ALB_MAIN_2.PRODUCT_NAME , ALB_MAIN_2.PRODUCT_ID , ALB_MAIN_2.PRODUCT_TYPE , ALB_MAIN_2.NSC_PROD_KIND , ALB_MAIN_2.TERR_YN , ALB_MAIN_2.TERR_PERIOD , ALB_MAIN_2.TERR_ST_DATE , ALB_MAIN_2.TERR_ED_DATE , ALB_MAIN_2.MAXIMUM_VIEWING_LENGTH , ALB_MAIN_2.LICENSING_WINDOW_START , ALB_MAIN_2.LICENSING_WINDOW_END , ALB_MAIN_2.SUPER_ID from ( select ALB_MAIN.CREATE_DATE , ALB_MAIN.ALBUM_ID , ALB_MAIN.SUGGESTED_PRICE , ALB_MAIN.ALBUM_NAME , ALB_MAIN.ALBUM_NO , ALB_MAIN.PRE_MAPPING_YN , ALB_MAIN.ONAIR_DATE , ALB_MAIN.PLAYER , ALB_MAIN.DIRECTOR , ALB_MAIN.GENRE_LARGE , ALB_MAIN.GENRE_MID , ALB_MAIN.GENRE_SMALL , ALB_MAIN.CP_NAME , ALB_MAIN.SERIES_ID_IMCS , ALB_MAIN.SERIES_NAME , coalesce(nullif(ALB_MAIN.VIEWING_FLAG, ''), 'N') as VIEWING_FLAG , ALB_MAIN.QD_FLAG , case when position('I20' in ALB_MAIN.CATEGORY_GB) > 0 then 'Y' else 'N' end as I20_YN , case when position('I30' in ALB_MAIN.CATEGORY_GB) > 0 then 'Y' else 'N' end as I30_YN , case when position('NSC' in ALB_MAIN.CATEGORY_GB) > 0 then 'Y' else 'N' end as NSC_YN , case when ((position('I20' in ALB_MAIN.CATEGORY_GB) > 0 or position('NSC' in ALB_MAIN.CATEGORY_GB) > 0) and position('UFX' in ALB_MAIN.NSC_GB) > 0) then 'Y' else 'N' end as UFLIX_YN , PP.IMCS_PRODUCT_NAME PRODUCT_NAME , PP.IMCS_PRODUCT_ID PRODUCT_ID , PP.IMCS_PRODUCT_TYPE PRODUCT_TYPE , coalesce(nullif(PP.IMCS_NSC_PROD_KIND, ''), '0') as NSC_PROD_KIND , ALB_MAIN.TERR_YN , ALB_MAIN.TERR_PERIOD , case when ALB_MAIN.TERR_ST_DATE ~ '^[0-9\.]+$' and length(ALB_MAIN.TERR_ST_DATE) = 12 then TO_CHAR(to_timestamp(ALB_MAIN.TERR_ST_DATE, 'YYYYMMDDHH24MI'), 'YYYY-MM-DD HH24:MI') else '' end as TERR_ST_DATE , case when ALB_MAIN.TERR_ED_DATE ~ '^[0-9\.]+$' and length(ALB_MAIN.TERR_ED_DATE) = 12 then TO_CHAR(to_timestamp(ALB_MAIN.TERR_ED_DATE, 'YYYYMMDDHH24MI'), 'YYYY-MM-DD HH24:MI') else '' end as TERR_ED_DATE , ALB_MAIN.MAXIMUM_VIEWING_LENGTH , ALB_MAIN.LICENSING_WINDOW_START , ALB_MAIN.LICENSING_WINDOW_END , ALB_MAIN.SUPER_ID from ( select /*+ LEADING(AST_INFO) USE_NL(AST_INFO ALB_INFO ALB_SUB ALB_PLF CATE CP_MST SER_MAP SER_MST PD) */ AST_INFO.CREATE_DATE , AST_INFO.ALBUM_ID , coalesce(AST_INFO.SUGGESTED_PRICE::integer, 0) as SUGGESTED_PRICE , REGEXP_REPLACE(SUBSTR(ARRAY_TO_STRING(ARRAY_AGG(CP_MST.CP_NAME order by CP_MST.CP_NAME), ','), 2), '([^,]+)(,\1)+', '\1') as CP_NAME , REGEXP_REPLACE(SUBSTR(ARRAY_TO_STRING(ARRAY_AGG(CAT.VOD_CATEGORY_GB order by CAT.VOD_CATEGORY_GB), ','), 2), '([^,]+)(,\1)+', '\1') as CATEGORY_GB , REGEXP_REPLACE(SUBSTR(ARRAY_TO_STRING(ARRAY_AGG(CAT.VOD_NSC_GB order by CAT.VOD_NSC_GB), ','), 2), '([^,]+)(,\1)+', '\1') as NSC_GB , ALB_INFO.ALBUM_NAME , ALB_INFO.ONAIR_DATE , ALB_INFO.SERIES_NO as ALBUM_NO , coalesce(nullif(ALB_INFO.PRE_MAPPING_YN, ''), 'N') as PRE_MAPPING_YN , ALB_SUB.PLAYER , ALB_SUB.DIRECTOR , ALB_SUB.GENRE_LARGE , ALB_SUB.GENRE_MID , ALB_SUB.GENRE_SMALL , SER_MAP.ALBUM_GROUP_ID as SERIES_ID_IMCS , SER_MST.ALBUM_GROUP_NM as SERIES_NAME , MAX(CAT_MAP.VOD_VIEWING_FLAG) as VIEWING_FLAG , MAX(CAT_MAP.VOD_QD_FLAG) as QD_FLAG , PD.PRODUCT_ID , MAX(ALB_PLF.TERR_YN) as TERR_YN , MAX(ALB_PLF.TERR_PERIOD) as TERR_PERIOD , MAX(ALB_PLF.TERR_ST_DATE) as TERR_ST_DATE , MAX(ALB_PLF.TERR_ED_DATE) as TERR_ED_DATE , coalesce(nullif(RTRIM(ALB_PLF.MAXIMUM_VIEWING_LENGTH), ''), '0') as MAXIMUM_VIEWING_LENGTH , MAX(ALB_PLF.LICENSING_WINDOW_START) as LICENSING_WINDOW_START , MAX(ALB_PLF.LICENSING_WINDOW_END) as LICENSING_WINDOW_END , '' as SUPER_ID from imcsuser.PT_LA_ASSET_INFO AST_INFO join imcsuser.PT_LA_ALBUM_INFO ALB_INFO on (AST_INFO.ALBUM_ID = ALB_INFO.ALBUM_ID and AST_INFO.SCREEN_TYPE = 'I' ) join imcsuser.PT_LA_ALBUM_SUB ALB_SUB on (AST_INFO.ALBUM_ID = ALB_SUB.ALBUM_ID and ALB_INFO.ALBUM_ID = ALB_SUB.ALBUM_ID) join imcsuser.PT_LA_ALBUM_PLATFORM ALB_PLF on (AST_INFO.ALBUM_ID = ALB_PLF.ALBUM_ID and ALB_INFO.ALBUM_ID = ALB_PLF.ALBUM_ID and ALB_SUB.ALBUM_ID = ALB_PLF.ALBUM_ID and ALB_PLF.SCREEN_TYPE = 'I' ) join imcsuser.PT_CD_CP_MST CP_MST on (ALB_PLF.CP_ID = CP_MST.CP_ID) left outer join VODUSER.PT_VO_CATEGORY_MAP_UNITED CAT_MAP on (ALB_INFO.ALBUM_ID = CAT_MAP.VOD_CONTENTS_ID) left outer join VODUSER.PT_VO_CATEGORY_UNITED CAT on (ALB_INFO.ALBUM_ID = CAT_MAP.VOD_CONTENTS_ID and CAT_MAP.VOD_CATEGORY_ID = CAT.VOD_CATEGORY_ID and CAT.VOD_CATEGORY_GB in ('I20',    'I30',    'NSC')) left outer join imcsuser.PT_LA_ALBUM_RELATION SER_MAP on (ALB_INFO.ALBUM_ID = SER_MAP.ALBUM_ID and SER_MAP.GROUP_TYPE = 'S') left outer join imcsuser.PT_LA_ALBUM_GROUP SER_MST on (SER_MST.ALBUM_GROUP_ID = SER_MAP.ALBUM_GROUP_ID and SER_MST.GROUP_TYPE = SER_MAP.GROUP_TYPE and SER_MST.GROUP_TYPE = 'S') left outer join imcsuser.PT_PD_PACKAGE_DETAIL PD on (AST_INFO.ASSET_ID = PD.CONTENTS_ID) where AST_INFO.CREATE_DATE between '20210101' and '20211231' and ALB_INFO.ALBUM_ID like 'M%' group by AST_INFO.CREATE_DATE , AST_INFO.ALBUM_ID , AST_INFO.SUGGESTED_PRICE , ALB_INFO.ALBUM_NAME , ALB_INFO.ONAIR_DATE , ALB_INFO.SERIES_NO , coalesce(nullif(ALB_INFO.PRE_MAPPING_YN, ''), 'N') , ALB_SUB.PLAYER , ALB_SUB.DIRECTOR , ALB_SUB.GENRE_LARGE , ALB_SUB.GENRE_MID , ALB_SUB.GENRE_SMALL , SER_MAP.ALBUM_GROUP_ID , SER_MST.ALBUM_GROUP_NM , case when coalesce(CAT_MAP.VOD_CONTENTS_ID, '') = '' then 'N' else 'Y' end , PD.PRODUCT_ID , coalesce(nullif(RTRIM(ALB_PLF.MAXIMUM_VIEWING_LENGTH), ''), '0') ) ALB_MAIN inner join IMCSUSER.PT_PD_PACKAGE_UNITED PP on ALB_MAIN.PRODUCT_ID = PP.IMCS_PRODUCT_ID where ALB_MAIN.PRODUCT_ID = PP.IMCS_PRODUCT_ID and PP.IMCS_PRODUCT_TYPE = '2' and coalesce(nullif(PP.IMCS_NSC_PROD_KIND, ''), '0') = '0' and UPPER(PP.IMCS_PRODUCT_NAME) like '%' || UPPER('PPS')|| '%' and PP.imcs_product_type = '2' and coalesce(nullif(PP.IMCS_NSC_PROD_KIND, ''), '0') = '0' ) ALB_MAIN_2 ) ALB_MAIN_3 where coalesce(nullif(ALB_MAIN_3.PRODUCT_ID, ''), 'NNNN') != 'NNNN' group by ALB_MAIN_3.PRODUCT_ID , ALB_MAIN_3.PRODUCT_NAME ) ALB_MAIN_4 order by ALB_MAIN_4.ALB_SER_ID desc;