Request exceeded high limit : error in vertica

abhi112

Hello All,

 

There is a query (report) which used to run fine several days ago is now failing due to this reason "Request exceeded high limit" .

 

I believe it is asking for more resources (memory) and getting failed.

I have checked that data has not increased by then.

Stats are also fine.

 

Below are the details of the resource_pool to which user is assigned and running the report.

 

 

 

(memorysize, maxmemorysize, executionparallelism, priority, runtimepriority, runtimeprioritythreshold, queuetimeout, plannedconcurrency, maxconcurrency, runtimecap, singleinitiator) VALUES
('0%', '', 'AUTO', 11, 'LOW', 2, 1800, 'AUTO', NULL, NULL, 'f')

 

What should be my approach on this?

Shall i run DBD on that?

 

Kindly advise.

 

Thanks,

Abhishek

K_Krutiy

 

can you share actual error message and actual request ?

abhi112

This is the reason/error which i captured from MC.

 

Request is as below (Table/schema name have been masked):

 

/* CES9 changes- remapped Order Action Type='RG' to 'CH', replaced FCT_ORDER_ACTION & FCT_ORDER_ITEM with FCTV9 */

SELECT
DT_DIM_CUSTOMER_LATEST.CUSTOMER_ID,
d.ds.AP_ID,
d.ds.SALES_PERSON_STAFF_ID,
sch.tablea.FORM_SIGN_DATETIME,
sch.tablea.ORDER_CREATION_DATETIME,
sch.tablea.VT_START_DATE,
sch.tablea.SALES_PERSON_CODE,
sch.tablea.RETAILER_CODE,
DT_FCT_ORDER_ITEM.PREFERRED_LOGIN_NAME_2,
INITCAPB(sch.tablea.SALES_CHANNEL_WORKGROUP) SALES_CHANNEL_WORKGROUP,
sch.tablea.CASE_ID,
sch.tablea.ORDER_ACTION_TYPE,
f.foa.ORDER_ACTION_REASON_CODE,
INITCAPB (d.ds.SUBSCRIPTION_MODE) SUBSCRIPTION_MODE,
sch.tablea.ACCOUNT_NO,
sch.tablea.SMART_CARD_NO,
d.ds.SUBSCRIPTION_ID,
sch.tablea.CAMPAIGN_CODE,
INITCAPB (d.da.ACCOUNT_NAME) ACCOUNT_NAME,
d.da.CUST_IDENTITY_NUM,
INITCAPB (DT_DIM_CUSTOMER_LATEST.CUST_TYPE) CUST_TYPE,
INITCAPB (DT_DIM_CUSTOMER_LATEST.CUST_CATEGORY) CUST_CATEGORY,
d.da.BIRTH_DATE,
INITCAPB (d.da.GENDER) GENDER,
CASE
WHEN (INITCAPB (d.da.RACE) = 'M') Then 'M'
WHEN (INITCAPB (d.da.RACE) = 'Y') Then 'Y'
WHEN (INITCAPB (d.da.RACE) ='A') Then 'A'
ELSE 'Others'
END RACE,
INITCAPB (d.da.SERVICE_ADD_1) SERVICE_ADD_1,
INITCAPB (d.da.SERVICE_ADD_2) SERVICE_ADD_2,
INITCAPB (d.da.SERVICE_ADD_3) SERVICE_ADD_3,
INITCAPB (d.da.SERVICE_ADD_4) SERVICE_ADD_4,
INITCAPB (d.da.SERVICE_ADD_5) SERVICE_ADD_5,
d.da.SERVICE_POSTCODE,
INITCAPB (d.da.SERVICE_CITY) SERVICE_CITY,
INITCAPB (d.da.SERVICE_STATE) SERVICE_STATE,
INITCAPB (d.da.SERVICE_REGION) SERVICE_REGION,
d.ds.SUBSCRIPTION_STATUS_CODE,
sch.tablea.DISCOUNT_OFFER,
INITCAPB(sch.tablea.BROADBAND_PACKAGE_NAME) BROADBAND_PACKAGE_NAME,
SUM(sch.tablea.BROADBAND_PACKAGE_PRICE) BROADBAND_PACKAGE_PRICE,
INITCAPB(sch.tablea.TV_PACKAGE_NAME) TV_PACKAGE_NAME,
SUM(sch.tablea.AS_PACKAGE_PRICE) AS_PACKAGE_PRICE,
CASE WHEN tablea.ORDER_ACTION_TYPE = 'CH' THEN INITCAPB(DT_OLD_SUBS_PKG_BEFORE_VT.SUBSCRIBED_PACKAGES)
ELSE NULL END AS PREVIOUS_TV_PACKAGE_NAME,
SUM(CASE WHEN tablea.ORDER_ACTION_TYPE = 'CH' THEN DT_OLD_SUBS_PKG_BEFORE_VT.TOTAL_GROSS_SUBS_AMT
ELSE NULL END) AS PREVIOUS_TV_PACKAGE_PRICE,
SUM(CASE WHEN tablea.ORDER_ACTION_TYPE = 'CH' THEN DT_OLD_SUBS_PKG_BEFORE_VT.HD_PVR_PRICE
ELSE NULL END) AS PREVIOUS_HD_PVR_PRICE,
SUM(CASE WHEN C.SOC_CODE='8449495' AND upper(tablea.TV_PACKAGE_NAME) LIKE '%SUPER PACK%' AND upper(tablea.TV_PACKAGE_NAME) LIKE '%AOTG%' THEN (CASE WHEN upper(tablea.TV_PACKAGE_NAME) LIKE '%SUPER PACK%' AND tablea.DISCOUNT_OFFER IS NULL THEN tablea.AS_PACKAGE_PRICE -25 ELSE tablea.AS_PACKAGE_PRICE END )- D.Other_gross_subs_amt
WHEN upper(tablea.TV_PACKAGE_NAME) LIKE '%SUPER PACK%' AND tablea.DISCOUNT_OFFER IS NULL THEN tablea.AS_PACKAGE_PRICE -25
ELSE tablea.AS_PACKAGE_PRICE END ) AS_Package_Price_Final,
DT_DIM_CUST_EXTENDED_INFO.EMAIL,
Fct_comm.COMMITMENT_END_DATE ,
First_VT_START_DATE,
DT_DIM_CUSTOMER_LATEST.Billing_frequency,
DT_DIM_ACC_PAYMENT_METHOD_LATEST.Payment_method_code ,
DD.CONNECTED_FLAG,
DD.FIRST_CONNECTED_DATE,
DD.LAST_CONNECTED_DATE,
BROADBAND_provider

