Fun with global temporary tables in Oracle 12c

Few months ago I wrote a post about 12c session specific statistics for global temporary tables (link). Long awaited feature no matter what.

Recently I had some discussions on the same subject with members of my team.

One interesting observation was the behavior of transaction specific GTTs with session specific statistics enabled. What attracted our interest was the fact that data in global temporary tables is not deleted after DBMS_STATS package is invoked.

Prior to 12c, a call to DBMS_STATS will result with an implicit commit. This would wipe out the content of a transaction specific global temporary table.

I’ll digress here a bit. Yes, I know, who would call DBMS_STATS to collect statistics on a transaction specific GTT knowing the data in the table will be lost. Well, things change a bit in 12c.

In Oracle 12c, no implicit commit is invoked when DBMS_STATS.GATHER_TABLE_STATS is invoked on a transaction specific with session specific statistics enabled thus letting users take advantage of session specific statistics for this type of GTTs.

This behavior is documented in Oracle documentation.

I’ll try to put some more light on this behavior through couple of examples:

For this purpose I’ll start with three tables. T1 and T2 are transaction specific temporary tables. T3 is a regular table. By default, in 12c, session specific statistics are used.

CREATE GLOBAL TEMPORARY TABLE t1 (id NUMBER);

CREATE GLOBAL TEMPORARY TABLE t2 (id NUMBER);

CREATE TABLE t3 (id NUMBER);

Scenario #1 – Insert 5 rows to each of the three tables and observe the state of the data after DBMS_STATS is invoked on a transaction specific GTT.

SQL> INSERT INTO t1 (SELECT rownum FROM dual CONNECT BY rownum<=5);
5 rows created.

SQL> INSERT INTO t2 (SELECT rownum FROM dual CONNECT BY rownum<=5);
5 rows created.

SQL> INSERT INTO t3 (SELECT rownum FROM dual CONNECT BY rownum<=5);
5 rows created.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(user,'T1');
PL/SQL procedure successfully completed.

SQL> SELECT count(1) FROM t1;
  COUNT(1)
----------
  5

As you can see the data in T1 is still present. Furthermore if you open another session you can also see that T3 has no rows. This means commit was not invoked when session specific statistics were collected for T1.

Scenario 2# Insert 5 rows in each of the three tables and collect statistics only on the regular table, T3.

SQL> INSERT INTO t1 (SELECT rownum FROM dual CONNECT BY rownum<=5);
5 rows created.

SQL> INSERT INTO t2 (SELECT rownum FROM dual CONNECT BY rownum<=5);
5 rows created.

SQL> INSERT INTO t3 (SELECT rownum FROM dual CONNECT BY rownum<=5);
5 rows created.

SQL> exec DBMS_STATS.GATHER_TABLE_STATS(user,'T3');
PL/SQL procedure successfully completed.

SQL> SELECT count(1) FROM t1;
  COUNT(1)
----------
  0

As you can see in this scenario implicit commit was invoked which resulted with data in T1 being purged.

Hope this helps … :-)

Cheers!