Global Temporary Table Statistics-Database Tables

The gathering and use of global temporary table statistics is listed next:

•\ By default, session-level statistics are generated when gathering statistics for temporary tables.

•\ Shared statistics can still be gathered, but you must first set the GLOBAL_TEMP_TABLE_STATS parameter (of the DBMS_STATS.SET_TABLE_PREFS procedure) to SHARED.

•\ For temporary tables defined as ON COMMIT DELETE ROWS, several DBMS_STATS procedures (such as GATHER_TABLE_STATS) no longer issue an implicit COMMIT; therefore, it’s possible to generate representative statistics for this type of temporary table.

•\ For temporary tables defined as ON COMMIT PRESERVE ROWS, session-­ level statistics are automatically generated for direct path operations (like CTAS and direct path INSERT statements); this eliminates the need to call DBMS_STATS to generate statistics for these specific operations.

We’ll look at each of the prior bullets in more detail, starting with session statistics.

Session Statistics

When you generate statistics for a temporary table, the statistics are specific to the session generating the statistics. This provides the Oracle optimizer with better information to create an execution plan tailored for the data generated per session. A small example will demonstrate this; first, a temporary table is created:

$ sqlplus eoda/foo@PDB1
SQL> create global temporary table gt(x number) on commit preserve rows; Table created.
Next, insert some data:
SQL> insert into gt select user_id from all_users; 51 rows created.

Now generate statistics for the table:
SQL> exec dbms_stats.gather_table_stats( user, ‘GT’ );PL/SQL procedure successfully completed.
We can verify the existence of session-level statistics by querying
USER_TAB_STATISTICS:
SQL> select table_name, num_rows, last_analyzed, scope from user_tab_statistics
where table_name like ‘GT’;TABLE_NAME NUM_ROWS LAST_ANALYZED SCOPEGT SHARED
GT 51 27-JUN-21 SESSION

We can further verify the optimizer’s awareness of session private statistics via autotrace:
SQL> set autotrace on;
SQL> select count(*) from gt;

Near the bottom of the output is this optimizer note:
Note—— Global temporary table session private statistics used
Keep in mind that session-level statistics are only valid for the duration of the session. If you disconnect and reconnect, the statistics are gone:
SQL> disconnect
SQL> connect eoda/foo@PDB1

Rerunning the query showing the existence of statistics shows that no session statistics exist now:
SQL> select table_name, num_rows, last_analyzed, scope from user_tab_statistics where table_name like ‘GT’;TABLE_NAME NUM_ROWS LAST_ANALYZED SCOPE
GT SHARED

Note If session-level statistics exist when querying a temporary table, the optimizer will use those. If no session-level statistics exist, then the optimizer will check if shared statistics exist, and if so, use those. If no statistics exist, the optimizer will use dynamic statistics (prior to 12c, this was known as dynamic sampling).