Q
Manage Learn to apply best practices and optimize your operations.

How to estimate database efficiency?

We do not have any formulas for calculating the exact efficiency of the database and a whole lot depends on what the end users experience. I want to know what are the topics based on which we can at least make a rough estimation of the database efficiency.

It is true that we do not have any formulas for calculating the exact efficiency of the database and a whole lot depends on what the end users experience.

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.

The problem when looking at Statspack or similar tools is that the tool will tell you where a problem is. The big question is whether this is a problem that is noticable to the end users. Unfortunately, no tool can directly tell you this. You'll have to talk with your end users to find out where they feel the most pain in the performance of the application.

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.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close