Very high CPU on OS side vs very high dbFileSeqRead on Oracle side...

From: Spendius <spendius_at_muchomail.com>
Date: 4 Mar 2004 02:22:08 -0800
Message-ID: <aba30b75.0403040222.6395f73a@posting.google.com>

Hi,
We have a DB in 8.1.7.1 on Sun/Solaris that endures pretty high reads activity, principally located on a few index files. I have to say that these blocks are constantly updated as well as a few tables are inserted *all the time* as well.
The application response time is correct, according to our users. The OS CPU consumption is very high (see below). It only leads sometimes -but WE DON'T KNOW WHY- to some delay between the last data available displayed to the users (this is an ASP app.) and the current time (it should never be > 2 mn., yet we sometimes reach 20 minutes of difference => I mean this INSERT activity is slowed down in some way).

Steve Adams' script response_time_breakdown.sql shows us 74% of waits concentrated on 'db file seq. read' event (SEVENTY FOUR percent). I determined that these waits principally happen on 2 or 3 index files.

My question: IS THERE A WAY TO RELIEVE A BIT the activity on these index blocks which are constantly read ?

Data:

---

Nb of client sessions:
# ps -ef|grep SID|grep LOCAL=NO|wc -l

     129

Here is the hardware:
# /usr/platform/sun4u/sbin/prtdiag|more
System Configuration: Sun Microsystems sun4u Sun Fire 280R

                                    (2 X UltraSPARC-III+) 

System clock frequency: 150 MHz
Memory size: 4096 Megabytes

Doing a 'sar' *always* shows you that kind of stuff:
               ^^^^^^

ARWP>sar 2 20
SunOS ... 5.8 Generic_108528-20 sun4u 03/04/04

10:08:52    %usr    %sys    %wio   %idle
10:08:55      90      10       0       0
10:08:57      90      10       0       0
10:08:59      84      16       0       0
10:09:01      81      19       0       0
10:09:03      83      17       0       0
10:09:05      88      12       0       0
10:09:07      88      12       0       0
10:09:09      78      22       0       0
10:09:11      84      16       0       0
10:09:13      80      20       0       0
10:09:15      90      10       0       0

SQL> show sga
Total System Global Area 647483552 bytes

Fixed Size                    73888 bytes
Variable Size             236744704 bytes
Database Buffers          409600000 bytes
Redo Buffers                1064960 bytes

Thanks a lot. Received on Thu Mar 04 2004 - 04:22:08 CST