Question: What
does the new 12c global_temp_table_stats do? When
would I want to use global_temp_table_stats on a global
temporary table?
Answer: Prior to 12c, Oracle
global temporary tables (GTT) shared a common set of CBO
statistics. New in 12c we see the
global_temp_table_stats procedure that allows coders to
collect optimizer statistics on individual GTT's.
In most databases the GTT does not have significantly
different characteristics to warrant separate statistics,
but for those rare cases when the GTT is significantly
different from the "norm", the ability to analyze statistics
will improve the execution plans of the SQL that reads the
GTT data.
The values can be either SESSION or SHARED
SELECT DBMS_STATS.get_prefs('GLOBAL_TEMP_TABLE_STATS') FROM
dual;
DBMS_STATS.GET_PARAM('GLOBAL_TEMP_TABLE_STATS')
----------------------------------------------------------------
SESSION
Here we set the global_temp_table_stats to SHARED, to
allow for individual CBO statistics on specific GTT's:
BEGIN
DBMS_STATS.set_global_prefs
(
pname => 'GLOBAL_TEMP_TABLE_STATS',
pvalue =>
'SHARED');
END;
Rampant author Dr. Tim Hall notes that when
session-private statistics are enabled, the following
behavior is present.
- Statistics gathered on a GTT 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.
As we see the ability to collect CBO statistics on a
individual GTT is rarely needed (to improve SQL execution
plans) but this new 12c feature is very useful in those
cases where the presence of GTT statistics will influence an
execution plan.
There is an "upgrade gotcha" you need to be aware of. If
you have upgraded to Oracle Database 12c and are using
optimizer_features_enable set to a pre-12.1.0.1 version
(let's say, for the sake of argument, it is '11.2.0.4') then
be aware that the Oracle Optimizer will not "see" session
private statistics.
|
|
|
|
|
Oracle Training from Don Burleson
The best on site
"Oracle
training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop!
|
|
|
|
| |
|
Burleson is the American Team
Note:
This Oracle
documentation was created as a support and Oracle training reference for use by our
DBA performance tuning consulting professionals.
Feel free to ask questions on our
Oracle forum.
Verify
experience!
Anyone
considering using the services of an Oracle support expert should
independently investigate their credentials and experience, and not rely on
advertisements and self-proclaimed expertise. All legitimate Oracle experts
publish
their Oracle
qualifications.
Errata?
Oracle technology is changing and we
strive to update our BC Oracle support information. If you find an error
or have a suggestion for improving our content, we would appreciate your
feedback. Just
e-mail:
 and include the URL for the page.
Copyright © 1996 - 2020
All rights reserved by
Burleson
Oracle ®
is the registered trademark of Oracle Corporation.
|
|
