| 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 - 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.
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
Sent: Wednesday, August 06, 2003 2:54 PM
To: Multiple recipients of list ORACLE-L
Dennis,
Is the explain plan the same between this run and the 30 minute run? The trace is just showing a FTS (looks like multi_block_read_count is 8 p3=8) of a table in file_id=197 and blocks between 103581 and 104237. Don't know which table that is (you can find out from dba_extents). I also don't know if the FTS is what you want or not. If the trace ran to completion, you can compare the actual stats in the trace file (it will also show up with a tkprof) to those in the explain plan (I don't see any in the plan you posted) to see if there is an issue with statistics. Wolfgang Breitling does a good job explaining this in his papers (http://www.centrexcc.com/)
Henry
-----Original Message-----
DENNIS WILLIAMS
Sent: Wednesday, August 06, 2003 1:29 PM
To: Multiple recipients of list ORACLE-L
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). -- 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 - 17:29:36 CDT
 |
 |