Performance: Full Tablescans on OrderLine ChainedFromOrderHeaderKey > Histograms, ANALYZE DerivedFromOrderHeaderKey BadSQLs

Resolving The Problem

Solution updated per 7.3 Performance Management Guide -
Database Management System 6.3.4.1.4 Columns with High Skew or Low Cardinality Columns, such as enterprise_key in the yfs_order_header, can exhibit high skew - for example, there could be many orders for one enterprise and a few orders for another enterprise. Columns such as derived_from_order_header_key in the yfs_order_header table could have low cardinality because they only contain spaces.

Queries, such as the following,
select *
from yfs_order_header
where derived_from_order_header_key = ?2003012412213801928344?;
against these columns can result in a table scan even if that column is indexed.

From the optimizer's perspective, the queries against these columns either will return a small or a very large result set. To err on the side of caution, the optimizer chooses a table scan over an index range scan. You can get the optimizer to choose a more optimal access plan by creating histograms.
To do that, issue the following command:

exec dbms_stats.gather_schema_stats (ownname => 'YANTRA', -
estimate_percent => dbms_stats.auto_sample_size, -
method_opt=>'for all indexed columns size skewonly', -
cascade=>true);

In some cases Customers have reported the above setting not working when they have ONLY Blanks in this table.
They have found that Oracle's recommendation below [Note: 1031826.6] seems to work.

SQL> exec dbms_stats.gather_table_stats('YANTRA', 'YFS_ORDER_LINE',
method_opt => 'FOR COLUMNS derived_from_order_header_key size 10');



Implementation
Yantra used to previously recommend the method_opt with the auto parameter to let Oracle decide whether histograms are to be created based on the column's data distribution and the way the columns are being used by the application. We have since corrected this to skew only, so that Oracle is forced to create histogram based solely on data distribution.