SEO

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

"Burkhard Kiesel" <burkhard.kiesel@siemens.com> wrote in message
news:3DFD733A.84080CD3@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.
>

Hi Burkhard,

As always, the best thing to do is check out any specific waits for the
sessions and see what the hold up might be.

I would suspect that cached info is being lost and needs to be reloaded via
the first few runs of these queries. This info could be sql areas that need
to be reparsed and/or the data blocks that need to re-read from disk.

Once everything is cached again, performance improves.

Then again, it could just be Oracle needing a few fags and cups of coffee to
get going in the morning

Cheers

Richard
>
> Regards
>
>
> Burkhard Kiesel
>

Burkhard Kiesel <burkhard.kiesel@siemens.com> wrote in message news:<3DFD733A.84080CD3@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

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

Burkhard Kiesel <burkhard.kiesel@siemens.com> wrote in message news:<3DFD733A.84080CD3@siemens.com>...

>
> 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.
>

You sure there isn't any stuff running at that time that
uses up all memory and makes Oracle be paged out?
Can't think of any reason for this slow-down early morning,
other than Richard's suggestion of lack of coffee...

Cheers
Nuno Souto
nsouto@optusnet.com.au.nospam