DB12c - Session-private statistics for global temporary tables

In previous releases, the database did not maintain statistics for global temporary tables and non-global temporary tables differently.

The database maintained one version of the statistics shared by all sessions, even though data in different sessions could differ.

Starting in Oracle Database 12c, you can set the table-level preference GLOBAL_TEMP_STATS to make statistics on a global temporary table shared or session-specific. If set to session-specific, then you can gather statistics for a global temporary table in one session, and then use the statistics for this session only. Meanwhile, users can continue to maintain a shared version of the statistics.

During optimization, the optimizer first checks whether a global temporary table has session-specific statistics. If yes, the optimizer uses them. Otherwise, the optimizer uses shared statistics if they exist.

SQL> select banner from v$version where rownum=1;

BANNER

--------------------------------------------------------------------------------

Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

Session 1

SQL> create global temporary table new_gtt on commit delete rows

  2  as

  3  select * from all_objects where 1=2;

Table created.

SQL> insert into new_gtt select * from all_objects;

77381 rows created.

SQL> SELECT DBMS_STATS.GET_PREFS( 'GLOBAL_TEMP_TABLE_STATS','HORIA','NEW_GTT')

  2  FROM DUAL;

DBMS_STATS.GET_PREFS('GLOBAL_TEMP_TABLE_STATS','HORIA','NEW_GTT')

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

-------------------------------------------------------------------------------

SESSION

SQL>

SQL> exec dbms_stats.gather_table_Stats('HORIA','NEW_GTT',METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO')

PL/SQL procedure successfully completed.

SQL> select /*+ gather_plan_statistics*/ count(*) from new_gtt;

  COUNT(*)

----------

     77381

SQL> select * from table (dbms_xplan.display_cursor(format=>'ALLSTATS LAST'));

PLAN_TABLE_OUTPUT

------------------------------------------------------------------------------------------------

------------------------------------------------------------------------------------------------

SQL_ID  8432nj7acjwpx, child number 0

-------------------------------------

select /*+ gather_plan_statistics*/ count(*) from new_gtt

Plan hash value: 1213234781

----------------------------------------------------------------------------------------

| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |         |      1 |        |      1 |00:00:00.01 |    1313 |

|   1 |  SORT AGGREGATE    |         |      1 |      1 |      1 |00:00:00.01 |    1313 |

|   2 |   TABLE ACCESS FULL| NEW_GTT |      1 |  77381 |  77381 |00:00:00.01 |    1313 |

----------------------------------------------------------------------------------------

Note

-----

   - Global temporary table session private statistics used

18 rows selected.

We can see that the session private statistics were used.

When querying the user_tab_Statistics we can see the entry specific to this session, showing correct number of rows.

SQL> select table_name, num_rows, scope

  2  from user_tab_statistics

  3  where table_name ='NEW_GTT';

TABLE_NAME                  NUM_ROWS SCOPE

------------------------- ---------- -------

NEW_GTT                              SHARED

NEW_GTT                        77384 SESSION

Session 2

SQL> insert into new_gtt select * from all_objects where owner <> 'SYS';

42081 rows created.

SQL>

SQL> exec dbms_stats.gather_table_Stats('HORIA','NEW_GTT',METHOD_OPT=>'FOR ALL COLUMNS SIZE AUTO');

PL/SQL procedure successfully completed.

SQL> select /*+ gather_plan_statistics*/ count(*) from new_gtt;

  COUNT(*)

----------

     42081

SQL> select * from table (dbms_xplan.display_cursor(format=>'ALLSTATS LAST'))

  2  ;

PLAN_TABLE_OUTPUT

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

SQL_ID  8432nj7acjwpx, child number 1

-------------------------------------

select /*+ gather_plan_statistics*/ count(*) from new_gtt

Plan hash value: 1213234781

----------------------------------------------------------------------------------------

| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |

----------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |         |      1 |        |      1 |00:00:00.01 |     722 |

|   1 |  SORT AGGREGATE    |         |      1 |      1 |      1 |00:00:00.01 |     722 |

|   2 |   TABLE ACCESS FULL| NEW_GTT |      1 |  42081 |  42081 |00:00:00.01 |     722 |

----------------------------------------------------------------------------------------

Note

-----

   - Global temporary table session private statistics used

18 rows selected.

SQL>

We can see that this second session did not share the cursor it used new one.

The optimizer used session-specific statistics.

SQL> select table_name, num_rows, scope

  2  from user_tab_statistics

  3  where table_name ='NEW_GTT';

TABLE_NAME                  NUM_ROWS SCOPE

------------------------- ---------- -------

NEW_GTT                              SHARED

NEW_GTT                        42084 SESSION

Reference

Oracle® Database

SQL Tuning Guide

12c Release 1 (12.1)

E15858-15