Re: Oracle 8.1.7.4 Performance Issues

Burkhard Kiesel wrote:
> Hi,
>
>
> I have a question about Oracle 8.1.7.4 performance, where I need to find
> the answer or solution.
>
> We have a Oracle 8.1.7.4 database installed on a SUN Enterprise 450 with
> 2 processors and 2 GB RAM. The shared_pool is approx. 150 MB in size.
> The db_block_buffer are in total 1,2 GB in size.
>
> When I run the regular queries on the database, the took approx. 0,05 to
> 0,4 seconds for each query. For my tests, I run the query frequently on
> the database and they took between 0,05 and 0,4 sec. That fine for me.
>
> In the next morning, I ran the same queries and the first say 20 queries
> are totally slow, means times from 5 sec up to 20 seconds for each
> query. After the first 20-30 queries, the query time goes down to 0,05
> to 0,4 seconds.
>
> What is the reason for that. There were no queries in the night on the
> database.
> Only a online Backup was running. Is this a normal case, that when
> starting in the morning, the first queries take this long time ?
> Are there any parameters which I can check within Oracle configuration.
>
>
> Regards
>
>
> Burkhard Kiesel
>

My first guess is You hit a delayed block clean out. Something (batch?) or somebody did Heavy update on your tables in question. To speed up creating free buffers for the update, the db-writer wrote the blocks 'as is' from the buffer cache to disk. That is, blocks that are not quite clean and compact/organized as usual. The next process that reads those blocks from disk, has inherited the cleanup work, witch will give prolonged execute time and more cpu usage.

rgds

/Svend Jensen Received on Mon Dec 16 2002 - 13:23:32 CST