Oracle database server becomes slow after sone time

Printable View

12-30-2005, 08:00 AM
tayalarun

Oracle database server becomes slow after sone time

Hi,

I am using Oracle 8i(8.1.17) on sun solaris.
After running the database for say 2-3 hours, it becomes slow. My queries takes long time to fetch the results.

If I shutdown the datbase and re-start it, then performance becomes OK.

SGA STATUS AT 30-Dec-2005 04:57:20 PM
=====================================
Object memory : 35721340 bytes (34.07MB)
Shared sql memory : 23490369 bytes (22.4MB)
Cursors memory : 368500 bytes (.35MB)
Shared pool utilization (total) : 71496251 bytes (68.18MB)
Free memory : 95885068 bytes (91.44MB)
Shared pool allocation (actual) : 131072000 bytes (125MB)
Percentage Utilized : 55%
----------------------------------------------------
redo buffer allocation retries : 20
redo log space wait time : 0
----------------------------------------------------
Event Total_wait Total Timeout
buffer busy waits : 7839 1582
db file sequential read : 5138 0

I am able to see that free memory is there.

What could be the reason for slowing down.

It would be appreciable to get a resonse.

Arun
12-30-2005, 08:12 AM
davey23uk

trace the queries, see where the time is being taken and stop guessing
12-30-2005, 09:58 AM
hrishy

Hi

Is teh whole database slow or some queries are slow ?

regards
Hrishy
12-30-2005, 11:53 AM
dbasan

Quote:

Originally Posted by tayalarun

Hi,

I am using Oracle 8i(8.1.17) on sun solaris.
After running the database for say 2-3 hours, it becomes slow. My queries takes long time to fetch the results.

If I shutdown the datbase and re-start it, then performance becomes OK.

SGA STATUS AT 30-Dec-2005 04:57:20 PM
=====================================
Object memory : 35721340 bytes (34.07MB)
Shared sql memory : 23490369 bytes (22.4MB)
Cursors memory : 368500 bytes (.35MB)
Shared pool utilization (total) : 71496251 bytes (68.18MB)
Free memory : 95885068 bytes (91.44MB)
Shared pool allocation (actual) : 131072000 bytes (125MB)
Percentage Utilized : 55%
----------------------------------------------------
redo buffer allocation retries : 20
redo log space wait time : 0
----------------------------------------------------
Event Total_wait Total Timeout
buffer busy waits : 7839 1582
db file sequential read : 5138 0

I am able to see that free memory is there.

What could be the reason for slowing down.

It would be appreciable to get a resonse.

Arun

> Increase the db_cache_size to reduce your buffer waits and you can see some improvement.
> Run the statspack to get statistics abt the performance again.
12-30-2005, 12:04 PM
Cookies

are you using CBO?
If so make sure you are refreshing stale statistics
12-30-2005, 02:39 PM
slimdave

this seems like a clear case for using statspack to determine the major waits on the system after startup and then after three hours.

I don't see how stale statistics could be relevant
12-30-2005, 03:56 PM
marist89

Quote:

Originally Posted by dbasan

Increase the db_cache_size to reduce your buffer waits and you can see some improvement.

:rolleyes: :rolleyes: :rolleyes: Next thing you know you'll be charging $300 and hour and have your own remote-dba service.

Quote:

Originally Posted by slimdave

this seems like a clear case for using statspack to determine the major waits on the system after startup and then after three hours.

Well, sort of. I'd take one while things are "fast", one when things started going "a little slow" and then every 10 minutes until things were "slow".

Quote:

I don't see how stale statistics could be relevant

I agree, unlikely.
12-30-2005, 05:40 PM
dbasan

Quote:

Originally Posted by marist89

:rolleyes: :rolleyes: :rolleyes: Next thing you know you'll be charging $300 and hour and have your own remote-dba service.

Well, sort of. I'd take one while things are "fast", one when things started going "a little slow" and then every 10 minutes until things were "slow".

I agree, unlikely.

Obviously, Thanks marist89.