This content has been marked as final. Show 6 replies
That sounds like inadequate memory allocation on the server. As usage goes up, memory requirement and allocation increases. A point is reached where the server starts swapping memory pages (including the SGA) to disk, at which all operations appear to be slow.
Is yours a 32bit installation ? If so, you could look at a AWE or VLM to allocate more memory.
Hemant K Chitale
To dig deeper into your database, you need to grab some statistics on database performance during the periods of slowdown. You can't license the Diagnostic pack to use AWR for Standard Edition, but you can install the free Statspack utility and start taking snapshots for analysis.
If you want a silver bullet, there is one very common cause of database slowdown (or even crashes) on Wintel machines - the virus scanner. If your server has a virus scanner installed, please create a scan exception so that it excludes all the oracle database files (data files, temp files, control files, etc). If there is a scheduled full system scan, check that it is not scheduled during business hours.
Having said all that, your server seems a very small machine if it only has 2GB. Even if the fault is not Oracle's some other memory-leaking software (welcome to Windows) running on the same machine can quickly cause problems when the machine has been up for extended periods without reboots. In my experience, many sysadmins schedule periodic reboots for Win Server 2003 machines.
New_To_Oracle_7912 wrote:You are running Windows 2003 on a machine with only 2gb of ram? I didn't know you could even purchase such a machine any more. Good grief! LAPTOPS come standard with 4gb, and mine has 8gb. Servers typically have much more.
I am using standard edition 1 (Oracle Edition) , Minimum 40+ members are going to be connected into that database.
But we are facing the problem,
1. Daily once the database gets slow,
for this issue, we are restarting the server so that the problem is getting solved temporarily.
Current Hardware spec:
OS: Server 2003
So please suggest me the Permanent solution for this problem
So please suggest me the Permanent solution for this problemWhat is/are your SGA_TARGET / SGA_MAX_SIZE / MEMORY_TARGET / MEMORY_MAX_SIZE / PGA_AGGREGATE_TARGET ?
2GB is quite insufficient for 40 concurrent sessions attaching to a database instance.
If yours is a 32bit install you should increase the RAM and look at AWE or VLM on Windows.
Hemant K Chitale