FROM
f.foa RIGHT JOIN sch.tablea ON (f.foa.ORDER_ACTION_ID=sch.tablea.ORDER_ACTION_ID)
LEFT JOIN d.dis ON d.dis.SUBSCRIPTION_ID=sch.tablea.SUBSCRIPTION_ID and d.dis.Current_record_flag=true
LEFT OUTER JOIN (
SELECT DISTINCT
ORDER_ACTION_ID
,CASE WHEN SUBSTR(foi.ITEM_ATRS_LIST,instr(foi.ITEM_ATRS_LIST, 'Preferred_LoginName_2+')+22) is not null and
INSTR(SUBSTR(foi.ITEM_ATRS_LIST,instr(foi.ITEM_ATRS_LIST, 'Preferred_LoginName_2+')+22),';') > 0 THEN SUBSTR(foi.ITEM_ATRS_LIST,instr(foi.ITEM_ATRS_LIST, 'Preferred_LoginName_2+')+22,INSTR(SUBSTR(foi.ITEM_ATRS_LIST,instr(foi.ITEM_ATRS_LIST, 'Preferred_LoginName_2+')+22),';')-1)
WHEN SUBSTR(foi.ITEM_ATRS_LIST,instr(foi.ITEM_ATRS_LIST, 'Preferred_LoginName_2+')+22) is not null and
INSTR(SUBSTR(foi.ITEM_ATRS_LIST,instr(foi.ITEM_ATRS_LIST, 'Preferred_LoginName_2+')+22),';') = 0 THEN SUBSTR(foi.ITEM_ATRS_LIST,instr(foi.ITEM_ATRS_LIST, 'Preferred_LoginName_2+')+22)
ELSE NULL END AS PREFERRED_LOGIN_NAME_2
FROM f.foi
WHERE SERVICE_TYPE = 'BROD' AND ORDER_ITEM_STATE='AS' and ORDER_ITEM_STATUS = 'AC'
) DT_FCT_ORDER_ITEM ON (sch.tablea.ORDER_ACTION_ID=DT_FCT_ORDER_ITEM.ORDER_ACTION_ID)
LEFT OUTER JOIN (
SELECT MINI_VT_START_DATE,B.SUBSCRIPTION_ID,SUBSCRIBED_PACKAGES,TOTAL_GROSS_SUBS_AMT,B.HD_PVR_PRICE
FROM
( SELECT MAX.EFFECTIVE_START_DATE,MAX.SUBSCRIPTION_ID,MAX.MINI_VT_START_DATE,SUM(RC_CHARGE_AMT) HD_PVR_PRICE
FROM
(SELECT MAX(EFFECTIVE_START_DATE) EFFECTIVE_START_DATE,DT_VT_MINI_START_DT_SUBSCRIPTION.SUBSCRIPTION_ID,MINI_VT_START_DATE
FROM
(SELECT MIN(VT_START_DATE) AS MINI_VT_START_DATE,SUBSCRIPTION_ID
FROM sch.tablea
GROUP BY SUBSCRIPTION_ID) DT_VT_MINI_START_DT_SUBSCRIPTION
INNER JOIN ( SELECT SUBSCRIPTION_ID,SUBSCRIBED_PACKAGES,EFFECTIVE_START_DATE,EFFECTIVE_END_DATE,TOTAL_GROSS_SUBS_AMT
FROM f.fsrga ) DT_SUBS_RC_GROSS_AMT ON (DT_VT_MINI_START_DT_SUBSCRIPTION.SUBSCRIPTION_ID=DT_SUBS_RC_GROSS_AMT.SUBSCRIPTION_ID
AND DT_SUBS_RC_GROSS_AMT.EFFECTIVE_START_DATE <= DT_VT_MINI_START_DT_SUBSCRIPTION.MINI_VT_START_DATE -1
AND DT_SUBS_RC_GROSS_AMT.EFFECTIVE_END_DATE <= DT_VT_MINI_START_DT_SUBSCRIPTION.MINI_VT_START_DATE )
WHERE UPPER(SUBSCRIBED_PACKAGES) NOT LIKE '%MBPS%'
GROUP BY DT_VT_MINI_START_DT_SUBSCRIPTION.SUBSCRIPTION_ID,MINI_VT_START_DATE) MAX
LEFT OUTER JOIN (SELECT SUBSCRIPTION_ID,EFFECTIVE_DATE,EXPIRATION_DATE,RC_CHARGE_AMT FROM f.frc
WHERE CHARGE_CODE IN ('XTVIRES','HDSFRES')) frc ON frc.SUBSCRIPTION_ID = MAX.SUBSCRIPTION_ID
AND MAX.EFFECTIVE_START_DATE BETWEEN frc.EFFECTIVE_DATE AND frc.EXPIRATION_DATE
GROUP BY MAX.EFFECTIVE_START_DATE,MAX.SUBSCRIPTION_ID,MAX.MINI_VT_START_DATE
) B
INNER JOIN f.fsrga ON fsrga.SUBSCRIPTION_ID = B.SUBSCRIPTION_ID AND fsrga.EFFECTIVE_START_DATE = B.EFFECTIVE_START_DATE
) DT_OLD_SUBS_PKG_BEFORE_VT ON (sch.tablea.SUBSCRIPTION_ID=DT_OLD_SUBS_PKG_BEFORE_VT.SUBSCRIPTION_ID)
RIGHT OUTER JOIN d.da ON (d.da.ACCOUNT_NO=sch.tablea.ACCOUNT_NO AND d.da.RECORD_START_DATE <= TO_DATE(sch.tablea.DATE_KEY,'YYYYMMDD') AND (d.da.RECORD_END_DATE IS NULL OR d.da.RECORD_END_DATE > TO_DATE(sch.tablea.DATE_KEY,'YYYYMMDD'))
)
RIGHT OUTER JOIN d.ds ON (d.ds.SUBSCRIPTION_ID=sch.tablea.SUBSCRIPTION_ID AND d.ds.RECORD_START_DATE <= TO_DATE(sch.tablea.DATE_KEY,'YYYYMMDD')
AND (d.ds.RECORD_END_DATE IS NULL OR d.ds.RECORD_END_DATE > TO_DATE(sch.tablea.DATE_KEY,'YYYYMMDD'))
)
RIGHT OUTER JOIN (
SELECT * FROM d.DIM_CUSTOMER
WHERE d.DIM_CUSTOMER.CURRENT_RECORD_FLAG=TRUE
) DT_DIM_CUSTOMER_LATEST ON (sch.tablea.CUSTOMER_ID=DT_DIM_CUSTOMER_LATEST.CUSTOMER_ID)
LEFT JOIN (
SELECT * from d.DIM_CUST_EXTENDED_INFO where d.DIM_CUST_EXTENDED_INFO.CURRENT_RECORD_FLAG=true
) DT_DIM_CUST_EXTENDED_INFO ON (DT_DIM_CUST_EXTENDED_INFO.ACCOUNT_NO=d.da.ACCOUNT_NO)
left JOIN f.FCT_SUBS_BILLING_OFFER C ON C.SUBSCRIPTION_ID=sch.tablea.SUBSCRIPTION_ID AND C.SOC_CODE='8449495'
Left Join f.fsrga D ON D.SUBSCRIPTION_ID=sch.tablea.SUBSCRIPTION_ID AND D.EFFECTIVE_START_DATE <= TO_DATE(sch.tablea.DATE_KEY,'YYYYMMDD') AND (D.EFFECTIVE_END_DATE > TO_DATE(sch.tablea.DATE_KEY,'YYYYMMDD') OR D.EFFECTIVE_END_DATE IS NULL)
Left Join (select ORDER_ACTION_ID,
CASE WHEN (upper(substr(ITEM_ATRS_LIST, 1, 8000)) ~~ '%;COMMITMENTENDDATE%') THEN btrim(substr(regexp_substr(regexp_substr(upper(substr(ITEM_ATRS_LIST, 1, 8000)), '[^;]+', instr(upper(substr(ITEM_ATRS_LIST, 1, 8000)), ';COMMITMENTENDDATE', 1, 1), 1, '', 0), '[^+]+', 1, 2, '', 0), 1, 500)) ELSE NULL END AS COMMITMENT_END_DATE
from f.foi
where
service_type='COMM') Fct_comm on (Fct_comm.order_action_id=sch.tablea.Order_action_id)

