2.1.1.2_16 Oracle SYS.DBMS_STATS 之参数 NO_INVALIDATE

已于 2022-04-29 14:33:31 修改
阅读量4.4k 收藏 6
点赞数 1
分类专栏： # 2.1.1 Oracle数据字典 文章标签： oracle
于 2020-12-28 16:50:52 首次发布
原文链接：https://blog.csdn.net/cpongo6/article/details/88793003
版权

目录

相关链接

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

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

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

  答案就是dbms_stats的no_invalidate参数。通过不同的参数配置，可以实现对Oracle失效共享游标行为的控制。

首先放出结论，后面是验证过程

  • DBMS_STATS.AUTO_INVALIDATE ： 默认取值。策略是“延时”，已经存在shared pool中的共享游标不会一次性的失效，而是“慢慢的”、“有差别的”失效。这样的策略避免了集中hard sparse（瞬间的性能高峰期）出现，保证了系统性能平稳化过程。
  • TRUE ： 表示shared pool中有依赖关系的 共享的游标（shared cursor）（执行计划）依然有效（不会被新的执行计划所失效），即使此时共享的游标已经不是最优的执行计划。
  • FALSE ： 当我们收集统计量使用no_invalidate为false的时候，原有的共享游标被失效，下一次在执行SQL的时候，Oracle会重新为其生成执行计划，也就是一次 硬解析（Hard Parse1） 过程。

 

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 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL 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=TRUE

 

  取值为TRUE，表示shared pool中有依赖关系的shared cursor（执行计划）依然有效（不会被新的执行计划所失效），即使这个过程中，共享的游标已经不是最优的执行计划。

  我们创建实验数据表。

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=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 |    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=1000
Plan 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=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 |    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=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 |    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=1000
Plan 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://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
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

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

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工作原理和设计思路。

20/12/28

M

  1. Hard Parse ：硬解析（Hard Parse），Oralce解析SQL方式分为 硬解析（hard rase） 和 软解析（soft prase） 当你发出一条sql语句交付Oracle，在执行和获取结果前，Oracle对此sql将进行几个步骤的处理过程：
     1、语法检查(syntax check)
       检查此sql的拼写是否语法。
     2、语义检查(semantic check)
      诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。
     3、对sql语句进行解析(prase)
       利用内部算法对sql进行解析，生成解析树(parse tree)及执行计划(execution plan)。
     4、执行sql，返回结果(execute and return)
      其中，软、硬解析就发生在第三个过程里。
     ● Oracle利用内部的hash算法来取得该sql的hash值，然后在library cache里查找是否存在该hash值；
      ○ 假设存在，则将此sql与cache中的进行比较；
      ○ 假设“相同”，就将利用已有的解析树与执行计划，而省略了优化器的相关工作。这也就是软解析的过程。
     ● 诚然，如果上面的2个假设中任有一个不成立，那么优化器都将进行创建解析树、生成执行计划的动作。这个过程就叫硬解析。
     ● 创建解析树、生成执行计划对于sql的执行来说是开销昂贵的动作，所以，应当极力避免硬解析，尽量使用软解析。
     ● 这就是在很多项目中，倡导开发设计人员对功能相同的代码要努力保持代码的一致性，以及要在程序中多使用绑定变量的原因。
     ↩︎

