X

Insights into Statistics, Query Optimization and the Oracle Optimizer

  • 12c |
    May 24, 2016

Global Temporary Tables and Upgrading to Oracle Database 12c - Don't Get Caught Out

Nigel Bayliss
Product Manager

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-12.1.0.1 version (let's say, for the sake of argument, it is '11.2.0.4') 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 11.2.0.4:

alter session set optimizer_features_enable = '11.2.0.4';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 = '11.2.0.4';
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 12.1.0.1.

Conclusions

  • If you're setting OFE prior to 12.1.0.1 and you have "on commit preserve rows" GTTs, you will probably want to change your GTT table preferences to SHARED so that statistics will be created and seen as expected.
  • If you are upgrading to Oracle Database 12c or developing new functionality on this release, you need to consider whether this change of behavior is relevant to your application. Do you have GTTs created with "on commit preserve rows" and does the application gather statistics on those tables? Has your application been modified to accommodate the new default? If not, you might need to change your GTT table preferences to SHARED.

Join the discussion

Comments ( 4 )
  • Houri Mohamed Tuesday, May 24, 2016

    And if you are using "on commit delete rows" GTTs in a 12c release you need to know that collecting statistics under a GTT table preferences set to SHARED will issue an implicit commit while there is no commit for the default GTT table preference after a call to dbms_stats package:

    https://community.oracle.com/message/13838878#13838878

    Best Regards


  • guest Tuesday, May 24, 2016

    Hi Houri,

    Yes, that's a good point.

    Regards,

    Nigel.


  • Chris Antognini Wednesday, May 25, 2016

    Hi Nigel

    > which will not be seen by queries executed against the GTT

    > if OFE remains set prior to 12.1.0.1

    According to my tests this is NOT the case if OFE is specified through an hint. Is it a bug or an expected behavior?

    Thank you,

    Chris


  • NBayliss Thursday, May 26, 2016

    Hi Chris,

    Definitely a bug. 23421703 has been created.

    Thanks for letting me know.

    Regards,

    Nigel.


Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services