Re: bind variable peeking and regular statistics gathering

From: Alexander Fatkulin <afatkulin_at_gmail.com>
Date: Thu, 31 May 2007 13:21:46 -0400
Message-ID: <49d668000705311021i5811c64fta1ed3dc6e091ac9f@mail.gmail.com>

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

|   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_at_gmail.com> 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

Received on Thu May 31 2007 - 12:21:46 CDT