Wolfgang - Yes, you are correct, it is using bind variables. To run the SQL standalone, we manually change these to literal variables.
Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -----Original Message----- Sent: Thursday, August 07, 2003 12:19 AM To: Multiple recipients of list ORACLE-L Is the sql you posted the exact sql as it is executed in the PLSQL procedure, i.e. is the procedure using literals such as 2004 in the predicates for sourcefiscalyear, or is it really using a bindvariable? 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). -- 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).
