Need advice on tuning slippery queries

On several occasions, we have had a "bad" plan generated by the CBO (I know this is old hat for you veterans in the field *grin*). A few in particular will demonstrate these symptoms:
* At some point in time (time A), the query is freshly parsed
(hard parsed) and a "less-than-optimal" plan is generated
* At some other point B (anytime > A), all other queries are soft
parsed (libcache hit) with the same grisly plan
* Further later on after B, the users finally get fed up and call
in the problem. DBAs get the call but cannot reproduce the problem in Test. Altering a test query in Production with a dummy comment (to change the signature), the query parses freshly (libcache miss) with a "good" plan.
* Lastly, the shared pool is flushed and the original query parses
once again, but this time with a "good" plan

I have at least two different SRs open on cases like this, and Oracle Support wants me to send them a test case, or generate 10053 and 10046 traces on a "bad" query. How do I do that? Ideally, in a perfect world, I would have baseline profiles on all query statements and know when a query is going against a new (whether "bad" or "good") plan. Alas, we are stuck in an imperfect world and woefully must wait for the blessed user to complain to us. (I am sure many of you will have helpful advice on how to stop depending on users in such an embarrassing way.) Ironically, after reading a little about the HotSOS Profiler and OraSRP, I could not find OraSRP on Egor's site.

My one thought, which causes me to cringe, is to turn on a 10053 trace at the system level to make sure we catch the dastardly plans in action. But surely there is a better way. I am hoping that I am simply missing something really obvious which would shame me, but at least should be a simple solution. I thought about login triggers, but that requires that you know which login conditions to watch for. While we have "usual suspects", they are not consistent. We have tried setting up tracing after the fact, but we never catch that initial bugger. I looked at the various dba_hist_sql% views, but I was having a hard time getting hard information out of them.

Where do I go from here?

charles schultz
oracle dba
aits - adsd
university of illinois

--
http://www.freelists.org/webpage/oracle-l