Oracle 统计量NO_INVALIDATE参数配置

Oracle统计量对于CBO执行是至关重要的。RBO是建立在数据结构的基础上的，DDL结构、约束会将SQL语句分为不同的成本结构等级。而CBO是在数据结构的基础上，加入数据表细粒度信息，将成本结构细化为成本cost值。

相对于数据表的DDL结构，统计量反映了当下数据表数据分布情况，可变性更强。我们经常遇到这样的场景，数据导入操作之后，原有一个运行良好的作业突然效率低下。当我们手工收集一下统计量之后，作业效率提升。这种现象也就是反映了统计量和执行计划的关系。

SGA中的shared pool是进行执行计划缓存的位置。Shared Cursor是SQL语句共享的主要对象。一句SQL语句，如果在Shared Pool中有缓存的执行计划。这个时候，有新的统计量收集动作，有新统计量收集到数据字典中，进而以为了新的执行计划需求。那么，Oracle是如何进行抉择呢？

答案就是dbms_stats的no_invalidate参数。通过不同的参数配置，可以实现对Oracle失效共享游标行为的控制。--------------------------------------分割线 --------------------------------------Oracle 11g 数据统计量Pending处理 http://www.linuxidc.com/Linux/2012-06/63944.htm--------------------------------------分割线 --------------------------------------1、no_invalidate参数 No_invalidate参数从字面上比较纠结。No和in都是否定含义，“负负得正”。参数含义就是validate，也就是是否有效。它决定了新统计量生成之后，如何处理此时已经生成的执行计划，也就是在Shared Pool中的执行计划。

统计量决定SQL执行计划，是CBO的一个特征。但是这个过程是针对新生成的执行计划，也就是新的Parse过程。对于已经生成的执行计划，Oracle是通过no_invalidate参数来处理shared cursor的失效过程。

一个对象（数据表、索引）新统计量生成之后，最简单的方法是一次性将在Shared Pool中有依赖关系的shared cursor失效。下一次再进行SQL执行的时候，必然会用新的执行计划Parse解析过程。另一个极端是无视新统计量的差异，维持现有的Shared Cursor，不会去让其失效。

从性能角度看，两个极端都是有其问题的。如果是一次性将其全部失效，会引起后续作业过程的“解析峰值”。因为，如果系统负载比较高，突然间缓存的执行计划全部被失效，Oracle作业必然要进行一些额外的成本进行执行计划重新生成。这个会体现在系统运行有一个峰值。

如果不将共享游标失效，那么新的统计量不会很快体现在更好执行计划生成的过程。性能提升无从谈起。所以，是否将游标失效，是一个“左右为难”的问题。在Oracle中，no_invalidate参数包括三个取值。 SQL> select * from v$version;BANNER--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - ProductionPL/SQL Release 11.2.0.1.0 - ProductionCORE 11.2.0.1.0 ProductionTNS for Linux: Version 11.2.0.1.0 - ProductionNLSRTL Version 11.2.0.1.0 - Production -- no_invalidate - Do not invalide the dependent cursors if set to TRUE.-- The procedure invalidates the dependent cursors immediately-- if set to FALSE.-- Use DBMS_STATS.AUTO_INVALIDATE to have oracle decide when to-- invalidate dependend cursors. This is the default. The default-- can be changed using set_param procedure.-- When the "cascade" argument is specified, not pertinent with certain-- types of indexes described in the gather_index_stats section. Oracle支持true、false和dbms_stats.auto_invalidate取值。如果取值为true，表示不进行游标失效动作，原有的shared cursor保持原有状态。如果取值为false，表示将统计量对象相关的所有cursor全部失效。如果设置为auto_invalidate，根据官方文档，Oracle自己决定shared cursor失效动作。

从10G开始，Oracle就将auto_invalidate作为默认的统计量收集行为。  SQL> select dbms_stats.get_param（pname => "no_invalidate"） from dual;DBMS_STATS.GET_PARAM（PNAME=>"N--------------------------------------------------------------------------------

