Re: Oracle Stats

From: <art_at_unsu.com>
Date: Fri, 15 May 2009 10:17:28 -0700 (PDT)
Message-ID: <58bb59cc-4384-42e7-b0a0-c08243101255_at_o14g2000vbo.googlegroups.com>



On May 15, 11:50 am, joel garry <joel-ga..._at_home.com> wrote:
> On May 15, 8:47 am, a..._at_unsu.com wrote:
>
> > Hi,
>
> > We're basically running some Oracle utility: awrrpt.   It gives us the
> > largest report we've even seen.   At any rate, most of the stats are
> > basically hard to understand.  Is there someplace that explains what
> > all these items are?
>
> This is an amazingly huge question, and has a number of disparate
> answers, depending on who you ask and what level you are really on.
>
> If you have technically oriented people, they may want to jump in the
> deep water, search for awr report on oracldoug.com/serendipity.  He's
> got some real good posts that walk you through.
>
> If you haven't gone through the concepts and performance manuals, you
> must do that first.  There are also online things, seehttp://dbaoracle.net/readme-cdos.htm#subj10 Too many things, as you
> may have noticed.  Jonathan Lewis wrote a book called Practical Oracle
> 8i, which is still worthwhile, and his blog and faq are still quite
> useful.  In fact, you can do things like google on his name and a
> particular stat, and often get quite relevant hits.  Sometimes you can
> even get clarification from the help in Enterprise Manager, or at
> least an official definition.  Googling on that information often
> leads to clarifications, too.
>
> Hopefully, some nice people will even explain these things here, but
> you have to demonstrate your good faith that you have tried to
> investigate the top things.
>
> No performance tuning answer would be complete without mentioning Cary
> Millsap's book, Tanel Poder's blog, a post on forums.oracle.com about
> "what to do when your performance is bad," and an admonition about not
> tuning for tuning's sake.  This answer is far from complete, anyways,
> and I've probably missed some real obvious things.
>
>
>
> > We do not understand all these stats and if a large or small number is
> > good.
>
> Is anyone complaining?  But it's good that you are asking, I've
> noticed that some of the better tuning methodologies have an implicit
> (or even explicit) assumption that things are more or less in the
> ballpark and just a few things are bad, but a system that hasn't
> really been tuned or checked may not be in that ballpark.  There are a
> number of reasons for this, even on systems that have been running for
> a while, but the defaults on the newer Oracle versions are often more
> or less correct.
>
> In the end, most performance problems are due to bad sql coding, but
> sometimes you can hit some low-hanging fruit with a health-check kind
> of first look.  For some reason (I suppose because in the past there
> were some very bad methodologies based on stupid myths, and nowadays
> there is an expectation that at least decent DBA work has been done),
> I often seem to be the only one saying this.
>
>
>
>
>
> > For example:
>
> > Operating System Statistics             DB/Inst: NI00/NI00  Snaps:
> > 13017-13041
>
> > Statistic                                       Total
> > -------------------------------- --------------------
> > BUSY_TIME                                  14,954,028
> > IDLE_TIME                                 121,666,971
> > NICE_TIME                                           0
> > SYS_TIME                                    6,017,299
> > USER_TIME                                   8,936,729
> > LOAD                                                3
> > RSRC_MGR_CPU_WAIT_TIME                              0
> > PHYSICAL_MEMORY_BYTES                          77,192
> > NUM_CPUS                                           16
> > NUM_CPU_SOCKETS                                     4
>
> > What is NICE_TIME?  What is BUSY_TIME?  Is a high number good?   How
> > about this section?
>
> >                                                                    Avg
> >                                              %Time  Total Wait
> > wait     Waits
> > Event                                 Waits  -outs    Time (s)
> > (ms)      /txn
> > ---------------------------- -------------- ------ ----------- -------
> > ---------
> > log file parallel write           9,039,920     .0       3,179
> > 0       1.8
>
> A lot of people will give cookbook answers for this, which may or may
> not be correct.  It does imply you've got issues writing your redo,
> one way or another.  This is the sort of thing that requires you to
> give an exact version (ie, 10.2.0.4) of oracle, and state your
> operating system versions and platform too, especially what kind and
> configuration of I/O you have.  It could be conflict with writing
> other files, RAID configuration, volume of transactions, size of log
> buffer, and on and on.  The people who say "why guess when you can
> know?" are generally spot-on for this one.
>
> I'll stop here while you post necessary information, seehttp://dbaoracle.net/readme-cdos.htm#subj12
>
> I'll re-emphasize, you can get a lot of good help here, if you show
> interest and willingness to work at it.
>
>
>
> > control file parallel write          89,669     .0         381
> > 4       0.0
> > events in waitclass Other            73,885     .0          48
> > 1       0.0
> > Log archive I/O                      15,517     .0          30
> > 2       0.0
> > latch: shared pool                    3,565     .0          22
> > 6       0.0
> > log file sequential read             16,071     .0          17
> > 1       0.0
> > latch: library cache                  3,521     .0          16
> > 5       0.0
> > os thread startup                        89     .0           5
> > 51       0.0
> > control file sequential read        115,717     .0           3
> > 0       0.0
> > db file sequential read               1,220     .0           1
> > 1       0.0
> > buffer busy waits                       912     .0           1
> > 1       0.0
> > log file single write                 1,021     .0           1
> > 1       0.0
> > log file switch completion                4     .0           0
> > 81       0.0
> > latch: redo writing                     496     .0           0
> > 0       0.0
> > direct path read                      6,177     .0           0
> > 0       0.0
> > direct path write                     6,177     .0           0
> > 0       0.0
> > latch: cache buffers chains             153     .0           0
> > 0       0.0
> > latch: row cache objects                 23     .0           0
> > 0       0.0
> > latch: library cache pin                  8     .0           0
> > 0       0.0
> > latch: library cache lock                 3     .0           0
> > 0       0.0
> > rdbms ipc message                 9,152,530    3.3     987,184
> > 108       1.8
> > pmon timer                           30,226  100.0      84,327
> > 2790       0.0
> > Streams AQ: qmn slave idle w          3,085     .0      84,122
> > 27268       0.0
> > Streams AQ: qmn coordinator           6,171   50.0      84,115
> > 13631       0.0
> > smon timer                            2,911     .8      83,197
> > 28580       0.0
> > Streams AQ: waiting for time             20  100.0      37,354
> > #######       0.0
> > SGA: MMAN sleep for componen          1,036   94.8          11
> > 10       0.0
>
> > -------------------------------------------------------------
>
> jg
> --
> _at_home.com is bogus.http://hardware.slashdot.org/article.pl?sid=09/05/15/0647254

Well, let's see if this helps:

Oracle: 10gR2
Linux: CentOS4.x
No RAID

What else:

db_cache_size=3000M
sga_target=10G
shared_pool_size=2G
large_pool_size=2G
Fairly busy OLTP.

Not sure what else you need.

We are not experiencing anything to serious. But, the AWR and understanding the kinds of stats and what they mean is something I want to know more about. I've read a bunch of stuff, but sometimes even that is hard to understand......most of those measures are not covered. I can post the entire report, but I doubt you want to see 2500 lines. I want to understand what these things mean......many books are just so generic in their 'tuning' and understanding the stats.....

Thanks! Received on Fri May 15 2009 - 12:17:28 CDT

Original text of this message