Database Reference
In-Depth Information
EODA@ORA11GR2> begin
2 dbms_stats.set_table_stats( ownname => USER,
3 tabname => 'T',
4 numrows => 500,
5 numblks => 7,
6 avgrlen => 100 );
7 end;
8 /
PL/SQL procedure successfully completed.
EODA@ORA11GR2> select table_name, num_rows, blocks, avg_row_len
2 from user_tables
3 where table_name = 'T';
TABLE_NAME NUM_ROWS BLOCKS AVG_ROW_LEN
-------------------- ---------- ---------- -----------
T 500 7 100
Now, the optimizer won't use its best guess—it will use
our
best guess for this information.
Statistics Starting with 12c
The gathering and use of global temporary table statistics vastly improves starting with Oracle 12
c
. Listed next is a
summary of the changes:
•
By default, session-level statistics are generated when gathering statistics for temporary tables.
•
Shared statistics can still be gathered (much like they were in 11
g
), but you must first set the
GLOBAL_TEMP_TABLE_STATS
parameter (of the
DBMS_STATS.SET_TABLE_PREFS
procedure) to
SHARED
.
ON COMMIT DELETE ROWS
, several
DBMS_STATS
procedures
(such as
GATHER_TABLE_STATS
) no longer issue an implicit
COMMIT
; therefore, it's possible to
generate representative statistics for this type of temporary table.
•
For temporary tables defined as
•
For temporary tables defined as
ON COMMIT PRESERVE ROWS
, session-level statistics are
automatically generated for direct-path operations (like CTAS and direct-path
INSERT
statements); this eliminates the need to call
DBMS_STATS
to generate statistics for these specific
operations.
We'll look at each of the prior bullets in more detail, starting with session statistics.
Session Statistics
Prior to 12
c
, the statistics generated for a temporary table were shared among all sessions using the temporary table.
This could lead to less than ideal execution plans, especially if different sessions generated disparate volumes of data
or had varying patterns of data.
Starting with 12
c
, when you generate statistics for a temporary table, the statistics are specific to the session
generating the statistics. This provides the Oracle optimizer with better information to create an execution plan
tailored for the data generated per session. A small example will demonstrate this; first, a temporary table is created:
EODA@ORA12CR1> create global temporary table gt(x number) on commit preserve rows;
Table created.
