sreadtim and mreadtim < 1ms
- From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 3 Nov 2009 10:09:54 -0700
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 :)
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.
Other related posts:
