Oracle-L: RE: Can't insert into partition

From: DENNIS WILLIAMS <DWILLIAMS_at_LIFETOUCH.COM>
Date: Thu, 07 Aug 2003 14:19:23 -0800
Message-ID: <F001.005C9866.20030807141923@fatcity.com>

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
---------------------------------------------------------------------