sreadtim and mreadtim < 1ms

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: