Re: Oracle 8.1.7.4 Performance Issues

Burkhard Kiesel <burkhard.kiesel_at_siemens.com> wrote in message news:<3DFD733A.84080CD3_at_siemens.com>...
> 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

You basically state here 'It doesn't work' Apart from that you don't provide too much clues. Your real problem however are your queries, not the database. You seem to have cranked up db_block_buffers to way beyond the threshold Oracle advises, in order to get performance. Oracle recommends sizing the SGA to not more than one third of RAM. Your SGA amounts to 60 percent. Your server must be heavily faulting. If you also have a default install of Solaris, you will have to cope with a file cache. There is no real distinction in Solaris between file cache and virtual memory. Backup is an I/O intensive operation, which *will* use the file cache.

Before you start blaming Oracle instead of your application, you'll need to find out (using statspack and/or v$system_events, v$session_events and v$session_waits) *what* the database is waiting for.
Do you have too many full table scans, too many sorts, inefficient queries with way too much logical I/O (your db_block_buffer setting clearly points to that)?Ultimately these are all factors which will saturate your disk.
You would also need to run the top command on the server to see what the server is actually doing. Is the cpu loaded or is it just waiting for I/O.

These are a few things which you would need to post, before anyone can give any sensible advice.

Regards

Sybrand Bakker
Senior Oracle DBA Received on Mon Dec 16 2002 - 06:24:38 CST