Oracle收集统计信息之NO_INVALIDATE参数

0    60    1

👉 本文共约16031个字,系统预计阅读时间或需61分钟。

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

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

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

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

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取值为**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=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;

本人提供Oracle、MySQL、PG等数据库的培训和考证业务,私聊QQ646634621或微信db_bao,谢谢!

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

标签:

头像

小麦苗

学习或考证,均可联系麦老师,请加微信db_bao或QQ646634621

您可能还喜欢...

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注

5 × 4 =

 

嘿,我是小麦,需要帮助随时找我哦
  • 18509239930
  • 个人微信

  • 麦老师QQ聊天
  • 个人邮箱
  • 点击加入QQ群
  • 个人微店

  • 回到顶部
返回顶部