Oracle 统计量NO_INVALIDATE参数配置（下）

本篇我们继续讨论NO_INVALIDATE参数。

从上篇（http://blog.itpub.net/17203031/viewspace-1067312/）讨论情况看，无论是取值true还是false，Oracle进行的行为都是缺乏考量的。如果选择true，表示旧的执行计划会持续的在shared pool中驻留，新的执行计划不会生成，如果系统sql运行比较频繁、Age Out现象比较少，更好地执行计划也许不会出现。

另一个极端是false取值，Oracle会将新统计量涉及的所有shared pool一次性设置为失效。这样的好处是可以保证更好执行计划的生成，但是也存在一个性能spike现象。通常统计量的收集是一个集中作业过程，也就是说，通常是绝大多数业务数据表同时进行统计量生成过程。如果设置为false，也就意味着在一个短时间内，Oracle Shared Pool中大部分的shared cursor全部失效，又重新生成执行计划。这样，从整体上就会有一个hard parse高峰期，严重的话会影响到业务运行。

4、no_invalidate=dbms_stats.auto_invalidate

针对这种左右为难的现象，Oracle 10g引入了参数dbms_stats.auto_invalidate作为NO_INVALIDATE的默认值。从官方解释看，这个参数的作业就是“让Oracle来决定是不是对shared cursor进行失效动作”。那么，其中的算法原则是如何呢？我们本篇来讨论这个取值过程。

Auto_invalidate过程的原则是避免true和false的极端情况，既要实现新执行计划的生成，也要避免性能spike的出现。Oracle选择的策略是“延时”，就是让shared pool中的共享游标不会一次性的失效，而是“慢慢的”、“有差别的”失效。这样就避免了hard parse过程中出现spike。

在auto_invalidate取值进行统计量收集的情况下，shared cursor失效原则如下：

ü当新对象的统计量获得时，与其有依赖关系的shared cursor对象不是一次性的失效，而是被进行标注。在Oracle中，被称为“Rolling Invalidation”；

ü当第二次sql进行解析的时候，会记录时间戳信息。这个时间戳会与系统内部隐含参数“_optimizer_invalidation_period”+一个随机时间秒数进行比较。如果时间差还没有超过这个设定，第二次sql就会依然使用之前的旧shared cursor。依然是一个软解析过程；

ü当一个sql解析过程中，设定的时间超过了时间间隔。Oracle会启动一个硬解析过程，生成一个新的child cusor执行计划。原有的子游标被标注为roll_invalidate，失效。我们可以通过视图v$sql_shared_cursor来查看；

从auto_invalidate的规则看，Oracle不是不进行共享游标的失效过程，而是将其分散在一个时间范围内，隐含参数“_optimizer_invalidation_period”来控制时间范围起点。通过这样的手段算法，来缓解硬解析带来的性能spike现象。

下面我们通过实验来证明结论。为防止11g的自适应游标影响，我们选择简单的10g版本进行测试。

sql> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/sql Release 10.2.0.1.0 - Production

CORE10.2.0.1.0Production

TNS for Linux: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

默认的参数取值为dbms_stats.no_invalidate。

sql> select dbms_stats.get_param('no_invalidate') from dual;

DBMS_STATS.GET_PARAM('NO_INVAL

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

DBMS_STATS.AUTO_INVALIDATE

默认隐含参数取值为18000s，也就是5小时。

sql> select x.ksppinm name,

2y.ksppstvl value,255);text-indent:2em;">3y.ksppstdf isdefault,255);text-indent:2em;">4decode(bitand(y.ksppstvf,7),255);text-indent:2em;">51,255);text-indent:2em;">6'MODIFIED',255);text-indent:2em;">74,255);text-indent:2em;">8'SYSTEM_MOD',255);text-indent:2em;">9'FALSE') ismod,255);text-indent:2em;">10decode(bitand(y.ksppstvf,2),2,'TRUE','FALSE') isadj

11from sys.x$ksppi x,sys.x$ksppcv y

12where x.inst_id = userenv('Instance')

13and y.inst_id = userenv('Instance')

14and x.indx = y.indx

15and x.ksppinm like '_optimizer_invalidation_period';

NAMEVALUEISDEFAULT ISMODISADJ

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

_optimizer_invalidation_period 18000TRUEFALSEFALSE

为了便于实验，我们将这个时间段设置稍短一些。

sql> alter system set "_optimizer_invalidation_period"=300;

System altered

创建实验数据表T，进行相关设置和第一次统计量收集。

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> select to_char(last_analyzed,'yyyy-mm-dd hh24:mi:ss') from dba_tables where table_name='T';