LEFT JOIN (SELECT MIN(VT_START_DATE) AS First_VT_START_DATE,SUBSCRIPTION_ID
FROM sch.tablea
GROUP BY SUBSCRIPTION_ID) FIRST_VT_START_DATE ON (sch.tablea.SUBSCRIPTION_ID=FIRST_VT_START_DATE.Subscription_id)
Left Join (
SELECT *
FROM d.DIM_ACC_PAYMENT_METHOD

) DT_DIM_ACC_PAYMENT_METHOD_LATEST ON (d.da.ACCOUNT_NO=DT_DIM_ACC_PAYMENT_METHOD_LATEST.ACCOUNT_NO AND DT_DIM_ACC_PAYMENT_METHOD_LATEST.RECORD_START_DATE <= TO_DATE(sch.tablea.DATE_KEY,'YYYYMMDD') AND (DT_DIM_ACC_PAYMENT_METHOD_LATEST.RECORD_END_DATE IS NULL OR DT_DIM_ACC_PAYMENT_METHOD_LATEST.RECORD_END_DATE > TO_DATE(sch.tablea.DATE_KEY,'YYYYMMDD'))
)
LEFT JOIN ( Select CASE WHEN MIN(REQUEST_DATETIME) IS NOT NULL THEN 'YES' ELSE 'NO' END AS CONNECTED_FLAG,
MIN(REQUEST_DATETIME) AS FIRST_CONNECTED_DATE,
MAX(REQUEST_DATETIME) AS LAST_CONNECTED_DATE
,SMART_CARD_NO from f.FCT_CONNECTED_STB group by SMART_CARD_NO) DD ON DD.SMART_CARD_NO=sch.tablea.SMART_CARD_NO
WHERE
TO_DATE(TO_CHAR(sch.tablea.VT_START_DATE,'YYYYMMDD'),'YYYYMMDD') BETWEEN '20160810' AND '20160811'
-- AND (sch.tablea.VT_START_DATE=MINI_VT_START_DATE OR MINI_VT_START_DATE IS NULL)

