sreadtim and mreadtim < 1ms

From: Allen, Brandon <Brandon.Allen_at_OneNeck.com>
Date: Tue, 3 Nov 2009 10:09:54 -0700
Message-ID: <64BAF54438380142A0BF94A23224A31E112E6AF600_at_ONEWS06.oneneck.corp>

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.

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

Received on Tue Nov 03 2009 - 11:09:54 CST