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