Global Temporary Table private statistics

Abstract

There is one Oracle running system launching simultaneously 49 sessions doing the same business for distinct partitions and distinct ranges of employees so that there will never be any collision in this process. However, this application uses bind variables and is, therefore, confronted to the classical performance threat of sharing the same execution plan between a set of bind variables not necessarily dealing with the same volume of data. This application was upgrading from 11gR2 to 12cR1. I was then asked to find a solution so that execution plans will not be shared between those 49 sessions.

I know that Adaptive Cursor Sharing has been implemented for such kind of situations. But I know also that the transition from a bind sensitive to a bind aware cursor depends on a combination of executions done at different bind variable values which makes it very difficult to guaranty each execution will have its proper optimized execution plan. So, I immediately ruled out this option. However, when I looked at one of those shared execution plans, I realized that those 49 sessions are extensively using global temporary table (GTT). This observation gave me an idea which reveals later to be very useful: use the cursor invalidation introduced by the new 12c GTT SESSION PRIVATE Statistics to force the CBO hard parsing a new execution plan for each of the 49 sessions.

Session PRIVATE statistics

Very often the Cost Based Optimizer does a wrong cardinality estimation when visiting Global Temporary Tables (GTT). This is particularly true when multiple sessions are visiting the same GTT but using different number of rows per session. In this case, sharing GTT statistics between sessions leads, generally, to a performance pain. Dynamic sampling at its default value might also reveal to be not sufficient to help the CBO in this situation. The application locks the statistics of the 11gR2 temporary tables used during the 49 sessions, so that it becomes impossible to see one session propagates its temporary table statistics to all other sessions. But this was before 12c. Starting from 12c, Oracle introduces a global statistics preference which makes possible to have session-private statistics for temporary tables:

SQL> @getPrefs
Enter value for preference: GLOBAL_TEMP_TABLE_STATS 
Enter value for tablename: 
PREFS 
-------- 
SESSION

Therefore, I decided to:

• Let each session has its proper GTT private SESSION statistics
• Use the cursor invalidation due to this GTT private statistics to avoid sharing execution plan between sessions

I started by unlocking the statistics of a couple of GTT tables involved in the 49 sessions DML queries as follows:

  SQL> execute dbms_stats.unlock_table_stats('SYSADM',T1_GTT_TEMP);

Parsing issue

Once I have implemented this change (unlock GTT statistics and use the default SESSION global parameter) I immediately realized that the 49 sessions were not anymore suffering from a performance issue due to execution plan sharing.But, as you might suspect, this doesn’t come free of charge. Indeed, I have spotted out new wait events appearing near the TOP 10 foreground wait events:

• cursor: pin S wait on X 
• library cache lock

These two wait events are symptoms of parsing effect.

While the 49 sessions were still running I issued the following query to get the SQL_ID suffering from this parsing issue:

SQL> select 
         sql_id
	 ,count(1) 
     from 
	  gv$active_session_history 
     where sample_time between to_date('05022019 15:29:00', 'ddmmyyyy hh24:mi:ss') 
	                      and to_date('05022019 16:00:00', 'ddmmyyyy hh24:mi:ss') 
     and event = 'cursor: pin S wait on X' 
     group by sql_id order by 2 desc fetch first 5 rows only
	
SQL_ID        COUNT(1) 
------------- ---------- 
5wgr71p4aj10v   28204
6fas07f6nptcu    6415 
                 5748 
fy64nh1g4ucxp    3063 
9rhtk5vh3qkjg    2394

I then tried to check why the above red bolded SQL_ID is being hard parsed so many times

