Recently I faced with the problem that some (theoretically irrelevant) formal changes in the code of a function (even adding or removing a space character to or from the code) can greatly affect the performance of the function. (see my previous questions here and here).
The mystery was solved by Jon Heller as
If adding spaces to the code changes performance, this is likely a plan management issue. Many Oracle tuning tools operate on the SQL_ID, which is like an MD5 hash of the SQL text. So if you change a single character of the SQL text, the optimizer treats the code like a brand new statement. Any plan management fixes, like a SQL profile, or plan outline, will not be applied to the new statement. Maybe a DBA tuned an old statement with an /*+ INDEX... */ hint, but that hint isn't carried over to the new statement. Compare the Note sections in the DBMS_XPLAN output.
and as
A space in a SQL statement would change the SQL_ID, which could cause the optimizer to no longer match the SQL statement with plan management features like profiles, outlines, baselines (possibly - they're supposed to be able to avoid this problem in some cases), patches, advanced rewrites, etc.
So the only question I have left is how can I get rid of the stuck bad execution plans? How can I "clean" Oracle from them?
This worked for me:
alter system flush shared_pool;