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

Oracle caches SQL in the shared pool for reuse. When SQL is found in the pool this saves Oracle from having to fully parse the SQL since it can reuse the access plan that it cached. It is likely that the SQL in question was no longer cached in the shared pool and the data buffers for the referenced tables and indexes may also no longer be cached depending on activity. Hence Oracle has to go reparse the SQL and fetch the associated buffers.

You may want to explain the queries in question to be sure they are tuned properly.

By the way allocating more than 50% of your real memory to the SGA may be excessive and could be causing paging and swaping at the OS level that harms Oracle performance more than having such a large buffer pool helps it. You may want to look at your buffer pool and see if it is being effectively utilized.

HTH -- Mark D Powell -- Received on Mon Dec 16 2002 - 09:10:55 CST