Auto trace is giving wrong plan.

From: Jagjeet Singh <jagjeet.malhi_at_gmail.com>
Date: 22 Jan 2006 05:16:58 -0800
Message-ID: <1137935818.802456.297160@g43g2000cwa.googlegroups.com>

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)

---

Received on Sun Jan 22 2006 - 07:16:58 CST