SQL> @gv$sql 
      Enter value for sql_id: 5wgr71p4aj10v 
	  
	  SQL_ID 		CHILD_NUMBER TO_CHAR(P.LAST_ACTI EXECUTIONS END_FETCH  INVALIDATIONS OBJECT_STATUS
	  ------------- ------------ ------------------- ---------- ---------- ------------- -------------- 
	  5wgr71p4aj10v 0            05/02/2019 15:39:30         0        0               8 VALID 
	  5wgr71p4aj10v 1            05/02/2019 15:36:49         1        1               9 INVALID_UNAUTH 
	  5wgr71p4aj10v 2            05/02/2019 15:39:30         0        0               6 VALID 
	  5wgr71p4aj10v 3            05/02/2019 15:39:30         0        0               6 VALID 
	  5wgr71p4aj10v 4            05/02/2019 15:39:30         0        0               7 VALID 
	  5wgr71p4aj10v 5            05/02/2019 15:39:30         0        0               6 VALID 
	  5wgr71p4aj10v 6            05/02/2019 15:39:30         0        0               4 VALID 
	  5wgr71p4aj10v 7            05/02/2019 15:39:30         0        0               5 VALID
	  5wgr71p4aj10v 8            05/02/2019 15:39:30         0        0               4 VALID 
	  5wgr71p4aj10v 9            05/02/2019 15:39:30         0        0               5 VALID 
	  5wgr71p4aj10v 10           05/02/2019 15:36:10         1        1               6 INVALID_UNAUTH 
	  5wgr71p4aj10v 11           05/02/2019 15:39:30         0        0               4 VALID 
	  5wgr71p4aj10v 12           05/02/2019 15:39:30         0        0               4 VALID 
	  5wgr71p4aj10v 13           05/02/2019 15:39:30         0        0               5 VALID
	  5wgr71p4aj10v 14           05/02/2019 15:39:30         0        0               4 VALID 
	  5wgr71p4aj10v 15           05/02/2019 15:37:18         1        1               5 INVALID_UNAUTH 
	  5wgr71p4aj10v 17           05/02/2019 15:39:29         0        0               3 VALID 
	  5wgr71p4aj10v 18           05/02/2019 15:39:30         0        0               3 VALID 
	  5wgr71p4aj10v 19           05/02/2019 15:39:30         0        0               2 VALID 
	  5wgr71p4aj10v 20           05/02/2019 15:39:30         0        0               2 VALID 
	  5wgr71p4aj10v 21           05/02/2019 15:39:30         0        0               2 VALID 

Notice the high number of invalidated cursors (several in about 1 min). The INVALID_UNAUTH status means that the underlying child cursor will not be shared the next time and will be aged out when there is a stress on the library cache.

The reason of this frequent invalidation is shown here below:

SQL> @nonshared 
     Show why existing SQL child cursors were not reused (V$SQL_SHARED_CURSOR)...

	 Enter value for 1: 5wgr71p4aj10v 
	 SQL_ID           : 5wgr71p4aj10v 
	 DDRESS           : 0000000308AB25B8 
	 CHILD_ADDRESS    : 00000002FCDABB40 
	 CHILD_NUMBER     : 0 
	 REASON           :<reason>Session Specific Cursor Session Mismatch(1)</reason> 
	 CON_ID           : 0 
	 ----------------- 
	 SQL_ID           : 5wgr71p4aj10v 
	 ADDRESS          : 0000000308AB25B8 
	 CHILD_ADDRESS    : 00000002FC680A48 
	 CHILD_NUMBER     : 1 
	 REASON           : <reason>Session Specific Cursor Session Mismatch(1)</reason> 
	 CON_ID           : 0
	 ----------------- 
	 ../.. 
	 SQL_ID           : 5wgr71p4aj10v 
	 ADDRESS          : 0000000308AB25B8
	 CHILD_ADDRESS    : 0000000317274F10 
	 CHILD_NUMBER     : 22 
	 REASON           : <reason>Session Specific Cursor Session Mismatch(1)</reason> 
	 CON_ID           : 0
	 ----------------- 

The Session Specific Cursor Session Mismatch reason indicates that Oracle has decided to invalidate the current cursor and to optimize a new execution plan because the current session is not allowed to use the GTT statistics of another session. It is crystal clear that when using SESSION PRIVATE statistics for GTT Oracle will not share the same child cursor that was optimized from another session. Each session will want to have its proper execution plan. it tries to pin the parent cursor and finds that it has been already pinned out by a preceding session. It then starts waiting on this famous wait event: cursor: pin S wait on X
It is important also to remember that Oracle gives us an information via the Note, at the bottom of the execution plan, when it is using a GTT private session statistics as shown below:

Note 
----- 
-- Global temporary table session private statistics used

Conclusion
This brief note shows, via a practical example taken from a running system, that we can use the 12c SESSION PRIVATE statistics to achieve two goals at least:

• Have each session with its proper GTT statistics without propagating them to another session
• Use the cursor invalidation side effect induced by the underlying code of the GTT SESSION PRIVATE statistics to avoid sharing execution plan between sessions

All things being equal you must balance between the performance improvement brought by this new GTT feature and the parsing side effect it introduces because of the underlying cursor invalidation. In my client case
the Library cache and Cursor Pin S wait on X wait events introduced by the SESSION PRIVATE statistics largely outweigh the performance penalty that comes when the 49 streams share the same GTT statistics.