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 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: Tanel Poder 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).
