We're Moving!

The Vertica Forum is moving to a new OpenText Analytics Database (Vertica) Community.

Join us there to post discussion topics, learn about

product releases, share tips, access the blog, and much more.

Create My New Community Account Now


Request exceeded high limit : error in vertica — Vertica Forum

Request exceeded high limit : error in vertica

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

  •  

    can you share actual error message and actual request ?

  • 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

     

  •  

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

     

    Can you please post it again.

  • This was the error/reason itself.

     

    "Request exceeded high limit"

Leave a Comment

BoldItalicStrikethroughOrdered listUnordered list
Emoji
Image
Align leftAlign centerAlign rightToggle HTML viewToggle full pageToggle lights
Drop image/file