Re: Poor Oracle performance with load CPU load

On Thu, 02 Aug 2007 15:59:57 -0000, "jshen.cad_at_gmail.com" <jshen.cad_at_gmail.com> wrote:

>hi,
>
> we run oracle8.1.7 on HP-UX 11i.
>
> The DB performance becomes very poor that a lot data is lost for
>application processing queue overflow.
>
> Application ---- > DB
> ^
> |
> data queue here
>
> I noticed that CPU load of db server is not high, but the top5
>process change from DB main process 'oraclecodb' to
>ora_lgwr_codb, ora_dbw1_codb, ora_dbw2_codb, ora_dbw3_codb ,
>ora_dbw0_codb .
>
> with sar -d 5 1000, it shows some disk is very busy

You disks don't seem to be overloaded.

Run multiple queues?
Insert more than 1 row at a time?
Look into tuning LGWR?
Get rid of some 'fake' sequence?
Get rid of some 'select for update'?

Like Joe said, we can only guess.
You'd really need to look into statspack to see what, if anything, the db is waiting on.

I think you need to talk to someone that knows what a dbw, lgwr and statspack are.
.......
We run Oracle 9iR2,10gR1/2 on RH4/RH3 and Solaris 10 (Sparc) remove NSPAM to email Received on Thu Aug 02 2007 - 23:52:17 CDT