RE: System Statistics oracle9i

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Williams, Trevor
Sent: Thursday, July 28, 2005 12:21 AM
To: Oracle-L (E-mail)
Subject: System Statistics oracle9i

Hi  

How do you handle system stats when your gather_system_stats returns MREADTIM < SREADTIM?

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 remainder?

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 SAN?   ... 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   Thanks

Trevor      

DISCLAIMER:
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.

--
http://www.freelists.org/webpage/oracle-l