Plan Regression After a Histogram Appears
An order-entry query uses bind variables and normally returns in 40ms. After a nightly statistics gather introduced a histogram on a heavily skewed STATUS column, one customer's execution takes 8 seconds. v$SQL shows two child cursors for the same SQL_ID with different plans; the slow child uses a nested loop that reads 1.4 million rows from ORDERS. The application connects through a pool that sets CURSOR_SHARING=FORCE at the session level. Adaptive plans are enabled with the default settings. A DBA proposes adding an INDEX hint to the query and re-gathering statistics with CASCADE=>FALSE to avoid future surprises. The query is called thousands of times per hour and is embedded in a vendor package that cannot be modified before the morning peak.
1. Diagnose the plan regression. Why are there two child cursors and why did performance collapse for one customer?
Read the worked answer
The query is bind-sensitive. The STATUS column is skewed, and the new histogram gives the optimizer information it did not have before. With bind peeking, the optimizer can generate a plan based on the first peeked value. If that value is rare, a nested loop is reasonable; if it is common, the same plan is disastrous. Adaptive cursor sharing is designed to create multiple child cursors for different bind values, which is exactly what v$SQL shows.
But there is a complication: CURSOR_SHARING=FORCE converts literals into system-generated binds. Histograms are generally not usable for system binds, so the optimizer may fall back to default cardinality estimates. At the same time, the existing child cursors may have been built from peeked values before the conversion, producing inconsistent plans across the pool. The slow child reads 1.4 million rows through a nested loop, which is consistent with a cardinality misestimate.
Diagnose: compare DBMS_XPLAN.DISPLAY_CURSOR for both child cursors, check V$SQL_SHARED_CURSOR for the reason for multiple children, and inspect the predicate values and bind capture. Confirm whether the histogram is being used or ignored under forced cursor sharing.
2. What immediate action stabilizes the query before the morning peak, given that the vendor SQL cannot be changed?
Read the worked answer
Use SQL plan management or a SQL patch; do not edit the vendor package under time pressure.
Best immediate action: capture the good plan as a SQL plan baseline. Identify the good child cursor's PLAN_HASH_VALUE, then load it with DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE for that SQL_ID and plan hash. Set OPTIMIZER_USE_SQL_PLAN_BASELINES=TRUE if it is not already enabled. The optimizer will then prefer the accepted plan while still allowing a better plan to be verified later.
If a baseline cannot be loaded quickly: create a SQL patch with DBMS_SQLDIAG.CREATE_SQL_PATCH that adds the necessary hint or fixes the estimate. This is a temporary, targeted fix that does not require changing the application.
Validate: run the query with the problematic bind value and confirm it uses the accepted plan via DBMS_XPLAN.DISPLAY_CURSOR. Monitor V$SQL for new child cursors. Document the patch or baseline so it is reviewed after the peak, not left as permanent hidden tuning.
3. Evaluate the DBA's proposal to add an INDEX hint and re-gather statistics with CASCADE=>FALSE.
Read the worked answer
Both parts of the proposal are risky.
The INDEX hint hard-codes an access path for one plan. If data distribution changes, the hinted index may become the wrong choice, and the optimizer cannot adapt. It also does not address the root cause, which is cardinality estimation under bind peeking and forced cursor sharing. A hint may work today and fail next month.
CASCADE=>FALSE gathers statistics for the table but leaves index statistics stale. Stale index statistics can cause worse plans, especially for indexes whose clustering or leaf block distribution has changed. Avoiding histograms is not the same as avoiding statistics; the fix is to control histogram creation deliberately.
Better approach: keep automatic statistics gathering, but set the histogram method to SIZE AUTO only where needed, or use DBMS_STATS.SET_COLUMN_STATS/SET_TABLE_PREFS to prevent histograms on columns that cause instability. Consider disabling forced cursor sharing for this pool if the application can be changed, because FORCE undermines histogram use. Use SQL plan baselines to preserve known-good plans and test statistics changes in a staging environment before they reach production.