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: Baker, Barbara <bbaker_at_denvernewspaperagency.com>
Date: Fri, 19 Jul 2002 12:55:49 -0800
Message-ID: <F001.0049D0FD.20020719125549@fatcity.com>


Doh!
Of course the hint goes in the sub-query. (It's Friday, after all)
That fixes it!!

Thanks so much everyone for your replys.

Barb

> ----------
> From: Jesse, Rich[SMTP:Rich.Jesse_at_qtiworld.com]
> Reply To: ORACLE-L_at_fatcity.com
> Sent: Friday, July 19, 2002 1:43 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: bind vars change explain plan
>
> Just wondering if placing the hint on the inner SELECT would affect the
> outcome since that's where WO is being called out.
>
> HTH! GL!
>
> Rich Jesse System/Database Administrator
> Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex, WI
> USA
>
>
> > -----Original Message-----
> > From: Baker, Barbara [mailto:bbaker_at_denvernewspaperagency.com]
> > Sent: Friday, July 19, 2002 1:23 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: bind vars change explain plan
> >
> >
> > 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: Jesse, Rich
> INET: Rich.Jesse_at_qtiworld.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: 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).
Received on Fri Jul 19 2002 - 15:55:49 CDT

Original text of this message

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