By Franck Pachot

.
Short summary of this post is that rolling invalidation do not occur when you gather statistics on Global Temporary Tables in 12c that have session statistic scope (which is the default) and this may cause too many hard parses. I’m sharing all details and comments are welcome.

Update28-OCT-16

I’ve written this having in mind a specific case I encountered. But what I said here is too wide: not all cursors are invalidated, but only those that have been created on same session private statistics. Thanks to Andrew Sayer (see comments) and to Mark from Oracle Support for their tests with cursors created by other sessions.

When you gather statistics on a table, the goal is to get new plan if statistics have changed, so you can expect cursor invalidation. However, invalidating immediately all cursors that have a dependency with the table may cause a hard parse storm and this is why by default rolling invalidation occurs: invalidation of cursor will be planned randomly in a time window that follows next execution. 12c comes with a new feature, global temporary table private stats where execution plans are not shared between sessions. And there’s another feature where statistics gathering is automatic when you bulk insert into an empty table.

In both cases, by default, invalidation is not rolling but immediate. Let’s see examples.

No GTT -> rolling invalidation

Here is an example with a regular table to show rolling invalidation:


21:14:36 SQL> create
21:14:36   2  table DEMOGTT1 as select * from dual;
Table created.
 
21:14:38 SQL> exec dbms_stats.gather_table_stats('DEMO','DEMOGTT1');
PL/SQL procedure successfully completed.
 
21:14:39 SQL> insert into DEMOGTT1 select * from dual;
1 row created.
 
21:14:39 SQL> alter session set optimizer_mode=first_rows;
Session altered.
 
21:14:39 SQL> insert into DEMOGTT1 select * from dual;
1 row created.
 
21:14:39 SQL> select object_status,child_number,address,hash_value,parse_calls,executions,is_rolling_invalid,invalidations from v$sql where sql_id='1pmuu9z02day7';
 
OBJECT_STATUS       CHILD_NUMBER ADDRESS          HASH_VALUE PARSE_CALLS EXECUTIONS IS_ROLLING_INVALID INVALIDATIONS
------------------- ------------ ---------------- ---------- ----------- ---------- ------------------ -------------
VALID                          0 000000008368BB98 3223759815           1          1 N                              0
VALID                          1 000000008368BB98 3223759815           1          1 N                              0
 
21:14:40 SQL> exec dbms_stats.gather_table_stats('DEMO','DEMOGTT1',no_invalidate=>null);
PL/SQL procedure successfully completed.
 
21:14:41 SQL> select object_status,child_number,address,hash_value,parse_calls,executions,is_rolling_invalid,invalidations from v$sql where sql_id='1pmuu9z02day7';
 
OBJECT_STATUS       CHILD_NUMBER ADDRESS          HASH_VALUE PARSE_CALLS EXECUTIONS IS_ROLLING_INVALID INVALIDATIONS
------------------- ------------ ---------------- ---------- ----------- ---------- ------------------ -------------
VALID                          0 000000008368BB98 3223759815           1          1 Y                              0
VALID                          1 000000008368BB98 3223759815           1          1 Y                              0
 
21:14:41 SQL> select table_name,scope,last_analyzed from user_tab_statistics where table_name='DEMOGTT1';
 
TABLE_NAME                     SCOPE   LAST_ANA
------------------------------ ------- --------
DEMOGTT1                       SHARED  21:14:40

Statistics on non-GTT are shared and the dbms_stats using default no_invalidate do rolling invalidation.

GTT with session private stats -> immediate invalidation

Here is the same example with a Global Temporary Table:


21:13:06 SQL> create
21:13:06   2  global temporary
21:13:06   3  table DEMOGTT1 as select * from dual;
Table created.
...
21:13:09 SQL> select object_status,child_number,address,hash_value,parse_calls,executions,is_rolling_invalid,invalidations from v$sql where sql_id='1pmuu9z02day7';
 
OBJECT_STATUS       CHILD_NUMBER ADDRESS          HASH_VALUE PARSE_CALLS EXECUTIONS IS_ROLLING_INVALID INVALIDATIONS
------------------- ------------ ---------------- ---------- ----------- ---------- ------------------ -------------
VALID                          0 000000008096DF10 3223759815           1          1 N                              0
VALID                          1 000000008096DF10 3223759815           1          1 N                              0
 
21:13:10 SQL> exec dbms_stats.gather_table_stats('DEMO','DEMOGTT1',no_invalidate=>null);
PL/SQL procedure successfully completed.
 
21:13:11 SQL> select object_status,child_number,address,hash_value,parse_calls,executions,is_rolling_invalid,invalidations from v$sql where sql_id='1pmuu9z02day7';
 
OBJECT_STATUS       CHILD_NUMBER ADDRESS          HASH_VALUE PARSE_CALLS EXECUTIONS IS_ROLLING_INVALID INVALIDATIONS
------------------- ------------ ---------------- ---------- ----------- ---------- ------------------ -------------
INVALID_UNAUTH                 0 000000008096DF10 3223759815           1          1 N                              1
INVALID_UNAUTH                 1 000000008096DF10 3223759815           1          1 N                              1
 
