| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Can't insert into partition
Henry - I thought somebody would ask for it and I've been wanting to try
tracing another session. Works great! Here is the level 8 trace.
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
Dump file /oracle8/admin/madmp/udump/ora_12544.trc
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
ORACLE_HOME = /oracle8/OraHome1
System name: OSF1
Node name: mnwhse1 Release: V4.0 Version: 1229 Machine: alpha
-----Original Message-----
Sent: Wednesday, August 06, 2003 12:20 PM
To: Multiple recipients of list ORACLE-L
Dennis,
Could you plese post the v$session_wait. Do you have a 10046 trace?
Henry
-----Original Message-----
DENNIS WILLIAMS
Sent: Wednesday, August 06, 2003 11:40 AM
To: Multiple recipients of list ORACLE-L
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 twicein 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.
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 (+);
>
>
>
>
>
>
> >
-- 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: Henry Poras INET: hporas_at_etal.uri.edu 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: 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).Received on Wed Aug 06 2003 - 12:29:26 CDT
 |
 |