Base Line for Performance purpose

Hello Listers.

On the database I support - a reasonably big, about 4TB in size DSS Database, which is fairly busy - I am able to resolve most of the performance related issues. But at times, a job might run longer than usual. The plan appears to be good. No unwanted wait events. But, it may turn out that plan is sub-optimal. Recently one of the monthly jobs was making index scan but won't complete. It had almost 100% on Sequential read on ASH. Finally I figured out that, with a full scan the query would finish very fast.

Now the question I have is this: While I think I know the system reasonably well, how do I keep a base line for performance? How can I notice that, this job which has been in production for a while now should have gone in for Full scan? Generally speaking a production DB will have many jobs. And how do we get a base line?

To trace each job to get an accurate picture while doing good may not be a possible - because there will be too many jobs. each jobs will have hundreds of SQLs, if not thousands. To dump the plan of all the SQLs in memory for one month to get all of them also would be too tidy, considering the volume.

I was wondering how are you addressing this issue?

Thanks for your input in advance.
Shiva

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