Database Reference
In-Depth Information
done for “regular” tables. As a result, most of the time and independently of how the object statistics are gathered,
no object statistics are available for global temporary tables. The reason is twofold. First, the
dbms_stats
package
executes a
COMMIT
at the beginning of the processing, and therefore, temporary tables created with the
on commit
delete rows
option (which is the default) are always empty. Second, if the gathering, as usually happens, takes place
in a job like the default gathering job, the global temporary tables are also empty. In general, the only way to get
meaningful object statistics is to manually set them. But even if you set them manually, it might not be possible to find
a set of object statistics that's good for everyone. In fact, every session can store a completely different amount of data
in those tables.
Finally, version 12.1 introduces a feature to handle global temporary tables correctly. The idea is that you can
choose between
shared statistics
(the only kind available in versions up to and including 11.2) and
session statistic
s.
If session statistics are used (which is the default for global temporary tables), every session can gather a set of object
statistics that won't be visible to other sessions. The gathering itself, as usual, is carried out with the
gather_table_stats
procedure of the
dbms_stats
package. This means that to take advantage of this feature, applications have to be
modified to include a call to the
gather_table_stats
procedure just after loading the global temporary table. Note
that to make this feature work, the
COMMIT
executed at the beginning of the processing by the
dbms_stats
package was
removed. Here's an example (based on the
gtt.sql
script) that illustrates how it works:
SQL> CREATE GLOBAL TEMPORARY TABLE t (id NUMBER, pad VARCHAR2(1000));
SQL> INSERT INTO t SELECT rownum, rpad('*',1000,'*') FROM dual CONNECT BY level <= 1000;
SQL> execute dbms_stats.gather_table_stats(ownname => user, tabname => 't')
SQL> SELECT num_rows, blocks, avg_row_len, scope
2 FROM user_tab_statistics
3 WHERE table_name = 'T';
NUM_ROWS BLOCKS AVG_ROW_LEN SCOPE
-------- ---------- ----------- -------
SHARED
1000 147 1005
SESSION
SQL> SELECT count(*)
2 FROM t
3 WHERE id BETWEEN 10 AND 100;
COUNT(*)
----------
91
SQL> SELECT * FROM table(dbms_xplan.display_cursor);
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 42 (100)| |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
|* 2 | TABLE ACCESS FULL| T | 92 | 368 | 42 (0)| 00:00:01 |
---------------------------------------------------------------------------
