What you are seeing is the result of Oracle automatically transforming SQL so it becomes more shareable. Oracle calls this feature cursor sharing. The related instance parameter, cursor_sharing, can take three options and can be altered at both the session and system level. With a value of exact, no transformation occurs. With a value of similar, Oracle looks for bind variables and transform them in, what I call, a kinder and gentler fashion. When set to force, Oracle transforms any and every literal value into a bind variable.

If you ask a group of performance analysts about their experiences with cursor sharing, you'll immediately get a seemingly conflicting and passionate discussion. Some, like myself, have had wonderful experiences with the similar option; others have had all sorts of problems. Some who have used the force option have seen their application SQL so aggressively transformed that the SQL result set was actually different.3 For example, instead of returning ten rows, the SQL returned two rows, effectively breaking the application!

Obviously, you need to talk to your colleagues, check with Oracle support, and test the various options in your particular environment. If physically altering the SQL to use bind variables is not possible or extremely painful, cursor sharing can work marvelously. But you must be very diligent in your testing before your use the option in a production environment.