Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Can't insert into partition

RE: Can't insert into partition

From: M Rafiq <rafiq9857_at_hotmail.com>
Date: Thu, 07 Aug 2003 15:59:25 -0800
Message-ID: <F001.005C9885.20030807155925@fatcity.com>


Dennis,

I am not pretty sure but you can try to increase degree of your table/index to > 1 ......

Regards
Rafiq

Reply-To: ORACLE-L_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: Thu, 07 Aug 2003 14:19:23 -0800

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

_________________________________________________________________
MSN 8 with e-mail virus protection service: 2 months FREE*  
http://join.msn.com/?page=features/virus


--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: M Rafiq INET: rafiq9857_at_hotmail.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).
Received on Thu Aug 07 2003 - 18:59:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US