You are viewing a plain text version of this content. The canonical link for it is here.
Posted to issues@trafodion.apache.org by "Alice Chen (JIRA)" <ji...@apache.org> on 2015/07/22 20:18:24 UTC
[jira] [Created] (TRAFODION-841) LP Bug: 1404271 - Regression in
HPIT workload due NJ costing checkin
Alice Chen created TRAFODION-841:
------------------------------------
Summary: LP Bug: 1404271 - Regression in HPIT workload due NJ costing checkin
Key: TRAFODION-841
URL: https://issues.apache.org/jira/browse/TRAFODION-841
Project: Apache Trafodion
Issue Type: Bug
Components: sql-cmp
Reporter: Ravisha Neelakanthappa
Assignee: Ravisha Neelakanthappa
Priority: Critical
Fix For: 1.0 (pre-incubation)
From: Hall, Gary
We’ve experienced a very large regression in our BENCH_HPIT test set as of the 141209 build, and the “NJ costing changes” is suspect, particularly since the five queries in the test set that have increased in response time by severalfold are also showing plan changes that have switched to using nested joins as of this build. What can we do to help you with this item?
Here’s a copy of one of the queries that is experiencing nearly over triple the response times. I’ve attached the corresponding full explain plans.
SQL>explain options 'f' select /* HPIT Query 05 using template Q5 */ totals.Customer, totals.Prodline, totals.K_Dol K_Dollars, totals.Prior_K_Dol Prior_K_Dollars, totals.Total_K_Dol Total_K_Dollars from ( select cust.CUST_AMID_LVL_4_NM as Customer, prodln.PROD_LN_ID as Prodline, SUM(case when dmth.FISC_YR_QTR_CD = '2007Q2' then SLS_NET_US_DLR_AM/1000 else 0 end) K_Dol, SUM(case when dmth.FISC_YR_QTR_CD = '2007Q1' then SLS_NET_US_DLR_AM/1000 else 0 end) Prior_K_Dol, SUM(case when dmth.FISC_YR_NM = 'FY2007' then SLS_NET_US_DLR_AM/1000 else 0 end) Total_K_Dol from HPIT_TOT_END_CUST_SLS_F fact, HPIT_DT_MTH_D dmth, HPIT_CUST_ACCT_HIER_D cust, HPIT_PROD_LN_D prodln where dmth.DT_MTH_KY = fact.DT_MTH_KY AND fact.END_CUST_ACCT_HIER_KY = cust.CUST_ACCT_HIER_KY AND fact.PROD_LN_KY = prodln.PROD_LN_KY AND dmth.fisc_yr_nm = 'FY2007' GROUP BY cust.CUST_AMID_LVL_4_NM, prodln.PROD_LN_ID ) totals ORDER BY Total_K_Dol desc;
OLD PLAN:
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
15 . 16 root 7.17E+003
14 . 15 esp_exchange 1:20(hash2) (m) 7.17E+003
13 . 14 sort 7.17E+003
12 . 13 hash_partial_groupby 7.17E+003
11 . 12 esp_exchange 20(hash2):20(hash2) 7.17E+003
10 . 11 hash_partial_groupby 7.17E+003
9 8 10 hybrid_hash_join 5.63E+005
. . 9 trafodion_scan HPIT_CUST_ACCT_HIER_ 1.85E+006
7 2 8 hybrid_hash_join u 5.63E+005
6 4 7 hybrid_hash_join u 5.63E+005
5 . 6 esp_exchange 20(hash2):10(hash2) 2.25E+006
. . 5 trafodion_scan HPIT_TOT_END_CUST_SL 2.25E+006
3 . 4 esp_exchange 20(rep-b):1 (m) 1.20E+001
. . 3 trafodion_scan HPIT_DT_MTH_D 1.20E+001
1 . 2 esp_exchange 20(rep-b):1 (m) 3.40E+002
. . 1 trafodion_scan HPIT_PROD_LN_D 3.40E+002
NEW PLAN:
LC RC OP OPERATOR OPT DESCRIPTION CARD
---- ---- ---- -------------------- -------- -------------------- ---------
15 . 16 root 7.17E+003
14 . 15 esp_exchange 1:20(hash2) (m) 7.17E+003
13 . 14 sort 7.17E+003
12 . 13 hash_partial_groupby 7.17E+003
11 . 12 esp_exchange 20(hash2):20(hash2) 7.17E+003
10 . 11 hash_partial_groupby 7.17E+003
8 9 10 nested_join 5.63E+005
. . 9 trafodion_scan HPIT_CUST_ACCT_HIER_ 1.00E+000
7 2 8 hybrid_hash_join u 5.63E+005
6 4 7 hybrid_hash_join u 5.63E+005
5 . 6 esp_exchange 20(hash2):10(hash2) 2.25E+006
. . 5 trafodion_scan HPIT_TOT_END_CUST_SL 2.25E+006
3 . 4 esp_exchange 20(rep-b):1 (m) 1.20E+001
. . 3 trafodion_scan HPIT_DT_MTH_D 1.20E+001
1 . 2 esp_exchange 20(rep-b):1 (m) 3.40E+002
. . 1 trafodion_scan HPIT_PROD_LN_D 3.40E+002
--
This message was sent by Atlassian JIRA
(v6.3.4#6332)