DBMS_STATS.AUTO_INVALIDATE 下面，笔者将通过一系列的实验，来证明no_invalidate参数取值的效果。 2、no_invalidate取值为YES 取值为YES，表示不经心共享游标失效动作，即使这个过程中，共享的游标已经不是最优的执行计划。我们创建实验数据表。  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语句，注意：出于篇幅原因，笔者将结果屏蔽。  SQL> select /*+demo*/object_id, owner from t where object_id=1000; 统计信息---------------------------------- 164 recursive calls 0 db block gets 23 consistent gets 0 physical reads （有省略……） 1 rows processed 此时shared pool中情况如下，出现第一个执行计划缓存对象。 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=1000Plan hash value: 514881935--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Ti

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

| 0 | SELECT STATEMENT | | | | 2 （100）|| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 11 | 2 （0）| 00

|* 2 | INDEX RANGE SCAN | IDX_T_ID | 1 | | 1 （0）| 00

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

Predicate Information （identified by operation id）:--------------------------------------------------- 2 - access（"OBJECT_ID"=1000） 19 rows selected 此时，最优的执行计划是索引路径。在shared pool中有一个父游标和子游标（version count=1），执行次数为1。第二次执行之后，Shared Pool中有共享现象。相同的共享游标执行两次。 SQL> select sql_id, executions, version_count from v$sqlarea where sql_text like "select /*+demo*/%";

SQL_ID EXECUTIONS VERSION_COUNT------------- ---------- -------------cnb0ktgvms6vq 2 1 之后，我们更新数据，修改数据分布结构。 SQL> update t set object_id=1000;72729 rows updated SQL> commit;Commit complete 此时，如果执行SQL语句，我们发现依然是使用原有的索引路径。此时全部T表中object_id都是1000，走索引不是好的选择。  SQL> select /*+demo*/object_id, owner from t where object_id=1000;已选择72729行。统计信息------------------------------------------ 0 recursive calls 0 db block gets 11157 consistent gets 0 physical reads    72729 rows processed SQL> select sql_id, executions, version_count from v$sqlarea where sql_text like "select /*+demo*/%";

SQL_ID EXECUTIONS VERSION_COUNT------------- ---------- -------------cnb0ktgvms6vq 3 1 此时的路径依然是Index Range Scan。这个明显是由于统计量的过时，外加游标共享，引起的错误路径。下面我们重新收集一下统计量，采用no_invaliate为true的情况。

  SQL> exec dbms_stats.flush_database_monitoring_info;PL/SQL procedure successfully completed  SQL> exec dbms_stats.gather_table_stats（user,"T",cascade => true,no_invalidate => true,method_opt => "for columns size 10 object_id"）;

PL/SQL procedure successfully completed 新统计量生成，我们使用explain plan查看一下，此时SQL应该采用的执行计划是什么？  SQL> explain plan for select /*+demo*/object_id, owner from t where object_id=1000;

Explained SQL> select * from table（dbms_xplan.display）; PLAN_TABLE_OUTPUT--------------------------------------------------------Plan hash value: 1601196873---------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 72722 | 639K| 266 （1）| 00:00:04 ||* 1 | TABLE ACCESS FULL| T | 72722 | 639K| 266 （1）| 00:00:04 |--------------------------------------------------------------------------Predicate Information （identified by operation id）:--------------------------------------------------- 1 - filter（"OBJECT_ID"=1000）13 rows selected 此时，FTS全表扫描是更好的选择。但是我们查看一下实际执行时候，路径情况。  SQL> select /*+demo*/object_id, owner from t where object_id=1000;已选择72729行。统计信息---------------------------------------------------------- 0 recursive calls 0 db block gets 10907 consistent gets 0 physical reads 0 redo size 72729 rows processed SQL> select sql_id, executions, version_count from v$sqlarea where sql_text like "select /*+demo*/%";

SQL_ID 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=1000Plan hash value: 1601196873--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |--------------------------------------------------------------------------| 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=1000Plan hash value: 514881935--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Ti

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

| 0 | SELECT STATEMENT | | | | 2 （100）|| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 11 | 2 （0）| 00

