首页 » ORACLE, ORACLE [C]系列 » Alert: Oracle 19c DDL “COMMENT on Table” sql cursor no invalidation(deferred invalidation增强)

Alert: Oracle 19c DDL “COMMENT on Table” sql cursor no invalidation(deferred invalidation增强)

在之前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

打赏

, ,

目前这篇文章还没有评论(Rss)

我要评论