Re: Oracle Stats
Date: Fri, 15 May 2009 09:50:37 -0700 (PDT)
Message-ID: <a5e363fc-80a0-40a2-b190-eceda194784b_at_a5g2000pre.googlegroups.com>
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, see http://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, see http://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/0647254Received on Fri May 15 2009 - 11:50:37 CDT