Request exceeded high limit : error in vertica

abhi112abhi112
in General Discussion

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

Comments

  • K_KrutiyK_Krutiy

     

    can you share actual error message and actual request ?

  • abhi112abhi112

    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_KrutiyK_Krutiy

     

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

     

    Can you please post it again.

  • abhi112abhi112

    This was the error/reason itself.

     

    "Request exceeded high limit"

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Format
SpoilerCodeQuote
Heading 2Heading 1
Emoji
:):D:(;):/:o:s:p:'(:|B):#:*<3o:)>:)
Url
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file
Home • General DiscussionComment As ...