RE: Base Line for Performance purpose

You mentioned ASH, so it sounds like you're on 10g, which keeps all the history you need in ASH/AWR - you should be able to see there when an execution plan changed and the resulting performance stats.  

If you were on 9i, I'd recommend regular (every hour at least) statspack snapshots with occasionaly level 6 snapshots to capture the explain plans as well.  

With the explain plan problem you describe below, it sounds like you're encountering the same problem I battle on a daily basis these days - bind variable peeking causing explain plans to be shared when they shouldn't be, i.e. between one execution for a very selective (i.e. unpopular) value and a later execution for a very unselective/popular value. The first should use an index and the latter should use FTS, but Oracle only peeks the first time and then uses the same plan for both executions. The solution is to force the preferred method with a hint or stored outline, or break the two executions into two syntactically different statements so they don't share the same cursor, e.g. with comments like:  

SELECT /*+ popular */ . . .  

SELECT /*+ unpopular */ . . .  

Or, disable bind variable peeking by setting the hidden parameter.  

Regards,
Brandon

Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

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