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

spendius_at_muchomail.com (Spendius) wrote in message news:<aba30b75.0403040222.6395f73a_at_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.

This is an application/database design issue, what you are looking at is the result of an ill-designed application which is constantly querying/updating the same keys over and over again. It has nothing to do with the O/S or the hardware.
2 suggestions
Configure the keep and recycle buffer pools and put the affected indexes in the keep buffer pool.
Second suggestion: try using reverse key indexes. You really need to do something about your application, the size of the SGA is ...mmmm... ridiculous. Seems like a case of silver bullet tuning and more is better.

Sybrand Bakker
Senior Oracle DBA Received on Thu Mar 04 2004 - 09:35:25 CST