Infrastructure at your Service

Oracle Team

12c GTT private statistics and cursor invalidation

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.

7 Comments

  • Andrew Sayer says:

    Thanks for the write up.
    I don’t see the problem, if you gather statistics that represent the data that only your session can see then you shouldn’t have to wait for the rolling invalidation before your execution plans could take advantage (by then the GTT could be used with different data or your session could have logged out).

    The only problem I can consider, and I’d appreciate a follow up, is if gathering private statistics in session A invalidates cursors in session B?

    If the GTT is always used in the same way then shared statistics are perfect. FWIW, I wouldn’t gather the statistics in the application code though, just once outside the application when some sample data is in the GTT should be more than sufficient.

  • Hi Andrew,
    >> The only problem I can consider, and I’d appreciate a follow up, is if gathering private statistics in session A invalidates cursors in session B?
    Yes, this is what invalidation does. That’s the problem. Non sharing cursor across sessions (and creating new child) is one thing. Invalidating cursors that are already there is another thing.
    Your point about not gathering in the application is good (so you don’t need this 12c feature) but then you must avoid truncate + insert /*+ append */ or disable online statistics gathering.
    Regards,
    Franck.

    • Andrew Sayer says:

      Thanks for the response Franck,
      >>Yes, this is what invalidation does. That’s the problem. Non sharing cursor across sessions (and creating new child) is one thing. Invalidating cursors that are already there is another thing.

      I’ve just reinstalled my 12c instance to double check this. From my test cases, gathering private statistics in one session does not invalidate a child cursor being used by another session, all sessions use their own child cursor and only invalidate their own session’s child cursor when gathering statistics.
      (Not sure how to format this nicely)

      Setup:

      truncate table DEMOGTT1;
      drop table DEMOGTT1;
      alter system flush shared_pool;
      create global temporary table DEMOGTT1
      on commit preserve rows
      as
      select * from all_objects where 1=0;
      exec dbms_stats.set_table_prefs(user,'DEMOGTT1','GLOBAL_TEMP_TABLE_STATS','SESSION');
       
      SQL> get sql_find
      1* select sql_id, executions, substr(sql_text,1,100) sql_text from v$sql where sql_text not like '%v$sql%' and upper(sql_text) like UPPER('%&1%')
      SQL> get sql_info
      1* select sql_id, child_number, executions, invalidations from v$sql where sql_id = '&1';
      SQL> get test_case
      1 select sys_context('userenv','sid') from dual;
      2 set serverout off
      3 var x varchar2(30);
      4 exec select object_name into :X from (select * from all_objects order by dbms_random.value) where rownum =1;
      5 insert into DEMOGTT1 select * from all_objects where rownum <=&1;
      6 exec dbms_stats.gather_table_stats(user,'DEMOGTT1')
      7 select count(*) from demogtt1 where object_name = :x;
      8 select count(*) from demogtt1 where object_name = :x;
      9* select count(*) from demogtt1 where object_name = :x;
       
      host start sqlplus andy/andy@pdborcl @test_case 2000
       
      SQL> @sql_find "select count(*) from demogtt1 where object_name = :x"
       
      SQL_ID EXECUTIONS
      ------------- ----------
      SQL_TEXT
      -----------------------------------------------------------------------------------------------------------
      3g5812fa0hdmc 3
      select count(*) from demogtt1 where object_name = :x
      &nbps;
       
      SQL> @sql_info 3g5812fa0hdmc
       
      SQL_ID CHILD_NUMBER EXECUTIONS INVALIDATIONS
      ------------- ------------ ---------- -------------
      3g5812fa0hdmc 0 3 0
       
      SQL> host start sqlplus andy/andy@pdborcl @test_case 20
       
      SQL> @sql_info 3g5812fa0hdmc
       
      SQL_ID CHILD_NUMBER EXECUTIONS INVALIDATIONS
      ------------- ------------ ---------- -------------
      3g5812fa0hdmc 0 3 0
      3g5812fa0hdmc 1 3 0

      > Rerun statement in first session

      SQL> @sql_info 3g5812fa0hdmc
       
      SQL_ID CHILD_NUMBER EXECUTIONS INVALIDATIONS
      ------------- ------------ ---------- -------------
      3g5812fa0hdmc 0 4 0
      3g5812fa0hdmc 1 3 0

      One child cursor per session does seem a little off putting, and I can appreciate it could be issues for some applications – but less so than the pre 12c situation. If you were already gathering statistics every time you populate the GTT then private statistics will help you use those statistics and not execute a child cursor that was created using statistics from another session.

      Good point about truncate and insert append (although both those operations would make me nervous at the best of times) but you can use the NO_GATHER_OPTIMIZER_STATISTICS hint (documented) to disable them for a statement.

      • Andrew Sayer says:

        Part of line 5 onwards from test_case.sql got trimmed from the comment:

        5 insert into DEMOGTT1 select * from all_objects where rownum <=&1;
        6 exec dbms_stats.gather_table_stats(user,'DEMOGTT1')
        7 select count(*) from demogtt1 where object_name = :x;
        8 select count(*) from demogtt1 where object_name = :x;
        9* select count(*) from demogtt1 where object_name = :x;

        i.e insert a number of rows (determined by caller), gather stats, run the query 3 times.

        • Hi Andrew,
          I’ve added the <code> and included the 5 lines into first comment. Thanks a lot for the example.
          I’ll try to understand the difference in the case I encountered. You are using session cursor cache here (reason why you run 3 times), right?
          Regards,
          Franck.

          • Andrew Sayer says:

            Franck, I’m not sure I understand your question. The test cases were run in standalone sessions so there was no risk of cursors being closed. If the cursors were closed (due to many other statements being fired), then I believe we’d only see soft parses – no change there.

            The second executions were to allow for statistics feedback to be accounted for, neither session experienced any feedback so it was unnecessary. The third executions had no (intentional) reason behind them.

            Glad to have helped, this is certainly an interesting new feature that some may be a bit too nervous of trying still.

  • Andrew, I’ve added a small update at the top of the blog post. As you have seen, the cursors that are not created from the same private statistics are not invalidated, which is a good point. My conclusion was not exact, thanks a lot for your tests.

Leave a Reply

Oracle Team
Oracle Team