oracle存储怎么跑,有个存储过程，今天莫名其妙跑不出来

本帖最后由 sqysl 于 2014-9-28 10:36 编辑

根据你发的awr，确定可能是过程p_rpt_goods_sales_new出了问题，具体在如下语句：

INSERT INTO RPT_GOODS_SALES_NEW ( SITE_ID, DATA_DATE, GOODS_SN, GOODS_LINK, GOODS_THUMB, STYLE_NAME, CATEGORY_NAME, SUB_CATEGORY_NAME, BRAND_NAME, SUPP_NAME, GOODS_PRICE, GOODS_QTY, GOODS_REVENUE, GOODS_CLICK, GOODS_EXPOSE, CART_GOODS, ORDER_GOODS, ORDER_SOURCE, INSERT_TIME, UPDATE_TIME, COUNTRY, MEDIUM_SOURCE ) SELECT T1.SITE_ID, ORACLE_TO_UNIX( :B1 ), T2.GOODS_SN, T3.GOODS_LINK, T3.GOODS_THUMB, T3.STYLE_NAME, T2.CATEGORY_NAME, T2.SUB_CATEGORY_NAME, T2.BRAND_NAME, T2.PROVIDE_NAME, T2.SHOP_PRICE, T1.GOODS_NUMBER, T1.GOODS_REVENUE, T1.GOODS_VISIT, T1.GOODS_IMPR, T1.ADD_NUM, T4.ORDER_COUNT, T1.ORDER_SN, TRUNC(SYSDATE)INSERT_TIME, TRUNC(SYSDATE), T1.COUNTRY, T1.MEDIUM_SOURCE FROM ( SELECT GOODS_ID, SITE_ID, SUM(GOODS_REVENUE) GOODS_REVENUE, SUM(GOODS_NUMBER) GOODS_NUMBER, ORDER_SN, COUNTRY , SUM(GOODS_IMPR) GOODS_IMPR, SUM(GOODS_VISIT) GOODS_VISIT, SUM(ADD_NUM) ADD_NUM, MEDIUM_SOURCE FROM ( SELECT NVL(B1.GOODS_ID, B2.GOODS_ID)GOODS_ID, NVL(B1.SITE_ID, B2.SITE_ID) SITE_ID, B1.GOODS_REVENUE, B1.GOODS_NUMBER, NVL(B1.ORDER_SN, '(unknown)') ORDER_SN, NVL(B1.COUNTRY_NAME, B2.COUNTRY)COUNTRY , B2.GOODS_IMPR, B3.GOODS_VISIT, B3.ADD_NUM, NVL(B1.MEDIUM_SOURCE, B2.MEDIUM_SOURCE) MEDIUM_SOURCE FROM ( SELECT A1.GOODS_ID, A1.SITE_ID, SUM(GOODS_REVENUE) GOODS_REVENUE, SUM(GOODS_NUMBER) GOODS_NUMBER, A2.ORDER_SN, COUNTRY, COUNTRY_NAME, MEDIUM_SOURCE FROM ( SELECT OG.GOODS_ID, OG.ORDER_ID, OG.SITE_ID, OG.GOODS_REVENUE, OG.GOODS_NUMBER FR OM DW_ORDER_GOODS_FACT OG WHERE TRUNC(OG.PAY_TIME) = :B1 AND OG.PAY_STATUS IN(1, 3) ) A1 INNER JOIN ( SELECT M1.ORDER_ID, M1.ORDER_SN, M1.SITE_ID, NVL(M2.COUNTRYNAME, M1.COUNTRY)COUNTRY, M1.COUNTRY_NAME, M1.MEDIUM_SOURCE FROM ( SELECT O.ORDER_ID, CASE WHEN O.SITE_ID=700 THEN 'm.jollychic.com' WHEN INSTR (O.ORDER_SN, 'B')>0 THEN 'jollychic.com' WHEN INSTR (O.ORDER_SN, 'F')>0 THEN 'jollychic.com' WHEN UPPER(O.ORDER_SN) LIKE 'VIP%' THEN 'jollychic.com' WHEN INSTR (O.ORDER_SN, 'SJ')>0 THEN 'app' ELSE '平台' END ORDER_SN, O.SITE_ID, O.COUNTRY , O.COUNTRY_NAME , NVL(O.MEDIUM, '(unknown)')||' / '||NVL(O.SOURCE, '(unknown)') MEDIUM_SOURCE FROM DW_ORDER_FACT O WHERE TRUNC(O.PAY_TIME) = :B1 AND O.PAY_STATUS IN(1, 3) )M1 LEFT JOIN COUNTRY M2 ON M1.COUNTRY=M2.COUNTRYCODE ) A2 ON A2.ORDER_ID = A1.ORDER_ID AND A2.SITE_ID = A1.SITE_ID GROUP BY A1.GOODS_ID, A1.SITE_ID, A2.ORDER_SN, COUNTRY, COUNTRY_NAME, MEDIUM_SOURCE )B1 LEFT JOIN ( SELECT GOODS_ID, SITE_ID, ORDER_SN, COUNTRY, MEDIUM_SOURCE , SUM(GOODS_IMPR) GOODS_IMPR FROM ( SELECT C1.GOODS_ID, C1.SITE_ID, NVL(C2.COUNTRY, '(unknown)') COUNTRY , C2.MEDIUM_SOURCE, C1.GOODS_IMPR, 'jollychic.com' ORDER_SN FROM ( SELECT I.GOODS_ID, I.SITE_ID, I.SESSION_ID, COUNT(*) GOODS_IMPR FROM DW_IMPRESSION_FACT I WHERE I.DATA_DATE=:B1 AND I.IS_SPIDER=0 GROUP BY I.GOODS_ID, I.SITE_ID, I.SESSION_ID )C1 INNER JOIN ( SELECT SESSION_ID, COUNTRY, SITE_ID, MEDIUM_SOURCE FROM ( SELECT S.SESSION_ID, S.COUNTRY, S.SITE_ID , NVL(S.MEDIUM, '(unknow n) ')||' / '||NVL(S.SOURCE, '(unknown)') MEDIUM_SOURCE FROM DW_SESSION_FACT S WHERE S.DATA_DATE=:B1 ) GROUP BY SESSION_ID, COUNTRY, SITE_ID, MEDIUM_SOURCE )C2 ON C1.SESSION_ID=C2.SESSION_ID AND C1.SITE_ID=C2.SITE_ID ) GROUP BY GOODS_ID, SITE_ID, COUNTRY, MEDIUM_SOURCE, ORDER_SN )B2 ON B1.GOODS_ID=B2.GOODS_ID AND B1.SITE_ID=B2.SITE_ID AND B1.COUNTRY=B2.COUNTRY AND B1.MEDIUM_SOURCE=B2.MEDIUM_SOURCE AND B1.ORDER_SN=B2.ORDER_SN LEFT JOIN ( SELECT GOODS_ID , ORDER_SN, SUM(GOODS_VISIT) GOODS_VISIT, SUM(ADD_NUM) ADD_NUM, SITE_ID, COUNTRY, MEDIUM_SOURCE FROM ( SELECT Z1.GOODS_ID , Z1.GOODS_VISIT, Z1.ADD_NUM, Z1.SITE_ID, Z2.COUNTRY, Z2.MEDIUM_SOURCE, 'jollychic.com' ORDER_SN FROM ( SELECT D1.GOODS_ID, D1.SESSION_ID, SUM(D1.GOODS_VISIT) GOODS_VISIT, SUM(D2.ADD_NUM) ADD_NUM, D1.SITE_ID FROM ( SELECT P.GOODS_ID, P.SITE_ID, P.SESSION_ID, P.PAGE_VIEW_ID, 1 GOODS_VISIT FROM DW_PAGE_VIEW_FACT P WHERE P.DATA_DATE=:B1 AND P.IS_SPIDER=0 AND P.IS_LANDING_PAGE=0 AND P.GOODS_ID IS NOT NULL )D1 LEFT JOIN ( SELECT E.SITE_ID , E.LABELS GOODS_ID , COUNT(*) ADD_NUM , E.PAGE_VIEW_ID , E.SESSION_ID FROM DW_EVENT_FACT E WHERE TRUNC(E.LOG_TIME)=:B1 AND E.EVENTYPE='Carts' AND E.ACTION= 'Add' GROUP BY SITE_ID, E.LABELS, E.PAGE_VIEW_ID, E.SESSION_ID ) D2 ON D1.SITE_ID=D2.SITE_ID AND CAST (D1.GOODS_ID AS VARCHAR2(64) )=D2.GOODS_ID AND D1.PAGE_VIEW_ID=D2.PAGE_VIEW_ID AND D1.SESSION_ID=D2.SESSION_ID GROUP BY D1.GOODS_ID, D1.SESSION_ID, D1.SITE_ID )Z1 INNER JOIN ( SELECT SESSION_ID, COUNTRY, SITE_ID, MEDIUM_SOUR CE FROM ( SELECT S.SESSION_ID, S.COUNTRY, S.SITE_ID , NVL(S.MEDIUM, '(unknown)')||' / '||NVL(S.SOURCE, '(unknown)') MEDIUM_SOURCE FROM DW_SESSION_FACT S WHERE S.DATA_DATE=:B1 ) GROUP BY SESSION_ID, COUNTRY, SITE_ID, MEDIUM_SOURCE )Z2 ON Z1.SESSION_ID=Z2.SESSION_ID AND Z1.SITE_ID=Z2.SITE_ID )GROUP BY GOODS_ID , SITE_ID, COUNTRY, MEDIUM_SOURCE , ORDER_SN )B3 ON B1.GOODS_ID =B3.GOODS_ID AND B1.SITE_ID=B3.SITE_ID AND B1.COUNTRY=B3.COUNTRY AND B1.MEDIUM_SOURCE=B3.MEDIUM_SOURCE AND B1.ORDER_SN=B3.ORDER_SN ) GROUP BY GOODS_ID, SITE_ID, COUNTRY, MEDIUM_SOURCE, ORDER_SN )T1 LEFT JOIN ( SELECT G.GOODS_SN, G.GOODS_ID, NVL(G.CATEGORY_NAME, '(unknown)')CATEGORY_NAME, NVL(G.SUB_CATEGORY_NAME, '(unknown)') SUB_CATEGORY_NAME, NVL(G.BRAND_NAME, '(unknown)') BRAND_NAME, NVL(G.PROVIDE_NAME, '(unknown)') PROVIDE_NAME, G.SHOP_PRICE, G.SITE_ID FROM DW_GOODS_ON_SALE G WHERE G.DATA_DATE= :B1 )T2 ON T1.GOODS_ID=T2.GOODS_ID AND T1.SITE_ID=T2.SITE_ID LEFT JOIN ( SELECT T1.*, NVL(T2.DESCRIPTION, '(unknown)') STYLE_NAME FROM ( SELECT B.GOODS_ID, 'http://www.jollychic.com/p/'|| REGEXP_REPLACE(REPLACE (REPLACE ( REPLACE ( REPLACE (FORM_URL_ENCODE(REGEXP_REPLACE(LOWER(REPLACE(B.GOODS_NAME, ' ', '-')) , '/[\.|\/|\?|&|(|)|\/|\+|\\\||"|, ]+/', '-'), 'UTF-8'), '+', '-'), '%', '-' ), '__', '-' ), '-.', '-' ), '/[-]+/', '-') || '-g'|| B.GOODS_ID||'.html' GOODS_LINK , 'http://img.jollychic.com/uploads/jollyimg'||B.GOODS_THUMB GOODS_THUMB, B.STYLE_ID , 400 SITE_ID FROM DW_GOODS_FACT B )T1 LEF T JOIN DW_GOODS_STYLE T2 ON T1.STYLE_ID=T2.STYLE_ID ) T3 ON T1.GOODS_ID=T3.GOODS_ID LEFT JOIN ( SELECT A1.GOODS_ID, A1.SITE_ID, A2.ORDER_SN, COUNTRY , MEDIUM_SOURCE, SUM(A1.GOODS_NUMBER) ORDER_COUNT FROM ( SELECT OG.GOODS_ID, OG.ORDER_ID, OG.SITE_ID, OG.GOODS_NUMBER FROM DW_ORDER_GOODS_FACT OG WHERE TRUNC(OG.ADD_TIME) = :B1 AND OG.PAY_STATUS = 0 ) A1 INNER JOIN ( SELECT M1.ORDER_ID, M1.ORDER_SN, M1.SITE_ID, NVL(M1.COUNTRY_NAME, NVL(M2.COUNTRYNAME, M1.COUNTRY) ) COUNTRY, M1.MEDIUM_SOURCE FROM ( SELECT O.ORDER_ID, CASE WHEN O.SITE_ID=700 THEN 'm.jollychic.com' WHEN INSTR (O.ORDER_SN, 'B')>0 THEN 'jollychic.com' WHEN INSTR (O.ORDER_SN, 'F')>0 THEN 'jollychic.com' WHEN UPPER(O.ORDER_SN) LIKE 'VIP%' THEN 'jollychic.com' WHEN INSTR (O.ORDER_SN, 'SJ')>0 THEN 'app' ELSE '平台' END ORDER_SN, O.SITE_ID, O.COUNTRY , O.COUNTRY_NAME , NVL(O.MEDIUM, '(unknown)')||' / '||NVL(O.SOURCE, '(unknown)') MEDIUM_SOURCE FROM DW_ORDER_FACT O WHERE TRUNC(O.ADD_TIME) = :B1 AND O.PAY_STATUS=0 )M1 LEFT JOIN COUNTRY M2 ON M1.COUNTRY=M2.COUNTRYCODE ) A2 ON A2.ORDER_ID = A1.ORDER_ID AND A2.SITE_ID = A1.SITE_ID GROUP BY A1.GOODS_ID, A1.SITE_ID, A2.ORDER_SN, COUNTRY, MEDIUM_SOURCE ) T4 ON T1.SITE_ID=T4.SITE_ID AND T1.GOODS_ID=T4.GOODS_ID AND T1.ORDER_SN=T4.ORDER_SN AND T1.COUNTRY=T4.COUNTRY AND T1.MEDIUM_SOURCE=T4.MEDIUM_SOURCE

应该是该语句消耗了大量的CPU，看看这个语句吧，没看到执行计划和数据情况，不好说具体原因。