共享游标的生命周期 | 一个dba的日常

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

游标标记失效

当一块共享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

这里模拟通过收集统计信息的方式来让一个游标失效

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 参数，不过优先级会语句里的低。

通过收集统计信息来模拟游标失效，测试延迟失效

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

可以看到跟之前的测试不一样，这里收集完统计信息，游标并未立即失效，重新执行语句，依然如此

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

这里就必须要等待时间超过之前我们设置的时间期限

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

重新执行查询语句

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 视图来查看原因

xb@PDB12C> select child_number,ROLL_INVALID_MISMATCH from v$sql_shared_cursor where sql_id = 'a2d8wpfzju8fr';

CHILD_NUMBER R
------------ -
           0 N
           1 Y

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