dbms_stats收集統計資訊時候no_invalidate引數
用於是否與收集相關object的cursor失效，defalut(9i false, 10g dbms_stats.auto_invalidate(既null))
true：當收集完統計資訊後，收集物件的cursor不會失效（不會產生新的執行計劃，子游標）
false:當收集完統計資訊後，收集物件的cursor會立即失效（新的執行計劃，新的子游標）
dbms_stats.auto_invalidate(既null):收集後，收集物件的cursor在一段時間後失效（新執行計劃，新子游標），避免集中失效

測試方法：
t1,t2表每表10000 rows
收集統計資訊執行select 執行計劃均為fts
對錶中資料做更新（del），重新收集執行計劃，t2表 no_invalidate=》false，執行select

SQL> select * from v$version where rownum<2;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod

SQL> show user
USER is "XH"
SQL> drop table t1;

Table dropped.

SQL> create table t1 (a int,b int) ;

Table created.

declare
   begin
   for i in 1..10000 loop
insert into t1 values(i,i+1);
   end loop;
    commit;
    end;

PL/SQL procedure successfully completed.

SQL> create index t1_ind on t1(a);

Index created.

SQL> execute dbms_stats.gather_table_stats('XH','T1');

PL/SQL procedure successfully completed.

create table t2 (a int,b int) ;

declare
   begin
   for i in 1..10000 loop
insert into t2 values(i,i+1);
   end loop;
    commit;
    end;

create index t2_ind on t2(a);

execute dbms_stats.gather_table_stats('XH','T2');

SQL> conn xh/a831115
Connected.
SQL> select distinct sid from v$mystat;

SID
----------
149

SQL> conn xh/a831115
Connected.
SQL>
SQL> select distinct sid from v$mystat;

SID
----------
147

SQL> select owner,num_rows from dba_tables where table_name in ('T1','T2');

OWNER                            NUM_ROWS
------------------------------ ----------
XH                                  10000
XH                                  10000

select * from t1 where a>4000;（sid 149）

SQL> col sql_text for a40
SQL> select b.sql_id ,b.sql_text,b.hash_value,b.child_number,b.plan_hash_value from v$session a ,v$sql b where a.sid=&sid and b.hash_value in (a.sql_hash_value,a.prev_hash_value);
Enter value for sid: 149
old 1: select b.sql_id ,b.sql_text,b.hash_value,b.child_number,b.plan_hash_value from v$session a ,v$sql b where a.sid=&sid and b.hash_value in (a.sql_hash_value,a.prev_hash_value)
new 1: select b.sql_id ,b.sql_text,b.hash_value,b.child_number,b.plan_hash_value from v$session a ,v$sql b where a.sid=149 and b.hash_value in (a.sql_hash_value,a.prev_hash_value)

SQL_ID        SQL_TEXT                                 HASH_VALUE CHILD_NUMBER
------------- ---------------------------------------- ---------- ------------
PLAN_HASH_VALUE
---------------
048c31zxwmr2w select * from t1 where a>4000            4224310364            0
     3617692013

