Auto trace is giving wrong plan.

Hi Friends,

Hi,



In this case "set autot on" is telling false plan with binding.



Can you put some light on this ...





SQL> desc ib big_table

Name Null Type

-------------------------- -------- ----------

R NUMBER

DATA VARCHAR2(300)



SQL> select count(*),r from big_Table w group by r;



COUNT(*) R

---------- ----------

49999 1

1 2



I have created index in Big_table ( R )



SQL> exec
dbms_stats.gather_Table_Stats(user,'BIG_TABLE',method_opt=>'for all
indexed columns',cascade=>true)



PL/SQL procedure successfully completed.



SQL>

SQL> set autot on exp





Here its showing the correct paln.

===================================



SQL> select count(data) from big_table where r = 1 ;



COUNT(DATA)

-----------

49999





Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1556 Card=1 Bytes=100)

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=1556 Card=49999
Bytes=4999900)









SQL> select count(data) from big_table where r = 2 ;



COUNT(DATA)

-----------

1





Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1 Bytes=100)

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (BY INDEX ROWID) OF 'BIG_TABLE' (Cost=4 Card=1
Bytes= 100)

3 2 INDEX (RANGE SCAN) OF 'IND_BIG_TABLE' (NON-UNIQUE) (Cost=3 Card=
1)







SQL> var n number

SQL>

SQL> exec :n := 1



PL/SQL procedure successfully completed.



SQL>

SQL> set autot on

SQL>

SQL> select count(data) from big_Table where r = :n ;



COUNT(DATA)

-----------

49999





Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1556 Card=1 Bytes=100)

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=1556 Card=25000
Bytes=250

0000)







Statistics

----------------------------------------------------------

0 db block gets

10024 consistent gets

0 physical read





SQL> exec :n := 2



PL/SQL procedure successfully completed.



SQL> select count(data) from big_Table where r = :n ;





COUNT(DATA)

-----------

1





Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1556 Card=1 Bytes=100)

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=1556 Card=25000
Bytes=2500000)







Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

10024 consistent gets



-- still it is using the same plan with 10024 block gets



-- I have put one space extra before where clause to make

-- it a new statement to hard parse





SQL> select count(data) from big_Table where r = :n ;



COUNT(DATA)

-----------

1





Execution Plan

----------------------------------------------------------

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1556 Card=1 Bytes=100)

1 0 SORT (AGGREGATE)

2 1 TABLE ACCESS (FULL) OF 'BIG_TABLE' (Cost=1556 Card=25000
Bytes=250

0000)





Statistics

----------------------------------------------------------

0 recursive calls

0 db block gets

4 consistent gets



Still showing "Full table Scan" but with 4 block gets.



--- From tkprof ---



********************************************************************************



select count(data)

from

big_Table where r = :n





call count cpu elapsed disk query current
rows

------- ------ -------- ---------- ---------- ---------- ----------
----------

Parse 1 0.00 0.00 0 0 0
0

Execute 1 0.00 0.00 0 0 0
0

Fetch 2 0.00 0.00 0 4 0
1

------- ------ -------- ---------- ---------- ---------- ----------
----------

total 4 0.00 0.00 0 4 0
1



Misses in library cache during parse: 1

Optimizer goal: CHOOSE

Parsing user id: 23



Rows Row Source Operation

------- ---------------------------------------------------

1 SORT AGGREGATE (cr=4 r=0 w=0 time=202 us)

1 TABLE ACCESS BY INDEX ROWID BIG_TABLE (cr=4 r=0 w=0 time=170
us)

1 INDEX RANGE SCAN IND_BIG_TABLE (cr=3 r=0 w=0 time=118
us)(object id 24087)



********************************************************************************

.

Relevant Pages