|* 2 | INDEX RANGE SCAN | IDX_T_ID | 1 | | 1 （0）| 00

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

Predicate Information （identified by operation id）:--------------------------------------------------- 2 - access（"OBJECT_ID"=1000） 19 rows selected 第二次执行相同SQL，我们可以看到生成的shared cursor进行共享。  SQL> select sql_id, executions, version_count, first_load_time from v$sqlarea where sql_text like "select /*+demo*/%";

SQL_ID EXECUTIONS VERSION_COUNT FIRST_LOAD_TIME------------- ---------- ------------- ----------------------------------------------------------------------------

cnb0ktgvms6vq 2 1 2014-01-06/00:04:29 修改数据object_id取值，改变数据分布。  SQL> update t set object_id=1000;72729 rows updated SQL> commit;Commit complete 第三次执行。 SQL> select /*+demo*/object_id, owner from t where object_id=1000;已选择72729行。统计信息-------------------------------------- 0 recursive calls 0 db block gets 11157 consistent gets 72729 rows processed 此时shared cursor状态如下：  SQL> select sql_id, executions, version_count, first_load_time from v$sqlarea where sql_text like "select /*+demo*/%";

SQL_ID EXECUTIONS VERSION_COUNT FIRST_LOAD_TIME------------- ---------- ------------- ----------------------------------------------------------------------------

cnb0ktgvms6vq 3 1 2014-01-06/00:04:29 执行计划是进行Index Range Scan动作。  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=1000Plan hash value: 514881935--------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Ti

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

| 0 | SELECT STATEMENT | | | | 2 （100）|| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 11 | 2 （0）| 00

|* 2 | INDEX RANGE SCAN | IDX_T_ID | 1 | | 1 （0）| 00

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

Predicate Information （identified by operation id）:--------------------------------------------------- 2 - access（"OBJECT_ID"=1000）19 rows selected 收集统计量，使用no_invalidate为false取值。  SQL> exec dbms_stats.flush_database_monitoring_info;PL/SQL procedure successfully completed SQL> exec dbms_stats.gather_table_stats（user,"T",cascade => true,no_invalidate => false,method_opt => "for columns size 10 object_id"）;

PL/SQL procedure successfully completed 第四次执行过程。  SQL> select /*+demo*/object_id, owner from t where object_id=1000;已选择72729行。统计信息---------------------------- 141 recursive calls 0 db block gets 5835 consistent gets    72729 rows processed  SQL> select sql_id, executions, version_count, first_load_time from v$sqlarea where sql_text like "select /*+demo*/%";

SQL_ID EXECUTIONS VERSION_COUNT FIRST_LOAD_TIME------------- ---------- ------------- -------------------cnb0ktgvms6vq 1 1 2014-01-06/00:04:29 注意：在相同的sql_id情况下，version_count和executions都为1。Executions是不可能减少的。所以，这个父游标是新生成的！此时，执行计划如下： 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=1000Plan hash value: 1601196873--------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost （%CPU）| Time |--------------------------------------------------------------------------| 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为false的时候，原有的共享游标被失效，下一次在执行SQL的时候，Oracle会重新为其生成执行计划，也就是一次hard parse过程。

True和false取值是比较简单的。我们接下来讨论dbms_stats.auto_invalidate取值情况。 更多详情见请继续阅读下一页的精彩内容： http://www.linuxidc.com/Linux/2014-05/101279p2.htm

Linux下利用文件描述符恢复的成功失败实验使用Procwatcher监控Oracle数据库锁定Contention相关资讯 Oracle参数 Oracle统计量

Oracle升级中的参数补充（05月31日）
获取Oracle隐含参数信息（11/05/2014 09:58:10）
Oracle动态服务器参数文件（09/26/2014 19:36:24）

Oracle 参数调优（04/12/2015 18:05:19）
Oracle初始化参数之memory_target （10/08/2014 13:21:41）
Oracle静态参数文件（09/26/2014 19:33:54）

本文评论查看全部评论（0）