oracle dbms_stats(no_invalidate参数)

最新推荐文章于 2022-10-18 11:49:41 发布

cuiye1492

最新推荐文章于 2022-10-18 11:49:41 发布

阅读量488

点赞数

文章标签：数据库

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

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> select * from table(dbms_xplan.display_cursor('048c31zxwmr2w'));

Plan hash value: 3617692013

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

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

1 - filter("A">4000)

18 rows selected.

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

可以看到plan hash value变了，执行计划也变

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

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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12020513/viewspace-627031/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12020513/viewspace-627031/

cuiye1492

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle dbms_stats(no_invalidate参数)

dbms_stats收集统计信息时候no_invalidate参数用于是否与收集相关object的cursor失效，defalut(9i false, 10g dbms_stats.auto_invalidate(既null))...
复制链接

扫一扫