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: bind vars change explain plan

Re: bind vars change explain plan

From: <Cherie_Machler_at_gelco.com>
Date: Fri, 19 Jul 2002 11:23:34 -0800
Message-ID: <F001.0049CE6C.20020719112334@fatcity.com> 






Barb,



Do you have histograms defined on the columns referenced in the where
clause?   Is your data heavily skewed?



Bind variables cannot use histograms.  So if you have an execution plan
that was using histograms to select a very fast access path, and you switch
to bind variables, then your SQL becomes ignorant of the data distribution
on the columns/indexes that have histograms defined on them.



Cherie Machler


Oracle DBA


Gelco Information Network



                                                                                                                           
                    "Baker, Barbara"                                                                                       
                    <bbaker_at_denvernewspapera       To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>     
                    gency.com>                     cc:                                                                     
                    Sent by:                       Subject:     bind vars change explain plan                              
                    root_at_fatcity.com                                                                                       
                                                                                                                           
                                                                                                                           
                    07/19/02 01:23 PM                                                                                      
                    Please respond to                                                                                      
                    ORACLE-L                                                                                               
                                                                                                                           
                                                                                                                           




Env:       OpenVMS 7.2-1
           Oracle 7.3.4.4
           VB code feeding data to a Crystal report





This query runs in less than a second without the bind variables.  Same
query takes about 10 minutes with the bind variables.
Originally I accused VB, but when I fake the bind variables in sql*plus and
run just this query directly on the server, the same thing happens.  I just
told a developer to run without bind variables!  Yuk!



The explain plan changes when using binds.  The non-bind-var code uses an
index on wo (i_alt_wo_act_date); the bind-var code does not use this index.
Is there something about using bind variables that will invalidate using an
index?  I tried forcing an index hint, but it does not work.



Sorry if I'm missing something obvious.


Thanks for any help!



Barb


