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: <dgoulet_at_vicr.com>
Date: Fri, 19 Jul 2002 12:18:36 -0800
Message-ID: <F001.0049D051.20020719121836@fatcity.com> 





Barb,



    Two questions:



    
  
  1.   When was the last time the WO table and it's indexes were analyzed?
  
  2.   What columns are in the I_ALT_WO_ACT_DATE and WO_ISSUE_DATE_PUB_IDX
indexes, along with their status from USER_VIEWS.






For some reason the optimizer has switched indexes on you.  This may well have
to do with the use of bind variables, but then it may not.  With bind variables
the optimizer cannot use the statistics as well.



Dick Goulet


____________________Reply Separator____________________
Author: "Baker; Barbara" <bbaker_at_denvernewspaperagency.com>
Date:       7/19/2002 10:23 AM

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: dgoulet_at_vicr.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 - 15:18:36 CDT












Original text of this message













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