First page Back Continue Last page Graphics
Cursor Sharing Caution
FORCE removes constants
- Optimizer knows less about the query
- Could generate different plan
Still get unnecessary soft parses
EXPLAIN and AUTOTRACE can be wrong
Perhaps consider cursor sharing a short-term fix
Notes:
We learned in the cursor sharing demonstration that, when using FORCE, subsequent queries that share the cursor can use the “wrong” plan, i.e. the second query will use the plan created for the first query executed, regardless of any values. The SIMILAR option, introduced with Oracle9i, was introduced to help fix this problem.
The cursor sharing feature can certainly help reduce hard parses without correcting the application code to make correct use of bind variables. However, as the TKPROF report shows, it does not eliminate unnecessary soft parses.
EXPLAIN and SQL*Plus AUTOTRACE can show a plan that will not be the plan actually used. For example, AUTOTRACE of the query traced in the demonstration shows a plan that was not used when the query was actually run:
Notes for this slide continue on the next page…