RE: System Statistics oracle9i

  • From: John Kanagaraj <john.kanagaraj@xxxxxxx>
  • To: "'Trevor.Williams@xxxxxxxxxx'" <Trevor.Williams@xxxxxxxxxx>, "Oracle-L (E-mail)" <Oracle-l@xxxxxxxxxxxxx>
  • Date: Fri, 29 Jul 2005 15:16:30 -0700

Interesting question!
I raised a similar question a while ago, and Jonathan Lewis answerd it.
Considering what he said and based on my own research, the gist of is that
(a) we will need stability in these values (so plans don't change on you).
(b) These values need to reflect reality (so you give the CBO real values it
can use) (c) the calculated MBRC could be adversely affected by the size of
the buffer cache and the existence of hot blocks on objects that are
undergoing FTS. In short, I would ignore the calculated MBRC and keep it set
at what the OS will allow (usually 64K if not tuned), and use an external
program such as IOZone that can measure your disk performance for  (almost
real-life) values for Single/Multi block reads and set them into system
stats manually.
As for two instances on the same server showing differing characteristics
for SRDTM and MRDTM, I would go back to the SAN/Volume Manager and see how
the SAN/SA carved up your LUNs for you. I had run into an issue a while ago
(3 years?) where the SA gave us apparently different filesystems but carved
them out of the same LUN. And I kept raising my voice until I got
filesystems on dedicated (but smaller) LUNs....
Let the list know how it goes!

John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)
Fear connects you to the Negative, but Faith connects you to the Positive! I
Jn 4:18
** The opinions and facts contained in this message are entirely mine and do
not reflect those of my employer or customers **


From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Williams, Trevor
Sent: Thursday, July 28, 2005 12:21 AM
To: Oracle-L (E-mail)
Subject: System Statistics oracle9i



How do you handle system stats when your gather_system_stats returns

Do you live with the optimizer using the adjusted dfmrc value?

Apply some calculation to SREADTIM to generate a larger MREADTIM?

Ignore all stats where MREADTIM<SREADTIM and pick some average for the

Or what?

We have dynamic memory buffer caching turned on here (HP-UX ia64 itanium).
Is this the likely reason why often MREADTIM<SREADTIM? Or do I blame the


... and ...


My gathered system statistics are much more variable that I would have
thought. For the same time period that is.

Is it possible that the two instances on the same server will affect each
other's system statistics?

Is there likely to be a problem with gathering system stats for both
instances at the same time?

Even so, what do you suggest to handle this variation? Plan A is to take the
average of all of the stats.


Instance1: 08:30-14:30


STATID          SRDTM      MRDTM        CPU       MBRC     MXTHRD

---------- ---------- ---------- ---------- ---------- ----------

D27JUL0830       .995       1.13       1194          6   94699520

D26JUL0830      1.378      1.649       1190          6   34324480

D25JUL0830      1.482      1.088       1191          5     386048

D22JUL0830       1.67       .723       1185          5   13148160


Instance2: 08:30-14:30


STATID          SRDTM      MRDTM        CPU       MBRC     MXTHRD

---------- ---------- ---------- ---------- ---------- ----------

D27JUL0830      4.065     10.643       1120         30   68080640

D26JUL0830       .659      7.064       1194         31   38184960

D25JUL0830       .622      9.988       1198         28   23548928

D22JUL0830      1.558      9.757       1195         29    1528832







Disclaimer. This e-mail is private and confidential. If you are not the
intended recipient, please advise us by return e-mail immediately, and
delete the e-mail and any attachments without using or disclosing the
contents in any way. The views expressed in this e-mail are those of the
author, and do not represent those of this company unless this is clearly
indicated. You should scan this e-mail and any attachments for viruses. This
company accepts no liability for any direct or indirect damage or loss
resulting from the use of any attachments to this e-mail. 

Other related posts: