Re: Strange problem.

From: Thomas Kyte <thomas.kyte_at_oracle.com>
Date: 9 Dec 2005 03:57:42 -0800
Message-ID: <144129462.0001619c.061.0001@drn.newsguy.com>

In article <Xns972499C5D4805skiloversofthomenet_at_213.155.197.138>, Chuck says...
>
>"hpuxrac" <johnbhurley_at_sbcglobal.net> wrote in
>news:1133897567.973214.13360_at_g44g2000cwa.googlegroups.com:
....
>
>Have you got an article number or URL? I've searched asktom.oracle.com for
>"explain plan" "explain plan wrong" "force execution pan and "execution
>plan". Each yields hundreds of articles none of which seem to be about
>forcing oracle to choose the execution plan that explain plan says it's
>going to choose.

explain plan cannot

1. bind variable peek
2. understand the datatype of the bind (it always thinks "string" regardless)

to see this, we can create the following objects:

create table t as select '99' id, to_char(rownum) id2, a.* from all_objects a; update t set id = '1' where rownum = 1;
create index t_idx on t(id);
create index t_idx2 on t(id2);
exec dbms_stats.gather_table_stats( user, 'T', method_opt=>'for all indexed columns' );

variable x varchar2(10)
variable y number

alter session set sql_trace=true;
set autotrace traceonly statistics;
exec :x := '1';
select * from t where id = :x;
exec :y := 1
select * from t where id2 = :y;
set autotrace off

Now, the first query select * from t where id = :x, when hard parsed with :x := '1', will "bind peek" and really optimize:

select* from t where id = '1';

however, explain plan isn't that smart, explain plan will not see the '1', will assume "anything" and explain plan will say "FULL SCAN". But reality will say "INDEX RANGE". We can see that in the tkprof:

select * from t where id = :x

Rows Row Source Operation

-------  ---------------------------------------------------
      0  TABLE ACCESS BY INDEX ROWID T (cr=2 pr=0 pw=0 time=28 us)
      0   INDEX RANGE SCAN T_IDX (cr=2 pr=0 pw=0 time=21 us)(object id 55369)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      0   TABLE ACCESS   MODE: ANALYZED (FULL) OF 'T' (TABLE)

I did the tkprof with explain=u/p - the row source operation is reality, execution plan is explain plan. they are different.

For the second one, we have a indexed column ID2 - it is a string. We are binding however a number. But explain plan sees "varchar2", hence - we cannot really index range scan (where id2 = :y is converted into where TO_NUMBER(id2) = :y when we bind the number, making the index not usable anymore):

---

select * from t where id2 = :y

Rows Row Source Operation

-------  ---------------------------------------------------
      1  TABLE ACCESS FULL T (cr=754 pr=0 pw=0 time=5 us)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   MODE: ALL_ROWS
      1   TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 'T' (TABLE)
      0    INDEX   MODE: ANALYZED (RANGE SCAN) OF 'T_IDX2' (INDEX)

so, explain plan say index - but reality says full scan....

Best to use explain plan with "constants", use to_date for all things you bind as dates (java can bind as a date depending on how you bind) and vary the constants to be representative of some of the binds you expect to be using.

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation

Received on Fri Dec 09 2005 - 05:57:42 CST