Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Very high CPU on OS side vs very high dbFileSeqRead on Oracle side...

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

From: Spendius <spendius_at_muchomail.com>
Date: 5 Mar 2004 00:16:40 -0800
Message-ID: <aba30b75.0403050016.4d375744@posting.google.com>


sybrandb_at_yahoo.com wrote...
> [...]
> 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.
That's what we've been beginning to suspect for a few days... But I guess you know the developers and project leaders: they can't stand the slightest form of critics.

> 2 suggestions
> Configure the keep and recycle buffer pools and put the affected
> indexes in the keep buffer pool.

Relevant.

> Second suggestion: try using reverse key indexes.
Not recommended: I know these constantly read/updated indexes are made on dates and that they're used with range scans (the app. displays tables with periods of time -selected by the user- on 99% of its pages).

> the size of the SGA is ...mmmm... ridiculous.
No, it's sufficient. The server first had only 2 gigs of RAM, we upgraded to 4G not to increase the SGA, but to allow for sessions-related areas of memory (PGA/UGA) to grow as required (the server used to swap so hugely that the server crashed several times).
Don't forget we have all the time between 130 to 170 sessions that easily eat up to 1G and more of memory. I checked it with

#select se.sid,
#       max(se.value) maxmem
#from v$sesstat se,
#     v$statname n
#where n.statistic# = se.statistic#
#and   n.name in ('session pga memory', 'session uga memory')
#group by se.sid
#order by 2

with a COMPUTE SUM OF MAXMEM ON REPORT.

> Seems like a case of silver bullet tuning and more is better.
Sorry here but my English is not good enough: what does "silver bullet" mean ?

Thanks. Received on Fri Mar 05 2004 - 02:16:40 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US