Currently Being Moderated

Introduction

In the past days i was involved in an Oracle 12c performance evaluation / certification for a non SAP application, which uses GTTs (global temporary tables) in different modules very regularly. Prior Oracle 12c the statistic handling of GTTs was pretty tricky and can lead to insufficient execution plans very easily. Tom Kyte has written an article about the different possible solutions (e.g dynamic sampling, DBMS_STATS or hinting). In several cases you have to catch a specific point in time with representative data to gather the "best possible" statistics, but even then it is very easy to fail in several cases. Luckily all of this is the past with Oracle 12c. Why? Let's check the official Oracle documentation for that new feature called "Session-Specific Statistics for Global Temporary Tables".

 

Oracle documentation

A global temporary table is a special table that stores intermediate session-private data for a specific duration. The ON COMMIT clause of CREATE GLOBAL TEMPORARY TABLE indicates whether the table is transaction-specific (DELETE ROWS) or session-specific (PRESERVE ROWS). Thus, temporary tables hold intermediate result sets for the duration of either a transaction or a session.

 

When you create a global temporary table, you create a definition that is visible to all sessions. No physical storage is allocated. When a session first puts data into the table, the database allocates storage space. The data in a temporary table is only visible to the current session.

 

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 Release 1 (12.1), you can set the table-level preference GLOBAL_TEMP_TABLE_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.


DBMS_STATS commits changes to session-specific global temporary tables, but not to transaction-specific global temporary tables. In previous releases, running DBMS_STATS.GATHER_TABLE_STATS on a transaction-specific temporary table (ON COMMIT DELETE ROWS) would delete all rows in the table, making the statistics show the table as empty. Starting in Oracle Database 12c Release 1 (12.1), the following procedures do not commit for transaction-specific temporary tables, so that data in these tables is not lost: GATHER_TABLE_STATS, DELETE_TABLE_STATS, DELETE_COLUMN_STATS, DELETE_INDEX_STATS, SET_TABLE_STATS, SET_COLUMN_STATS, SET_INDEX_STATS, GET_TABLE_STATS, GET_COLUMN_STATS, GET_INDEX_STATS

 

Basically the documentation also states the previously discussed main issue ("The database maintained one version of the statistics shared by all sessions, even though data in different sessions could differ") with GTTs and statistics. Let's continue with a demo to show the enhanced behavior between Oracle 11g R2 and Oracle 12c R1.

 

 

Demo

The following demo was run on OEL 6.4 with an Oracle 11g R2 (11.2.0.3.6) and Oracle 12c R1 (12.1.0.1) database.

At first i will demonstrate the old behavior and its issue and then the new feature "session specific statistics for global temporary tables" with Oracle 12c.


Oracle 11g R2 (11.2.0.3.6)

SQL> create global temporary table MYGTT (a number) on commit preserve rows;
















 

SQL> select PREFERENCE_NAME , PREFERENCE_VALUE  from DBA_TAB_STAT_PREFS
where table_name = 'MYGTT';
no rows selected
















 

** Session 1
SQL> insert into MYGTT select rownum from dba_objects where rownum <= 5000;
SQL> commit;
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER,'MYGTT');
SQL> select TABLE_NAME, NUM_ROWS from USER_TAB_STATISTICS where TABLE_NAME = 'MYGTT';
TABLE_NAME             NUM_ROWS
------------------------------ ----------
MYGTT                     5000
















 

** Session 2
SQL> insert into MYGTT select rownum from dba_objects where rownum <= 10;
SQL> commit;
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER,'MYGTT');
SQL> select TABLE_NAME, NUM_ROWS from USER_TAB_STATISTICS where TABLE_NAME = 'MYGTT';
TABLE_NAME             NUM_ROWS
------------------------------ ----------
MYGTT                       10
















 

** Session 1
SQL> select /*+ gather_plan_statistics */ * from MYGTT;

















11gR2_Execution_Plan_Sess1.png

 

