Re: OSF/1 + Oracle7 performance with interactive & batch jobs

In article <1994Sep5.191932.1_at_cc.helsinki.fi>, jaakola_at_cc.helsinki.fi wrote:

> We have a DEC 4000/610 with OSF/1 V1.3 running Oracle7 7.0.15.4.
> We have about 55 interactive SQL*Forms 3.0 users and we run at the same
> time about 5..10 batch reports. We use MTS with 10 server processes. We
> have 196 MB RAM and the exp dump file size is about 500MB.
>
> We have a performance problem: when we have many large batch reports
> running the interactive response time is slow. And when I look at
> "vmstat" output, the run queue is about 15. I think that the problem is
> that the big reports are very CPU intensive (after having loaded all
> their data into Oracle databases buffers in RAM) and that they use all
> of their CPU timeslice. So the interactive processes have to wait long
> before they get their slice of the CPU - i.e. the run queue is long.
>
> How do I reduce the maximum timeslice a CPU-intensive process can get in
> OSF/1? I read man an hour yesterday without getting any info. They
> referred to a printed manual which I don't have at hand.
>
> Another question: would upgrading to OSF/1 V2.X & Oracle 7.0.16.4 affect
> performance in any way?
> --
> Juhani Jaakola, jaakola_at_cc.helsinki.fi

A problem you may not be thinking about is the processes are continually moving the data out of the SGA. The on-line users grab their data and load it into
the db_buffers. The bacth process may then come along and read large amounts
of data which may cause the on-line user to have to re-read his data from disk because their data was pushed out by the batch process.

Another problem I have had with intense batch jobs, is they have a habit of hogging the rollback segments. I have seen users seem hung, when in reality, they were just attached to the same rollback as one of the batch jobs that was doing heavy updates.

You can also have locking problems if the batch and on-line processes are accessing the same tables/records.

Have you used the Oracle Monitor yet to look at statistics from that view.

Frank Abney, sxfla_at_orca.alaska.edu Received on Wed Sep 07 1994 - 18:35:33 CEST