becks wrote:There is NO direct relationship between amount of RAM & size of the datafiles.
I have a COTS product which uses 32-bit Oracle database. Currently, there 40+GB of datafiles. While monitoring the database server, I realised the number of processes hit>80 which leads to browsing my COTS product's performance deteroriated. People have recommend to increase the PGA and SGA, it seems better.
Just wondering, how much datafiles can a 32-bit database handle? Cause the max memory that i can assign to a 32-bit server is only 4GB
becks wrote:How much disk space is there? A 32bit database refers to using 32bit addressing for memory. Not for accessing data on disk.
Just wondering, how much datafiles can a 32-bit database handle?
Cause the max memory that i can assign to a 32-bit server is only 4GBIncorrect. Yes, a 32bit address limits the addressing to 4GB of memory. However, there are kernel memory management extensions that enables one to address more memory. These existed in some form or another even back in the old MS-DOS days via drivers such as himem.sys and emm386.exe. Today this is done via Physical Address Extension (PAE) - a feature implemented by 32bit Intel and AMD processors.
becks wrote:What is the actual root cause?
Understand datafiles size is limited the disk space, was thinking if a 32-bit database can process that amount of datafiles w/o increasing the RAM. I asking this because when the database is first setup, we do not experience any performanace issue. Now we are experiencing the deteroriate and the situation is better after increasing the SGA and PGA.
I believe increasing SGA and PGA is related to the RAM of the database server.Correct.
So was thinking that we have to keep increasing SGA and PGA when the size of datafiles increased again.As already mentioned - there is no direct relationship between the number of data files and the SGA. PGA relates to the PL/SQL engine (private process memory) - and this too has nothing to do with the number of data files.
So thinking that 4GB is limited by 32-bit server, there will be a MAX memory that i can increas the SGA and PGA.Perhaps you should tell the forum what the actual performance problem is - instead of discussion a solution (increasing the SGA/PGA) that has no direct bearing on the number of data files used by the database.
becks wrote:What processes. Job processes? Dedicated server processes? Parallel processing processes? Advance Queue processes? Have you identified these processes?
okie. The root cause is as my first post, the number of process running is >80 in my database server.
And i have suggestions on increasing the SGA.There's only one reason that springs to mind immediately for as increased number of users sessions impacting the SGA and requiring a larger SGA. Shared Servers. The UGA (user session memory on the server) resides in the SGA for shared servers. For dedicated servers, this resides in the private process memory (aka PGA) of the actual server process, and not in shared server memory.
My inital thinking was that the high number of process is due to the hugh datafiles size.Not really... Number of I/O processes (assuming DB writer and slave processes) are dependent on amount of I/O that needs to be done. Not the number of data files.
becks wrote:Old o/s. On Windows, Oracle uses the standard Win32 threading model. So all Oracle database instance "processes" (which would be physical process images on a Linux/Unix kernel), will be a thread in the oracle.exe process image.
I using windows server 2003 standard edition.
Why using 32-bit, this is the question that i been asking but this server have been around for some years so do not know the exact reason.You should also be asking why Windows? And why not Linux?