This was first published on https://blog.dbi-services.com/12c-gtt-private-statistics-and-cursor-invalidation (2016-10-27)
Republishing here for new followers. The content is related to the the versions available at the publication date
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.
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.
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:40Statistics on non-GTT are shared and the dbms_stats using default no_invalidate do rolling 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:10By 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.
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:56No invalidation: this is rolling invalidation
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:40No 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…
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:52Same behaviour here. The online statistics gathering has gathered private statistics and invalidated all cursors.
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:28Here, 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.
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.
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.
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:
> Rerun statement in first session 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.
Part of line 5 onwards from test_case.sql got trimmed from the comment:
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.
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.