We have a situation where a process can't insert into a partition of a partitioned table. The process just keeps running. - A stored procedure executes a SQL insert statement (listing below). - It normally completes in 30 minutes, but now just runs for hours. - Oracle 8.1.6 on Dec/Compaq/HP Alpha - In tracing the process, it is waiting on "db file scattered read". - This is a relatively new process, but it has completed successfully twice in production and numerous times in test. - I was able to perform a simple insert into the partition. - I created a test table (non-partitioned) and the process worked fine there, completing in the normal 30 minutes. - I rebuilt the partition (drop partition and create partition), to no effect. I'm stumped and looking for any suggestions:
Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] Here is the SQL and the explain plan: > 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).