SQL> select * from table(dbms_xplan.display_cursor('048c31zxwmr2w'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 048c31zxwmr2w, child number 0
-------------------------------------
select * from t1 where a>4000

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |     6 (100)|          |
|* 1 | TABLE ACCESS FULL| T1   | 6001 | 42007 |     6   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------

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

1 - filter("A">4000)

18 rows selected.

select * from t2 where a>4000;（sid 147）

SQL> select b.sql_id ,b.sql_text,b.hash_value,b.child_number,b.plan_hash_value from v$session a ,v$sql b where a.sid=&sid and b.hash_value in (a.sql_hash_value,a.prev_hash_value);
Enter value for sid: 147
old 1: select b.sql_id ,b.sql_text,b.hash_value,b.child_number,b.plan_hash_value from v$session a ,v$sql b where a.sid=&sid and b.hash_value in (a.sql_hash_value,a.prev_hash_value)
new 1: select b.sql_id ,b.sql_text,b.hash_value,b.child_number,b.plan_hash_value from v$session a ,v$sql b where a.sid=147 and b.hash_value in (a.sql_hash_value,a.prev_hash_value)

SQL_ID        SQL_TEXT                                 HASH_VALUE CHILD_NUMBER
------------- ---------------------------------------- ---------- ------------
PLAN_HASH_VALUE
---------------
8xn3g0g1qc7nq select * from t2 where a>4000            3278249622            0
     1513984157

SQL> select * from table(dbms_xplan.display_cursor('8xn3g0g1qc7nq'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 8xn3g0g1qc7nq, child number 0
-------------------------------------
select * from t2 where a>4000

Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |     6 (100)|          |
|* 1 | TABLE ACCESS FULL| T2   | 6001 | 42007 |     6   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------

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

1 - filter("A">4000)

18 rows selected.

SQL> delete t1 where a>=5000;

5001 rows deleted.

SQL> delete t2 where a>=5000;

5001 rows deleted.

SQL> commit;

Commit complete.

SQL>

SQL> execute dbms_stats.gather_table_stats('XH','T1');

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.gather_table_stats('XH','T2',no_invalidate=>FALSE);***********

PL/SQL procedure successfully completed.

SQL> select owner,num_rows from dba_tables where table_name in ('T1','T2');

OWNER                            NUM_ROWS
------------------------------ ----------
XH                                   4999
XH                                   4999

select * from t1 where a>4000;（sid 149)

SQL> select b.sql_id ,b.sql_text,b.hash_value,b.child_number,b.plan_hash_value from v$session a ,v$sql b where a.sid=&sid and b.hash_value in (a.sql_hash_value,a.prev_hash_value);
Enter value for sid: 149
old 1: select b.sql_id ,b.sql_text,b.hash_value,b.child_number,b.plan_hash_value from v$session a ,v$sql b where a.sid=&sid and b.hash_value in (a.sql_hash_value,a.prev_hash_value)
new 1: select b.sql_id ,b.sql_text,b.hash_value,b.child_number,b.plan_hash_value from v$session a ,v$sql b where a.sid=149 and b.hash_value in (a.sql_hash_value,a.prev_hash_value)

SQL_ID        SQL_TEXT                                 HASH_VALUE CHILD_NUMBER
------------- ---------------------------------------- ---------- ------------
PLAN_HASH_VALUE
---------------
048c31zxwmr2w select * from t1 where a>4000            4224310364            0
     3617692013

SQL> select * from table(dbms_xplan.display_cursor('048c31zxwmr2w'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 048c31zxwmr2w, child number 0
-------------------------------------
select * from t1 where a>4000

Plan hash value: 3617692013

--------------------------------------------------------------------------
| Id | Operation         | Name | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |      |       |       |     6 (100)|          |
|* 1 | TABLE ACCESS FULL| T1   | 6001 | 42007 |     6   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------

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

1 - filter("A">4000)

18 rows selected.

select * from t2 where a>4000;（sid 147）

SQL> select b.sql_id ,b.sql_text,b.hash_value,b.child_number,b.plan_hash_value from v$session a ,v$sql b where a.sid=&sid and b.hash_value in (a.sql_hash_value,a.prev_hash_value);
Enter value for sid: 147
old 1: select b.sql_id ,b.sql_text,b.hash_value,b.child_number,b.plan_hash_value from v$session a ,v$sql b where a.sid=&sid and b.hash_value in (a.sql_hash_value,a.prev_hash_value)
new 1: select b.sql_id ,b.sql_text,b.hash_value,b.child_number,b.plan_hash_value from v$session a ,v$sql b where a.sid=147 and b.hash_value in (a.sql_hash_value,a.prev_hash_value)

SQL_ID        SQL_TEXT                                 HASH_VALUE CHILD_NUMBER
------------- ---------------------------------------- ---------- ------------
PLAN_HASH_VALUE
---------------
8xn3g0g1qc7nq select * from t2 where a>4000            3278249622            0
     1173409066

可以看到plan hash value變了，執行計劃也變

SQL> select * from table(dbms_xplan.display_cursor('8xn3g0g1qc7nq'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 8xn3g0g1qc7nq, child number 0
-------------------------------------
select * from t2 where a>4000

Plan hash value: 1173409066

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

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------

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

| 0 | SELECT STATEMENT | | | | 6 (100)|
|

| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 999 | 6993 | 6 (0)| 00:0
0:01 |

|* 2 | INDEX RANGE SCAN | T2_IND | 999 | | 4 (0)| 00:0

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
0:01 |

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

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

2 - access("A">4000)

19 rows selected.

oracle dbms_stats(no_invalidate引數)

相關文章