GROUP BY
DT_DIM_CUSTOMER_LATEST.CUSTOMER_ID,
d.ds.AP_ID,
d.ds.SALES_PERSON_STAFF_ID,
sch.tablea.FORM_SIGN_DATETIME,
sch.tablea.ORDER_CREATION_DATETIME,
sch.tablea.VT_START_DATE,
sch.tablea.SALES_PERSON_CODE,
sch.tablea.RETAILER_CODE,
DT_FCT_ORDER_ITEM.PREFERRED_LOGIN_NAME_2,
INITCAPB(sch.tablea.SALES_CHANNEL_WORKGROUP),
sch.tablea.CASE_ID,
sch.tablea.ORDER_ACTION_TYPE,
f.foa.ORDER_ACTION_REASON_CODE,
INITCAPB (d.ds.SUBSCRIPTION_MODE) ,
sch.tablea.ACCOUNT_NO,
sch.tablea.SMART_CARD_NO,
d.ds.SUBSCRIPTION_ID,
sch.tablea.CAMPAIGN_CODE,
INITCAPB (d.da.ACCOUNT_NAME) ,
d.da.CUST_IDENTITY_NUM,
INITCAPB (DT_DIM_CUSTOMER_LATEST.CUST_TYPE) ,
INITCAPB (DT_DIM_CUSTOMER_LATEST.CUST_CATEGORY),
d.da.BIRTH_DATE,
INITCAPB (d.da.GENDER),
CASE
WHEN (INITCAPB (d.da.RACE) = 'M') Then 'M'
WHEN (INITCAPB (d.da.RACE) = 'Y') Then 'Y'
WHEN (INITCAPB (d.da.RACE) ='A') Then 'A'
ELSE 'Others'
END,
INITCAPB (d.da.SERVICE_ADD_1) ,
INITCAPB (d.da.SERVICE_ADD_2) ,
INITCAPB (d.da.SERVICE_ADD_3) ,
INITCAPB (d.da.SERVICE_ADD_4) ,
INITCAPB (d.da.SERVICE_ADD_5) ,
d.da.SERVICE_POSTCODE,
INITCAPB (d.da.SERVICE_CITY),
INITCAPB (d.da.SERVICE_STATE),
INITCAPB (d.da.SERVICE_REGION),
d.ds.SUBSCRIPTION_STATUS_CODE,
sch.tablea.DISCOUNT_OFFER,
INITCAPB(sch.tablea.BROADBAND_PACKAGE_NAME) ,
INITCAPB(sch.tablea.TV_PACKAGE_NAME),
INITCAPB(DT_OLD_SUBS_PKG_BEFORE_VT.SUBSCRIBED_PACKAGES) , DT_DIM_CUST_EXTENDED_INFO.EMAIL,
Fct_comm.COMMITMENT_END_DATE ,
First_VT_START_DATE,
DT_DIM_CUSTOMER_LATEST.Billing_frequency,
DT_DIM_ACC_PAYMENT_METHOD_LATEST.Payment_method_code ,
DD.CONNECTED_FLAG,
DD.FIRST_CONNECTED_DATE,
DD.LAST_CONNECTED_DATE,
BROADBAND_provider

 

K_Krutiy

 

Looks like reason/error did not get in to reply.

 

Can you please post it again.

abhi112

This was the error/reason itself.

 

"Request exceeded high limit"