ITPub博客

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

Oracle 作者:xypincle 时间:2017-04-09 18:10:09 0 删除 编辑

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

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

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

 

4no_invalidate=dbms_stats.auto_invalidate

 

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

Auto_invalidate过程的原则是避免truefalse的极端情况,既要实现新执行计划的生成,也要避免性能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

CORE 10.2.0.1.0 Production

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,

  2         y.ksppstvl value,

  3         y.ksppstdf isdefault,

  4         decode(bitand(y.ksppstvf, 7),

  5                1,

  6                'MODIFIED',

  7                4,

  8                'SYSTEM_MOD',

  9                'FALSE') ismod,

 10         decode(bitand(y.ksppstvf, 2), 2, 'TRUE', 'FALSE') isadj

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

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

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

 14     and x.indx = y.indx

 15     and x.ksppinm like '_optimizer_invalidation_period';

 

NAME                           VALUE      ISDEFAULT ISMOD      ISADJ

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

_optimizer_invalidation_period 18000      TRUE      FALSE      FALSE

 

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

 

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;

 

统计信息

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

        381  recursive calls

          0  db block gets

         57  consistent 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_ID        EXECUTIONS VERSION_COUNT FIRST_LOAD_TIME

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

4rw3pyskdgqtc          1             1 2014-01-06/10:16:55

 

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

 

SQL> select sql_id, executions, version_count, first_load_time, to_char(last_load_time,'yyyy-mm-dd hh24:mi:ss') from v$sqlarea where sql_text like 'select /*+demo*/* from t%';

 

SQL_ID        EXECUTIONS VERSION_COUNT FIRST_LOAD_TIME      TO_CHAR(LAST_LOAD_TIME,'YYYY-M

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

4rw3pyskdgqtc          2             1 2014-01-06/10:16:55  2014-01-06 10:16:55

 

此时的执行计划如下:

 

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

 

PLAN_TABLE_OUTPUT

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

SQL_ID  4rw3pyskdgqtc, child number 0

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

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

Plan hash value: 514881935

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

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Ti

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

|   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,'T',cascade => true,method_opt => 'for columns size 10 object_id');

PL/SQL procedure successfully completed

 

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

 

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

已选择49745行。

 

统计信息

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

          0  recursive calls

          0  db block gets

       7441  consistent gets

        

      49745  rows processed

 

此时shared cursor情况如下:

 

SQL> select sql_id, executions, version_count, first_load_time, to_char(last_load_time,'yyyy-mm-dd hh24:mi:ss') from v$sqlarea where sql_text like 'select /*+demo*/* from t%';

 

SQL_ID        EXECUTIONS VERSION_COUNT FIRST_LOAD_TIME      TO_CHAR(LAST_LOAD_TIME,'YYYY-M

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

4rw3pyskdgqtc          3             1 2014-01-06/10:16:55  2014-01-06 10:16:55

 

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

过一会进行第四次执行。

 

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

已选择49745行。

统计信息

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

          0  recursive calls

          0  db block gets

       7441  consistent gets

          0  physical reads

      49745  rows processed

 

SQL> select sql_id, executions, version_count, first_load_time, to_char(last_load_time,'yyyy-mm-dd hh24:mi:ss') from v$sqlarea where sql_text like 'select /*+demo*/* from t%';

 

SQL_ID        EXECUTIONS VERSION_COUNT FIRST_LOAD_TIME      TO_CHAR(LAST_LOAD_TIME,'YYYY-M

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

4rw3pyskdgqtc          4             1 2014-01-06/10:16:55  2014-01-06 10:16:55

 

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

 

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

已选择49745行。

统计信息

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

        173  recursive calls

          0  db block gets

       3987  consistent gets

        

      49745  rows processed

 

10:27:16 SQL>

 

游标共享情况如下:

 

 

SQL> select sql_id, executions, version_count, first_load_time, to_char(last_load_time,'yyyy-mm-dd hh24:mi:ss') from v$sqlarea where sql_text like 'select /*+demo*/* from t%';

 

SQL_ID        EXECUTIONS VERSION_COUNT FIRST_LOAD_TIME      TO_CHAR(LAST_LOAD_TIME,'YYYY-M

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

4rw3pyskdgqtc          5             2 2014-01-06/10:16:55  2014-01-06 10:27:11

 

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

 

 

SQL> select sql_id, child_number,ROLL_INVALID_MISMATCH from v$sql_shared_cursor where sql_id='4rw3pyskdgqtc';

 

SQL_ID        CHILD_NUMBER ROLL_INVALID_MISMATCH

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

4rw3pyskdgqtc            0 N

4rw3pyskdgqtc            1 Y

 

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

 

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

 

CHILD_NUMBER EXECUTIONS FIRST_LOAD_TIME      LAST_LOAD_TIME

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

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

           1          1 2014-01-06/10:16:55  2014-01-06/10:27:11

 

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

 

 

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

 

PLAN_TABLE_OUTPUT

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

SQL_ID  4rw3pyskdgqtc, child number 1

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

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

Plan hash value: 1601196873

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

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

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

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

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

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

Predicate Information (identified by operation id):

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

   1 - filter("OBJECT_ID"=1000)

 

18 rows selected

 

SQL> select * from table(dbms_xplan.display_cursor('4rw3pyskdgqtc','0'));

 

PLAN_TABLE_OUTPUT

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

SQL_ID  4rw3pyskdgqtc, child number 0

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

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

Plan hash value: 514881935

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

| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Ti

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

|   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

 

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

 

5、结论

 

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


本文转自:http://blog.itpub.net/17203031/viewspace-1067620/

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

请登录后发表评论 登录
全部评论

注册时间:2013-05-11

  • 博文量
    208
  • 访问量
    91531