Re: What is the best strategy to evaluate performance of a big system
Date: Sat, 24 Sep 2011 11:04:40 -0500
Message-ID: <CAAWtT=-KrAkLhyGx0_NS3z6xb73fu5+XtrvzQxygi9mO2v3BXQ_at_mail.gmail.com>
Your question is rather broad. I would suggest using production to isolate the expensive sql. V$sql will help here. Look at the definition of that view and decide what characteristics you will use to separate the sheep from the goats. v$sql can give you executions and physical and logical i/o's. Is your big system CPU bound, I/O bound? Please tell us about your hardware environment. Is your development system a reasonable fraction of your production? Could you clone by copying the datafiles to dev preserving the data distribution? Can you correlate the perceived performance problems with the state of the OS?
If you can select sql's of significance then get explain plans for the expensive statements and work from there. Remember that a small query executed many times can be a performance problem.
Allan
On Sat, Sep 24, 2011 at 10:46 AM, Eriovaldo Andrietta <ecandrietta_at_gmail.com
> wrote:
> 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 - 11:04:40 CDT