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=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 scattered read' ela= 0 p1=197 p2=104077 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104085 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104093 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104101 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104109 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104117 p3=8 WAIT #14: nam='db file scattered read' ela= 0 p1=197 p2=104125 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104133 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104141 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104149 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104157 p3=8 WAIT #14: nam='db file scattered read' ela= 2 p1=197 p2=104165 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104173 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104181 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104189 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104197 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104205 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104213 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104221 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104229 p3=8 WAIT #14: nam='db file scattered read' ela= 1 p1=197 p2=104237 p3=8 -----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 twice in production and numerous times in test. - I was able to perform a simple insert into the partition. - I created a test table (non-partitioned) and the process worked fine there, completing in the normal 30 minutes. - I rebuilt the partition (drop partition and create partition), to no effect. I'm stumped and looking for any suggestions: Dennis Williams DBA, 80%OCP, 100% DBA Lifetouch, Inc. [EMAIL PROTECTED] Here is the SQL and the explain plan: > SQL> explain plan for > 2 INSERT /*+ APPEND */INTO CURRJOBFACT NOLOGGING > 3 ( bunch of columns ) > 21 SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */ > 22 CJS.JOBNBR,CJS.SOURCEFISCALYEAR,CJS.LIFETOUCHID,MD.PROGRAMID, > 23 CJS.MARKETINGCODE,CJS.PLANTRECEIPTDATE,CJS.PHOTOGRAPHYDATE, > 24 > CJS.SHIPDATE,CJS.SELLINGMETHODCODE,CJS.MDRPRIMARYID,CJS.SUBPROGRAMCODE, > 25 CJS.TERRCODE,CJS.SUBTERRCODE,CJS.BIDIND,CJS.PDKIND,CJS.PDKPARTNBR, > 26 CJS.RETAKEIND, > 27 > TO_NUMBER(TRANSLATE(UPPER(SUBSTR(CJS.JOBNBR,10,1)),'ABCDEFGHIJKLMNOPQRSTUV > , > 28 DECODE(TO_DATE(CJS.SHIPDATE,'YYYY/MM/DD'), NULL ,'N','Y'), > 29 DECODE(NVL(GREATEST(CJS.YTDCASHRECEIVEDAMT,0),0),0, > 30 DECODE(CJS.PAYSTATUSIND,'F','Y','N'),'Y') PAIDJOBIND, > 31 CJS.PAYSTATUSIND,PS.PAIDSHIPPEDJOBIND, > 32 DECODE(SUBSTR(CJS.JOBNBR,9,1),'I','Y','N') PREJOBIND,CJS.PLANTCODE, > 33 > CJS.SHOTQTY,PS.MTDSHIPPEDPKGQTY,PS.MTDCHARGEBACKAMT,PS.MTDTERRCMSNAMT, > 34 NVL(PS.MTDTERRCMSNAMT,0) - NVL(PS.MTDCHARGEBACKAMT,0) > ,PS.MTDCASHRECEIVEDA, > 35 PS.MTDCASHRETAINEDAMT,PS.MTDESTACCTCMSNAMT,PS.MTDACCTCMSNPAIDAMT, > 36 > PS.MTDGROSSCASHAMT,PS.MTDSALESTAXAMT,CJS.YTDSHIPPEDPKGQTY,CJS.YTDCHARGEBAC > , > 37 CJS.YTDTERRCMSNAMT,CJS.YTDTERREARNINGSAMT,CJS.YTDCASHRECEIVEDAMT, > 38 CJS.YTDCASHRETAINEDAMT,CJS.YTDESTACCTCMSNAMT,CJS.YTDACCTCMSNPAIDAMT, > 39 CJS.YTDCASHRECEIVEDAMT +YTDESTACCTCMSNAMT + YTDACCTCMSNPAIDAMT , > 40 CJS.YTDSALESTAXAMT,CJS.YTDPERFECTSALEAMT,PS.PRELIMYTDSHIPPEDPKGQTY, > 41 PS.PRELIMYTDCHARGEBACKAMT,PS.PRELIMYTDTERRCMSNAMT, > 42 PS.PRELIMYTDTERRCMSNAMT - PS.PRELIMYTDCHARGEBACKAMT > ,PS.PRELIMYTDCASHRECEI, > 43 PS.PRELIMYTDCASHRETAINEDAMT,PS.PRELIMYTDESTACCTCMSNAMT, > 44 > PS.PRELIMYTDACCTCMSNPAIDAMT,PS.PRELIMYTDGROSSCASHAMT,PS.PRELIMYTDSALESTAXA > , > 45 PS.PRELIMYTDPERFECTSALEAMT,CJS.YTDJTEPAIDPKGQTY,CJS.YTDPAIDPKGQTY, > 46 CJS.YTDUNPAIDPKGQTY,CJS.YTDXNOPURCHASEQTY,PS.YTDPAIDPKGSHIPQTY, > 47 > PS.YTDUNPAIDPKGSHIPQTY,CJS.PROOFPOSEQTY,CJS.PROOFCOUNTQTY,CJS.EXTRACTDATE, > 48 CJS.ORIGINALRECEIVEDDATE,PS.CMSNSTATUSCODE,WV1.FIRSTPOSITIVECASHDATE, > 49 DECODE(C.LIFETOUCHID, NULL ,'New','Retained') RENEWALSTATUSCODE, > 50 NVL(WV.PRELIMYTDESTACCTCMSNAMT, > 51 DECODE(NVL(PS.PRELIMYTDESTACCTCMSNAMT,0),0, NULL > ,PS.PRELIMYTDESTACCTCMSNA, > 52 APC.AVGPKGPRICE > 53 FROM CURRJOB_STAGE CJS,PERIOD_STAGE PS,MARKETINGDIM MD, > 54 (SELECT A1.JOBNBR,DECODE(SUM(PAIDOFFERQTY),0,0, > 55 SUM(A1.OFFERPRICEAMT * A1.PAIDOFFERQTY ) / SUM(A1.PAIDOFFERQTY) ) > AVGPKGPR > 56 FROM (SELECT DISTINCT > A.JOBNBR,A.OFFERNAME,A.PAIDOFFERQTY,A.OFFERPRICEAT > 57 FROM OFFERLOAD_STAGE A > 58 WHERE A.OFFERNAME IN ( > 'A','B','C','D','E','F','G','H','I','J','K > 59 GROUP BY A1.JOBNBR ) APC, > 60 (SELECT DISTINCT C.LIFETOUCHID,C.PROGRAMID,MD.MARKETINGCODE > 61 FROM CURRJOBFACT C,MARKETINGDIM MD WHERE C.SOURCEFISCALYEAR < 2004 > 62 AND C.MARKETINGCODE = MD.MARKETINGCODE ) C, > 63 (SELECT JOBNBR,MIN(DEPOSITDATE) FIRSTPOSITIVECASHDATE > 64 FROM CASHTXNFACT > 65 WHERE SOURCEFISCALYEAR = 2004 GROUP BY JOBNBR ) WV1, > 66 (SELECT X.JOBNBR,X.WEEKENDDATE,X.PRELIMYTDESTACCTCMSNAMT > 67 FROM WKLYJOBFACT X, > 68 (SELECT JOBNBR,MAX(WEEKENDDATE) MAXWEEKENDDATE > 69 FROM WKLYJOBFACT WHERE SOURCEFISCALYEAR = 2004 > 70 AND NVL(PRELIMYTDESTACCTCMSNAMT,0) > 0 > 71 GROUP BY JOBNBR ) W1 > 72 WHERE X.JOBNBR = W1.JOBNBR AND X.WEEKENDDATE = W1.MAXWEEKENDDATE ) > WV > 73 WHERE CJS.JOBNBR = PS.JOBNBR (+) > 74 AND CJS.JOBNBR = APC.JOBNBR (+) > 75 AND CJS.MARKETINGCODE = MD.MARKETINGCODE > 76 AND CJS.LIFETOUCHID = C.LIFETOUCHID (+) > 77 AND CJS.MARKETINGCODE = C.MARKETINGCODE (+) > 78 AND CJS.JOBNBR = WV1.JOBNBR (+) > 79 AND CJS.JOBNBR = WV.JOBNBR (+); > > Explained. > > SQL> @explain > > Query Plan > -------------------------------------------------------------------------- > ------ > INSERT STATEMENT Cost = 181253 > LOAD AS SELECT > HASH JOIN OUTER > HASH JOIN OUTER > HASH JOIN OUTER > HASH JOIN OUTER > HASH JOIN OUTER > HASH JOIN > TABLE ACCESS FULL MARKETINGDIM > TABLE ACCESS FULL CURRJOB_STAGE > VIEW > > Query Plan > -------------------------------------------------------------------------- > ------ > SORT GROUP BY > VIEW > SORT UNIQUE > TABLE ACCESS FULL OFFERLOAD_STAGE > VIEW > SORT GROUP BY > TABLE ACCESS FULL CASHTXNFACT > VIEW > SORT UNIQUE > NESTED LOOPS > PARTITION RANGE ITERATOR > > Query Plan > -------------------------------------------------------------------------- > ------ > TABLE ACCESS FULL CURRJOBFACT > INDEX UNIQUE SCAN SYS_C00889 > TABLE ACCESS FULL PERIOD_STAGE > VIEW > HASH JOIN > VIEW > SORT GROUP BY > PARTITION RANGE ALL > TABLE ACCESS FULL WKLYJOBFACT > PARTITION RANGE ALL > TABLE ACCESS FULL WKLYJOBFACT > > 33 rows selected. > > > Table truncated. > > SQL> -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Henry Poras 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Henry Poras 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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
