Prior to Oracle Database 12c, global temporary tables (GTTs) shared statistics between sessions, so each GTT had one set of statistics visible to all sessions using it. Of course, gathering statistics on GTTs can be very useful because it will help the optimizer find better execution plans, but this advantage was sometimes tempered by the fact that multiple session would see the same set of stats. To resolve this dilemma, Oracle Database 12c included the ability to create session-private statistics, allowing sessions to create and use their own set of statistics for each GTT. This new behavior is controlled by a DBMS_STATS preference GLOBAL_TEMP_TABLE_STATS, which can be set to SHARED or SESSION.
It is important to remember that the default in Oracle Database 12c is now SESSION, and that this has some implications if you're upgrading to this release. Firstly, you should consider whether your database application depends on SHARED GTT statistics. For example, you might have an ETL job that consist of multiple processes, and perhaps one process uses a database session to gather statistics on a GTT for other sessions to make use of later on. If your database applications are upgraded without taking into account the new default, then workloads relying on shared statistics won't see statistics on GTTs where they had seen them before. Dynamic sampling might kick in where it hadn't before (depending on your optimizer_dynamic_sampling level), and this could result in new or sub-optimal execution plans. So - if you're experiencing SQL execution plan degredations in Oracle Database 12c after an upgrade, check to see if any of the problem queries are associated with GTTs.
What should you do about this? Firstly, you can use the following query to see if GTT preferences match your expectations (you'll need to modify it to include your own application schema names):
select owner, table_name, dbms_stats.get_prefs('GLOBAL_TEMP_TABLE_STATS',owner,table_name) gtt_stats_preference from dba_tables where temporary = 'Y' and owner in ('my_application_owner') and duration = 'SYS$SESSION' order by owner,table_name;
Note that the predicate on the duration column is used to identify GTTs created with "on commit preserve rows".
If you want to be very selective, it is easy to change preferences on individual GTTs using a PL/SQL procedure similar to this. Just adjust the query to select the GTTs you want to change:
declare cursor c1 is select owner, table_name from dba_tables where temporary = 'Y' and duration = 'SYS$SESSION' and owner in ('my_application_owner'); begin for r in c1 loop dbms_stats.set_table_prefs (r.owner,r.table_name, 'GLOBAL_TEMP_TABLE_STATS','SHARED'); end loop; end; /
You can use a bigger and easier switch to set the preference at the global, database and schema-level too. For example:
exec dbms_stats.set_global_prefs('GLOBAL_TEMP_TABLE_STATS','SHARED'); exec dbms_stats.set_schema_prefs('my_application_owner','GLOBAL_TEMP_TABLE_STATS','SHARED');
There is an "upgrade gotcha" you need to be aware of. If you have upgraded to Oracle Database 12c and are using optimizer_features_enable (OFE) set to a pre-220.127.116.11 version (let's say, for the sake of argument, it is '18.104.22.168') then be aware that the Oracle Optimizer will not "see" session private statistics.
You can see this in action if you do something like this:
-- Set dynamic sampling to the default value to help us indentify when stats are missing alter session set optimizer_dynamic_sampling = 2; -- Create a GTT create global temporary table gtt1 (id number(10)) on commit preserve rows; insert into gtt1 values (10); commit; -- Just to make sure you're using the default preference value, "SESSION": exec dbms_stats.set_table_prefs(user,'GTT1','GLOBAL_TEMP_TABLE_STATS','SESSION') -- Gather stats exec dbms_stats.gather_table_stats(user,'gtt1');
Execute a query on the GTT:
set autotrace on select * from gtt1 where id = 1;
The execution plan will include the following comment in the "Note" section:
Global temporary table session private statistics used
If you now do the same with OFE set to 22.214.171.124:
alter session set optimizer_features_enable = '126.96.36.199';set autotrace on select * from gtt1 where id = 1;
You will see the following note in the execution plan, where the use of dynamic sampling indicates that the session-private statistics are not being seen:
dynamic statistics used: dynamic sampling (level=2)
Note also, that if you do this:
exec dbms_stats.set_table_prefs(user,'GTT1','GLOBAL_TEMP_TABLE_STATS','SESSION') alter session set optimizer_features_enable = '188.8.131.52'; exec dbms_stats.gather_table_stats(user,'gtt1');
...then you will gather session private statistics, which will not be seen by queries executed against the GTT if OFE remains set prior to 184.108.40.206.