Oracle收集统计信息之NO_INVALIDATE参数

0    259    1

Tags:

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

简介

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

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

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

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

是否设置NO_INVALIDATE为FALSE。该选项有TRUE、FALSE和DBMS_STATS.AUTO_INVALIDATE这3个值。如果取值为TRUE,那么表示收集统计信息后不进行游标失效动作,原有的Shared Cursor保持原有状态。如果取值为FALSE,那么表示将统计信息对象相关的所有Cursor全部失效,目标SQL语句在下次执行时就会使用硬解析。如果设置为AUTO_INVALIDATE,那么Oracle自己决定Shared Cursor失效动作,当SQL再次执行时间距离上次收集统计信息的时间超过5小时(隐含参数“_OPTIMIZER_INVALIDATION_PERIOD”决定)则对SQL重新做硬解析。AUTO_INVALIDATE为默认选项。有些DBA在收集统计信息时,没有使用NO_INVALIDATE=>FALSE选项,所以,即使收集了统计信息,执行计划也不会立即改变。可以在表级别设置让所有依赖于该表的游标不失效,设置方法为:

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参数包括三个取值。

Oracle支持true、false和dbms_stats.auto_invalidate取值。如果取值为true,表示不进行游标失效动作,原有的shared cursor保持原有状态。如果取值为false,表示将统计量对象相关的所有cursor全部失效。如果设置为auto_invalidate,根据官方文档,Oracle自己决定shared cursor失效动作。

从10G开始,Oracle就将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;

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):

本人提供Oracle(OCP、OCM)、MySQL(OCP)、PostgreSQL(PGCA、PGCE、PGCM)等数据库的培训和考证业务,私聊QQ646634621或微信dbaup66,谢谢!
AiDBA后续精彩内容已被站长无情隐藏,请输入验证码解锁本文!
验证码:
获取验证码: 请先关注本站微信公众号,然后回复“验证码”,获取验证码。在微信里搜索“AiDBA”或者“dbaup6”或者微信扫描右侧二维码都可以关注本站微信公众号。

标签:

Avatar photo

小麦苗

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

您可能还喜欢...

发表回复