共享游标的生命周期

当优化器解析一个新的非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 VALIDATIONALTER 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只在UNUSABLEREBUILD时才支持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

这表示新的子查询计划生成的原因是因为滚动失效游标值不匹配