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

Given the limited information in your posting, I feel that it would be very difficult dianose your problem.

If you were to install statspack and collect a snapshot during the high CPU activity time period, we could get a good idea of where you would need to start.

If you wanted to trace the problem while it was running you could tie the v$session_wait, v$session, v$sql, and dba_extents views together to find out the indexes that are being read and which program(s) is causing the high number of reads.

FYI - Most queries can be tuned to use fewer resources and run much faster than you might think if you can get the help of someone that is good at performance tuning.

I think I read that you had 400M in your buffer cache. That's normally enough memory for most databases. The fact that you are still seeing many disk I/O's means that the offending query is probably doing a large range scan. (You'll need to re-work the query so that it filters out as much of the final resultset before it gets to this part of the access plan.)

I was working on a 2 Terabyte OLTP instance last year, and we found that adding more than 400M to the cache didn't help at all. We were joining 50+ gig tables.

One last recommendation...

You might consider picking up a copy of "Optimizing Oracle Performance" by Cary Milsap. It's an excellent resource if you don't know where to start.

Mark Simmons
Sr. Oracle DBA
Sabre-Holdings

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.
Received on Fri Mar 05 2004 - 18:01:25 CST