Histograms
provide improved selectivity estimates in the presence of data skew, resulting
in optimal execution plans with non-uniform data distributions. A histogram
partitions the values of the column into bands so that all column values in a
band fall within the same range. CBO uses data within the histograms to get
accurate estimates of the distribution of column data.
Oracle uses
height-balanced histograms or frequency-based histograms based on the number of
distinct values and the number of bands.
Candidate Columns for Histograms Within PeopleSoft Applications
Histograms can
affect performance and should be used only when they substantially improve
query plans. In general, histograms should be created for:
• Columns that are frequently used in
WHERE clauses of queries.
• A highly skewed data distribution.
• A literal value that is provided
instead of using a bind variable for the given column in the WHERE clause.
• Columns that are part of indexes.
Because the
typical PeopleSoft Application uses SQL with binds, you should TURN OFF
histogram to get an optimal execution plan for PeopleSoft applications.
This example is
to generate statistics on a table without a histogram:
Execute
sys.DBMS_STATS.GATHER_TABLE_STATS ('PSFT', ‘PS_JOB’, ESTIMATE_PERCENT=>NULL,
degree=>8,method_opt=> 'FOR ALL INDEXED COLUMNS SIZE 1’);
This example is
to generate statistics at a schema level without a histogram:
Execute
sys.DBMS_STATS.gather_schema_stats (ownname => 'PSFT',
ESTIMATE_PERCENT=>NULL,
degree=>8,method_opt=>
'FOR ALL INDEXED COLUMNS SIZE 1');
Histograms are
persistent objects, so there are maintenance and storage costs for using them.
For uniformly
distributed data, the CBO can make fairly accurate estimates about the cost of
executing a particular statement without the use of histograms. Histograms,
like all other optimizer statistics, are static and only change when new
statistics are generated with the “SIZE” option within the DBMS_STATS
procedures.
Histograms are
not useful for columns with the following characteristics:
• Column data is uniformly distributed.
• Column is not used in WHERE clauses of
queries.
• Column is unique.
• Column is used in a WHERE clause but
contains a bind variable instead of a literal.
Note: Bind Peeking (BP) was designed to fix
the last bullet point above.
Columns such as PROCESS_INSTANCE,
ORD_STATUS are likely candidates that benefit from histograms.
This example is
of using update statistics to make the optimizer use a composite index with PROCESS_INSTANCE:
Execute
.DBMS_STATS.GATHER_TABLE_STATS ('PSFT', 'PS_JRNL_LN',ESTIMATE_PERCENT=>NULL,
degree=>8,method_opt=>
'FOR COLUMNS SIZE 10 PROCESS_INSTANCE');
Viewing Histograms
Information
about whether a table contains histograms can be displayed using the following
dictionary views:
• USER_HISTOGRAMS
• ALL_HISTOGRAMS
• DBA_HISTOGRAMS
The number of bands within the histogram of a column can be displayed
using the following dictionary views:
• USER_TAB_COLUMNS
• ALL_TAB_COLUMNS
• DBA_TAB_COLUMNS
Note: In general, you
should not generate histogram statistics on PeopleSoft tables. To deactivate
histogram generation, use “METHOD_OPT” of “… SIZE 1”.
Bind Peeking Behavior Changed with Adaptive Cursor Sharing
A common problem
with previous releases of the database was that when bind variables were used,
the initial plan could be suboptimal because of:
• Future values used in future executions
share the initial plan.
• The first set of binds used may not be
representative of the majority of executions and, therefore, that initial plan
could be considered bad in the context of what is best for most executions.
Oracle 11G
introduces the concept of Adaptive Cursor Sharing. The idea now is to no longer
blindly share plans but to do so only if you believe the bind values will not
degrade a currently stored execution plan. Adaptive Cursor Sharing will
instruct Oracle to peek all binds instead of just the first set of binds and
then select the plan that is optimal for each bind value set.
This is how it
works. The first execution of a SQL statement will cause a hard parse. If a
histogram exists, then bind peeking is enabled and the cursor uses bind values
to estimate the selectivity of each column value and the SQL will be marked as
bind sensitive. In subsequent executions of the SQL, Oracle will peek at its
bind values again and compute the selectivity. If a different execution plan
tresults, then the SQL will be marked as bind aware.
In summary,
changes introduced by Adaptive Cursor Sharing are:
• Share the plan when binds values are
equivalent.
• Plans are marked with selectivity
range.
• If current bind values fall within
range Oracle uses the same plan.
• Create a new plan if binds are not
equivalent, generating a new plan with a different selectivity range.
• This behavior is controlled by database
parameter _optim_peek_user_binds; by default, it is enabled.
• The cursor is being monitored by two
new columns added to V$SQL:
• IS_BIND_SENSITIVE - Optimizer believes the plan may
depend on the value of bind.
• IS_BIND_AWARE - Multiple execution plans exist for
this statement.
