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: HINTS , how to add correctly

RE: HINTS , how to add correctly

From: Koivu, Lisa <lkoivu_at_qode.com>
Date: Fri, 16 Mar 2001 08:10:43 -0800
Message-ID: <F001.002CED1E.20010316051052@fatcity.com>

Also, as our esteemed colleagues on this list have pointed out in the past, deleting statistics and then analyzing can make a difference as well.  Don't ask why.

Lisa Rutland Koivu
Oracle Database Administrator
Qode.com
4850 North State Road 7
Suite G104
Fort Lauderdale, FL  33319

V: 954.484.3191, x174
F: 954.484.2933 
C: 954.658.5849

http://www.qode.com

"The information contained herein does not express the opinion or position of Qode.com and cannot be attributed to or made binding upon Qode.com."

-----Original Message-----
From: Khedr, Waleed [mailto:Waleed.Khedr_at_FMR.COM] Sent: Thursday, March 15, 2001 7:21 PM
To: Multiple recipients of list ORACLE-L Subject: RE: HINTS , how to add correctly

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: bruce.taneja_at_mcd.com
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: 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.com -- Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.COM
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: 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 Fri Mar 16 2001 - 10:10:43 CST

Original text of this message

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