I am not pretty sure but you can try to increase degree of your table/index to > 1 ......
Regards Rafiq
Reply-To: [EMAIL PROTECTED] To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> 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. [EMAIL PROTECTED]
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.
>[EMAIL PROTECTED]
>
>
>-----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: [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).
_________________________________________________________________
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: [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).