Re: What is the best strategy to evaluate performance of a big system

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@xxxxxxxxx>
To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
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-l

Other related posts: