Shared Statistics-Database Tables

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. 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:
SQL> create global temporary table gt(x number) on commit preserve rows; Table created.
SQL> insert into gt select user_id from all_users; 51 rows created.
Now set the GLOBAL_TEMP_TABLE_STATS preference to SHARED:
SQL> exec dbms_stats.set_table_prefs(user, – > ‘GT’,’GLOBAL_TEMP_TABLE_STATS’,’SHARED’);

Next, generate statistics for the temporary table:
SQL> exec dbms_stats.gather_table_stats( user, ‘GT’ );
We can verify that shared statistics have been generated by executing the following query:
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 51 27-JUN-21 SHARED

Shared statistics for a global temporary table persist until they are explicitly removed.
You can remove shared statistics as follows:
SQL> exec dbms_stats.delete_table_stats( user, ‘GT’ );

We can verify that the shared statistics have been removed by running the following query:
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 Statistics for ON COMMIT DELETE ROWS

As shown earlier, when running procedures such as GATHER_TABLE_STATS, there is an implicit COMMIT that takes place. Therefore, when generating statistics for temporary tables defined as ON COMMIT DELETE ROWS, the statistics gathered reflect those of a table with zero rows in it (the statistics in this case are useless because you need the statistics to reflect the data within the temporary table before it is removed by a COMMIT).

Several of the procedures in DBMS_STATS (such as GATHER_TABLE_STATS) do not issue an implicit COMMIT after gathering statistics for temporary tables defined as ON COMMIT DELETE ROWS. This means it is possible to gather representative statistics for this type of temporary table. A simple example will demonstrate this concept; first, create a temporary table with ON COMMIT DELETE ROWS:
SQL> create global temporary table gt(x number) on commit delete 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 schema:
SQL> exec dbms_stats.gather_table_stats( user, ‘GT’ );PL/SQL procedure successfully completed.

A quick count will verify the rows still exist in the GT table:
SQL> select count() from gt;COUNT()———-51

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 SCOPE
GT SHARED
GT 51 27-JUN-21 SESSION

This allows you to generate useful statistics for temporary tables where you desire the rows to be deleted after each transaction.

Note The following procedures of DBMS_STATS do not issue a COMMIT as part of gathering temporary table statistics for tables created with ON COMMIT DELETE ROWS: 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. The prior procedures do issue an implicit COMMIT for temporary tables defined as ON COMMIT PRESERVE ROWS.