** Session 2
SQL> select /*+ gather_plan_statistics */ * from MYGTT;

















11gR2_Execution_Plan_Sess2.png

As you can see the basic table statistics only represent the data pattern of the session who executed the (last) DBMS_STATS call. In consequence the execution plan in the first session has a heavily underestimated cardinality (estimated 10 rows to actual 5000 rows), which can lead to bad execution plans very easily. You also can see that both session share the same child cursor. Let's do exactly the same exercise with Oracle 12c R1 now.

 

Oracle 12c R1 (12.1.0.1)

SQL> create global temporary table MYGTT (a number) on commit preserve rows;














 

SQL> select PREFERENCE_NAME , PREFERENCE_VALUE  from DBA_TAB_STAT_PREFS
where table_name = 'MYGTT';
no rows selected














 

SQL> select DBMS_STATS.GET_PREFS('GLOBAL_TEMP_TABLE_STATS','TESTUSER','MYGTT')
as GLOBAL_TEMP_TABLE_STATS from dual;
GLOBAL_TEMP_TABLE_STATS
----------------------------
SESSION














 

Let's stop shortly at this point here. The official documentation states "... you can set the table-level preference GLOBAL_TEMP_TABLE_STATS to make statistics on a global temporary table shared or session-specific", but it is seems like Oracle forgot to mention that this new feature (session specific statistics) is already the "default". So in consequence session specific statistics are gathered on GTTs by DBMS_STATS, even if you do not set the table preference attribute explicitly. This may lead to a new behavior with GTTs in Oracle 12c very easily.

 

** Session 1
SQL> insert into MYGTT select rownum from dba_objects where rownum <= 5000;
SQL> commit;
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER,'MYGTT');
SQL> select TABLE_NAME, NUM_ROWS, SCOPE from USER_TAB_STATISTICS
where TABLE_NAME = 'MYGTT';
TABLE_NAME             NUM_ROWS SCOPE
------------------------------ ---------- -------
MYGTT                      SHARED
MYGTT                     5000 SESSION














 

** Session 2
SQL> insert into MYGTT select rownum from dba_objects where rownum <= 10;
SQL> commit;
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(USER,'MYGTT');
SQL> select TABLE_NAME, NUM_ROWS, SCOPE from USER_TAB_STATISTICS
where TABLE_NAME = 'MYGTT';
TABLE_NAME             NUM_ROWS SCOPE
------------------------------ ---------- -------
MYGTT                      SHARED
MYGTT                       10 SESSION














 

** Session 1
SQL> select /*+ gather_plan_statistics */ * from MYGTT;














12cR1_Execution_Plan_Sess1.png

 

** Session 2
SQL> select /*+ gather_plan_statistics */ * from MYGTT;














12cR1_Execution_Plan_Sess2.png

 

As you can see the basic table statistics have an additional scope (SESSION in our case) right now. The DBMS_STATS call gathered session specific statistics for the GTT and in consequence the cardinality estimates are correct in both cases. DBMS_XPLAN also prints a separate note, if session specific statistics were used by the CBO. You may also notice that each session has its own child cursor (by using session specific statistics) - so let's check the reason for these child cursors at last.


 

SQL> @http://blog.tanelpoder.com/files/scripts/nonshared2.sql PRINT fkucn6k7cwwgm














12cR1_Child_Cursor_Reason.png

 

 

Summary

Session specific statistics for global temporary tables makes the life easier and can lead to better cardinality and cost calculations by the CBO. It also removes most of the needed work-arounds and hacks prior Oracle 12c R1.

However keep in mind that this feature is enabled by default for GTTs and is used even if you do not explicitly set the table preference. Please be aware of that this feature does not work with the SYS account, if you want to research or test it.

 

If you have any further questions - please feel free to ask or get in contact directly, if you need assistance by troubleshooting Oracle database performance or CBO issues.

 

 

References

Comments

Actions

Filter Blog

By author:
By date:
By tag: