19c RAC dbms_stats with no_invalidate=>false Invalidate Cursors Differently In Local Instance and Remote Instance When Non-Blocking Hard Parse Is Enabled By Default (Doc ID 2913131.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

•  In 19c RAC environment, after executed dbms_stats.gather_xxx_stats with no_invalidate=>false, the dependent cursors of local instance are invalidated immediately, but the cursors of remote instance cannot be invalidated until the _optimizer_invalidation_period of auto rolling invalidation period expired. But, in 11.2.0.4 and 12.1.0.2, the dependent cursors in all instances are invalidated immediately.
• Test log:  

  1. ===========session 1 (instance 1)=================
  set line 200 pages 1000 time on
  --Setting the "rolling invalidataion" for get the result as soon as possible.

  alter system set "_optimizer_invalidation_period"=180;

  drop table t1 purge;
  create table t1 (c1 number, c2 number);
  insert into t1 select level, 1 from dual connect by level <=10;
  commit;
  create index t1i1 on t1(c1);
  exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'for all columns size 255',no_invalidate=>false);

  alter system flush shared_pool;
  -- SQL_ID ="5nyyd8512pcub"
  select sum(c2) from t1 where c1=1;
  select sum(c2) from t1 where c1=1;
  select sum(c2) from t1 where c1=1;

  2. ===========session 2 (instance 2)=================
  set line 200 pages 1000 time on
  alter system flush shared_pool;
  select sum(c2) from t1 where c1=1;
  select sum(c2) from t1 where c1=1;
  select sum(c2) from t1 where c1=1;

  col FIRST_LOAD_TIME for a20
  col LAST_LOAD_TIME for a20
  select inst_id,sql_id,child_number,plan_hash_value phv,IS_OBSOLETE,IS_SHAREABLE,IS_ROLLING_INVALID,IS_ROLLING_REFRESH_INVALID
  ,USERS_OPENING open,LOADED_VERSIONS load_vers,OPEN_VERSIONS open_vers,EXECUTIONS exec,LOADS,INVALIDATIONS invals,PARSE_CALLS
  ,FIRST_LOAD_TIME,LAST_LOAD_TIME,to_char(LAST_ACTIVE_TIME,'yyyy-mm-dd/hh24:mi:ss') LAST_ACTIVE_TIME
  from gv$sql where sql_id='5nyyd8512pcub';

  INST_ID SQL_ID CHILD_NUMBER PHV I I I I OPEN LOAD_VERS OPEN_VERS EXEC LOADS INVALS PARSE_CALLS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE_TIME
  ------- ------------- ------------ ---------- - - - - ---------- ---------- ---------- ---------- ---------- ---------- ----------- -------------------- -------------------- -------------------
  2 5nyyd8512pcub 0 36035814 N Y N N 1 1 1 3 1 0 3 2022-11-03/14:16:10 2022-11-03/14:16:10 2022-11-03/14:16:10
  1 5nyyd8512pcub 0 36035814 N Y N N 1 1 1 3 1 0 3 2022-11-03/14:16:01 2022-11-03/14:16:01 2022-11-03/14:16:01

  --Insert data and recollect the statstics with option :no_invalidate=>false
  insert into t1 select 1, 1 from dual connect by level <=1000;
  commit;
  exec dbms_stats.gather_table_stats(user,'T1',method_opt=>'for all columns size 255',no_invalidate=>false);

  select inst_id,sql_id,child_number,plan_hash_value phv,IS_OBSOLETE,IS_SHAREABLE,IS_ROLLING_INVALID,IS_ROLLING_REFRESH_INVALID
  ,USERS_OPENING open,LOADED_VERSIONS load_vers,OPEN_VERSIONS open_vers,EXECUTIONS exec,LOADS,INVALIDATIONS invals,PARSE_CALLS
  ,FIRST_LOAD_TIME,LAST_LOAD_TIME,to_char(LAST_ACTIVE_TIME,'yyyy-mm-dd/hh24:mi:ss') LAST_ACTIVE_TIME
  from gv$sql where sql_id='5nyyd8512pcub';

  INST_ID SQL_ID CHILD_NUMBER PHV I I I I OPEN LOAD_VERS OPEN_VERS EXEC LOADS INVALS PARSE_CALLS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE_TIME
  ------- ------------- ------------ ---------- - - - - ---------- ---------- ---------- ---------- ---------- ---------- ----------- -------------------- -------------------- -------------------
  2 5nyyd8512pcub 0 36035814 N Y Y N 0 1 0 3 1 0 3 2022-11-03/14:16:10 2022-11-03/14:16:10 2022-11-03/14:16:10
  1 5nyyd8512pcub 0 36035814 N Y Y N 1 1 1 3 1 0 3 2022-11-03/14:16:01 2022-11-03/14:16:01 2022-11-03/14:16:01
  --IS_ROLLING_INVALID=Y

  --execute the SQL "5nyyd8512pcub"

  select sum(c2) from t1 where c1=1;
  select sum(c2) from t1 where c1=1;

  3. ===========session 1 (instance 1)=================
  --We execute the SQL "5nyyd8512pcub" again .
  select sum(c2) from t1 where c1=1;
  select sum(c2) from t1 where c1=1;

  col FIRST_LOAD_TIME for a20
  col LAST_LOAD_TIME for a20
  select inst_id,sql_id,child_number,plan_hash_value phv,IS_OBSOLETE,IS_SHAREABLE,IS_ROLLING_INVALID,IS_ROLLING_REFRESH_INVALID
  ,USERS_OPENING open,LOADED_VERSIONS load_vers,OPEN_VERSIONS open_vers,EXECUTIONS exec,LOADS,INVALIDATIONS invals,PARSE_CALLS
  ,FIRST_LOAD_TIME,LAST_LOAD_TIME,to_char(LAST_ACTIVE_TIME,'yyyy-mm-dd/hh24:mi:ss') LAST_ACTIVE_TIME
  from gv$sql where sql_id='5nyyd8512pcub';

  INST_ID SQL_ID CHILD_NUMBER PHV I I I I OPEN LOAD_VERS OPEN_VERS EXEC LOADS INVALS PARSE_CALLS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE_TIME
  ------- ------------- ------------ ---------- - - - - ---------- ---------- ---------- ---------- ---------- ---------- ----------- -------------------- -------------------- -------------------
  1 5nyyd8512pcub 0 36035814 N Y X N 1 1 1 5 1 0 5 2022-11-03/14:16:01 2022-11-03/14:16:01 2022-11-03/14:17:01
  2 5nyyd8512pcub 0 36035814 N Y Y N 0 1 0 3 1 0 4 2022-11-03/14:16:10 2022-11-03/14:16:10 2022-11-03/14:16:58
  2 5nyyd8512pcub 1 3724264953 N Y N N 1 1 1 2 1 0 1 2022-11-03/14:16:10 2022-11-03/14:16:58 2022-11-03/14:16:58
  --We could see the new plan generated on node2, but on node1, the OLD plan is still used, and IS_ROLLING_INVALID is now be :X

  --With in (_optimizer_invalidation_period), we execute the SQL "5nyyd8512pcub" serial times, but new plan won't be generated

  --Wait 180, then issue the SQL again.
  host sleep 180
  select sum(c2) from t1 where c1=1;

  select inst_id,sql_id,child_number,plan_hash_value phv,IS_OBSOLETE,IS_SHAREABLE,IS_ROLLING_INVALID,IS_ROLLING_REFRESH_INVALID
  ,USERS_OPENING open,LOADED_VERSIONS load_vers,OPEN_VERSIONS open_vers,EXECUTIONS exec,LOADS,INVALIDATIONS invals,PARSE_CALLS
  ,FIRST_LOAD_TIME,LAST_LOAD_TIME,to_char(LAST_ACTIVE_TIME,'yyyy-mm-dd/hh24:mi:ss') LAST_ACTIVE_TIME
  from gv$sql where sql_id='5nyyd8512pcub';

  INST_ID SQL_ID CHILD_NUMBER PHV I I I I OPEN LOAD_VERS OPEN_VERS EXEC LOADS INVALS PARSE_CALLS FIRST_LOAD_TIME LAST_LOAD_TIME LAST_ACTIVE_TIME
  ------- ------------- ------------ ---------- - - - - ---------- ---------- ---------- ---------- ---------- ---------- ----------- -------------------- -------------------- -------------------
  1 5nyyd8512pcub 0 36035814 N Y X N 0 1 0 5 1 0 5 2022-11-03/14:16:01 2022-11-03/14:16:01 2022-11-03/14:17:01
  1 5nyyd8512pcub 1 3724264953 N Y N N 0 1 0 1 1 0 1 2022-11-03/14:16:01 2022-11-03/14:21:09 2022-11-03/14:21:09
  2 5nyyd8512pcub 0 36035814 N Y Y N 0 1 0 3 1 0 4 2022-11-03/14:16:10 2022-11-03/14:16:10 2022-11-03/14:16:58
  2 5nyyd8512pcub 1 3724264953 N Y N N 1 1 1 2 1 0 1 2022-11-03/14:16:10 2022-11-03/14:16:58 2022-11-03/14:16:58
  --Until now , the new plan geneated on node1.

   

Changes

 Upgrade to 19c

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.