I want to know what are the topics based on which we can at least make a rough estimation of the database efficiency -- say, for example, what are all the topics to be seen in the Statspack, or any other reports or parameters from the data dictionary views, etc.
This does not mean that Statspack reports or the like are not useful even if you do not have feedback from the users. Typically when I look at a Statspack report, I am investigating a specific problem. However, there are times when I look at Statspack to get an overall feel of how things are going. The first thing I look at is the top 5 wait events. What events are in the top 5? Are these normal events? You cannot answer that last question unless you look at your Statspack report during normal operations. In one of my databases, it is normal for me to see 'db file sequential read' and 'CPU time' as the top 2 events. I know this because I have looked at the reports for these systems many, many times. If I look at a Statspack report and another event is in the top 2, then I know something is out of place and further investigation is warranted.
The next place I look is at the SQL statements in Statspack. I typically know the most resource-intensive SQL statements in my database. If I see a new SQL statement as one of the most resource-intensive, then I may need to tune that new statement.
The last place I look in my Statspack report is at my disk usage summaries. Again, I look for things that are abnormal. All of this means you must know what is normal. What is normal for me may not be normal for you. So you'll have to look at your Statspack report on a regular basis to get a general baseline of normal behavior. Oracle 10g takes this a step further by letting you collect a baseline of normal behavior and letting you compare future metrics against this baseline with the ADDM.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.