HI Experts,
i am on Oracle 11.2.0.4 on Linux. I had a very tricky issue with a long 200+ line sql. The sql ideally should be simplified but that can't be done in short term -this is a given fact. The sql had a strange issue that when we run it from sqlplus it came back in less than a second and when we run from App. it took anywhere from 15 to 20 seconds. In the short term, so I decided to use SPM. I found good and bad plans and fixed the good plan and disabled the bad plans. (by using procedures like: DBMS_SPM.load_plans_from_cursor_cache, DBMS_SPM.alter_sql_plan_baseline and setting enabled attribute to NO for the bad plans, and setting the attribute 'fixed' to YES for the good plan etc.)
I wanted to ask there are there any known pitfalls with SPM - like even if the good plan is fixed it will not get used in some situations etc.? Has someone come across with a bad experience trying to use SPM and finding it was not worknig out? Bythe way, in our tesing (it has been just half a day though) we are finding SPM to work thus far.
Thanks a lot!