Base Line for Performance purpose
- From: "Shivaswamy Raghunath" <shivaswamykr@xxxxxxxxx>
- To: oracle-l <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 5 Jan 2007 14:07:21 -0500
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
Other related posts:
