A common problem often encountered in application development is the lack of bind variables; thus requiring additional cpu resources due to the continual hard parsing of statements. The advantages of bind variables have been discussed by many experts over the years. An excellent summary by Chris Saxon can be found here.
The point of this article is not to rehash those excellent points or to try to convince you to use bind variables. Rather, I acknowledge in many cases a DBA or developer may inherit a poorly written application either from prior developers or maybe through purchase from a vendor. In such cases it might not be feasible or permissible to alter the application design and implementation to use binds.
At this point I’d say the application has shot itself in the foot.
In these cases, Oracle provides a workaround in the form of the CURSOR_SHARING parameter. When this is set to FORCE, either at the system or session level, it will cause all of the value-literals to be replaced with bind variables before completing the plan generation and execution.
At this point, I’d say the application has a band-aid on the wound.
This is not an ideal situation; but not uncommon by any means and many applications continue to perform adequately under these circumstances.
However, it can get worse. I have encountered multiple applications where the developers embedded dynamic comments within their SQL statements. That is, in addition to the dynamic sql embedding value literals in each generated statement, there are also distinct comments generated for each statement as well. In some cases these are timestamps. At other times, I’ve seen the intended values embedded that are then forced to binds by the CURSOR_SHARING. My assumption in this latter case is it was an attempt to compensate for the CURSOR_SHARING when viewing the statements in v$sql or AWR reports. If the literals were still present, it wouldn’t be necessary to include a comment because the value itself would still be there.
At this point, I’d say the application has now reloaded.
I say this because these comments now negate the potential benefit of the forced cursor sharing. Each unique comment causes the SQL statement as a whole to be unique, thus forcing a full re-parse even if the executable portion is identical after the binds are implemented.
This is sort of a worst case scenario because using binds can hide some metadata information the optimizer could use to make better decisions. The time and resources saved by reduced parsing usually more than compensate for these possible benefits. But, using CURSOR_SHARING=FORCE along with unique comments that preclude sharing causes the negation of both the benefits of metadata and the benefits of cursor sharing.
Hopefully anyone reading will avoid this situation and if at all feasible do try to use binds where they are appropriate.
But, if you must shoot yourself in the foot, please don’t reload.