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).