-
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
-
trace the queries, see where the time is being taken and stop guessing
-
Hi
Is teh whole database slow or some queries are slow ?
regards
Hrishy
-
 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.
"What is past is PROLOGUE"
-
are you using CBO?
If so make sure you are refreshing stale statistics
- Cookies
-
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
-
 Originally Posted by dbasan
Increase the db_cache_size to reduce your buffer waits and you can see some improvement.
Next thing you know you'll be charging $300 and hour and have your own remote-dba service.
 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".
I don't see how stale statistics could be relevant
I agree, unlikely.
Jeff Hunter
-
 Originally Posted by marist89
 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.
"What is past is PROLOGUE"
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|