RE: Can't insert into partition
More information: - Running the insert statement from SQL*Plus works fine. - Normally this is run by executing a stored procedure that is in a package. Specifically, a master procedure calls a series of procedures within the package. The first 5 work fine, then this one doesn't complete. - Next we plan to attempt directly executing this stored procedure. - Stephane, thanks for your suggestion. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Wednesday, August 06, 2003 10: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 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 ) > 21SELECT /*+ 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 > , > 28DECODE(TO_DATE(CJS.SHIPDATE,'/MM/DD'), NULL ,'N','Y'), > 29DECODE(NVL(GREATEST(CJS.YTDCASHRECEIVEDAMT,0),0),0, > 30DECODE(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 > 70AND NVL(PRELIMYTDESTACCTCMSNAMT,0) > 0 > 71 GROUP BY JOBNBR ) W1 > 72 WHERE X.JOBNBR = W1.JOBNBR AND X.WEEKENDDATE = W1.MAXWEEKEND
RE: Can't insert into partition
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 ) > 21SELECT /*+ 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 > , > 28DECODE(TO_DATE(CJS.SHIPDATE,'/MM/DD'), NULL ,'N','Y'), > 29DECODE(NVL(GREATEST(CJS.YTDCASHRECEIVEDAMT,0),0),0, > 30DECODE(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 > 70AND 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 >
RE: Can't insert into partition
Thanks Wolfgang! And thanks to the others who have helped us unravel this problem. Your suggestion put us on the right track. I started running a SQL trace/tkprof, and lo and behold, when the stored procedure submits the SQL, CBO does everything as NESTED LOOPS. The next question is how to induce CBO to consider HASH JOIN? The original query had USE_HASH hints on the subqueries. Somewhere I thought I recalled that you could only put hints on the outer SQL statement -- is that true? So we tried adding the USE_HASH hint to the overall INSERT statement, to no effect. Here is the relevant portion of the tkprof output. Thanks again to eveyone. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] INSERT /*+ APPEND use_hash(CJS, PS, APC, MD, C, WV1, WV) */INTO CURRJOBFACT NOLOGGING ( JOBNBR,SOURCEFISCALYEAR,LIFETOUCHID,PROGRAMID,MARKETINGCODE, PLANTRECEIPTDATE,PHOTOGRAPHYDATE,SHIPDATE,SELLINGMETHODCODE,MDRPRIMARYID, SUBPROGRAMCODE,TERRCODE,SUBTERRCODE,BIDIND,PDKIND,PDKPARTNBR,RETAKEIND, RETAKENBR,SHIPPEDJOBIND,PAIDJOBIND,PAYSTATUSIND,PAIDSHIPPEDJOBIND,PREJOBIND, PLANTCODE,SHOTQTY,MTDSHIPPEDPKGQTY,MTDCHARGEBACKAMT,MTDTERRCMSNAMT, MTDTERREARNINGSAMT,MTDCASHRECEIVEDAMT,MTDCASHRETAINEDAMT,MTDESTACCTCMSNAMT, MTDACCTCMSNPAIDAMT,MTDGROSSCASHAMT,MTDSALESTAXAMT,YTDSHIPPEDPKGQTY, YTDCHARGEBACKAMT,YTDTERRCMSNAMT,YTDTERREARNINGSAMT,YTDCASHRECEIVEDAMT, YTDCASHRETAINEDAMT,YTDESTACCTCMSNAMT,YTDACCTCMSNPAIDAMT,YTDGROSSCASHAMT, YTDSALESTAXAMT,YTDPERFECTSALEAMT,PRELIMYTDSHIPPEDPKGQTY, PRELIMYTDCHARGEBACKAMT,PRELIMYTDTERRCMSNAMT,PRELIMYTDTERREARNINGSAMT, PRELIMYTDCASHRECEIVEDAMT,PRELIMYTDCASHRETAINEDAMT,PRELIMYTDESTACCTCMSNAMT, PRELIMYTDACCTCMSNPAIDAMT,PRELIMYTDGROSSCASHAMT,PRELIMYTDSALESTAXAMT, PRELIMYTDPERFECTSALEAMT,YTDJTEPAIDPKGQTY,YTDPAIDPKGQTY,YTDUNPAIDPKGQTY, YTDXNOPURCHASEQTY,PRELIMYTDPAIDPKGSHIPQTY,PRELIMYTDUNPAIDPKGSHIPQTY, PROOFPOSEQTY,PROOFCOUNTQTY,EXTRACTDATE,ORIGINALRECEIVEDDATE,CMSNSTATUSCODE, FIRSTPOSITIVECASHDATE,RENEWALSTATUSCODE,ORIGESTACCTCMSNAMT,AVGPKGPRICE ) SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */CJS.JOBNBR, (columns omitted) PS.PRELIMYTDESTACCTCMSNAMT)) ORIGESTACCTCMSNAMT,APC.AVGPKGPRICE FROM CURRJOB_STAGE CJS,PERIOD_STAGE PS,MARKETINGDIM MD,(SELECT A1.JOBNBR, DECODE(SUM(PAIDOFFERQTY),0,0,SUM(A1.OFFERPRICEAMT * A1.PAIDOFFERQTY ) / SUM(A1.PAIDOFFERQTY) ) AVGPKGPRICE FROM (SELECT DISTINCT A.JOBNBR, A.OFFERNAME,A.PAIDOFFERQTY,A.OFFERPRICEAMT FROM OFFERLOAD_STAGE A WHERE A.OFFERNAME IN ( 'A','B','C','D','E','F','G','H','I','J','K' )) A1 GROUP BY A1.JOBNBR ) APC,(SELECT DISTINCT C.LIFETOUCHID,C.PROGRAMID, MD.MARKETINGCODE FROM CURRJOBFACT C,MARKETINGDIM MD WHERE C.SOURCEFISCALYEAR < :b1 AND C.MARKETINGCODE = MD.MARKETINGCODE ) C, (SELECT JOBNBR,MIN(DEPOSITDATE) FIRSTPOSITIVECASHDATE FROM CASHTXNFACT WHERE SOURCEFISCALYEAR = :b1 GROUP BY JOBNBR ) WV1,(SELECT X.JOBNBR, X.WEEKENDDATE,X.PRELIMYTDESTACCTCMSNAMT FROM WKLYJOBFACT X,(SELECT JOBNBR, MAX(WEEKENDDATE) MAXWEEKENDDATE FROM WKLYJOBFACT WHERE SOURCEFISCALYEAR = :b1 AND NVL(PRELIMYTDESTACCTCMSNAMT,0) > 0 GROUP BY JOBNBR ) W1 WHERE X.JOBNBR = W1.JOBNBR AND X.WEEKENDDATE = W1.MAXWEEKENDDATE ) WV WHERE CJS.JOBNBR = PS.JOBNBR (+)AND CJS.JOBNBR = APC.JOBNBR (+)AND CJS.MARKETINGCODE = MD.MARKETINGCODE AND CJS.LIFETOUCHID = C.LIFETOUCHID (+)AND CJS.MARKETINGCODE = C.MARKETINGCODE (+)AND CJS.JOBNBR = WV1.JOBNBR (+)AND CJS.JOBNBR = WV.JOBNBR (+) call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.01 0.01 0 0 0 0 Execute 1351.171349.5112086871212777866 0 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total2351.181349.5212086871212777866 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 21 (recursive depth: 1) Rows Row Source Operation --- --- 0 LOAD AS SELECT 0 NESTED LOOPS OUTER 1NESTED LOOPS OUTER 2 NESTED LOOPS OUTER 2 HASH JOIN OUTER 6412 HASH JOIN OUTER 6412HASH JOIN 246 TABLE ACCESS FULL MARKETINGDIM 6412 TABLE ACCESS FULL CURRJOB_STAGE 3093VIEW 3093 SORT GROUP BY 13728 VIEW 13728 SORT UNIQUE 35929TABLE ACCESS FULL OFFERLOAD_STAGE 47 VIEW 47SORT UNIQUE 177 NESTED LOOPS 178 PARTITION RANGE ITERATOR PARTITION: KEY (null) 180 TABLE ACCESS FULL CURRJOBFACT PARTITION: KEY (null) 177 INDEX UNIQUE SCAN (object id 2941) 2 TABLE ACCESS FULL PERIOD_STAGE 0
RE: Can't insert into partition
You are welcome. Happens a lot. You see what should be there rather than what IS there and wonder why it's not working as designed. Someone else, uninvolved, comes along, takes one look at the thing, points out the error and leaves you (me) feeling like an idiot. At 12:54 PM 8/8/2003 -0800, you wrote: Wolfgang Thank you so much! You spotted something that we had overlooked! The dot/comma was indeed the problem. And thanks to you and everyone else for the help that helped narrow the problem down to this point. It seems that as you pointed out, the hint had a syntax error all along, but CBO was making a good decision anyway for awhile, then for some reason didn't make a good decision anymore. 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).
Re: Can't insert into partition
Hi! IIRC, 8.1.6 didn't write any execution plan stats to trace file, it's a feature from 8.1.7. I might remember wrong though. Tanel. - Original Message - To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Wednesday, August 06, 2003 10:54 PM > 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. > [EMAIL PROTECTED] > > > 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 > Instance name: madmp > Redo thread mounted by this instance: 1 > Oracle process number: 12 > Unix process pid: 12544, image: [EMAIL PROTECTED] (TNS V1-V3) > > *** 2003-08-06 08:46:26.129 > *** SESSION ID:(25.33691) 2003-08-06 08:46:26.031 > WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103581 p3=8 > WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103589 p3=8 > WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103597 p3=8 > WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103605 p3=8 > WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103613 p3=8 > WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103621 p3=8 > WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103629 p3=8 > WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103637 p3=8 > WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103645 p3=8 > WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103653 p3=8 > WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103661 p3=8 > WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103669 p3=8 > WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103677 p3=8 > WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103685 p3=8 > WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103693 p3=8 > WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103701 p3=8 > WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103709 p3=8 > WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103717 p3=8 > WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103725 p3=8 > WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103733 p3=8 > WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103741 p3=8 > WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103749 p3=8 > WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103757 p3=8 > WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103765 p3=8 > WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103773 p3=8 > WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103781 p3=8 > WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103789 p3=8 > WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103797 p3=8 > WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103805 p3=8 > WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103813 p3=8 > WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103821 p3=8 > WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103829 p3=8 > WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103837 p3=8 > WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103845 p3=8 > WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103853 p3=8 > WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103861 p3=8 > WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103869 p3=8 > WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103877 p3=8 > WAIT #14: nam='db file scattered read' ela= 3 p1=197 p2=103885 p3=8 > WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103893 p3=8 > WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103901 p3=8 > WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103909 p3=8 > WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103917 p3=8 > WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103925 p
RE: Can't insert into partition
But then it's not the same sql anymore and the access plan can be wildly different. You need to use bind variables in your sqlplus session as well. Unfortunately, even then it is not guaranteed that you'll get the same plan as you get in the plsql proc. At 06:44 AM 8/7/2003 -0800, you wrote: 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? 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).
RE: Can't insert into partition
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. [EMAIL PROTECTED] 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 Instance name: madmp Redo thread mounted by this instance: 1 Oracle process number: 12 Unix process pid: 12544, image: [EMAIL PROTECTED] (TNS V1-V3) *** 2003-08-06 08:46:26.129 *** SESSION ID:(25.33691) 2003-08-06 08:46:26.031 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103581 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103589 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103597 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103605 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103613 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103621 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103629 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103637 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103645 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103653 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103661 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103669 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103677 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103685 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103693 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103701 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103709 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103717 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103725 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103733 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103741 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103749 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103757 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103765 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103773 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103781 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103789 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103797 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103805 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103813 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103821 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103829 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103837 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103845 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103853 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103861 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103869 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103877 p3=8 WAIT #14: nam='db file scattered read' ela= 3 p1=197 p2=103885 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103893 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103901 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103909 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103917 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103925 p3=8 WAIT #14: nam='db file scattered read' ela= 3 p1=197 p2=103933 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103941 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103949 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103957 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103965 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103973 p3=8 WAIT #14: nam='db file scattered
RE: Can't insert into partition
Not certain that this is the case, but could it be that statistics are missing for one partition, thus occulting (as in 'undefined and something is undefined') statistics for the other partitions and the table ? Not necessarily for the table you are trying to insert into. Not sure that it is a good idea but I have a knack for picking up customers where air conditioning is out of order and above 25C I work at 50% of my capacity with an exponential decrease. As the temperature stands, if anybody is willing to water me from time to time, I'll appreciate. >- --- Original Message --- - >From: DENNIS WILLIAMS <[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Wed, 06 Aug 2003 07:39:36 > > >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] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Stephane Faroult 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).
RE: Can't insert into partition
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 ) > > 21SELECT /*+ 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 > > , > > 28DECODE(TO_DATE(CJS.SHIPDATE,'/MM/DD'), NULL ,'N','Y'), > > 29DECODE(NVL(GREATEST(CJS.YTDCASHRECEIVEDAMT,0),0),0, > > 30DECODE(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 > > 70AND 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
RE: Can't insert into partition
Dennis, I am not pretty sure but you can try to increase degree of your table/index to > 1 .. Regards Rafiq Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> Date: Thu, 07 Aug 2003 14:19:23 -0800 Thanks Wolfgang! And thanks to the others who have helped us unravel this problem. Your suggestion put us on the right track. I started running a SQL trace/tkprof, and lo and behold, when the stored procedure submits the SQL, CBO does everything as NESTED LOOPS. The next question is how to induce CBO to consider HASH JOIN? The original query had USE_HASH hints on the subqueries. Somewhere I thought I recalled that you could only put hints on the outer SQL statement -- is that true? So we tried adding the USE_HASH hint to the overall INSERT statement, to no effect. Here is the relevant portion of the tkprof output. Thanks again to eveyone. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] INSERT /*+ APPEND use_hash(CJS, PS, APC, MD, C, WV1, WV) */INTO CURRJOBFACT NOLOGGING ( JOBNBR,SOURCEFISCALYEAR,LIFETOUCHID,PROGRAMID,MARKETINGCODE, PLANTRECEIPTDATE,PHOTOGRAPHYDATE,SHIPDATE,SELLINGMETHODCODE,MDRPRIMARYID, SUBPROGRAMCODE,TERRCODE,SUBTERRCODE,BIDIND,PDKIND,PDKPARTNBR,RETAKEIND, RETAKENBR,SHIPPEDJOBIND,PAIDJOBIND,PAYSTATUSIND,PAIDSHIPPEDJOBIND,PREJOBIND, PLANTCODE,SHOTQTY,MTDSHIPPEDPKGQTY,MTDCHARGEBACKAMT,MTDTERRCMSNAMT, MTDTERREARNINGSAMT,MTDCASHRECEIVEDAMT,MTDCASHRETAINEDAMT,MTDESTACCTCMSNAMT, MTDACCTCMSNPAIDAMT,MTDGROSSCASHAMT,MTDSALESTAXAMT,YTDSHIPPEDPKGQTY, YTDCHARGEBACKAMT,YTDTERRCMSNAMT,YTDTERREARNINGSAMT,YTDCASHRECEIVEDAMT, YTDCASHRETAINEDAMT,YTDESTACCTCMSNAMT,YTDACCTCMSNPAIDAMT,YTDGROSSCASHAMT, YTDSALESTAXAMT,YTDPERFECTSALEAMT,PRELIMYTDSHIPPEDPKGQTY, PRELIMYTDCHARGEBACKAMT,PRELIMYTDTERRCMSNAMT,PRELIMYTDTERREARNINGSAMT, PRELIMYTDCASHRECEIVEDAMT,PRELIMYTDCASHRETAINEDAMT,PRELIMYTDESTACCTCMSNAMT, PRELIMYTDACCTCMSNPAIDAMT,PRELIMYTDGROSSCASHAMT,PRELIMYTDSALESTAXAMT, PRELIMYTDPERFECTSALEAMT,YTDJTEPAIDPKGQTY,YTDPAIDPKGQTY,YTDUNPAIDPKGQTY, YTDXNOPURCHASEQTY,PRELIMYTDPAIDPKGSHIPQTY,PRELIMYTDUNPAIDPKGSHIPQTY, PROOFPOSEQTY,PROOFCOUNTQTY,EXTRACTDATE,ORIGINALRECEIVEDDATE,CMSNSTATUSCODE, FIRSTPOSITIVECASHDATE,RENEWALSTATUSCODE,ORIGESTACCTCMSNAMT,AVGPKGPRICE ) SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */CJS.JOBNBR, (columns omitted) PS.PRELIMYTDESTACCTCMSNAMT)) ORIGESTACCTCMSNAMT,APC.AVGPKGPRICE FROM CURRJOB_STAGE CJS,PERIOD_STAGE PS,MARKETINGDIM MD,(SELECT A1.JOBNBR, DECODE(SUM(PAIDOFFERQTY),0,0,SUM(A1.OFFERPRICEAMT * A1.PAIDOFFERQTY ) / SUM(A1.PAIDOFFERQTY) ) AVGPKGPRICE FROM (SELECT DISTINCT A.JOBNBR, A.OFFERNAME,A.PAIDOFFERQTY,A.OFFERPRICEAMT FROM OFFERLOAD_STAGE A WHERE A.OFFERNAME IN ( 'A','B','C','D','E','F','G','H','I','J','K' )) A1 GROUP BY A1.JOBNBR ) APC,(SELECT DISTINCT C.LIFETOUCHID,C.PROGRAMID, MD.MARKETINGCODE FROM CURRJOBFACT C,MARKETINGDIM MD WHERE C.SOURCEFISCALYEAR < :b1 AND C.MARKETINGCODE = MD.MARKETINGCODE ) C, (SELECT JOBNBR,MIN(DEPOSITDATE) FIRSTPOSITIVECASHDATE FROM CASHTXNFACT WHERE SOURCEFISCALYEAR = :b1 GROUP BY JOBNBR ) WV1,(SELECT X.JOBNBR, X.WEEKENDDATE,X.PRELIMYTDESTACCTCMSNAMT FROM WKLYJOBFACT X,(SELECT JOBNBR, MAX(WEEKENDDATE) MAXWEEKENDDATE FROM WKLYJOBFACT WHERE SOURCEFISCALYEAR = :b1 AND NVL(PRELIMYTDESTACCTCMSNAMT,0) > 0 GROUP BY JOBNBR ) W1 WHERE X.JOBNBR = W1.JOBNBR AND X.WEEKENDDATE = W1.MAXWEEKENDDATE ) WV WHERE CJS.JOBNBR = PS.JOBNBR (+)AND CJS.JOBNBR = APC.JOBNBR (+)AND CJS.MARKETINGCODE = MD.MARKETINGCODE AND CJS.LIFETOUCHID = C.LIFETOUCHID (+)AND CJS.MARKETINGCODE = C.MARKETINGCODE (+)AND CJS.JOBNBR = WV1.JOBNBR (+)AND CJS.JOBNBR = WV.JOBNBR (+) call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.01 0.01 0 0 0 0 Execute 1351.171349.5112086871212777866 0 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total2351.181349.5212086871212777866 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 21 (recursive depth: 1) Rows Row Source Operation --- --- 0 LOAD AS SELECT 0 NESTED LOOPS OUTER 1NESTED LOOPS OUTER 2 NESTED LOOPS OUTER 2 HASH JOIN OUTER 6412 HASH JOIN OUTER 6412HASH JOIN 246 TABLE ACCESS FULL MARKETINGDIM 6412 TABLE ACCESS FULL CURRJOB_STAGE 3093VIEW 3093 SORT GROUP BY 13728 VIEW 13728 SORT UNIQUE 35929TABLE ACCESS FULL OFFERLOAD_STAGE 47 VIEW 47SORT UNIQUE 177 NESTED LOOPS 122
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. [EMAIL PROTECTED] 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 Instance name: madmp Redo thread mounted by this instance: 1 Oracle process number: 12 Unix process pid: 12544, image: [EMAIL PROTECTED] (TNS V1-V3) *** 2003-08-06 08:46:26.129 *** SESSION ID:(25.33691) 2003-08-06 08:46:26.031 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103581 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103589 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103597 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103605 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103613 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103621 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103629 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103637 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103645 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103653 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103661 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103669 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103677 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103685 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103693 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103701 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103709 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103717 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103725 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103733 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103741 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103749 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103757 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103765 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103773 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103781 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103789 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103797 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103805 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103813 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103821 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103829 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103837 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103845 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103853 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103861 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103869 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103877 p3=8 WAIT #14: nam='db file scattered read' ela= 3 p1=197 p2=103885 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103893 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103901 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103909 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103917 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103925 p3=8 WAIT #14: nam='db file scattered read' ela= 3 p1=197 p2=103933 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103941 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103949 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103957 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103965 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103973 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103981 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103989 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103997 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104005 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104013 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104021 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104029 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104037 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104045 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104053 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104061 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104069 p3=8 WAIT #14: nam='db file
RE: Can't insert into partition
Wolfgang Thank you so much! You spotted something that we had overlooked! The dot/comma was indeed the problem. And thanks to you and everyone else for the help that helped narrow the problem down to this point. It seems that as you pointed out, the hint had a syntax error all along, but CBO was making a good decision anyway for awhile, then for some reason didn't make a good decision anymore. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] -Original Message- Sent: Thursday, August 07, 2003 5:49 PM To: Multiple recipients of list ORACLE-L No, you can put a hint in inner sql and subselects. Some hints you NEED to put on a subselect to make any sense. Is that sql verbatim? The hint has a syntax error. There is a dot rather than a comma after ps which - pooof - may turn the princely hint into an ugly toad (no pun intended) comment. Another thnig you can try, since you are using bind variables, is to jack up db_file_multiblock_read_count, hash_multiblock_io_count, hash_area_size, optimizer_index_cost_adj (to 1), create a stored outline of the sql - hopefully it will use hash joins with all the help, and then revert to the normal init_ora settings and tell oracle to use the stored outline. At 02:19 PM 8/7/2003 -0800, you wrote: >Thanks Wolfgang! And thanks to the others who have helped us unravel this >problem. >Your suggestion put us on the right track. I started running a SQL >trace/tkprof, and lo and behold, when the stored procedure submits the SQL, >CBO does everything as NESTED LOOPS. The next question is how to induce CBO >to consider HASH JOIN? >The original query had USE_HASH hints on the subqueries. Somewhere I >thought I recalled that you could only put hints on the outer SQL statement >-- is that true? So we tried adding the USE_HASH hint to the overall INSERT >statement, to no effect. Here is the relevant portion of the tkprof output. >Thanks again to eveyone. > >Dennis Williams >DBA, 80%OCP, 100% DBA >Lifetouch, Inc. >[EMAIL PROTECTED] > >INSERT /*+ APPEND use_hash(CJS, PS, APC, MD, C, WV1, WV) */INTO CURRJOBFACT > NOLOGGING ( JOBNBR,SOURCEFISCALYEAR,LIFETOUCHID,PROGRAMID,MARKETINGCODE, > PLANTRECEIPTDATE,PHOTOGRAPHYDATE,SHIPDATE,SELLINGMETHODCODE,MDRPRIMARYID, > SUBPROGRAMCODE,TERRCODE,SUBTERRCODE,BIDIND,PDKIND,PDKPARTNBR,RETAKEIND, > >RETAKENBR,SHIPPEDJOBIND,PAIDJOBIND,PAYSTATUSIND,PAIDSHIPPEDJOBIND,PREJOBIND , > PLANTCODE,SHOTQTY,MTDSHIPPEDPKGQTY,MTDCHARGEBACKAMT,MTDTERRCMSNAMT, > >MTDTERREARNINGSAMT,MTDCASHRECEIVEDAMT,MTDCASHRETAINEDAMT,MTDESTACCTCMSNAMT, > MTDACCTCMSNPAIDAMT,MTDGROSSCASHAMT,MTDSALESTAXAMT,YTDSHIPPEDPKGQTY, > YTDCHARGEBACKAMT,YTDTERRCMSNAMT,YTDTERREARNINGSAMT,YTDCASHRECEIVEDAMT, > YTDCASHRETAINEDAMT,YTDESTACCTCMSNAMT,YTDACCTCMSNPAIDAMT,YTDGROSSCASHAMT, > YTDSALESTAXAMT,YTDPERFECTSALEAMT,PRELIMYTDSHIPPEDPKGQTY, > PRELIMYTDCHARGEBACKAMT,PRELIMYTDTERRCMSNAMT,PRELIMYTDTERREARNINGSAMT, > PRELIMYTDCASHRECEIVEDAMT,PRELIMYTDCASHRETAINEDAMT,PRELIMYTDESTACCTCMSNAMT, > PRELIMYTDACCTCMSNPAIDAMT,PRELIMYTDGROSSCASHAMT,PRELIMYTDSALESTAXAMT, > PRELIMYTDPERFECTSALEAMT,YTDJTEPAIDPKGQTY,YTDPAIDPKGQTY,YTDUNPAIDPKGQTY, > YTDXNOPURCHASEQTY,PRELIMYTDPAIDPKGSHIPQTY,PRELIMYTDUNPAIDPKGSHIPQTY, > >PROOFPOSEQTY,PROOFCOUNTQTY,EXTRACTDATE,ORIGINALRECEIVEDDATE,CMSNSTATUSCODE, > FIRSTPOSITIVECASHDATE,RENEWALSTATUSCODE,ORIGESTACCTCMSNAMT,AVGPKGPRICE ) > SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */CJS.JOBNBR, > (columns omitted) > PS.PRELIMYTDESTACCTCMSNAMT)) ORIGESTACCTCMSNAMT,APC.AVGPKGPRICE FROM > CURRJOB_STAGE CJS,PERIOD_STAGE PS,MARKETINGDIM MD,(SELECT A1.JOBNBR, > DECODE(SUM(PAIDOFFERQTY),0,0,SUM(A1.OFFERPRICEAMT * A1.PAIDOFFERQTY ) / > SUM(A1.PAIDOFFERQTY) ) AVGPKGPRICE FROM (SELECT DISTINCT A.JOBNBR, > A.OFFERNAME,A.PAIDOFFERQTY,A.OFFERPRICEAMT FROM OFFERLOAD_STAGE A WHERE > A.OFFERNAME IN ( 'A','B','C','D','E','F','G','H','I','J','K' )) A1 GROUP > BY A1.JOBNBR ) APC,(SELECT DISTINCT C.LIFETOUCHID,C.PROGRAMID, > MD.MARKETINGCODE FROM CURRJOBFACT C,MARKETINGDIM MD WHERE > C.SOURCEFISCALYEAR < :b1 AND C.MARKETINGCODE = MD.MARKETINGCODE ) C, > (SELECT JOBNBR,MIN(DEPOSITDATE) FIRSTPOSITIVECASHDATE FROM CASHTXNFACT > WHERE SOURCEFISCALYEAR = :b1 GROUP BY JOBNBR ) WV1,(SELECT X.JOBNBR, > X.WEEKENDDATE,X.PRELIMYTDESTACCTCMSNAMT FROM WKLYJOBFACT X,(SELECT >JOBNBR, > MAX(WEEKENDDATE) MAXWEEKENDDATE FROM WKLYJOBFACT WHERE SOURCEFISCALYEAR >= >:b1 AND NVL(PRELIMYTDESTACCTCMSNAMT,0) > 0 GROUP BY JOBNBR ) W1 WHERE > X.JOBNBR = W1.JOBNBR AND X.WEEKENDDATE = W1.MAXWEEKENDDATE ) WV WHERE > CJS.JOBNBR = PS.JOBNBR (+)AND CJS.JOBNBR = APC.JOBNBR (+)AND > CJS.MARKETINGCODE = MD.MARKETINGCODE AND CJS.LIFETOUCHID = C.LIFETOUCHID > (+)AND CJS.MARKETINGCODE = C.MARKETINGCODE (+)AND CJS.JOBNBR = > WV1.JOBNBR (+)AND CJS.JOBNBR = WV.JOBNBR (+) > > > >call count cpuelapsed disk querycurrent >rows >--- -- -
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" <[EMAIL PROTECTED]> 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. > [EMAIL PROTECTED] > > Here is the SQL and the explain plan: > > > > SQL> explain plan for > > 2 INSERT /*+ APPEND */INTO CURRJOBFACT NOLOGGING > > 3 ( bunch of columns ) > > 21SELECT /*+ 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 > > , > > 28DECODE(TO_DATE(CJS.SHIPDATE,'/MM/DD'), NULL ,'N','Y'), > > 29DECODE(NVL(GREATEST(CJS.YTDCASHRECEIVEDAMT,0),0),0, > > 30DECODE(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, > >
RE: Can't insert into partition
No, you can put a hint in inner sql and subselects. Some hints you NEED to put on a subselect to make any sense. Is that sql verbatim? The hint has a syntax error. There is a dot rather than a comma after ps which - pooof - may turn the princely hint into an ugly toad (no pun intended) comment. Another thnig you can try, since you are using bind variables, is to jack up db_file_multiblock_read_count, hash_multiblock_io_count, hash_area_size, optimizer_index_cost_adj (to 1), create a stored outline of the sql - hopefully it will use hash joins with all the help, and then revert to the normal init_ora settings and tell oracle to use the stored outline. At 02:19 PM 8/7/2003 -0800, you wrote: Thanks Wolfgang! And thanks to the others who have helped us unravel this problem. Your suggestion put us on the right track. I started running a SQL trace/tkprof, and lo and behold, when the stored procedure submits the SQL, CBO does everything as NESTED LOOPS. The next question is how to induce CBO to consider HASH JOIN? The original query had USE_HASH hints on the subqueries. Somewhere I thought I recalled that you could only put hints on the outer SQL statement -- is that true? So we tried adding the USE_HASH hint to the overall INSERT statement, to no effect. Here is the relevant portion of the tkprof output. Thanks again to eveyone. Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] INSERT /*+ APPEND use_hash(CJS, PS, APC, MD, C, WV1, WV) */INTO CURRJOBFACT NOLOGGING ( JOBNBR,SOURCEFISCALYEAR,LIFETOUCHID,PROGRAMID,MARKETINGCODE, PLANTRECEIPTDATE,PHOTOGRAPHYDATE,SHIPDATE,SELLINGMETHODCODE,MDRPRIMARYID, SUBPROGRAMCODE,TERRCODE,SUBTERRCODE,BIDIND,PDKIND,PDKPARTNBR,RETAKEIND, RETAKENBR,SHIPPEDJOBIND,PAIDJOBIND,PAYSTATUSIND,PAIDSHIPPEDJOBIND,PREJOBIND, PLANTCODE,SHOTQTY,MTDSHIPPEDPKGQTY,MTDCHARGEBACKAMT,MTDTERRCMSNAMT, MTDTERREARNINGSAMT,MTDCASHRECEIVEDAMT,MTDCASHRETAINEDAMT,MTDESTACCTCMSNAMT, MTDACCTCMSNPAIDAMT,MTDGROSSCASHAMT,MTDSALESTAXAMT,YTDSHIPPEDPKGQTY, YTDCHARGEBACKAMT,YTDTERRCMSNAMT,YTDTERREARNINGSAMT,YTDCASHRECEIVEDAMT, YTDCASHRETAINEDAMT,YTDESTACCTCMSNAMT,YTDACCTCMSNPAIDAMT,YTDGROSSCASHAMT, YTDSALESTAXAMT,YTDPERFECTSALEAMT,PRELIMYTDSHIPPEDPKGQTY, PRELIMYTDCHARGEBACKAMT,PRELIMYTDTERRCMSNAMT,PRELIMYTDTERREARNINGSAMT, PRELIMYTDCASHRECEIVEDAMT,PRELIMYTDCASHRETAINEDAMT,PRELIMYTDESTACCTCMSNAMT, PRELIMYTDACCTCMSNPAIDAMT,PRELIMYTDGROSSCASHAMT,PRELIMYTDSALESTAXAMT, PRELIMYTDPERFECTSALEAMT,YTDJTEPAIDPKGQTY,YTDPAIDPKGQTY,YTDUNPAIDPKGQTY, YTDXNOPURCHASEQTY,PRELIMYTDPAIDPKGSHIPQTY,PRELIMYTDUNPAIDPKGSHIPQTY, PROOFPOSEQTY,PROOFCOUNTQTY,EXTRACTDATE,ORIGINALRECEIVEDDATE,CMSNSTATUSCODE, FIRSTPOSITIVECASHDATE,RENEWALSTATUSCODE,ORIGESTACCTCMSNAMT,AVGPKGPRICE ) SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */CJS.JOBNBR, (columns omitted) PS.PRELIMYTDESTACCTCMSNAMT)) ORIGESTACCTCMSNAMT,APC.AVGPKGPRICE FROM CURRJOB_STAGE CJS,PERIOD_STAGE PS,MARKETINGDIM MD,(SELECT A1.JOBNBR, DECODE(SUM(PAIDOFFERQTY),0,0,SUM(A1.OFFERPRICEAMT * A1.PAIDOFFERQTY ) / SUM(A1.PAIDOFFERQTY) ) AVGPKGPRICE FROM (SELECT DISTINCT A.JOBNBR, A.OFFERNAME,A.PAIDOFFERQTY,A.OFFERPRICEAMT FROM OFFERLOAD_STAGE A WHERE A.OFFERNAME IN ( 'A','B','C','D','E','F','G','H','I','J','K' )) A1 GROUP BY A1.JOBNBR ) APC,(SELECT DISTINCT C.LIFETOUCHID,C.PROGRAMID, MD.MARKETINGCODE FROM CURRJOBFACT C,MARKETINGDIM MD WHERE C.SOURCEFISCALYEAR < :b1 AND C.MARKETINGCODE = MD.MARKETINGCODE ) C, (SELECT JOBNBR,MIN(DEPOSITDATE) FIRSTPOSITIVECASHDATE FROM CASHTXNFACT WHERE SOURCEFISCALYEAR = :b1 GROUP BY JOBNBR ) WV1,(SELECT X.JOBNBR, X.WEEKENDDATE,X.PRELIMYTDESTACCTCMSNAMT FROM WKLYJOBFACT X,(SELECT JOBNBR, MAX(WEEKENDDATE) MAXWEEKENDDATE FROM WKLYJOBFACT WHERE SOURCEFISCALYEAR = :b1 AND NVL(PRELIMYTDESTACCTCMSNAMT,0) > 0 GROUP BY JOBNBR ) W1 WHERE X.JOBNBR = W1.JOBNBR AND X.WEEKENDDATE = W1.MAXWEEKENDDATE ) WV WHERE CJS.JOBNBR = PS.JOBNBR (+)AND CJS.JOBNBR = APC.JOBNBR (+)AND CJS.MARKETINGCODE = MD.MARKETINGCODE AND CJS.LIFETOUCHID = C.LIFETOUCHID (+)AND CJS.MARKETINGCODE = C.MARKETINGCODE (+)AND CJS.JOBNBR = WV1.JOBNBR (+)AND CJS.JOBNBR = WV.JOBNBR (+) call count cpuelapsed disk querycurrent rows --- -- -- -- -- -- -- Parse1 0.01 0.01 0 0 0 0 Execute 1351.171349.5112086871212777866 0 Fetch0 0.00 0.00 0 0 0 0 --- -- -- -- -- -- -- total2351.181349.5212086871212777866 0 Misses in library cache during parse: 1 Optimizer goal: CHOOSE Parsing user id: 21 (recursive depth: 1) Rows Row Source Operation --- --- 0 LOAD AS SELECT 0 NESTED LOOPS OUTER 1NE
Can't insert into partition
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 ) > 21SELECT /*+ 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 > , > 28DECODE(TO_DATE(CJS.SHIPDATE,'/MM/DD'), NULL ,'N','Y'), > 29DECODE(NVL(GREATEST(CJS.YTDCASHRECEIVEDAMT,0),0),0, > 30DECODE(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 > 70AND 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 > HAS
RE: Can't insert into partition
Dennis, If I understand your question correctly, ... Your 10046 trace file will contain the execution plan that the PL/SQL procedure is seeing, if you let the process close the cursor before you shut off the trace. I think you can also get the plan information you're looking for from the 10053 data, too. I believe I've heard both Wolfgang and Tim describe how to force the data into the trace file, even if you can't trace until the cursor closes. Cary Millsap Hotsos Enterprises, Ltd. http://www.hotsos.com Upcoming events: - Hotsos Clinic 101 in Denver, Sydney - Hotsos Symposium 2004, March 7-10 Dallas - Visit www.hotsos.com for schedule details... -Original Message- DENNIS WILLIAMS Sent: Wednesday, August 06, 2003 5:30 PM To: Multiple recipients of list ORACLE-L 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. [EMAIL PROTECTED] -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. [EMAIL PROTECTED] 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 Instance name: madmp Redo thread mounted by this instance: 1 Oracle process number: 12 Unix process pid: 12544, image: [EMAIL PROTECTED] (TNS V1-V3) *** 2003-08-06 08:46:26.129 *** SESSION ID:(25.33691) 2003-08-06 08:46:26.031 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103581 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103589 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103597 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103605 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103613 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103621 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103629 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103637 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103645 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103653 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103661 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103669 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103677 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103685 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103693 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103701 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103709 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103717 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103725 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103733 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103741 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103749 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103757 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103765 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103773 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103781 p3=8 WAIT #14
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. [EMAIL PROTECTED] -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. [EMAIL PROTECTED] 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 Instance name: madmp Redo thread mounted by this instance: 1 Oracle process number: 12 Unix process pid: 12544, image: [EMAIL PROTECTED] (TNS V1-V3) *** 2003-08-06 08:46:26.129 *** SESSION ID:(25.33691) 2003-08-06 08:46:26.031 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103581 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103589 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103597 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103605 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103613 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103621 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103629 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103637 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103645 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103653 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103661 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103669 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103677 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103685 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103693 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103701 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103709 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103717 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103725 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103733 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103741 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103749 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103757 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103765 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103773 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103781 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103789 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103797 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=103805 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=103813 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103821 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103829 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103837 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103845 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103853 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103861 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=103869 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=
RE: Can't insert into partition
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 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 ) > 21SELECT /*+ 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 > , > 28DECODE(TO_DATE(CJS.SHIPDATE,'/MM/DD'), NULL ,'N','Y'), > 29DECODE(NVL(GREATEST(CJS.YTDCASHRECEIVEDAMT,0),0),0, > 30DECODE(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 > 70AND 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 > -