21:13:11 SQL> select table_name,scope,last_analyzed from user_tab_statistics where table_name='DEMOGTT1';
 
TABLE_NAME                     SCOPE   LAST_ANA
------------------------------ ------- --------
DEMOGTT1                       SHARED
DEMOGTT1                       SESSION 21:13:10

By default, gathered statistics are private to the session and you see that all cursors have been invalidated immediately. Next execution will need to hard parse.

GTT with shared stats -> no invalidation

When setting shared statistics on the GTT we come back to the 11g behavior:


21:28:52 SQL> create
21:28:52   2  global temporary
21:28:52   3  table DEMOGTT1 as select * from dual;
Table created.
 
21:28:52 SQL> exec dbms_stats.set_table_prefs(user,'DEMOGTT1','GLOBAL_TEMP_TABLE_STATS','SHARED');
PL/SQL procedure successfully completed.
...
21:28:55 SQL> select object_status,child_number,address,hash_value,parse_calls,executions,is_rolling_invalid,invalidations from v$sql where sql_id='1pmuu9z02day7';
 
OBJECT_STATUS       CHILD_NUMBER ADDRESS          HASH_VALUE PARSE_CALLS EXECUTIONS IS_ROLLING_INVALID INVALIDATIONS
------------------- ------------ ---------------- ---------- ----------- ---------- ------------------ -------------
VALID                          0 0000000079782A08 3223759815           1          1 N                              0
VALID                          1 0000000079782A08 3223759815           1          1 N                              0
 
21:28:56 SQL> exec dbms_stats.gather_table_stats('DEMO','DEMOGTT1',no_invalidate=>null);
PL/SQL procedure successfully completed.
 
21:28:57 SQL> select object_status,child_number,address,hash_value,parse_calls,executions,is_rolling_invalid,invalidations from v$sql where sql_id='1pmuu9z02day7';
 
OBJECT_STATUS       CHILD_NUMBER ADDRESS          HASH_VALUE PARSE_CALLS EXECUTIONS IS_ROLLING_INVALID INVALIDATIONS
------------------- ------------ ---------------- ---------- ----------- ---------- ------------------ -------------
VALID                          0 0000000079782A08 3223759815           1          1 Y                              0
VALID                          1 0000000079782A08 3223759815           1          1 Y                              0
 
21:28:57 SQL> select table_name,scope,last_analyzed from user_tab_statistics where table_name='DEMOGTT1';
 
TABLE_NAME                     SCOPE   LAST_ANA
------------------------------ ------- --------
DEMOGTT1                       SHARED  21:28:56

No invalidation: this is rolling invalidation

GTT with session stats but “_optimizer_use_gtt_session_stats”=false

Here is an exemple when disabling the private statistics feature:


21:15:36 SQL> create
21:15:36   2  global temporary
21:15:36   3  table DEMOGTT1 as select * from dual;
Table created.
 
21:15:36 SQL> alter session set "_optimizer_use_gtt_session_stats"=false;
Session altered.
...
21:15:38 SQL> select object_status,child_number,address,hash_value,parse_calls,executions,is_rolling_invalid,invalidations from v$sql where sql_id='1pmuu9z02day7';
 
OBJECT_STATUS       CHILD_NUMBER ADDRESS          HASH_VALUE PARSE_CALLS EXECUTIONS IS_ROLLING_INVALID INVALIDATIONS
------------------- ------------ ---------------- ---------- ----------- ---------- ------------------ -------------
VALID                          0 000000007A373A08 3223759815           1          1 N                              0
VALID                          1 000000007A373A08 3223759815           1          1 N                              0
 
21:15:39 SQL> exec dbms_stats.gather_table_stats('DEMO','DEMOGTT1',no_invalidate=>null);
PL/SQL procedure successfully completed.
 
21:15:41 SQL> select object_status,child_number,address,hash_value,parse_calls,executions,is_rolling_invalid,invalidations from v$sql where sql_id='1pmuu9z02day7';
 
OBJECT_STATUS       CHILD_NUMBER ADDRESS          HASH_VALUE PARSE_CALLS EXECUTIONS IS_ROLLING_INVALID INVALIDATIONS
------------------- ------------ ---------------- ---------- ----------- ---------- ------------------ -------------
VALID                          0 000000007A373A08 3223759815           1          1 N                              0
VALID                          1 000000007A373A08 3223759815           1          1 N                              0
 
21:15:41 SQL> select table_name,scope,last_analyzed from user_tab_statistics where table_name='DEMOGTT1';
 
TABLE_NAME                     SCOPE   LAST_ANA
------------------------------ ------- --------
DEMOGTT1                       SHARED
DEMOGTT1                       SESSION 21:15:40

