Oracle Histograms Within PeopleSoft Applications

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:


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:

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:


The number of bands within the histogram of a column can be displayed using the following dictionary views:


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.