At 02:29 PM 8/6/2003 -0800, you wrote:
Henry - Thanks. I feel like I'm getting an education today on the Oracle Wait Interface today. Nothing like a live problem for everything to make sense. Thanks for pointing out that I could find the table. It is our WKLYJOBFACT table. Not one we suspected. We have been doing an EXPLAIN PLAN by extracting the SQL from the stored procedure. I posted that. But when the SQL is extracted from the stored procedure, it runs just fine. Does anyone know how to get the explain plan that the PL/SQL procedure is seeing? Thanks to everyone for helping narrow the problem down this far. It has kept me from rebuilding the table which probably would have accomplished nothing.
[...]
> SQL> explain plan for > 2 INSERT /*+ APPEND */INTO CURRJOBFACT NOLOGGING > 3 ( bunch of columns ) > 21 SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */ > 22 CJS.JOBNBR,CJS.SOURCEFISCALYEAR,CJS.LIFETOUCHID,MD.PROGRAMID, > 23 CJS.MARKETINGCODE,CJS.PLANTRECEIPTDATE,CJS.PHOTOGRAPHYDATE, > 24 > CJS.SHIPDATE,CJS.SELLINGMETHODCODE,CJS.MDRPRIMARYID,CJS.SUBPROGRAMCODE, > 25 CJS.TERRCODE,CJS.SUBTERRCODE,CJS.BIDIND,CJS.PDKIND,CJS.PDKPARTNBR, > 26 CJS.RETAKEIND, > 27 > TO_NUMBER(TRANSLATE(UPPER(SUBSTR(CJS.JOBNBR,10,1)),'ABCDEFGHIJKLMNOPQRSTUV > , > 28 DECODE(TO_DATE(CJS.SHIPDATE,'YYYY/MM/DD'), NULL ,'N','Y'), > 29 DECODE(NVL(GREATEST(CJS.YTDCASHRECEIVEDAMT,0),0),0, > 30 DECODE(CJS.PAYSTATUSIND,'F','Y','N'),'Y') PAIDJOBIND, > 31 CJS.PAYSTATUSIND,PS.PAIDSHIPPEDJOBIND, > 32 DECODE(SUBSTR(CJS.JOBNBR,9,1),'I','Y','N') PREJOBIND,CJS.PLANTCODE, > 33 > CJS.SHOTQTY,PS.MTDSHIPPEDPKGQTY,PS.MTDCHARGEBACKAMT,PS.MTDTERRCMSNAMT, > 34 NVL(PS.MTDTERRCMSNAMT,0) - NVL(PS.MTDCHARGEBACKAMT,0) > ,PS.MTDCASHRECEIVEDA, > 35 PS.MTDCASHRETAINEDAMT,PS.MTDESTACCTCMSNAMT,PS.MTDACCTCMSNPAIDAMT, > 36 > PS.MTDGROSSCASHAMT,PS.MTDSALESTAXAMT,CJS.YTDSHIPPEDPKGQTY,CJS.YTDCHARGEBAC > , > 37 CJS.YTDTERRCMSNAMT,CJS.YTDTERREARNINGSAMT,CJS.YTDCASHRECEIVEDAMT, > 38 CJS.YTDCASHRETAINEDAMT,CJS.YTDESTACCTCMSNAMT,CJS.YTDACCTCMSNPAIDAMT, > 39 CJS.YTDCASHRECEIVEDAMT +YTDESTACCTCMSNAMT + YTDACCTCMSNPAIDAMT , > 40 CJS.YTDSALESTAXAMT,CJS.YTDPERFECTSALEAMT,PS.PRELIMYTDSHIPPEDPKGQTY, > 41 PS.PRELIMYTDCHARGEBACKAMT,PS.PRELIMYTDTERRCMSNAMT, > 42 PS.PRELIMYTDTERRCMSNAMT - PS.PRELIMYTDCHARGEBACKAMT > ,PS.PRELIMYTDCASHRECEI, > 43 PS.PRELIMYTDCASHRETAINEDAMT,PS.PRELIMYTDESTACCTCMSNAMT, > 44 > PS.PRELIMYTDACCTCMSNPAIDAMT,PS.PRELIMYTDGROSSCASHAMT,PS.PRELIMYTDSALESTAXA > , > 45 PS.PRELIMYTDPERFECTSALEAMT,CJS.YTDJTEPAIDPKGQTY,CJS.YTDPAIDPKGQTY, > 46 CJS.YTDUNPAIDPKGQTY,CJS.YTDXNOPURCHASEQTY,PS.YTDPAIDPKGSHIPQTY, > 47 > PS.YTDUNPAIDPKGSHIPQTY,CJS.PROOFPOSEQTY,CJS.PROOFCOUNTQTY,CJS.EXTRACTDATE, > 48 CJS.ORIGINALRECEIVEDDATE,PS.CMSNSTATUSCODE,WV1.FIRSTPOSITIVECASHDATE, > 49 DECODE(C.LIFETOUCHID, NULL ,'New','Retained') RENEWALSTATUSCODE, > 50 NVL(WV.PRELIMYTDESTACCTCMSNAMT, > 51 DECODE(NVL(PS.PRELIMYTDESTACCTCMSNAMT,0),0, NULL > ,PS.PRELIMYTDESTACCTCMSNA, > 52 APC.AVGPKGPRICE > 53 FROM CURRJOB_STAGE CJS,PERIOD_STAGE PS,MARKETINGDIM MD, > 54 (SELECT A1.JOBNBR,DECODE(SUM(PAIDOFFERQTY),0,0, > 55 SUM(A1.OFFERPRICEAMT * A1.PAIDOFFERQTY ) / SUM(A1.PAIDOFFERQTY) ) > AVGPKGPR > 56 FROM (SELECT DISTINCT > A.JOBNBR,A.OFFERNAME,A.PAIDOFFERQTY,A.OFFERPRICEAT > 57 FROM OFFERLOAD_STAGE A > 58 WHERE A.OFFERNAME IN ( > 'A','B','C','D','E','F','G','H','I','J','K > 59 GROUP BY A1.JOBNBR ) APC, > 60 (SELECT DISTINCT C.LIFETOUCHID,C.PROGRAMID,MD.MARKETINGCODE > 61 FROM CURRJOBFACT C,MARKETINGDIM MD WHERE C.SOURCEFISCALYEAR < 2004 > 62 AND C.MARKETINGCODE = MD.MARKETINGCODE ) C, > 63 (SELECT JOBNBR,MIN(DEPOSITDATE) FIRSTPOSITIVECASHDATE > 64 FROM CASHTXNFACT > 65 WHERE SOURCEFISCALYEAR = 2004 GROUP BY JOBNBR ) WV1, > 66 (SELECT X.JOBNBR,X.WEEKENDDATE,X.PRELIMYTDESTACCTCMSNAMT > 67 FROM WKLYJOBFACT X, > 68 (SELECT JOBNBR,MAX(WEEKENDDATE) MAXWEEKENDDATE > 69 FROM WKLYJOBFACT WHERE SOURCEFISCALYEAR = 2004 > 70 AND NVL(PRELIMYTDESTACCTCMSNAMT,0) > 0 > 71 GROUP BY JOBNBR ) W1 > 72 WHERE X.JOBNBR = W1.JOBNBR AND X.WEEKENDDATE = W1.MAXWEEKENDDATE ) > WV > 73 WHERE CJS.JOBNBR = PS.JOBNBR (+) > 74 AND CJS.JOBNBR = APC.JOBNBR (+) > 75 AND CJS.MARKETINGCODE = MD.MARKETINGCODE > 76 AND CJS.LIFETOUCHID = C.LIFETOUCHID (+) > 77 AND CJS.MARKETINGCODE = C.MARKETINGCODE (+) > 78 AND CJS.JOBNBR = WV1.JOBNBR (+) > 79 AND CJS.JOBNBR = WV.JOBNBR (+); > > Explained. > > SQL> @explain > > Query Plan > -------------------------------------------------------------------------- > ------ > INSERT STATEMENT Cost = 181253 > LOAD AS SELECT > HASH JOIN OUTER > HASH JOIN OUTER > HASH JOIN OUTER > HASH JOIN OUTER > HASH JOIN OUTER > HASH JOIN > TABLE ACCESS FULL MARKETINGDIM > TABLE ACCESS FULL CURRJOB_STAGE > VIEW > > Query Plan > -------------------------------------------------------------------------- > ------ > SORT GROUP BY > VIEW > SORT UNIQUE > TABLE ACCESS FULL OFFERLOAD_STAGE > VIEW > SORT GROUP BY > TABLE ACCESS FULL CASHTXNFACT > VIEW > SORT UNIQUE > NESTED LOOPS > PARTITION RANGE ITERATOR > > Query Plan > -------------------------------------------------------------------------- > ------ > TABLE ACCESS FULL CURRJOBFACT > INDEX UNIQUE SCAN SYS_C00889 > TABLE ACCESS FULL PERIOD_STAGE > VIEW > HASH JOIN > VIEW > SORT GROUP BY > PARTITION RANGE ALL > TABLE ACCESS FULL WKLYJOBFACT > PARTITION RANGE ALL > TABLE ACCESS FULL WKLYJOBFACT > > 33 rows selected. > > > Table truncated. > > SQL> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Henry Poras INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Henry Poras INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling INET: [EMAIL PROTECTED]
Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
