标签：INVALIDATE object NO sql SYS SQL Oracle id select
文档课题：Oracle统计信息之NO_INVALIDATE参数.
1、理论知识
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参数决定当新统计信息搜集之后，如何处理此前已经生成的执行计划，也就是在Shared Pool中的执行计划.统计信息决定SQL执行计划是CBO的一个特征，但该过程是针对新生成的执行计划，也就是新的Parse过程.对于已经生成的执行计划，Oracle是通过no_invalidate参数来处理shared cursor的失效过程.
一个对象(数据表、索引)的新统计信息收集之后，最简单的方法是一次性将Shared Pool中有依赖关系的shared cursor失效，下一次执行SQL的时候，必然会用新的统计信息进行解析，产生新的执行计划.而另一个极端是忽略新统计信息的差异，维持现有的Shared Cursor，不让其失效.从性能角度分析，两个极端都有问题.若是一次性将其全部失效，会引起后续作业过程的“解析峰值”.如果系统负载很高，突然间缓存的执行计划全部失效，Oracle作业必然要消耗额外的成本重新生成执行计划.如果不将共享游标失效，那新的统计信息不会很快体现在更好的执行计划上，性能提升无从谈起.所以是否将游标失效是一个进退两难的问题.
Oracle中no_invalidate参数包括三个取值，分别是true、false和dbms_stats.auto_invalidate.true表示不进行游标失效动作，shared cursor保持原有状态.false表示将统计信息对象相关的所有cursor全部失效.auto_invalidate表示Oracle自己决定shared cursor失效动作.
从10G开始，Oracle将auto_invalidate作为默认的统计量收集行为.
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit 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
SYS@orcl> select dbms_stats.get_param(pname => 'no_invalidate') from dual;
DBMS_STATS.GET_PARAM(PNAME=>'NO_INVALIDATE')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_INVALIDATE
2、no_invalidate测试
2.1、no_invalidate取值true
注意：YES表示不进行共享游标失效动作，即使该共享游标已不是最优执行计划.
说明：由于篇幅原因，笔者省略部分查询结果.
2.1.1、建测试数据
--建测试数据.
SYS@orcl> create table test as select * from dba_objects;
Table created.
SYS@orcl> create index idx_test_id on test(object_id);
Index created.
--第一次收集统计信息.
SYS@orcl> exec dbms_stats.gather_table_stats('SYS','TEST',cascade => true);
PL/SQL procedure successfully completed.
2.1.2、查执行计划
--执行一条sql语句，然后查看其执行计划.
SYS@orcl> select /*+demo*/object_id,owner from test where object_id=1000;
OBJECT_ID OWNER
---------- ------------------------------
1000 SYS
此时shared pool情况如下，出现第一个执行计划缓存对象.
SYS@orcl> select sql_id,executions,version_count from v$sqlarea where sql_text like 'select /*+demo*/%';
SQL_ID EXECUTIONS VERSION_COUNT
------------- ---------- -------------
fvxa0axff6hsx 1 1
SYS@orcl> set pagesize 200
SYS@orcl> select * from table(dbms_xplan.display_cursor(sql_id=>'fvxa0axff6hsx'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID fvxa0axff6hsx, child number 0
-------------------------------------
select /*+demo*/object_id,owner from test where object_id=1000
Plan hash value: 1345973065
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 11 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1000)
19 rows selected.
说明：此时最优执行计划是索引路径，在shared pool中有一个父游标和子游标(version count=1)，执行次数为1.
--如下进行第二次查询，shared pool中有共享现象，相同的共享游标执行两次.
SYS@orcl> select /*+demo*/object_id,owner from test where object_id=1000;
OBJECT_ID OWNER
---------- ------------------------------
1000 SYS
SYS@orcl> select sql_id,executions,version_count from v$sqlarea where sql_text like 'select /*+demo*/%';
SQL_ID EXECUTIONS VERSION_COUNT
------------- ---------- -------------
fvxa0axff6hsx 2 1
2.1.3、更新表数据
--然后更新数据，修改数据分布结构.
SYS@orcl> update test set object_id=1000;
91167 rows updated.
SYS@orcl> commit;
Commit complete.
2.1.4、再次查看执行计划
--再次执行sql语句，发现依然使用的是原有索引路径，此时表test中object_id全是1000，走索引根本不是好的选择.
SYS@orcl> select /*+demo*/object_id,owner from test where object_id=1000;
71895 rows selected.
SYS@orcl> select * from table(dbms_xplan.display_cursor('','',''));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID fvxa0axff6hsx, child number 0
-------------------------------------
select /*+demo*/object_id,owner from test where object_id=1000
Plan hash value: 1345973065
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 11 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1000)
19 rows selected.
SYS@orcl> select sql_id,executions,version_count from v$sqlarea where sql_text like 'select /*+demo*/%';
SQL_ID EXECUTIONS VERSION_COUNT
------------- ---------- -------------
fvxa0axff6hsx 3 1
说明：此时执行计划依然是index range scan，原因是统计信息过时，外加游标共享，导致使用错误的执行计划.
2.1.5、no_invalidate为true搜集统计信息
--如下采用no_invalidate为true重新收集统计信息.
SYS@orcl> exec dbms_stats.flush_database_monitoring_info;
PL/SQL procedure successfully completed.
SYS@orcl> exec dbms_stats.gather_table_stats('SYS','TEST',cascade=>true,no_invalidate=>true,method_opt=>'for columns size 10 object_id');
PL/SQL procedure successfully completed.
2.1.6、explain plan查看执行计划
--新统计信息生成，使用explain plan查看此时sql采用的执行计划.
SYS@orcl> explain plan for select /*+demo*/object_id,owner from test where object_id=1000;
Explained.
SYS@orcl> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 71888 | 631K| 280 (1)| 00:00:04 |
|* 1 | TABLE ACCESS FULL| TEST | 71888 | 631K| 280 (1)| 00:00:04 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_ID"=1000)
13 rows selected.
说明：此时FTS全表扫描是更好的选择，但实际的执行计划如下.
2.1.7、实际的执行计划
SYS@orcl> select /*+demo*/object_id,owner from test where object_id=1000;
71895 rows selected.
SYS@orcl> select * from table(dbms_xplan.display_cursor('','',''));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID fvxa0axff6hsx, child number 0
-------------------------------------
select /*+demo*/object_id,owner from test where object_id=1000
Plan hash value: 1345973065
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 11 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1000)
19 rows selected.
SYS@orcl> select sql_id,executions,version_count from v$sqlarea where sql_text like 'select /*+demo*/%';
SQL_ID EXECUTIONS VERSION_COUNT
------------- ---------- -------------
fvxa0axff6hsx 4 1
说明：此时Oracle依然选择原来的Index路径，原有的shared cursor没有失效，若此时将shared pool清空，新的FTS执行计划也就生成.
2.1.8、flush shared_pool后的执行计划
SYS@orcl> alter system flush shared_pool;
System altered.
SYS@orcl> select sql_id,executions,version_count from v$sqlarea where sql_text like 'select /*+demo*/%';
no rows selected
SYS@orcl> select /*+demo*/object_id,owner from test where object_id=1000;
71895 rows selected.
--新的shared cursor形成
SYS@orcl> select sql_id,executions,version_count from v$sqlarea where sql_text like 'select /*+demo*/%';
SQL_ID EXECUTIONS VERSION_COUNT
------------- ---------- -------------
fvxa0axff6hsx 1 1
--FTS执行计划
SYS@orcl> select * from table(dbms_xplan.display_cursor(sql_id => 'fvxa0axff6hsx'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID fvxa0axff6hsx, child number 0
-------------------------------------
select /*+demo*/object_id,owner from test where object_id=1000
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 280 (100)| |
|* 1 | TABLE ACCESS FULL| TEST | 71888 | 631K| 280 (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后，新的执行计划才能生成.
2.2、no_invalidate取值false
2.2.1、建测试数据
如下测试取值为false的情况，为避免影响，重建测试数据.
SYS@orcl> drop table test purge;
Table dropped.
SYS@orcl> alter system flush shared_pool;
System altered.
SYS@orcl> create table test as select * from dba_objects;
Table created.
SYS@orcl> create index idx_test_id on test(object_id);
Index created.
SYS@orcl> exec dbms_stats.gather_table_stats('SYS','TEST',cascade=>true);
PL/SQL procedure successfully completed.
2.2.2、查执行计划
第一次执行sql语句，形成index路径的执行计划.
SYS@orcl> select /*+demo*/object_id,owner from test where object_id=1000;
OBJECT_ID OWNER
---------- ------------------------------
1000 SYS
SYS@orcl> select sql_id,executions,version_count from v$sqlarea where sql_text like 'select /*+demo*/%';
SQL_ID EXECUTIONS VERSION_COUNT
------------- ---------- -------------
fvxa0axff6hsx 1 1
SYS@orcl> select * from table(dbms_xplan.display_cursor(sql_id => 'fvxa0axff6hsx'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID fvxa0axff6hsx, child number 0
-------------------------------------
select /*+demo*/object_id,owner from test where object_id=1000
Plan hash value: 1345973065
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 11 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1000)
19 rows selected.
--第二次执行相同的sql，可以看到生成的shared cursor进行共享.
SYS@orcl> select /*+demo*/object_id,owner from test where object_id=1000;
OBJECT_ID OWNER
---------- ------------------------------
1000 SYS
SYS@orcl> 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
------------- ---------- ------------- -------------------------
fvxa0axff6hsx 2 1 2023-06-03/17:44:44
2.2.3、更新表数据
修改表列object_id的值，改变数据分布.
SYS@orcl> update test set object_id=1000;
71895 rows updated.
SYS@orcl> commit;
Commit complete.
2.2.4、再次查看执行计划
--再次执行sql语句，发现依然使用的是原有索引路径，此时表test中object_id全是1000，走索引根本不是好的选择.
SYS@orcl> select /*+demo*/object_id,owner from test where object_id=1000;
71895 rows selected.
--此时shared cursor信息如下.
SYS@orcl> 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
------------- ---------- ------------- -------------------------
fvxa0axff6hsx 3 1 2023-06-03/17:44:44
SYS@orcl> select * from table(dbms_xplan.display_cursor(sql_id=>'fvxa0axff6hsx'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID fvxa0axff6hsx, child number 0
-------------------------------------
select /*+demo*/object_id,owner from test where object_id=1000
Plan hash value: 1345973065
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 11 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1000)
19 rows selected.
说明：执行计划进行的是index range scan动作.
2.2.5、no_invalidate为false搜集统计信息
--使用no_invalidate为false进行统计信息搜集.
SYS@orcl> exec dbms_stats.gather_table_stats('SYS','TEST',cascade=>true,no_invalidate=>false,method_opt=>'for columns size 10 object_id');
PL/SQL procedure successfully completed.
2.2.6、查最新执行计划
--第四次执行sql查询语句
SYS@orcl> select /*+demo*/object_id,owner from test where object_id=1000;
71895 rows selected.
SYS@orcl> 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
------------- ---------- ------------- -------------------------
fvxa0axff6hsx 1 1 2023-06-03/17:44:44
注意：在相同sql_id的情况下，version_count和executions都为1，executions是不可能减少的，所以这个父游标是新生成的.
此时执行计划如下：
SYS@orcl> select * from table(dbms_xplan.display_cursor(sql_id=>'fvxa0axff6hsx'));
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID fvxa0axff6hsx, child number 0
-------------------------------------
select /*+demo*/object_id,owner from test where object_id=1000
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 280 (100)| |
|* 1 | TABLE ACCESS FULL| TEST | 71888 | 631K| 280 (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过程.
2.3、no_invalidate取值dbms_stats.auto_invalidate
2.3.1、理论知识
true和false的取值比较简单，无论取值true or false，都缺乏考量，true表示旧的执行计划会持续在shared pool中驻留，如果系统sql运行比较频繁，age out现象比较少，更好的执行计划就可能不会出现.false表示会将统计信息涉及到的所有shared pool一次性设置为失效，如此保证有更好的执行计划生成，但也存在一个性能spike现象，若很多业务数据表同时进行统计信息生成，而no_invalidate又设置为false，那就意味着在短时间内oracle shared pool中大部分的shared cursor会全部失效，需要重新生成执行计划，如此从整体上就会有一个hard parse高峰期，严重的话会影响业务运行.
针对这种左右为难的场景，oracle 10g引入参数dbms_stats.auto_invalidate作为no_invalidate的默认值，该参数表示“由oracle决定是否对shared cursor进行失效动作”， 接下来讨论no_invalidate=dbms_stats.auto_invalidate的取值情况.
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现象.
2.3.2、修改_optimizer_invalidation_period参数
下面我们通过实验来证明结论.为防止11g的自适应游标影响，选择10.2.0.1版本进行测试.
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
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
SQL> select dbms_stats.get_param('no_invalidate') from dual;
DBMS_STATS.GET_PARAM('NO_INVALIDATE')
--------------------------------------------------------------------------------
DBMS_STATS.AUTO_INVALIDATE
说明：默认的参数取值为dbms_stats.no_invalidate.
默认隐含参数取值为18000s，也就是5小时.
SQL> col name for a45
SQL> col value for a20
SQL> set line 200
SQL> r
1 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
--为便于实验，将该参数设置为300s.
SQL> alter system set "_optimizer_invalidation_period"=300;
System altered.
2.3.3、建测试数据
--创建实验数据表test，进行相关设置和第一次统计信息收集.
SQL> create table test as select * from dba_objects;
Table created.
SQL> create index idx_test_id on test(object_id);
Index created.
SQL> exec dbms_stats.gather_table_stats('SYS','TEST',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='TEST';
TO_CHAR(LAST_ANALYZ
-------------------
2023-06-04 10:39:58
2.3.4、查共享游标信息
--第一次执行sql语句，使用autotrace.
SQL> set autotrace traceonly stat
SQL> select /*+demo*/* from test where object_id=1000;
Statistics
----------------------------------------------------------
381 recursive calls
0 db block gets
57 consistent gets
0 physical reads
0 redo size
1418 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
Shared cursor情况如下：
SQL> set autotrace off;
SQL> select sql_id,executions,version_count,first_load_time from v$sqlarea where sql_text like 'select /*+demo*/* from test%';
SQL_ID EXECUTIONS VERSION_COUNT FIRST_LOAD_TIME
------------- ---------- ------------- ----------------------------------------------------------------------------
39kn0q6hquf9q 1 1 2023-06-04/10:42:25
说明：以上结果表示形成第一个游标共享，执行一次.
--第二次执行sql，游标共享情况如下.
SQL> select /*+demo*/* from test where object_id=1000;
OWNER OBJECT_NAME SUBOBJECT_NAME
------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ------------------------------
OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_ TIMESTAMP STATUS T G S
---------- -------------- ------------------- --------- --------- ------------------- ------- - - -
SYS V_$BUFFER_POOL_STATISTICS
1000 VIEW 22-OCT-05 22-OCT-05 2005-10-22:21:45:05 VALID N N N
SQL> select sql_id,executions,version_count,first_load_time from v$sqlarea where sql_text like 'select /*+demo*/* from test%';
SQL_ID EXECUTIONS VERSION_COUNT FIRST_LOAD_TIME
------------- ---------- ------------- ----------------------------------------------------------------------------
39kn0q6hquf9q 2 1 2023-06-04/10:42:25
2.3.5、查执行计划
--此时执行计划如下.
SQL> set pagesize 200
SQL> select * from table(dbms_xplan.display_cursor('39kn0q6hquf9q'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 39kn0q6hquf9q, child number 0
-------------------------------------
select /*+demo*/* from test where object_id=1000
Plan hash value: 1345973065
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 93 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1000)
19 rows selected.
说明：执行index range scan路径，在视图v$sql_shared_cursor中，有共享信息.
2.3.6、更新表数据
如下更新数据分布，改变布局.
SQL> update test set object_id=1000;
49745 rows updated.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats('SYS','TEST',cascade=>true,method_opt=>'for columns size 10 object_id');
PL/SQL procedure successfully completed.
说明：默认参数为auto_invalidate，从经验判断oracle选择fts才是最优路径，
2.3.7、再次查看执行计划
--第三次执行sql语句.
SQL> select /*+demo*/* from test where object_id=1000;
49745 rows selected.
--此时shared cursor情况如下：
SQL> col first_load_time for a25
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 test%';
SQL_ID EXECUTIONS VERSION_COUNT FIRST_LOAD_TIME TO_CHAR(LAST_LOAD_T
------------- ---------- ------------- ------------------------- -------------------
39kn0q6hquf9q 3 1 2023-06-04/10:42:25 2023-06-04 10:42:25
说明：第三次依然使用原有的index range scan执行计划，没有新的子游标对象生成，执行次数增加一次.
2.3.8、过5分钟执行sql后的游标数据情况
注意：此时5分钟后在进行第四次sql语句的执行.
SQL> select /*+demo*/* from test where object_id=1000;
49745 rows selected.
--游标共享情况如下：
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 test%';
SQL_ID EXECUTIONS VERSION_COUNT FIRST_LOAD_TIME TO_CHAR(LAST_LOAD_T
------------- ---------- ------------- ------------------------- -------------------
39kn0q6hquf9q 4 2 2023-06-04/10:42:25 2023-06-04 11:25:13
说明：形成一个新的子游标对象，有新的解析动作发生，查看v$sql_shared_cursor视图，可看到变化.
SQL> select sql_id,child_number,roll_invalid_mismatch from v$sql_shared_cursor where sql_id='39kn0q6hquf9q';
SQL_ID CHILD_NUMBER R
------------- ------------ -
39kn0q6hquf9q 0 N
39kn0q6hquf9q 1 Y
说明：child cursor 0号由于roll invalidate原因被拒绝共享，游标1信息如下：
SQL> col last_load_time for a20
SQL> select child_number,executions,first_load_time,last_load_time from v$sql where sql_id='39kn0q6hquf9q';
CHILD_NUMBER EXECUTIONS FIRST_LOAD_TIME LAST_LOAD_TIME
------------ ---------- ------------------------- --------------------
0 3 2023-06-04/10:42:25 2023-06-04/10:42:25
1 1 2023-06-04/10:42:25 2023-06-04/11:25:13
说明：子游标1和0分别代表不同的执行计划.
2.3.9、不同子游标的执行计划
SQL> select * from table(dbms_xplan.display_cursor('39kn0q6hquf9q','1'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 39kn0q6hquf9q, child number 1
-------------------------------------
select /*+demo*/* from test where object_id=1000
Plan hash value: 1357081020
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 154 (100)| |
|* 1 | TABLE ACCESS FULL| TEST | 49740 | 4420K| 154 (2)| 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('39kn0q6hquf9q','0'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID 39kn0q6hquf9q, child number 0
-------------------------------------
select /*+demo*/* from test where object_id=1000
Plan hash value: 1345973065
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 93 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TEST_ID | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_ID"=1000)
19 rows selected.
说明：如上可知当统计信息收集采用no_invalidate=dbms_stats.auto_invalidate时，已存在的共享游标会在一个时间段之后被失效，该策略避免集中hard sparse出现，保证系统性能平稳.
结论：Oracle统计信息对于执行计划至关重要，理解no_invalidate参数可以更好地理解oracle工作原理和设计思路.
参考网址：https://blog.csdn.net/cpongo6/article/details/88793003
标签：INVALIDATE,
object,
NO,
sql,
SYS,
SQL,
Oracle,
id,
select
From： https://blog.51cto.com/u_12991611/6410610