DBA Hub

📋Steps in this guide1/2

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

See how session-private and shared statistics are used for global temporary tables (GTTs) in Oracle Database 12c Release 1.

oracle 12cconfigurationintermediate
by OracleDba
14 views
1

Controlling Session-Private Statistics

Session-private statistics are controlled using the global preference in the package. By default session-private statistics are enabled, as shown below. If you wish to set them to shared, you can alter the preference with the following command. 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 , , , and views, with the column indicating the type of statistics.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
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>

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;
/
2

Example

Create a global temporary table (GTT). Create some shared statistics on the GTT. Start a new session, create some session-private statistics and display the available statistics. Start a new session and display the statistics without gathering any session-private statistics. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
CONN test/test@pdb1

DROP TABLE gtt1;

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

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;
/

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>

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>

Comments (0)

Please to add comments

No comments yet. Be the first to comment!