Back in 2015, I upgraded our Oracle 11.2.0.4 databases to 12.1.0.2 and experienced some performance issues related to our use of GTTs. I blogged about those issues here.
The crux of the issue I was trying to solve was that a behavior change in 12c lead to Oracle saving stats that the GTT has zero rows when it doesn’t. Stats showing the number of rows equals zero lead to full table scans and cartesian products on queries that involve the GTT. As I stated in that blog post, we used DBMS_STATS.SET_TABLE_STATS after we populated the table with data so that each session would have proper stats to arrive at a better execution plan.
After we upgraded to Oracle 19c, we started seeing other performance issues related to the GTT. Queries that used the GTT started waiting on the “cursor pin:S wait on X” wait event. This could have been a behavior change with the new Oracle version, but it also could have been our developers using the GTT more often in our code and have nothing to do with the new version.
For the queries involved in the Cursor Pin wait event, I noticed a high number of versions of the SQL statement in the Shared Pool. When I queried V$SQL_SHARED_CURSOR, I discovered that PURGED_CURSOR=’Y’ for these SQL statements. The cursor is becoming invalidated.
When researching this issue, I discovered that what happens is that every time we called DBMS_STATS.SET_TABLE_STATS to get session-based stats on the GTT, it invalidates all SQL statements that use that GTT. Hence the wait. The wait was not lengthy so many end users didn’t even notice the issue.
But then we had a new problem. When you make a call to SET_TABLE_STATS, Oracle writes an entry into SYS.WRI$_OPTSTAT_TAB_HISTORY and you can see the values the session set for the table’s stats. By default, this table stores 30 days of history. The table was growing very largely and consuming most of SYSAUX. Every so often (hourly?) Oracle will remove entries more than 30 days old. This regular pruning of this table was now negatively impacting end user performance. The following is a performance graph from Lighty showing the impact of the pruning of this table:
All of that scary red color is when the old rows were being removed from SYS.WRI$_OPTSTAT_TAB_HISTORY.
The cultural expectations of male sexuality inhibit numerous men from seeking uk viagra sales medical attention that they actually need. These pills cheap viagra prices provide you bigger, harder, and longer lasting erections. The medicine in all of its form contains Sildenafil citrate 5mg cialis price as the active constituent. Female cialis viagra levitra sexual dysfunction can be due to physical, psychological, or social problems.So my performance “fix” five years ago introduced another performance issue. To improve performance, what I did was to create shared stats on the GTT and stop using session stats. Here are the steps:
--set prefs to SHARED globally
exec DBMS_STATS.set_global_prefs ( pname => 'GLOBAL_TEMP_TABLE_STATS', pvalue => 'SHARED');
--set the table and index stats
exec dbms_stats.set_table_stats(ownname=>'MY_SCHEMA',tabname=>'MY_GTT_TABLE',numrows=>1000,numblks=>2,avgrlen=>15);
exec dbms_stats.set_index_stats(ownname=>'MY_SCHEMA',indname=>'GTT_INDEX',indlevel=>1,numlblks=>2,numdist=>15,clstfct=>28,numrows=>1000);
-- set prefs back to SESSION
exec DBMS_STATS.set_global_prefs ( pname => 'GLOBAL_TEMP_TABLE_STATS', pvalue => 'SESSION');
-- verify stats set
select num_rows,blocks,last_analyzed,scope
from dba_tab_statistics
where table_name ='MY_GTT_TABLE';
select blevel,leaf_blocks,distinct_keys,num_rows,clustering_factor,last_analyzed,scope
from dba_ind_statistics
where index_name='GTT_INDEX' and owner='MY_SCHEMA';
Once the shared stats were in place, we remove the calls to DBMS_SET_TABLE_STATS from our code.
