Oracle 统计量NO_INVALIDATE参数配置 - 数据库编程

EXECUTIONS VERSION_COUNT

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

cnb0ktgvms6vq 4 1

此时，Oracle依然选择了原来的Index路径，原有的shared cursor没有失效！！如果我们此时将shared pool清空，新的FTS执行计划也就生成。

SQL> alter system flush shared_pool;

System altered

SQL> select sql_id, executions, version_count from v$sqlarea where sql_text like 'select /*+demo*/%';

SQL_ID EXECUTIONS VERSION_COUNT

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

SQL> select /*+demo*/object_id, owner from t where object_id=1000;

已选择72729行。

统计信息

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

243 recursive calls

0 db block gets

5855 consistent gets

0 physical reads

72729 rows processed

--新的shared cursor形成

SQL> select sql_id, executions, version_count from v$sqlarea where sql_text like 'select /*+demo*/%';

SQL_ID EXECUTIONS VERSION_COUNT

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

cnb0ktgvms6vq 1 1

--FTS执行计划

SQL> select * from table(dbms_xplan.display_cursor(sql_id => 'cnb0ktgvms6vq'));

PLAN_TABLE_OUTPUT

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

SQL_ID cnb0ktgvms6vq, child number 0

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

select /*+demo*/object_id, owner from t where object_id=1000

Plan hash value: 1601196873

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

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

| 0 | SELECT STATEMENT | | | | 266 (100)| |

|* 1 | TABLE ACCESS FULL| T | 72722 | 639K| 266 (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

1 - filter("OBJECT_ID"=1000)

18 rows selected

结论：当我们使用no_invalidate为true的时候，原有的shared cursor不会被失效，可以支持共享。只有当被age out或者flush out出shared pool之后，新执行计划才能生成。

3、no_invalidate=false

下面我们看看取值为false的情况，实验场景相同。为避免影响，我们重新构建数据表。

SQL> drop table t purge;

Table dropped

SQL> alter system flush shared_pool;

System altered

SQL> create table t as select * from dba_objects;

Table created

SQL> create index idx_t_id on t(object_id);

Index created

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

第一次执行SQL语句，形成Index路径执行计划。

SQL> select /*+demo*/object_id, owner from t where object_id=1000;

统计信息

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

164 recursive calls

0 db block gets

23 consistent gets

1 rows processed

SQL> select sql_id, executions, version_count from v$sqlarea where sql_text like 'select /*+demo*/%';

SQL_ID EXECUTIONS VERSION_COUNT

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

cnb0ktgvms6vq 1 1

SQL> select * from table(dbms_xplan.display_cursor(sql_id=>'cnb0ktgvms6vq'));

PLAN_TABLE_OUTPUT

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

SQL_ID cnb0ktgvms6vq, child number 0

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

select /*+demo*/object_id, owner from t where object_id=1000

Plan hash value: 514881935

Oracle 统计量NO_INVALIDATE参数配置(四)