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.
dwilliams_at_lifetouch.com

-----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 10000), 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.
>dwilliams_at_lifetouch.com
>
>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 cpu elapsed disk query current
>rows
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>Parse 1 0.01 0.01 0 0 0
>0
>Execute 1 351.17 1349.51 1208687 1212777 866
>0
>Fetch 0 0.00 0.00 0 0 0
>0
>------- ------ -------- ---------- ---------- ---------- ----------
>----------
>total 2 351.18 1349.52 1208687 1212777 866
>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
> 1 NESTED LOOPS OUTER
> 2 NESTED LOOPS OUTER
> 2 HASH JOIN OUTER
> 6412 HASH JOIN OUTER
> 6412 HASH JOIN
> 246 TABLE ACCESS FULL MARKETINGDIM
> 6412 TABLE ACCESS FULL CURRJOB_STAGE
> 3093 VIEW
> 3093 SORT GROUP BY
> 13728 VIEW
> 13728 SORT UNIQUE
> 35929 TABLE ACCESS FULL OFFERLOAD_STAGE
> 47 VIEW
> 47 SORT UNIQUE
>1222277 NESTED LOOPS
>1222278 PARTITION RANGE ITERATOR PARTITION: KEY (null)
>1222280 TABLE ACCESS FULL CURRJOBFACT PARTITION: KEY (null)
>1222277 INDEX UNIQUE SCAN (object id 2941)
> 2 TABLE ACCESS FULL PERIOD_STAGE
> 0 VIEW
> 55 HASH JOIN
> 110 VIEW
> 110 SORT GROUP BY
> 98 PARTITION RANGE ALL PARTITION: START=1 STOP=31
> 98 TABLE ACCESS FULL WKLYJOBFACT PARTITION: START=1 STOP=31
>32824864 PARTITION RANGE ALL PARTITION: START=1 STOP=31
>32824864 TABLE ACCESS FULL WKLYJOBFACT PARTITION: START=1 STOP=31
> 1 VIEW
> 2324 SORT GROUP BY
> 5908 TABLE ACCESS FULL CASHTXNFACT
>
>
>-----Original Message-----
>Sent: Thursday, August 07, 2003 10:55 AM
>To: Multiple recipients of list ORACLE-L
>
>
>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.
> >dwilliams_at_lifetouch.com
> >
> >
> >-----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: breitliw_at_centrexcc.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: 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).

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: breitliw_at_centrexcc.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: 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).