Re: sreadtim and mreadtim < 1ms
Date: Tue, 3 Nov 2009 22:32:18 -0500
Message-ID: <54b1f9980911031932m784c6f4dhb0dd6b9157fe2fd0_at_mail.gmail.com>
Brandon, what I did recently to ensure caching did not affect the results of my system statistics testing was to create a set of large tables (approx 5Gb in my case), and let those tables "sit" for a day or more to make sure anything that was cached had a proper chance to be removed before my testing. I went back in later and did a full table scan of each one of those tables only once to test various MBRC settings. This test was very similar to what Jonathan Lewis book explains for MBRC testing. I understand you are trying SREADTIM and MREADTIM, which may be a bit different, but maybe this though helps with how I got around any sort of caching.
Thanks,
Michael Wehrle
On Tue, Nov 3, 2009 at 12:09 PM, Allen, Brandon <Brandon.Allen_at_oneneck.com>wrote:
> Hi List,
>
>
>
> I’ve got a database that is constantly reporting sreadtim and mreadtim
> values under 1 millisecond according to gather_system_stats due to caching
> at the OS level. I’ve searched Metalink, the docs and the Internet but
> can’t find any discussion or even a single suggestion of how best to handle
> system stats in this situation so I’m just wondering if anyone has any
> suggestions? The question will probably come up so I’ll answer it first –
> there isn’t any particular problem I’m trying to solve here – I just have
> some spare time so I’m looking at opportunities for system optimization and
> noticed that this database doesn’t have system stats in place so figured I’d
> gather them as a standard Oracle “best practice” and see if I can make
> performance even better than it already is. Yes, I’m suffering from
> compulsive tuning disorder, but it’s only temporary until the next fire or
> project comes up J
>
>
>
> I ran a 10053 trace and noticed that it looks like the CBO is rounding
> down, and maybe completely ignoring the values of sreadtim and mreadtim
> since it just shows them as zero (see below) even though you can see they
> are actually .1 and .3 according to aux_stats$ (I set these values manually
> based on some averages I’ve gathered in production).
>
>
>
> I’m considering manually setting them to sreadtim=1 and mreadtim=1.5 or
> similar, or maybe just playing it safe and sticking with the default stats
> instead. This is Oracle 10.2.0.4 on Oracle Linux 5.2.
>
>
>
> Thanks,
>
> Brandon
>
>
>
> *****************************
>
> SYSTEM STATISTICS INFORMATION
>
> *****************************
>
> Using WORKLOAD Stats
>
> CPUSPEED: 2170 millions instructions/sec
>
> SREADTIM: 0 milliseconds
>
> MREADTIM: 0 millisecons
>
> MBRC: 21.000000 blocks
>
> MAXTHR: 9000000 bytes/sec
>
> SLAVETHR: -1 bytes/sec
>
> ***************************************
>
>
>
> SQL>select * from aux_stats$;
>
>
>
> SNAME PNAME PVAL1
> PVAL2
>
> ------------------------------ ------------------------------ ----------
> --------------------
>
> SYSSTATS_INFO STATUS
> COMPLETED
>
> SYSSTATS_INFO DSTART
> 11-02-2009 15:58
>
> SYSSTATS_INFO DSTOP
> 11-02-2009 15:58
>
> SYSSTATS_INFO FLAGS 1
>
> SYSSTATS_MAIN CPUSPEEDNW 2193.815
>
> SYSSTATS_MAIN IOSEEKTIM 10
>
> SYSSTATS_MAIN IOTFRSPEED 4096
>
> SYSSTATS_MAIN SREADTIM .1
>
> SYSSTATS_MAIN MREADTIM .3
>
> SYSSTATS_MAIN CPUSPEED 2170
>
> SYSSTATS_MAIN MBRC 21
>
> SYSSTATS_MAIN MAXTHR 9000000
>
> SYSSTATS_MAIN SLAVETHR
>
>
>
>
>
> ------------------------------
> Privileged/Confidential Information may be contained in this message or
> attachments hereto. Please advise immediately if you or your employer do not
> consent to Internet email for messages of this kind. Opinions, conclusions
> and other information in this message that do not relate to the official
> business of this company shall be understood as neither given nor endorsed
> by it.
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Nov 03 2009 - 21:32:18 CST