SLOW (using bind's)



SELECT /*+ INDEX(WO I_ALT_WO_ACT_DATE) */

job_nbr, sig_code, text, ROWID


FROM orr ox


WHERE job_nbr IN (SELECT job_nbr FROM wo
WHERE act_date_time

  BETWEEN TO_DATE(:v_bdat,'MM/DD/YYYY HH12:MI:SS AM')
      AND TO_DATE(:v_edat,'MM/DD/YYYY HH12:MI:SS AM')
      AND (wo.pub IN ('ROP','ISRT','TMC','COMC','TVWK','PRNT')
       OR (wo.pub = 'WEB' AND wo.edition NOT LIKE 'C%')))


AND ox.orr_date_time <= TO_DATE(:v_odat,'MM/DD/YYYY HH12:MI:SS AM')
ORDER BY job_nbr, ROWID



call     count       cpu    elapsed       disk      query    current
rows


------- ------  -------- ---------- ---------- ---------- ----------




Parse        1      0.00       0.02          0          0          0
0


Execute      1      0.00       0.00          0          0          0
0


Fetch        1     44.13     534.16      90292     284168          2
0


------- ------  -------- ---------- ---------- ---------- ----------




total        3     44.13     534.18      90292     284168          2
0



Misses in library cache during parse: 0


Optimizer goal: CHOOSE


Parsing user id: 21  (ADMARC)



Rows     Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   SORT (ORDER BY)
      7    HASH JOIN
      1     VIEW
      1      SORT (UNIQUE)
      0       CONCATENATION
      0        TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF 'WO'
      2         INDEX   GOAL: ANALYZED (RANGE SCAN) OF


                    'I_ALT_WO_ACT_DATE' (NON-UNIQUE)

      0        TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF 'WO'
      2         INDEX   GOAL: ANALYZED (RANGE SCAN) OF


                    'I_ALT_WO_ACT_DATE' (NON-UNIQUE)

     31        TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF 'WO'
     32         INDEX   GOAL: ANALYZED (RANGE SCAN) OF


                    'WO_ISSUE_DATE_PUB_IDX' (NON-UNIQUE)

      6        TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF 'WO'
      7         INDEX   GOAL: ANALYZED (RANGE SCAN) OF


                    'WO_ISSUE_DATE_PUB_IDX' (NON-UNIQUE)

      0        TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF 'WO'
      1         INDEX   GOAL: ANALYZED (RANGE SCAN) OF


                    'WO_ISSUE_DATE_PUB_IDX' (NON-UNIQUE)

   8280        TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF 'WO'
   8281         INDEX   GOAL: ANALYZED (RANGE SCAN) OF


                    'WO_ISSUE_DATE_PUB_IDX' (NON-UNIQUE)

 188242        TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF 'WO'
 188243         INDEX   GOAL: ANALYZED (RANGE SCAN) OF


                    'WO_ISSUE_DATE_PUB_IDX' (NON-UNIQUE)

 129553     TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'ORR'






FAST (without binds)



SELECT job_nbr, sig_code, text, ROWID


FROM orr


WHERE job_nbr IN (SELECT job_nbr FROM wo
WHERE act_date_time


  BETWEEN TO_DATE('07/17/2002 3:00:00 PM','MM/DD/YYYY HH12:MI:SS AM')


      AND TO_DATE('07/17/2002 4:00:00 PM','MM/DD/YYYY HH12:MI:SS AM')
      AND (wo.pub IN ('ROP','ISRT','TMC','COMC','TVWK','PRNT')
       OR (wo.pub = 'WEB'
          AND wo.edition NOT LIKE 'C%')))


AND orr.orr_date_time <=


  TO_DATE('07/17/2002 4:00:00 PM','MM/DD/YYYY HH12:MI:SS AM')

ORDER BY job_nbr, ROWID



call     count       cpu    elapsed       disk      query    current
rows


------- ------  -------- ---------- ---------- ---------- ----------




Parse        1      0.01       0.00          0          0          0
0


Execute      1      0.00       0.00          0          0          0
0


Fetch        1      0.72       4.40       9507       4546          2
0


------- ------  -------- ---------- ---------- ---------- ----------




total        3      0.73       4.40       9507       4546          2
0



Misses in library cache during parse: 0


Optimizer goal: CHOOSE


Parsing user id: 21  (ADMARC)



Rows     Execution Plan

-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      0   SORT (ORDER BY)
      7    HASH JOIN
      1     VIEW
      1      SORT (UNIQUE)
      0       CONCATENATION
      0        TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF 'WO'
      2         INDEX   GOAL: ANALYZED (RANGE SCAN) OF


                    'I_ALT_WO_ACT_DATE' (NON-UNIQUE)

      0        TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF 'WO'
      2         INDEX   GOAL: ANALYZED (RANGE SCAN) OF


                    'I_ALT_WO_ACT_DATE' (NON-UNIQUE)

      0        TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF 'WO'
      2         INDEX   GOAL: ANALYZED (RANGE SCAN) OF


                    'I_ALT_WO_ACT_DATE' (NON-UNIQUE)

      0        TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF 'WO'
      2         INDEX   GOAL: ANALYZED (RANGE SCAN) OF


                    'I_ALT_WO_ACT_DATE' (NON-UNIQUE)

      0        TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF 'WO'
      2         INDEX   GOAL: ANALYZED (RANGE SCAN) OF


                    'I_ALT_WO_ACT_DATE' (NON-UNIQUE)

      0        TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF 'WO'
      2         INDEX   GOAL: ANALYZED (RANGE SCAN) OF


                    'I_ALT_WO_ACT_DATE' (NON-UNIQUE)

      1        TABLE ACCESS   GOAL: ANALYZED (BY ROWID) OF 'WO'
      2         INDEX   GOAL: ANALYZED (RANGE SCAN) OF


                    'I_ALT_WO_ACT_DATE' (NON-UNIQUE)

 129553     TABLE ACCESS   GOAL: ANALYZED (FULL) OF 'ORR'




--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Baker, Barbara
  INET: bbaker_at_denvernewspaperagency.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: 
  INET: Cherie_Machler_at_gelco.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 Jul 19 2002 - 14:23:34 CDT












Original text of this message













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