GroupiesM
关注
  • 1
    点赞
  • 踩
  • 6
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
六千字带你了解 Oracle 统计信息和执行计划
JiekeXu的博客
04-14 1210
作者 | JiekeXu来源 |JiekeXu之路（ID: JiekeXu_IT）转载请联系授权 |(微信ID：xxq1426321293)大家好，我是 JiekuXu,很高兴又和...
Oracle-sql优化之统计信息
sinat_36757755的博客
04-19 2748
本文章主要讲述生产运维中常见的统计信息导致执行计划异常的情形以及如何确认统计信息是否存在问题，Oracle的执行计划生成是基于cost，而cost则是根据表的统计信息以及服务器性能指标去计算的，所以一个正确的统计信息对于执行计划的生成非常重要。
Oracle收集统计信息之NO_INVALIDATE参数
coco3600的博客
05-18 785
Oracle收集统计信息之NO_INVALIDATE参数 Oracle统计量对于CBO执行是至关重要的。RBO是建立在数据结构的基础上的，DDL结构、约束会将SQL语句分为不同的成本结构等级。而CBO是...
ORACLE SQL 优化相关概念
u011868279的博客
10-18 462
ORACLE SQL 优化相关概念
oracle dbms_stats(no_invalidate参数)
cuiye1492的博客
02-08 501
dbms_stats收集统计信息时候no_invalidate参数用于是否与收集相关object的cursor失效，defalut(9i false, 10g dbms_stats.auto_invalidate(既null))...
Oracle统计信息（2）
congcong112233的博客
05-15 724
统计信息的常用的脚本 DBMS_STATS.GATHER_TABLE_STATS参数 ...
[Oracle] dbms_metadata.get_ddl 的使用方法总结
09-10
Oracle数据库中的`dbms_metadata.get_ddl`是一个非常实用的包，它允许开发人员和管理员获取数据库对象的创建语句（DDL），这对于备份和恢复、迁移或者理解对象定义非常有帮助。下面我们将深入探讨`dbms_metadata.get...
ORACLE SYS.DBMS_REGISTRY_SYS has errors
03-03
标题中的问题“ORACLE SYS.DBMS_REGISTRY_SYS has errors”指的是在Oracle数据库系统中，系统包BODY `SYS.DBMS_REGISTRY_SYS` 出现错误，导致了一系列的PL/SQL调用失败。这种错误通常与数据库的元数据注册功能有关，...
DBMS.rar_DBMS.rar_DBMs C_dbms_oracle_表格
09-22
实现建立数据库表的结构的功能。要求： （1）能输入任意关系名。  （2）字段个数、名称任意给定（包括字段名、字段类型、长度任意）。 （3）要求以文件形式保存基本表。（存储结构也可自行设计） ...
DBMS_STATS.GATHER_TABLE_STATS详解.pdf
02-01
DBMS_STATS.GATHER_TABLE_STATS ( ownname VARCHAR2, tabname VARCHAR2, partname VARCHAR2, estimate_percent NUMBER, block_sample BOOLEAN, method_opt VARCHAR2, degree NUMBER, granularity VARCHAR2, cascade ...
DATADIC.rar_DATADIC_cxGridLevel.d_delphi MySQL oracle_oracle_ora
09-24
标题中的"DATADIC.rar_DATADIC_cxGridLevel.d_delphi MySQL oracle_oracle_ora"揭示了这个压缩包文件的核心内容。它涉及到一个名为"DATADIC"的数据字典项目，该字典可能是一个软件工具，用于在不同的数据库管理系统...
Oracle 统计量NO_INVALIDATE参数配置（上）
ciqu9915的博客
01-05 150
Oracle统计量对于CBO执行是至关重要的。RBO是建立在数据结构的基础上的，DDL结构、约束会将SQL语句分为不同的成本结构等级。而CBO是在数据结构的基础上，加入数据表细粒度信息，将成本结构细化为成本...
2.1.1.2_2 Oracle SYS.DBMS_STATS存储过程整理
GroupiesM的博客
09-25 4277
Oracle SYS.DBMS_STATS存储过程整理 ，来自PLSQL 版本11g
Oracle 统计量NO_INVALIDATE参数配置（下）
ciqu9915的博客
01-06 165
本篇我们继续讨论NO_INVALIDATE参数。 从上篇（http://blog.itpub.net/17203031/viewspace-1067312/）讨论情况看，无论是取值true还是f...
ORA-20001: ORA-06512: at "SYS.DBMS_STATS", line 2242
cuizhi4718的博客
08-26 1714
某客户数据库在执行查询select * fromDBA_TAB_STATISTICS报如下错误： ORA-20001: BIN$12EX+RAYNZZGQB4KEQF0BW==$0 is an invalid i...
【性能优化】dbms_stats在ORACLE中的使用
cojm55771的博客
08-19 234
dbms_stats能良好地估计统计数据（尤其是针对较大的分区表），并能获得更好的统计结果，最终制定出速度更快的SQL执行计划。 　　exec dbms_stats.gather_schema_stats( 　　ownnam...
DBMS_STATS.GATHER_TABLE_STATS参数详解
喝醉酒的小白
01-08 3801
统计表,列,索引的统计信息（默认参数下是对表进行直方图信息收集，包含该表的自身-表的行数、数据块数、行长等信息；列的分析--列值的重复数、列上的空值、数据在列上的分布情况；索引的分析-索引页块的数量、索引的深度、索引聚合因子）。
表分析（1）
Jenya's blog
03-24 459
报表数据库相当于一个数据仓库，数据量一般来说都比较大，这个时候性能就要特别注意。为了促使oracle选择最优的执行计划，最好定期进行表分析，以使oracle得到正确的统计信息。一般如果数据分布和表结构发生变化的话，就要重新进行表分析。报表数据库数据分布发生变化时有发生。所以建议报表库进行定期的表分析。 表分析语句参考如下： 单表分析：     分区表：     BEGIN       D
Oracle分析表使用DBMS_STATS.GATHER_TABLE_STATS并行收集统计信息
最新发布
tttzzzqqq2018的博客
09-02 2269
表分析，简单的说，就是收集表和索引的信息，CBO根据这些信息决定SQL最佳的执行路径。通过对表的分析，可以产生一些统计信息，通过这些信息oracle的优化程序可以进行优化。
dbms_stats.gathrt_table_stats
07-25
`DBMS_STATS.GATHER_TABLE_STATS` 是 Oracle 数据库中一个过程（procedure），用于收集表的统计信息。它可以替代旧版的 `ANALYZE TABLE` 语句，提供更高效和更精确的统计信息收集方法。 使用 `DBMS_STATS.GATHER_TABLE_STATS` 可以收集表的各种统计信息，包括行数、块数、列的分布等。这些统计信息对于优化查询性能非常重要，因为它们可以帮助优化器生成更准确的执行计划。 以下是使用 `DBMS_STATS.GATHER_TABLE_STATS` 收集表统计信息的示例： ```sql BEGIN DBMS_STATS.GATHER_TABLE_STATS( ownname => 'schema_name', tabname => 'table_name', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE ); END; ``` 在上面的示例中，`schema_name` 是表所属的模式名，`table_name` 是要收集统计信息的表名。`estimate_percent` 参数控制了采样的百分比，使用 `DBMS_STATS.AUTO_SAMPLE_SIZE` 可以自动选择合适的采样大小。`method_opt` 参数指定了统计信息收集的方法和选项，'FOR ALL COLUMNS SIZE AUTO' 表示对所有列进行统计，并自动选择合适的大小。`cascade` 参数表示是否同时收集相关索引的统计信息。 需要注意的是，`DBMS_STATS.GATHER_TABLE_STATS` 还有其他可选的参数，可以根据具体需求进行设置，如 `granularity`、`degree` 等。详细的参数说明可以参考 Oracle 官方文档。 通过使用 `DBMS_STATS.GATHER_TABLE_STATS`，可以更灵活地、更高效地收集表的统计信息，并提升查询性能。

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值