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