Re: What is the best strategy to evaluate performance of a big system
Date: Sat, 24 Sep 2011 10:43:06 -0700 (PDT)
Message-ID: <1316886186.23055.YahooMailNeo_at_web113211.mail.gq1.yahoo.com>
Some good suggestions I think. You have to remember to look at the whole architecture from a holistic point-of-view though. You can't just concentrate on the database if what you want is performance gains for the application.
I believe the first step is to start addressing the question from the application point-of-view. Go out and talk to the business and discover what their pain points are. Who cares if a query takes 1 second to return if that query only runs 15 times a day and the users are not experiencing pain associated with that query. You have to avoid the compulsion to tune everything. Make your efforts very directed, very strategic and laser like. Find the pain points in the application. Find out from the customer what the current response times are and what they would consider acceptable. In terms of prioritization, also find out from the customer how the organization would benefit from any tuning. For example, if 500 concurrent users will be positivity impacted by your tuning effort, this is likely to have priority over efforts where 1 person will benefit (unless it's the CEO, in which case all bets are off!). So you need to scope your efforts and make sure you are getting the biggest bang for the buck. You also need to scale your efforts, leaving room for your other duties and time to go to the bathroom, take a break once in a while and so on. :)
I usually like to start collecting metrics at this point. I want to know where the problem really is. Is it in the application layer, is it in the database layer. Where is all the time being spent? For example, let's say you have identified that a given application task that is frequently used, takes about 2 minutes to return it's results to the user. The user indicates that they really need the results back in a few seconds. The task involves several calls to the database by the application including inserts, updates, commits and queries.
In collecting metrics, you find that the sum total time actually spent in database calls is on the order of 30 seconds. What does this tell you? First it tells you that at best, database tuning is going to buy you a run-time improvement of no more than 30 seconds (reducing total run time to at best 1.5 minutes) and that you will be far away from your few second goal. Clearly the problem is somewhere else, likely at the application layer, but we want solid metrics to prove that. One thing we could do is look at a 10046 trace and look at network waits between calls to the application. If there is an insert and subsequent update statement coming from the application process and there is a 30 second network delay between the two calls, then we would want to figure out what was causing that delay.
This being said, it's important to have the application/business folks behind you on these efforts. It's no fun running into roadblocks when tuning because the people on the application side refuse to work with you or don't feel your tuning efforts are sufficient or important.
So, that's kind of my methodology overall. You can look at statspack and look for the worst performing queries, but my preference is to start with the people actually using/developing the application.
Cheers
Robert
Robert G. Freeman
Master Principal Consultant, Oracle Corporation, Oracle ACE
Author of various books on RMAN, New Features and this shorter signature line.
Blog: http://robertgfreeman.blogspot.com
Note: THIS EMAIL IS NOT AN OFFICIAL ORACLE SUPPORT COMMUNICATION. It is just the opinion of one Oracle employee. I can be wrong, have been wrong in the past and will be wrong in the future. If your problem is a critical production problem, you should always contact Oracle support for assistance. Statements in this email in no way represent Oracle Corporation or any subsidiaries and reflect only the opinion of the author of this email.
From: Eriovaldo Andrietta <ecandrietta_at_gmail.com> To: ORACLE-L <oracle-l_at_freelists.org> Sent: Saturday, September 24, 2011 8:46 AM Subject: What is the best strategy to evaluate performance of a big system
Hello Friends,
I would like to know, what is the best strategy to identify and provide
performance in a big system that is running in the production.
Imagine a system written in java, C, perl, pl/sql, triggers ... and others
... several kinds of language ..
Everything is connecting to Oracle 10g, doing small queries and complex
queries too ...
So ...
I understand that there must have "pointed bad queries" that can be
identified only running the applicaton ... It maybe take more time than the
expected...
What would be the other way to obtain the problematic queries?
- Turn on trace, read the tracefile, identify problems and make changes
based on trace ?
- Analyse application logs, read the log, identify problems and make
changes based on log ?
- The tester identify problems by himself, testing all
system functionality ? In this case, trace and log are resources to obtain
the bad sql statment.
What is the best option for environment ?
- Apply the strategy on the production environment, without leaving this
database to another environment, because this
action will reorganize the database and provide a different data
organization in comparison with the original environment and probably
different performance.
- Apply the strategy on new database (using same database parameters) in
another environment and populate it with the data from the production
environment and use trace or logs ?
At first, the resource for it would be : a tester skill and a DBA skill
professionals.
Thanks and Regards !!
Eriovaldo
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Sat Sep 24 2011 - 12:43:06 CDT