| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Can't insert into partition
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.
dwilliams_at_lifetouch.com
-----Original Message-----
Sent: Wednesday, August 06, 2003 2:54 PM
To: Multiple recipients of list ORACLE-L
Dennis,
Is the explain plan the same between this run and the 30 minute run? The
trace is just showing a FTS (looks like multi_block_read_count is 8
p3=8) of
a table in file_id=197 and blocks between 103581 and 104237. Don't know
which table that is (you can find out from dba_extents). I also don't
know
if the FTS is what you want or not. If the trace ran to completion, you
can
compare the actual stats in the trace file (it will also show up with a
tkprof) to those in the explain plan (I don't see any in the plan you
posted) to see if there is an issue with statistics. Wolfgang Breitling
does
a good job explaining this in his papers (http://www.centrexcc.com/)
Henry
-----Original Message-----
DENNIS WILLIAMS
Sent: Wednesday, August 06, 2003 1:29 PM
To: Multiple recipients of list ORACLE-L
Henry - I thought somebody would ask for it and I've been wanting to try tracing another session. Works great! Here is the level 8 trace.
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
Dump file /oracle8/admin/madmp/udump/ora_12544.trc
Oracle8i Enterprise Edition Release 8.1.6.0.0 - Production
With the Partitioning option
JServer Release 8.1.6.0.0 - Production
ORACLE_HOME = /oracle8/OraHome1
System name: OSF1
Node name: mnwhse1 Release: V4.0 Version: 1229 Machine: alpha
-----Original Message-----
Sent: Wednesday, August 06, 2003 12:20 PM
To: Multiple recipients of list ORACLE-L
Dennis,
Could you plese post the v$session_wait. Do you have a 10046 trace?
Henry
-----Original Message-----
DENNIS WILLIAMS
Sent: Wednesday, August 06, 2003 11:40 AM
To: Multiple recipients of list ORACLE-L
We have a situation where a process can't insert into a partition of a partitioned table. The process just keeps running.
- A stored procedure executes a SQL insert statement (listing below). - It normally completes in 30 minutes, but now just runs for hours. - Oracle 8.1.6 on Dec/Compaq/HP Alpha - In tracing the process, it is waiting on "db file scattered read". - This is a relatively new process, but it has completed successfullytwice
Dennis Williams
DBA, 80%OCP, 100% DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
Here is the SQL and the explain plan:
> SQL> explain plan for
> 2 INSERT /*+ APPEND */INTO CURRJOBFACT NOLOGGING
> 3 ( bunch of columns )
> 21 SELECT /*+ use_hash(cjs, ps. md, a, o, x, wv, apc, wv1) */
> 22 CJS.JOBNBR,CJS.SOURCEFISCALYEAR,CJS.LIFETOUCHID,MD.PROGRAMID,
> 23 CJS.MARKETINGCODE,CJS.PLANTRECEIPTDATE,CJS.PHOTOGRAPHYDATE,
> 24
>
CJS.SHIPDATE,CJS.SELLINGMETHODCODE,CJS.MDRPRIMARYID,CJS.SUBPROGRAMCODE,
> 25
CJS.TERRCODE,CJS.SUBTERRCODE,CJS.BIDIND,CJS.PDKIND,CJS.PDKPARTNBR,
> 26 CJS.RETAKEIND,
> 27
>
TO_NUMBER(TRANSLATE(UPPER(SUBSTR(CJS.JOBNBR,10,1)),'ABCDEFGHIJKLMNOPQRST
UV
> ,
> 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.YTDCHARGEB
AC
> ,
> 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.PRELIMYTDSALESTA
XA
> ,
> 45
PS.PRELIMYTDPERFECTSALEAMT,CJS.YTDJTEPAIDPKGQTY,CJS.YTDPAIDPKGQTY,
> 46 CJS.YTDUNPAIDPKGQTY,CJS.YTDXNOPURCHASEQTY,PS.YTDPAIDPKGSHIPQTY,
> 47
>
PS.YTDUNPAIDPKGSHIPQTY,CJS.PROOFPOSEQTY,CJS.PROOFCOUNTQTY,CJS.EXTRACTDAT
E,
> 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
>
--Received on Wed Aug 06 2003 - 18:04:23 CDT
> ------
> 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: DWILLIAMS_at_LIFETOUCH.COM Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Henry Poras INET: hporas_at_etal.uri.edu Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: DWILLIAMS_at_LIFETOUCH.COM Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Henry Poras INET: hporas_at_etal.uri.edu Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: DENNIS WILLIAMS INET: DWILLIAMS_at_LIFETOUCH.COM Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Cary Millsap INET: cary.millsap_at_hotsos.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
 |
 |