ANBOB™

在之前blog《Troubleshooting long wait “enq: US – contention” & “enq: IV – contention” after DDL, alert show “libcache interrupt action by LCK”》 记录过DDL 会导致和对象相关的所有SQL cursor invalidation无效，而引发一系列的SQL解析风暴问题，SQL 重新解析的几种情况如收集统计信息、DDL(alert or comment)、DCL( grant or revoke)、create index、shared_pool age out(sharepool size small、flush shared_pool, purge sql cursor)等，但是注意ORACLE的不同的版本行为也在变化，为了减少不比要的sql cursor invalidation，因为sql parse的对于繁忙的系统代码是巨大的. 但是对于SQL调优时，有时我们是希望做了一些改变后希望SQL再次解析生成更好的执行计划(maybe)， 通常是comment DDL或grant select on xx to system等相对影响较小的操作。但是注意“COMMENT ON” DDL 在Oracle 19c中行为貌似又改变了(12c未改变，18c不确认)，SQL CURSOR不再失效invalidation，这也正是我这篇主要描述的，因为这正是我经常让SQL hard parse force常用的。我没有找到相关官方的文档.

SQL cursor  deferred invalidation

在12c 版本以前在dbms_stats中有no_invalidate选项，可以控制在统计信息相关改变后SQL CURSOR是否立即失效还在指定的时间窗口内滚动失效，滚动失效的窗口是有参数“_optimizer_invalidation_period”，单位秒，默认5小时。改变该参数使用：

SQL> alter system set "_optimizer_invalidation_period"=15; --15 second
System altered.

12c 又引入了DDL deferred invalidation 特性，在一些DDL 时可以指定“deferred invalidation”选荐，另外还有系统参数cursor_invalidation控制，默认为immediate，可以修改为deferred实现相同的效果。

对于SQL是否即将滚动失效，12C 在 V$SQL列引入了IS_ROLLING_INVALID列，值为Y\N\X, 意思应该为”是\否\滚动窗口开始”

DEMO

下面演示一下,版本 Oracle 19.2

SQL> create table testinv(id int,name varchar2(10));

SQL> Select * from testinv; --repeat 4 times

SQL> select sql_id,sql_text,child_number,invalidations,loads,parse_calls,executions,is_rolling_invalid from v$sql where sql_text like 'S%testinv%' order by sql_text;

SQL_ID        SQL_TEXT                       CHILD_NUMBER INVALIDATIONS      LOADS PARSE_CALLS EXECUTIONS I
------------- ------------------------------ ------------ ------------- ---------- ----------- ---------- -
481ahkqp95agh Select * from testinv                     0             0          1           4          4 N

SQL> comment on table testinv is 'test comment cursor invalidations';
Comment created.

SQL> Select * from testinv; --repeat 2 times

SQL> select sql_id,sql_text,child_number,invalidations,loads,parse_calls,executions,is_rolling_invalid from v$sql where sql_text like 'S%testinv%' order by sql_text;

SQL_ID        SQL_TEXT                       CHILD_NUMBER INVALIDATIONS      LOADS PARSE_CALLS EXECUTIONS I
------------- ------------------------------ ------------ ------------- ---------- ----------- ---------- -
481ahkqp95agh Select * from testinv                     0             0          1           6          6 N

SQL> Select id from testinv; --repeat 3 times 

SQL> select sql_id,sql_text,child_number,invalidations,loads,parse_calls,executions,is_rolling_invalid from v$sql where sql_text like 'S%testinv%' order by sql_text;

SQL_ID        SQL_TEXT                       CHILD_NUMBER INVALIDATIONS      LOADS PARSE_CALLS EXECUTIONS I
------------- ------------------------------ ------------ ------------- ---------- ----------- ---------- -
481ahkqp95agh Select * from testinv                     0             2          2           1          1 N
5pwupv8srwh0b Select id from testinv                    0             1          2           3          3 N

SQL> alter table testinv add c2 int  deferred invalidation;
Table altered.

SQL> Select id from testinv;--repeat 2 times

SQL> select sql_id,sql_text,child_number,invalidations,loads,parse_calls,executions,is_rolling_invalid from v$sql where sql_text like 'S%testinv%' order by sql_text;

SQL_ID        SQL_TEXT                       CHILD_NUMBER INVALIDATIONS      LOADS PARSE_CALLS EXECUTIONS I
------------- ------------------------------ ------------ ------------- ---------- ----------- ---------- -
481ahkqp95agh Select * from testinv                     0             2          2           1          1 N
5pwupv8srwh0b Select id from testinv                    0             2          3           2          2 N

SQL> create index idx_testinv_c1 on testinv(c1)  deferred invalidation;
Index created.

SQL> Select id from testinv;--repeat 2 times

SQL> select sql_id,sql_text,child_number,invalidations,loads,parse_calls,executions,is_rolling_invalid from v$sql where sql_text like 'S%testinv%' order by sql_text;

SQL_ID        SQL_TEXT                       CHILD_NUMBER INVALIDATIONS      LOADS PARSE_CALLS EXECUTIONS I
------------- ------------------------------ ------------ ------------- ---------- ----------- ---------- -
481ahkqp95agh Select * from testinv                     0             2          2           1          1 N
5pwupv8srwh0b Select id from testinv                    0             2          3           4          4 X

打赏

微信扫一扫，打赏作者吧～