TO_CHAR(LAST_ANALYZED,'YYYY-MM

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

2014-01-06 10:13:57

第一次执行sql语句，我们依然使用autotrace平台，结果集合有省略。

sql> set autotrace traceonly stat

sql> select /*+demo*/* from t where object_id=1000;

统计信息

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

381recursive calls

0db block gets

57consistent gets

rows processed

Shared Cursor情况如下：

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

sql_IDEXECUTIONS VERSION_COUNT FIRST_LOAD_TIME

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

4rw3pyskdgqtc11 2014-01-06/10:16:55

形成第一个游标共享，执行一次。第二次执行sql，游标有共享情况。

sqlarea where sql_text like 'select /*+demo*/* from t%';

sql_IDEXECUTIONS VERSION_COUNT FIRST_LOAD_TIMETO_CHAR(LAST_LOAD_TIME,'YYYY-M

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

4rw3pyskdgqtc212014-01-06/10:16:552014-01-06 10:16:55

此时的执行计划如下：

sql> select * from table(dbms_xplan.display_cursor('4rw3pyskdgqtc'));

PLAN_TABLE_OUTPUT

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

sql_ID4rw3pyskdgqtc,child number 0

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

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

Plan hash value: 514881935

|0 | SELECT STATEMENT||||2 (100)|

|1 |TABLE ACCESS BY INDEX ROWID| T|1 |93 |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

执行Index Range Scan路径。在视图v$sql_shared_cursor中，有共享信息。下面修改数据分布，改变布局。

sql> update t set object_id=1000;

49745 rows updated

sql> commit;

Commit complete

sql> exec dbms_stats.gather_table_stats(user,cascade => true,method_opt => 'for columns size 10 object_id');

默认参数就是auto_invalidate。从经验看，Oracle只有选择FTS才是最优路径。第三次执行sql语句。

已选择49745行。

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

0recursive calls

7441consistent gets

49745rows processed

此时shared cursor情况如下：

sql> select sql_id,255);text-indent:2em;">sql_IDEXECUTIONS VERSION_COUNT FIRST_LOAD_TIMETO_CHAR(LAST_LOAD_TIME,255);text-indent:2em;">------------- ---------- ------------- -------------------- ------------------------------

4rw3pyskdgqtc312014-01-06/10:16:552014-01-06 10:16:55

第三次执行依然使用了原有的Index Range Scan执行计划，没有新的父子游标对象生成，执行次数上增加了一次。

过一会进行第四次执行。

10:23:44 sql> select /*+demo*/* from t where object_id=1000;

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

0physical reads

4rw3pyskdgqtc41 2014-01-06/10:16:552014-01-06 10:16:55

第四次执行之后，Oracle依然没有让游标失效。经过三四分钟之后，执行不同的效果。

10:23:51 sql> select /*+demo*/* from t where object_id=1000;

173recursive calls

3987consistent gets

10:27:16 sql>

游标共享情况如下：

4rw3pyskdgqtc52 2014-01-06/10:16:552014-01-06 10:27:11

形成了一个新的子游标对象，有新的解析动作发生。查看v$sql_shared_cursor视图，可以看到变化。

sql_shared_cursor where sql_id='4rw3pyskdgqtc';

sql_IDCHILD_NUMBER ROLL_INVALID_MISMATCH

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

4rw3pyskdgqtc0 N

4rw3pyskdgqtc1 Y

Child cursor 0号由于Roll Invalidate原因被拒绝共享。游标1信息如下：

sql> select child_number,last_load_time from v$sql where sql_id='4rw3pyskdgqtc';

CHILD_NUMBER EXECUTIONS FIRST_LOAD_TIMELAST_LOAD_TIME

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

04 2014-01-06/10:16:552014-01-06/10:16:55

11 2014-01-06/10:16:552014-01-06/10:27:11

子游标1和0分别代表了不同的执行计划。

sql> select * from table(dbms_xplan.display_cursor('4rw3pyskdgqtc','1'));

Plan hash value: 1601196873

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

|0 | SELECT STATEMENT||||155 (100)||

|*1 |TABLE ACCESS FULL| T| 49740 |4420K|155(3)| 00:00:02 |

1 - filter("OBJECT_ID"=1000)

18 rows selected

|*2 |INDEX RANGE SCAN| IDX_T_ID |1 ||1(0)| 00

从上面实验，我们可以得到结论：当统计量收集采用no_invalidate=dbms_stats.auto_invalidate的时候，已经存在的共享游标会在一个时间段之后被失效。这样的策略避免了集中hard sparse出现，保证了系统性能平稳化过程。

5、结论

Oracle统计量对于执行计划至关重要，理解no_invalidate参数含义和设置，可以帮助我们更好地理解Oracle工作原理和设计思路。

原文链接：https://www.f2er.com/oracle/206038.html

Oracle 统计量NO_INVALIDATE参数配置（下）

猜你在找的Oracle相关文章