Re: Oracle Stats
From: <art_at_unsu.com>
Date: Fri, 15 May 2009 11:33:33 -0700 (PDT)
Message-ID: <e8878d1b-04a4-44e7-9fe9-5d61b83e4a10_at_v17g2000vbb.googlegroups.com>
On May 15, 12:57 pm, joel garry <joel-ga..._at_home.com> wrote:
> On May 15, 10:17 am, a..._at_unsu.com wrote:
>
>
>
> > 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.
>
> I forgot to ask, how often are your redo logs switching? If it's like
> every 20 minutes under the worst load, then my initial feeling would
> be you have a well-configured system and must beware the Obsessive
> Tuning Disorder - although, it is still good to want to understand AWR
> before it becomes needed.
>
> I also forgot to mention, between Collaborate, Oracle Open World, and
> many local user groups, there are many papers and presentations
> specifically geared to understanding AWR and statspack. The basic
> stuff you are asking about may be found in statspack presentations
> too, I haven't really looked, preferring to answer your more excellent
> question of how to figure it out yourself.
>
>
>
> > 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.....
>
> Google is your friend (although sometimes it can be your frenemy...)
> a quick search on log file parallel ...
>
> read more »
Date: Fri, 15 May 2009 11:33:33 -0700 (PDT)
Message-ID: <e8878d1b-04a4-44e7-9fe9-5d61b83e4a10_at_v17g2000vbb.googlegroups.com>
On May 15, 12:57 pm, joel garry <joel-ga..._at_home.com> wrote:
> On May 15, 10:17 am, a..._at_unsu.com wrote:
>
>
>
> > 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.
>
> I forgot to ask, how often are your redo logs switching? If it's like
> every 20 minutes under the worst load, then my initial feeling would
> be you have a well-configured system and must beware the Obsessive
> Tuning Disorder - although, it is still good to want to understand AWR
> before it becomes needed.
>
> I also forgot to mention, between Collaborate, Oracle Open World, and
> many local user groups, there are many papers and presentations
> specifically geared to understanding AWR and statspack. The basic
> stuff you are asking about may be found in statspack presentations
> too, I haven't really looked, preferring to answer your more excellent
> question of how to figure it out yourself.
>
>
>
> > 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.....
>
> Google is your friend (although sometimes it can be your frenemy...)
> a quick search on log file parallel ...
>
> read more »
Log Switch: log_checkpoint_interval = 0 Received on Fri May 15 2009 - 13:33:33 CDT