Siebel, ClarifyCRM or PeopleSoft client intermittently use slower access plan in Oracle

It is known that intermittently Siebel, ClarifyCRM, or PeopleSoft client will use slower access plan to access Oracle database while the tables and indexes statistics do not change.

This is due to the query is using bind variable and Oracle engine peeked the value the decided to poor plan. It will cached the access plan, and continue to use the plan.

To workaround this problem:
1. alter system flush shared_pool;  This will force database engine to re-evaluate the access plan
2. Use query outline to tell the query which access plan to use