Database Tables - Expert Oracle Database Architecture - page 424

■ 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 12 c , this was known as dynamic sampling ).

Note

Shared Statistics
As shown in the previous section, when you generate statistics for a temporary table, the statistics are visible only
to the session that generated the statistics; this is by default in Oracle 12 c . If you require multiple sessions to share
the same statistics for a temporary table, you must first use the DBMS_STATS.SET_TABLE_STATS procedure to set the
GLOBAL_TEMP_TABLE_STATS preference to SHARED (the default for this preference is SESSION ). To demonstrate this, let's
create a temporary table and insert some data:

EODA@ORA12CR1> create global temporary table gt(x number) on commit preserve rows;
Table created.

EODA@ORA12CR1> insert into gt select user_id from all_users;
51 rows created.

Now set the GLOBAL_TEMP_TABLE_STATS preference to SHARED :

EODA@ORA12CR1> exec dbms_stats.set_table_prefs(user, -
> 'GT','GLOBAL_TEMP_TABLE_STATS','SHARED');

Next, generate statistics for the temporary table:

EODA@ORA12CR1> exec dbms_stats.gather_table_stats( user, 'GT' );

We can verify that shared statistics have been generated by executing following query:

EODA@ORA12CR1> select table_name, num_rows, last_analyzed, scope
2 from user_tab_statistics
3 where table_name like 'GT';

TABLE_NAME NUM_ROWS LAST_ANALYZED SCOPE
--------------- ---------- --------------- -------
GT 51 18-JAN-14 SHARED

Shared statistics for a global temporary table persist until they are explicitly removed. You can remove shared
statistics as follows:

EODA@ORA12CR1> exec dbms_stats.delete_table_stats( user, 'GT' );

We can verify that the shared statistics have been removed by running the following query:

EODA@ORA12CR1> select table_name, num_rows, last_analyzed, scope
2 from user_tab_statistics
3 where table_name like 'GT';