Yes, I did add statistics, though 'estimated' one only..
thanks, Bruce
"Khedr,
Waleed" To: Multiple recipients of list ORACLE-L
<Waleed.Khedr <[EMAIL PROTECTED]>
@FMR.COM> cc:
Sent by: Subject: RE: HINTS , how to add correctly
root@fatcity.
com
03/15/2001
06:20 PM
Please
respond to
ORACLE-L
Did you update the statistics for these tables?
-----Original Message-----
Sent: Thursday, March 15, 2001 6:20 PM
To: Multiple recipients of list ORACLE-L
My query runs fine (5 sec) under rule based optimizer
but takes 1.5 minutes for cost based..
Our company wants to use only cost based optimizer and
does not want to use the *+rule* hint..
So I thought I can use the hints as suggested by the explain plan
of rule based optimizer..
In specific the cost based optimizer
used nested loops instead of merge join.. and using hints does
not make it switch back to using merge join..
Any help would be a life saver!
RULE plan from the Fast 5 second execution
========================================
Operation Name
SELECT STATEMENT
SORT ORDER BY
MERGE JOIN OUTER
SORT JOIN
MERGE JOIN OUTER
SORT JOIN
TABLE ACCESS BY INDE TBRPTTB
INDEX RANGE SCAN I1RPTTB
SORT JOIN
VIEW R1STRCUS
FILTER
TABLE ACCESS FULL TBSTRCUS
NESTED LOOPS
INDEX RANGE SCAN I1USRRGN
TABLE ACCESS BY ITBCCSITE
INDEX RANGE SCAN I1CCSITE
SORT JOIN
VIEW R1CUSGRP
FILTER
TABLE ACCESS FULL TBCUSGRP
INDEX UNIQUE SCAN I1USRRGN
the SLOW/ COST based plan - 1.5 plan..
(hint making no diiference!)
=======================================
Operation Name Rows Bytes Cost
SELECT STATEMENT 1 1K 40
SORT ORDER BY 1 1K 40
NESTED LOOPS OUTER 1 1K 38
NESTED LOOPS OUTER 1 1K 37
TABLE ACCESS BY INDEX TBRPTTB 1 1K 1
INDEX RANGE SCAN I1RPTTB 1 1
VIEW B1STRCUS 101 1K
HASH JOIN 101 4K 36
NESTED LOOPS 477 15K 32
INDEX RANGE SCAN I1USRRGN 2 24 2
TABLE ACCESS BY IND TBCCSITE 53K 1M 16
INDEX RANGE SCAN I2CCSITE 53K 20
TABLE ACCESS FULL TBSTRCUS 11K 88K 2
VIEW R1CUSGRP 18 468
FILTER
TABLE ACCESS FULL TBCUSGRP 18 396 1
INDEX UNIQUE SCAN I1USRRGN 1 12 1
Adding Hints to query making no difference..
I guess this is where I need help! how to add
hints correctly!
select /*+ USE_MERGE(TBRPTTB,B1STRCUS) */
rept_clmn_11_ds reg_cd,
rept_clmn_27_ds reg_na,
rept_clmn_15_ds owsh_sort_rept_cd,
rept_clmn_16_ds|| ' STORES' OWNER,
rept_clmn_08_ds OPERATOR,
rept_clmn_01_qt site_id_nu,
substr(rept_clmn_01_ds,1,16) ADDRESS,
rept_clmn_01_dt str_open_dt,
decode(rept_clmn_05_qt,null,0,rept_clmn_05_qt) act_mon_sls_am,
decode(rept_clmn_10_qt,null,0,rept_clmn_10_qt) act_mon_sls_am,
(rept_clmn_05_qt - rept_clmn_10_qt) DIFF,
((rept_clmn_05_qt - rept_clmn_10_qt)
/ decode(rept_clmn_10_qt,0,decode(rept_clmn_05_qt,0,1,rept_clmn_05_qt),
rept_clmn_10_qt))*100 DIFF1,
decode(rept_clmn_06_qt,null,0,rept_clmn_06_qt) act_mon_cust_am,
decode(rept_clmn_11_qt,null,0,rept_clmn_11_qt) act_mon_cust_am,
decode(rept_clmn_14_qt,null,0,rept_clmn_14_qt) drv_thu_trn_qt,
decode(rept_clmn_15_qt,null,0,rept_clmn_15_qt) brkf_trn_qt,
( rept_clmn_06_qt - rept_clmn_11_qt ) DIFF2,
(( rept_clmn_06_qt - rept_clmn_11_qt)
/decode(rept_clmn_11_qt,0,decode(rept_clmn_06_qt,0,1,rept_clmn_06_qt),
rept_clmn_11_qt))*100 DIFF3,
rept_clmn_08_qt drv_thu,
rept_clmn_09_qt brkf_sls,
decode(rept_clmn_26_ds,null,null,'*') comp_fl,
decode(rept_clmn_05_qt,0,'*',null) no_curr_sls_fl,
decode(b.cusm_sort_grp_ds,null,'NONE',b.cusm_sort_grp_ds) cusm_sort_grp_ds,
decode(c.cusm_sort_grp_cd,null,'0',c.cusm_sort_grp_cd)cusm_sort_grp_cd
from inf_v1rpttb a, ste_r1cusgrp b, ste.b1strcus c
where rept_clmn_01_qt = c.site_id_nu(+)
and c.cusm_sort_grp_cd = b.cusm_sort_grp_cd(+)
and rept_seq_nu = 353000
and rept_clmn_01_dt <= '2/1/2001'
and rept_clmn_28_ds = 'PRIMARY'
order by rept_clmn_11_ds, c.cusm_sort_grp_cd, rept_clmn_15_ds,
rept_clmn_08_ds, rept_clmn_01_qt
thanks much in advance!!
Bruce Taneja
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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.com
--
Author: Khedr, Waleed
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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.com
--
Author:
INET: [EMAIL PROTECTED]
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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).
