Re: bind variable peeking and regular statistics gathering

yes it will

consider:

SQL> create table t as
 2   select case when level < 10000 then 0 else 1 end n, level m
 3    from dual
 4    connect by level <= 10000;

Table created.

SQL> create index i_t on t (n) nologging;

Index created.

SQL> begin
 2   dbms_stats.gather_table_stats(
 3    ownname => user,
 4    tabname => 't',
 5    method_opt => 'for all columns size 2',
 6    cascade => true,
 7    no_invalidate => false
 8   );
 9  end;
10  /

PL/SQL procedure successfully completed.

SQL> variable x number;
SQL> exec :x:=1;

PL/SQL procedure successfully completed.

SQL> select * from t where n=:x;

        N          M
---------- ----------
        1      10000

SQL> select sql_id
 2   from v$sql
 3   where sql_text='select * from t where n=:x';

SQL_ID
-------------
fd249hypt6ktq

QL> select *
2   from table (dbms_xplan.display_cursor('fd249hypt6ktq'));

LAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
QL_ID  fd249hypt6ktq, child number 0
------------------------------------
elect * from t where n=:x

lan hash value: 2928007915

-----------------------------------------------------------------------------------
Id  | Operation                   | Name | Rows  | Bytes | Cost
(%CPU)| Time     |
-----------------------------------------------------------------------------------
  0 | SELECT STATEMENT            |      |       |       |     2
(100)|          |
  1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |     5 |     2
(0)| 00:00:01 |
*  2 |   INDEX RANGE SCAN          | I_T  |     1 |       |     1
(0)| 00:00:01 |
-----------------------------------------------------------------------------------

redicate Information (identified by operation id):
--------------------------------------------------

 2 - access("N"=:X)


9 rows selected.

now change the variable value

SQL> exec :x:=0;

PL/SQL procedure successfully completed.

SQL> select * from t where n=:x;

9999 rows selected.

SQL> select sql_id
 2   from v$sql
 3   where sql_text='select * from t where n=:x';

SQL_ID
-------------
fd249hypt6ktq

SQL> select *
 2   from table (dbms_xplan.display_cursor('fd249hypt6ktq'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------
SQL_ID  fd249hypt6ktq, child number 0
-------------------------------------
select * from t where n=:x

Plan hash value: 2928007915

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost
(%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |       |       |     2
(100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |     1 |     5 |     2
(0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | I_T  |     1 |       |     1
(0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  2 - access("N"=:X)


19 rows selected.

the plan is the same and there is no child cursors

SQL> begin
 2   dbms_stats.gather_table_stats(
 3    ownname => user,
 4    tabname => 't',
 5    method_opt => 'for all columns size 2',
 6    cascade => true,
 7    no_invalidate => false
 8   );
 9  end;
10  /

PL/SQL procedure successfully completed.

SQL> select * from t where n=:x;

9999 rows selected.

SQL> select *
 2   from table (dbms_xplan.display_cursor('fd249hypt6ktq'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------
SQL_ID  fd249hypt6ktq, child number 0
-------------------------------------
select * from t where n=:x

Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     5 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |  9999 | 49995 |     5   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - filter("N"=:X)


18 rows selected.

plan changed considering the new variable value

On 5/31/07, Ujang Jaenudin <ujang.jaenudin@xxxxxxxxx> wrote:

all,

i'm not sure about the relation between bind variable peeking and
invalidation in the regard of regularly gathering statistics.

will the bind variable peeking will re-peek when invalidation occurs
after gathering statistics?

--
regards
ujang
--
http://www.freelists.org/webpage/oracle-l






--
Alexander Fatkulin
--
http://www.freelists.org/webpage/oracle-l

Other related posts: