Re: AWR Sample Report

On Nov 21, 6:28 am, raja <dextersu..._at_gmail.com> wrote:
> Hi,
>
> I will try to get a AWR Report which has an interval of 10-15 minutes
> interval.
> Currently i have only this report and i am trying to analyze it.
>
> In AWR Report, SQL Statistics, there are many parts like :
> SQL ordered by Elapsed Time
> SQL ordered by CPU Time
> SQL ordered by Gets
> SQL ordered by Reads
> SQL ordered by Executions
> SQL ordered by Parse Calls
> SQL ordered by Sharable Memory
> SQL ordered by Version Count
> Which i should look into first ? why ?
>
> With Regards,
> Raja.

The why relates to why you are looking at this report in the first place. You should be familiar with the system and how it is running, and have some idea of what is wrong. It's easy to fall into the trap of tuning for tuning's sake, also wittily referred to as OTD - Obsessive Tuning Disorder. It's also easy to fall into the trap of saying "no one is complaining, so therefore nothing is wrong."

Personally, I think there is value in checking a system to be sure nothing is about to become a problem, or become a problem when two notproblems  run concurrently. Also, when a bottleneck is created, things can go downhill in a hurry, one wants to be aware of what to watch for. This falls under the part of a DBA's job called "knowing your system."

Tuning is an iterative process, too. You need to order the importance of what is wrong, and from there decide which to look for first. Once you've fixed it, something else may show up. It is even possible the fix may make things worse. For example, you may discover lots of context switching due to unnecessary PL calls by a major app, and decide to fix it by converting a particular bit of code to SQL. Then the processor is free to do everything faster, overwhelming the I/O system with calls.

So, it depends. That's why you follow a particular methodology, preferably based on rational inquiry like Charles is demonstrating, rather than just twiddling with ratios or expecting some cookbook scheme to help. The trick is to tease out the clues that send you in the right direction, and ignore the red herrings. So what is the database waiting for? Are the waits what you would expect given what your application is doing? Charles' SDU comment is certainly worth investigating further.

You might want to briefly look at top sql by parse calls if you are using a packaged app, some vendors write poor code (ie, without bind variables) and it becomes painfully obvious when your most-used code suffers from excessive parsing. Also compare the top executed with all the others, at least to be sure your most executed code is working as it should. Sometimes some blatant easily fixable errors pop out there. But then again, the same applies to all those top sql lists.

jg

--
@home.com is bogus.
http://home.nestor.minsk.by/jazz/news/2008/11/1804.html