当优化器解析一个新的非DDL的SQL语句时，oracle会分配一块共享的SQL空间，所需要分配的内存大小取决于这个SQL的复杂程度。
当发现这个SQL生成的游标长期未被使用时，oracle也会将这块SQL空间从共享内存中除去。当这个游标之后被重新使用时，则oracle会重新解析语句，然后分配一块新的共享SQL空间。当然如果语句正在执行或者语句获取的数据还未获取完毕时，数据库是不会清除游标的。
当sql语句中涉及到的对象发生了改变或者统计信息发生了变化，那么共享SQL空间会被标记为失效，oracle通常使用两种方法来管理游标的生命周期：失效和滚动失效（invalidation and rolling invalidation.）。
游标标记失效
当一块共享SQL空间被标记失效，那么oracle就可以将它从共享池中清掉，同时清掉一些长期未使用的游标。
有些情况下，数据库必须要用一些失效的游标来执行语句，那么数据库会先进行硬解析，然后再执行语句。
在以下情形下，数据库会立刻将共享SQL空间标记为失效：
-
通过DBMS_STATS来对表或索引等进行统计信息的收集，并且NO_INVALIDATE参数为FALSE
-
SQL语句中相关的对象被DDL类语句进行了修改，DDL语句是默认会立即让游标失效。
你可以手动指定语句的立即失效，通过ALTER TABLE ... IMMEDIATE VALIDATION和ALTER INDEX ... IMMEDIATE VALIDATION，或者设置参数CURSOR_INVALIDATION为IMMEDIATE
当上述情况发生以后，数据库会自动在下次执行的时候修复这些问题。
当数据库失效一个游标时，V$SQL.INVALIDATIONS的值会增加，V$SQL.OBJECT_STATUS的值会显示INVALID_UNAUTH
这里模拟通过收集统计信息的方式来让一个游标失效
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
| xb@PDB12C> select count(1) from t1;
COUNT(1)
----------
4
xb@PDB12C> SELECT PREV_SQL_ID SQL_ID FROM V$SESSION WHERE SID = SYS_CONTEXT('userenv', 'SID');
SQL_ID
-------------
a2d8wpfzju8fr
select child_number, executions,parse_calls, invalidations, object_status
from v$sql where sql_id = 'a2d8wpfzju8fr';
CHILD_NUMBER EXECUTIONS PARSE_CALLS INVALIDATIONS OBJECT_STATUS
------------ ---------- ----------- ------------- -------------------
0 1 1 0 VALID
xb@PDB12C> exec dbms_stats.gather_table_stats(null,'t1',no_invalidate => false);
PL/SQL procedure successfully completed.
select child_number, executions,parse_calls, invalidations, object_status
from v$sql where sql_id = 'a2d8wpfzju8fr';
CHILD_NUMBER EXECUTIONS PARSE_CALLS INVALIDATIONS OBJECT_STATUS
------------ ---------- ----------- ------------- -------------------
0 1 1 1 INVALID_UNAUTH >>>>==== 游标失效，计数从0->1
|
游标标记为滚动失效
当游标被标记为滚动失效时（V$SQL.IS_ROLLING_INVALID为Y），数据库会逐步的根据情况进行硬解析的操作，而不是同时。
目的
因为做硬解析会很明显的造成数据库性能的下降，滚动失效——也称为延迟失效，对于那些同时要造成大量失效游标的情况非常有帮助。数据库能给每个失效游标分配一个随机生成的时间期限，同时失效的sql区域通常具有不同的时间期限。
只有在查询语句访问游标的时候已经超出这个时间期限，这时才会重新做硬解析，随着时间的推移，数据库将这些硬解析的开销分散开来。
如果并发sql被标记为滚动失效，那么不管是否超出这个时间期限，都会在下一次sql执行时重新硬解析。在RAC环境中，这样保证了并发执行时服务器执行计划和查询调度器的一致性。
延迟失效说明
默认情况下DDL会让所有涉及到目标对象的游标立即失效，但是如果DDL语句支持延迟失效的话，你就可以通过类似ALTER TABLE ... DEFERRED INVALIDATION的语句让游标不会立刻失效。这个选项取决于具体的DDL语句是否支持，比如ALTER INDEX只在UNUSABLE和REBUILD时才支持DEFERRED INVALIDATION。
也可以通过在session或system级别设置CURSOR_INVALIDATION参数，不过优先级会语句里的低。
通过收集统计信息来模拟游标失效，测试延迟失效
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
| xb@PDB12C> select count(1) from t1;
COUNT(1)
----------
4
xb@PDB12C> SELECT PREV_SQL_ID SQL_ID FROM V$SESSION WHERE SID = SYS_CONTEXT('userenv', 'SID');
SQL_ID
-------------
a2d8wpfzju8fr
select child_number, executions,parse_calls, invalidations, object_status
from v$sql where sql_id = 'a2d8wpfzju8fr';
CHILD_NUMBER EXECUTIONS PARSE_CALLS INVALIDATIONS OBJECT_STATUS
------------ ---------- ----------- ------------- -------------------
0 1 1 0 VALID
# 修改隐含参数，设置时间期限
xb@PDB12C> alter system set "_optimizer_invalidation_period" = 300 scope=memory;
System altered.
# 重新收集统计信息
xb@PDB12C> exec dbms_stats.gather_table_stats(null,'t1',no_invalidate => DBMS_STATS.AUTO_INVALIDATE);
PL/SQL procedure successfully completed.
select child_number, executions,parse_calls, invalidations, object_status
from v$sql where sql_id = 'a2d8wpfzju8fr';
CHILD_NUMBER EXECUTIONS PARSE_CALLS INVALIDATIONS OBJECT_STATUS
------------ ---------- ----------- ------------- -------------------
0 1 1 0 VALID
|
可以看到跟之前的测试不一样，这里收集完统计信息，游标并未立即失效，重新执行语句，依然如此
1
2
3
4
5
6
7
8
9
10
11
12
| xb@PDB12C> select count(1) from t1;
COUNT(1)
----------
4
select child_number, executions,parse_calls, invalidations, object_status
2 from v$sql where sql_id = 'a2d8wpfzju8fr';
CHILD_NUMBER EXECUTIONS PARSE_CALLS INVALIDATIONS OBJECT_STATUS
------------ ---------- ----------- ------------- -------------------
0 2 2 0 VALID
|
这里就必须要等待时间超过之前我们设置的时间期限
1
2
3
4
5
6
7
8
| xb@PDB12C> ! sleep 300
select child_number, executions,parse_calls, invalidations, object_status
2 from v$sql where sql_id = 'a2d8wpfzju8fr';
CHILD_NUMBER EXECUTIONS PARSE_CALLS INVALIDATIONS OBJECT_STATUS
------------ ---------- ----------- ------------- -------------------
0 2 2 0 VALID
|
重新执行查询语句
1
2
3
4
5
6
7
8
9
10
11
12
13
| xb@PDB12C> select count(1) from t1;
COUNT(1)
----------
4
select child_number, executions,parse_calls, invalidations, object_status
2 from v$sql where sql_id = 'a2d8wpfzju8fr';
CHILD_NUMBER EXECUTIONS PARSE_CALLS INVALIDATIONS OBJECT_STATUS
------------ ---------- ----------- ------------- -------------------
0 2 2 0 VALID
1 1 1 0 VALID
|
这时发现数据库重新做了硬解析，生成了一个新的子查询计划，可以通过v$sql_shared_cursor视图来查看原因
1
2
3
4
5
6
| xb@PDB12C> select child_number,ROLL_INVALID_MISMATCH from v$sql_shared_cursor where sql_id = 'a2d8wpfzju8fr';
CHILD_NUMBER R
------------ -
0 N
1 Y
|
这表示新的子查询计划生成的原因是因为滚动失效游标值不匹配