We need help here as in what could we be the ways to further optimize as it is taking forever to run.
There are two branches on a high level/ Left branch - Union of 6 regions data with required customizations/joins happening for each branch (data - total of approx 25 M rows) and then a right branch Union of 2 sources with required customizations/joins happening for each branch (data - approx 0.13 M rows)
6 regions manipulated data is individually cached in PostgreSQL DB before the Union of all 6 branches and then the whole right branch (Union of 2 sources) is cached again in PostgreSQL.
Then we are doing a following join between these two left/right branches which is taking a lot of time forever to run, can you suggest the ways to optimize this?
SELECT
fv_ktv_union_all_region.ds_source_system_desc AS ds_source_system_desc,
fv_ktv_union_all_region.cd_cycle_code AS cd_cycle_code,
fv_ktv_union_all_region.gn_cycle_type AS gn_cycle_type,
fv_ktv_union_all_region.gn_day_number AS gn_day_number,
fv_ktv_union_all_region.gn_group AS gn_group,
fv_ktv_union_all_region.gn_brand_name AS gn_brand_name,
fv_ktv_union_all_region.ds_product_desc AS ds_product_desc,
fv_ktv_union_all_region.cd_segment_code AS cd_segment_code,
fv_ktv_union_all_region.cd_vehicle_type_code AS cd_vehicle_type_code,
fv_ktv_union_all_region.cd_energy_type_code AS cd_energy_type_code,
fv_ktv_union_all_region.cd_market_iso_code AS cd_market_iso_code,
fv_ktv_union_all_region.region AS region,
fv_ktv_union_all_region.sub_region AS sub_region,
fv_ktv_union_all_region.sub_group AS sub_group,
fv_ktv_union_all_region.country AS country,
fv_ktv_union_all_region.nm_shipment_number AS nm_shipment_number,
fv_ktv_union_all_region.nm_shipment_mtd_number AS nm_shipment_mtd_number,
fv_ktv_union_all_region.nm_sales_number AS nm_sales_number,
fv_ktv_union_all_region.nm_sales_mtd_number AS nm_sales_mtd_number,
fv_ktv_union_all_region.nm_production_number AS nm_production_number,
fv_ktv_union_all_region.nm_production_mtd_number AS nm_production_mtd_number,
fv_ktv_union_all_region.nm_end_customer_order_number AS nm_end_customer_order_number,
fv_ktv_union_all_region.nm_end_customer_order_mtd_number AS nm_end_customer_order_mtd_number,
fv_ktv_union_all_region.nm_end_customer_order_retail_number AS nm_end_customer_order_retail_number,
fv_ktv_union_all_region.nm_end_customer_order_dealer_number AS nm_end_customer_order_dealer_number,
fv_ktv_union_all_region.nm_network_stock_number AS nm_network_stock_number,
fv_ktv_union_all_region.nm_property_stock_number AS nm_property_stock_number,
fv_ktv_union_all_region.nm_property_stock_transit_number AS nm_property_stock_transit_number,
fv_ktv_union_all_region.nm_end_customer_order_compound_number AS nm_end_customer_order_compound_number,
fv_ktv_union_all_region.nm_property_stock_compound_number AS nm_property_stock_compound_number,
fv_ktv_union_all_region.nm_property_stock_direct_number AS nm_property_stock_direct_number,
fv_ktv_union_all_region.nm_network_stock_aged_number AS nm_network_stock_aged_number,
fv_ktv_union_all_region.nm_property_stock_aged_number AS nm_property_stock_aged_number,
fv_ktv_union_all_region.nm_num_work_day_number AS nm_num_work_day_number,
fv_ktv_union_all_region.nm_registration_number AS nm_registration_number,
fv_ktv_union_all_region.dt_last_modified_timestamp AS dt_last_modified_timestamp,
fv_ktv_union_all_region.nm_network_stock_aged_from_prod_number AS nm_network_stock_aged_from_prod_number,
fv_ktv_union_all_region.gn_year_number AS gn_year_number,
fv_ktv_union_all_region.gn_month_number AS gn_month_number,
case
WHEN
(
fv_ktv_union_all_region.gn_day_number <> 0
)
THEN
coalesce( p_bv_tb_calendar_data.nm_market_working_days_month_to_day_number, p_bv_tb_calendar_data1.nm_market_working_days_month_to_day_number )
ELSE
0
END
AS nm_market_working_days_month_to_day_number,
case
WHEN
(
( fv_ktv_union_all_region.gn_day_number = 0
AND coalesce( p_bv_tb_calendar_data.fl_working, p_bv_tb_calendar_data1.fl_working ) = 1 )
)
THEN
( coalesce( p_bv_tb_calendar_data.nm_market_working_days_to_end_month_number, p_bv_tb_calendar_data1.nm_market_working_days_to_end_month_number ) + 1 )
ELSE
coalesce( p_bv_tb_calendar_data.nm_market_working_days_to_end_month_number, p_bv_tb_calendar_data1.nm_market_working_days_to_end_month_number )
END
AS nm_market_working_days_to_end_month_number
FROM
(
fv_ktv_union_all_region AS fv_ktv_union_all_region HASH
LEFT OUTER JOIN
p_bv_tb_calendar_data AS p_bv_tb_calendar_data
ON ( fv_ktv_union_all_region.cd_market_iso_code = p_bv_tb_calendar_data.cd_market
AND fv_ktv_union_all_region.gn_year_number = p_bv_tb_calendar_data.dt_year
AND fv_ktv_union_all_region.gn_month_number = p_bv_tb_calendar_data.dt_month
AND
case
WHEN
(
fv_ktv_union_all_region.gn_day_number = 0
)
THEN
1
ELSE
fv_ktv_union_all_region.gn_day_number
END
= p_bv_tb_calendar_data.dt_day )
)
HASH
LEFT OUTER JOIN
p_bv_tb_calendar_data AS p_bv_tb_calendar_data1
ON ( fv_ktv_union_all_region.gn_year_number = p_bv_tb_calendar_data1.dt_year
AND fv_ktv_union_all_region.gn_month_number = p_bv_tb_calendar_data1.dt_month
AND p_bv_tb_calendar_data1.cd_market = 'XX'
AND coalesce( p_bv_tb_calendar_data.cd_market, 'XX' ) = p_bv_tb_calendar_data1.cd_market
AND
case
WHEN
(
fv_ktv_union_all_region.gn_day_number = 0
)
THEN
1
ELSE
fv_ktv_union_all_region.gn_day_number
END
= p_bv_tb_calendar_data1.dt_day )
Execution Trace:
QUERY:
select
count(1)
from
fv_ktv EXECUTION PLAN ( name = Execution startTime = Wed Feb 16 18:31:02 794 IST 2022 endTime = - responseTime = - numRows = 0 state = STOPPED completed = false waitingTime = 0 STATIC OPTIMIZATION ( staticOptimized = false staticOptimizationTime = 50 staticOptimizationStart = * staticOptimizationEnd = * ) QUERY BLOCKS ( 549274432 ( leafViews = [vdb_commercial_116.fv_ktv_union_all_region, vdb_commercial_116.p_bv_tb_calendar_data] effectiveTime = 1453762 ms comparable = true ) ) CACHE PLAN ( name = _g92e5420c - 3785 - 431f - ba78 - 7604b2ebac50_d1348d6a - 76b0 - 4e93 - 8e74 - 224a93325972 database = vdb_commercial_116 startTime = Wed Feb 16 18:31:02 894 IST 2022 endTime = - responseTime = - numRows = 0 state = STOPPED completed = false queryBlockIds = [549274432] fields = [count] search conditions = [] filter conditions = [] order ByFields = [] ordered = false numOfFilteredTuples = 0 numOfDuplicatedTuples = 0 numOfSwappedTuples = 0 swapping = false memoryLimitReached = false mainTime = 0 ns viewType =
GROUP BY
indexes = [] JDBC WRAPPER ( name = _g92e5420c - 3785 - 431f - ba78 - 7604b2ebac50_d1348d6a - 76b0 - 4e93 - 8e74 - 224a93325972 database = admin startTime = * endTime = - responseTime = - numRows = 0 state = PROCESSING completed = false searchConditions = [] order ByFields = [] projectedFields = [count] additionalSubPlans = 0 additionalErroneousSubPlans = 0 JDBC ROUTE ( name = _g92e5420c - 3785 - 431f - ba78 - 7604b2ebac50_d1348d6a - 76b0 - 4e93 - 8e74 - 224a93325972 # 0 datasource = vdpcachedatasource datasource database = admin startTime = * endTime = - responseTime = - numRows = 0 state = PROCESSING completed = false SQLSentence =
SELECT
count( ? ) AS count_1
FROM
(
SELECT
s1.ds_source_system_desc AS ds_source_system_desc
FROM
(
(
SELECT
u0.gn_day_number AS gn_day_number, u0.gn_year_number AS gn_year_number, u0.gn_month_number AS gn_month_number, u0.ds_source_system_desc AS ds_source_system_desc, u0.cd_market_iso_code AS cd_market_iso_code
FROM
(
SELECT
t0.gn_day_number,
t0.gn_year_number,
t0.gn_month_number,
t0.ds_source_system_desc,
t0.cd_market_iso_code
FROM
c_iv2_ktv_exfca_union_fact117725670279976387632465283348362175 t0
WHERE
t0.rowstatus = ?
AND t0.expirationdate = ?
UNION ALL
SELECT
t1.gn_day_number,
t1.gn_year_number,
t1.gn_month_number,
t1.ds_source_system_desc,
t1.cd_market_iso_code
FROM
c_iv2_ktv_expsa_union_fact306755170358603730444606379126173382 t1
WHERE
t1.rowstatus = ?
AND t1.expirationdate = ?
UNION ALL
SELECT
t2.gn_day_number,
t2.gn_year_number,
t2.gn_month_number,
t2.ds_source_system_desc,
t2.cd_market_iso_code
FROM
c_iv2_ktv_maserati_union_fact208050255546932771625493015099572 t2
WHERE
t2.rowstatus = ?
AND t2.expirationdate = ?
UNION ALL
SELECT
t3.gn_day_number,
t3.gn_year_number,
t3.gn_month_number,
t3.ds_source_system_desc,
t3.cd_market_iso_code
FROM
c_iv2_ktv_apac_union_fact0636907405705869626561022268656205562 t3
WHERE
t3.rowstatus = ?
AND t3.expirationdate = ?
UNION ALL
SELECT
t4.gn_day_number,
t4.gn_year_number,
t4.gn_month_number,
t4.ds_source_system_desc,
t4.cd_market_iso_code
FROM
c_iv2_ktv_latam_union_fact492364201688037608611625188142497632 t4
WHERE
t4.rowstatus = ?
AND t4.expirationdate = ?
UNION ALL
SELECT
t5.gn_day_number,
t5.gn_year_number,
t5.gn_month_number,
t5.ds_source_system_desc,
t5.cd_market_iso_code
FROM
c_iv2_ktv_na_union_fact913649351535885618361572436703571990048 t5
WHERE
t5.rowstatus = ?
AND t5.expirationdate = ?
)
u0) s1
LEFT JOIN
(
SELECT
t6.cd_market AS cd_market,
t6.dt_year AS dt_year,
t6.dt_month AS dt_month,
t6.dt_day AS dt_day
FROM
c_p_bv_tb_calendar_data903510248835594758733803186042940450098 t6
WHERE
t6.rowstatus = ?
AND t6.expirationdate = ?
)
s2
ON ((s1.cd_market_iso_code = s2.cd_market
AND s1.gn_year_number = s2.dt_year
AND s1.gn_month_number = s2.dt_month
AND
CASE
WHEN
s1.gn_day_number = ?
THEN
?
ELSE
s1.gn_day_number
END
= s2.dt_day) )
)
LEFT JOIN
(
SELECT
t7.cd_market AS cd_market,
t7.dt_year AS dt_year,
t7.dt_month AS dt_month,
t7.dt_day AS dt_day
FROM
c_p_bv_tb_calendar_data903510248835594758733803186042940450098 t7
WHERE
t7.rowstatus = ?
AND t7.expirationdate = ?
)
s3
ON ((s1.gn_year_number = s3.dt_year
AND s1.gn_month_number = s3.dt_month
AND s3.cd_market = ?
AND coalesce(s2.cd_market, ? , null) = s3.cd_market
AND
CASE
WHEN
s1.gn_day_number = ?
THEN
?
ELSE
s1.gn_day_number
END
= s3.dt_day) )
)
s4 LIMIT 150 parameters = [1, V, 0, V, 0, V, 0, V, 0, V, 0, V, 0, V, 0, 0, 1, V, 0, XX, XX, 0, 1] SQLSentence info = DBUri = * ) ) ) )x
Please help with the possible options to optimize this, its very urgent and this is the max info I could collate. Thank much in advance for the help.