No invalidation here as in previous versions. But interesting thing is that I still have session statistics. The setting just disables its usage. But then, there were no invalidation and no rolling invalidation. Not sure how to interpret that…

Invalidation with online statistics gathering

In all those examples I’ve used dbms_stats with default no_invalidate. But in 12c statistics gathering can occur automatically during bulk insert. Let’s try that:


...
21:38:50 SQL> select object_status,child_number,address,hash_value,parse_calls,executions,is_rolling_invalid,invalidations from v$sql where sql_id='1pmuu9z02day7';
 
OBJECT_STATUS       CHILD_NUMBER ADDRESS          HASH_VALUE PARSE_CALLS EXECUTIONS IS_ROLLING_INVALID INVALIDATIONS
------------------- ------------ ---------------- ---------- ----------- ---------- ------------------ -------------
VALID                          0 000000007A9D8860 3223759815           1          1 N                              0
VALID                          1 000000007A9D8860 3223759815           1          1 N                              0
 
21:38:51 SQL> truncate table DEMOGTT1;
Table truncated.
21:38:52 SQL> insert /*+ append */ into  DEMOGTT1 select * from dual;
1 row created.
 
21:38:53 SQL> select object_status,child_number,address,hash_value,parse_calls,executions,is_rolling_invalid,invalidations from v$sql where sql_id='1pmuu9z02day7';
 
OBJECT_STATUS       CHILD_NUMBER ADDRESS          HASH_VALUE PARSE_CALLS EXECUTIONS IS_ROLLING_INVALID INVALIDATIONS
------------------- ------------ ---------------- ---------- ----------- ---------- ------------------ -------------
INVALID_UNAUTH                 0 000000007A9D8860 3223759815           1          1 N                              1
INVALID_UNAUTH                 1 000000007A9D8860 3223759815           1          1 N                              1
21:38:53 SQL> select table_name,scope,last_analyzed from user_tab_statistics where table_name='DEMOGTT1';
 
TABLE_NAME                     SCOPE   LAST_ANA
------------------------------ ------- --------
DEMOGTT1                       SHARED
DEMOGTT1                       SESSION 21:38:52

Same behaviour here. The online statistics gathering has gathered private statistics and invalidated all cursors.

NO_INVALIDATE=true

We can explicitly disable invalidation with no_invalidate=>true:


...
21:43:25 SQL> select object_status,child_number,address,hash_value,parse_calls,executions,is_rolling_invalid,invalidations from v$sql where sql_id='1pmuu9z02day7';
 
OBJECT_STATUS       CHILD_NUMBER ADDRESS          HASH_VALUE PARSE_CALLS EXECUTIONS IS_ROLLING_INVALID INVALIDATIONS
------------------- ------------ ---------------- ---------- ----------- ---------- ------------------ -------------
VALID                          0 0000000075873D60 3223759815           1          1 N                              0
VALID                          1 0000000075873D60 3223759815           1          1 N                              0
 
21:43:28 SQL> exec dbms_stats.gather_table_stats('DEMO','DEMOGTT1',no_invalidate=>true);
PL/SQL procedure successfully completed.
 
21:43:29 SQL> select object_status,child_number,address,hash_value,parse_calls,executions,is_rolling_invalid,invalidations from v$sql where sql_id='1pmuu9z02day7';
 
OBJECT_STATUS       CHILD_NUMBER ADDRESS          HASH_VALUE PARSE_CALLS EXECUTIONS IS_ROLLING_INVALID INVALIDATIONS
------------------- ------------ ---------------- ---------- ----------- ---------- ------------------ -------------
VALID                          0 0000000075873D60 3223759815           1          1 N                              0
VALID                          1 0000000075873D60 3223759815           1          1 N                              0
 
21:43:29 SQL> select table_name,scope,last_analyzed from user_tab_statistics where table_name='DEMOGTT1';
 
TABLE_NAME                     SCOPE   LAST_ANA
------------------------------ ------- --------
DEMOGTT1                       SHARED
DEMOGTT1                       SESSION 21:43:28

Here, as requested, private statistics has been gathered but without cursor invalidation. However I’ll have new hard parse for my query because private statistics prevent sharing another cursor, but it’s not an invalidation of all cursors. The other sessions will continue to re-use their plan.

So what?

With those new features, we have the famous parsing dilemma again: do we want to avoid too many hard parses and share cursor with the risk of executing an execution plan that has been optimized for different data? Or do we prefer to optimize each query at the risk of more CPU consumption and shared pool contention? Given that 12c comes with adaptive dynamic sampling that can make hard parse longer, and sometimes very very long, all those new features may be gauged carefully.

If you want to avoid hard parses, you should set preferences to SHARED statistics and then gather statistics when the GTT is filled with the data you want to optimize for, and then lock it. If you don’t, then you are back to the problem that private statistics tries to solve: sharing a plan optimized for few rows and executed on thousands.