8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Session-Private Statistics for Global Temporary Tables in Oracle Database 12c Release 1 (12.1)

In previous releases, statistics gathered for global temporary tables (GTTs) were common to all sessions. If you knew the GTTs would need vastly different statistics for each session, you could avoid statistics and rely on dynamic sampling to provide the relevant information. In Oracle database 12c it is possible to have session-private statistics for global temporary tables.

Controlling Session-Private Statistics
Example

Controlling Session-Private Statistics

Session-private statistics are controlled using the GLOBAL_TEMP_TABLE_STATS global preference in the DBMS_STATS package. By default session-private statistics are enabled, as shown below.

CONN test/test@pdb1

SELECT DBMS_STATS.get_prefs('GLOBAL_TEMP_TABLE_STATS') FROM dual;

DBMS_STATS.GET_PARAM('GLOBAL_TEMP_TABLE_STATS')
----------------------------------------------------------------------------------------------------
SESSION

SQL>

If you wish to set them to shared, you can alter the preference with the following command.

CONN sys@pdb1 AS SYSDBA

BEGIN
  DBMS_STATS.set_global_prefs (
    pname   => 'GLOBAL_TEMP_TABLE_STATS',
    pvalue  => 'SHARED');
END;
/

BEGIN
  DBMS_STATS.set_global_prefs (
    pname   => 'GLOBAL_TEMP_TABLE_STATS',
    pvalue  => 'SESSION');
END;
/

When session-private statistics are enabled, the following behaviour is present.

Statistics gathered are only available to the optimizer in the current session.
If session-private statistics are present, they will be used in preference to the shared statistics.
Gathering statistics will invalidate any related cursors in the current session only.
Statistics are deleted as soon as the session ends.
Pending statistics are not supported for GTTs.

Both shared and session-private statistics are visible in the DBA_TAB_STATISTICS, DBA_IND_STATISTICS, DBA_TAB_HISTOGRAMS, and DBA_TAB_COL_STATISTICS views, with the SCOPE column indicating the type of statistics.

Example

Create a global temporary table (GTT).

CONN test/test@pdb1

DROP TABLE gtt1;

CREATE GLOBAL TEMPORARY TABLE gtt1 (
  id NUMBER,
  description VARCHAR2(20)
)
ON COMMIT PRESERVE ROWS;

Create some shared statistics on the GTT.

CONN sys@pdb1 AS SYSDBA

-- Set the GTT statistics to SHARED.
BEGIN
  DBMS_STATS.set_global_prefs (
    pname   => 'GLOBAL_TEMP_TABLE_STATS',
    pvalue  => 'SHARED');
END;
/

-- Insert some data and gather the shared statistics.
INSERT INTO test.gtt1
SELECT level, 'description'
FROM   dual
CONNECT BY level <= 5;

EXEC DBMS_STATS.gather_table_stats('TEST','GTT1');

-- Display the statistics information and scope.
COLUMN table_name FORMAT A20

SELECT table_name, num_rows, scope
FROM   dba_tab_statistics
WHERE  owner = 'TEST'
AND    table_name = 'GTT1';

TABLE_NAME             NUM_ROWS SCOPE
-------------------- ---------- -------
GTT1                          5 SHARED

SQL>

-- Reset the GTT statistics preference to SESSION.
BEGIN
  DBMS_STATS.set_global_prefs (
    pname   => 'GLOBAL_TEMP_TABLE_STATS',
    pvalue  => 'SESSION');
END;
/

Start a new session, create some session-private statistics and display the available statistics.

CONN test/test@pdb1

INSERT INTO gtt1
SELECT level, 'description'
FROM   dual
CONNECT BY level <= 1000;
COMMIT;

EXEC DBMS_STATS.gather_table_stats('TEST','GTT1');

-- Display the statistics information and scope.
COLUMN table_name FORMAT A20

SELECT table_name, num_rows, scope
FROM   dba_tab_statistics
WHERE  owner = 'TEST'
AND    table_name = 'GTT1';

TABLE_NAME             NUM_ROWS SCOPE
-------------------- ---------- -------
GTT1                          5 SHARED
GTT1                       1000 SESSION

SQL>

Start a new session and display the statistics without gathering any session-private statistics.

CONN test/test@pdb1

-- Display the statistics information and scope.
COLUMN table_name FORMAT A20

SELECT table_name, num_rows, scope
FROM   dba_tab_statistics
WHERE  owner = 'TEST'
AND    table_name = 'GTT1';

TABLE_NAME             NUM_ROWS SCOPE
-------------------- ---------- -------
GTT1                          5 SHARED

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.