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).

Are you sure the ASP app isn't fighting Oracle for the CPU and I/O? I've noticed those sorts of apps seem to wake up periodically and do all sorts of stuff - even with no external activity at all. And if you happen to be sitting next to the box, you can hear the disk wailing away. If you try to access the db or the app while it is doing whatever, things seem real slow. FWIW. The point being, Steve's totally cool script can point out things that can be helped within Oracle, but the real cause of the problem might be Oracle being shut out - so whenever it is given a slice of CPU, it discovers it's still waiting to do the read - it may be possible to help here by tuning the system file cache a bit bigger. Oracle might tell you to recreate the index to make the sequential reads on the blocks faster through more leaf contiguity, but that would be flamebait in this group.

>
> 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.

150MHz isn't very much for modern software.

jg

--
@home.com is bogus.
"Weather is one of life's few changing constants." - Robert Krier