Re: Can't insert into partition

Btw, have you noticed that you have a dot instead of comma in your hash hint:

SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */

How big are your tables - why do you want to have hash join on all of them? Hash joins aren't fast if you got huge datasets and little hash_area_size... especially when statistics aren't correct.

Check for v$session_event for your session and for what does it wait the most (also compare with CPU used by this session statistic from v$sesstat although from execution plan I'd say you got IO problem rather than CPU bottleneck).

Tanel.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Wednesday, August 06, 2003 6:39 PM

>
> 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.
> dwilliams_at_lifetouch.com
>
> 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: DWILLIAMS_at_LIFETOUCH.COM
>
> 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: ListGuru_at_fatcity.com (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: Tanel Poder
  INET: tanel.poder.003_at_mail.ee

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: ListGuru_at_fatcity.com (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).