On 32 bit OS we can increase the SGA maximum up to 1700MB but by using /3GB switchs for complete memory addressed on windows server 2003 you can use maximum up to 3GB SGA for Oracle.
Additionally by using /PAE & /NoExecute=AlwaysOn switch you can use more than 3.2GB Physical RAM on 32bit windows server 2003 for all programs other than oracle.
Means
In my test case i have 6 GB Physical RAM and 2Gb paging from OS. So if I use these switches I have maximum 3GB SGA and remaining 3GB+2GB for OS and other windows program.
NOTE
If I don't use these switches we have to face to possible errors
ORA-04030: out of process memory when trying to allocate 123404 bytes (QERHJ hash-joi,kllcqas:kllsltba)
OR
TNS-12518: TNS: listener could not hand off client connection
this error usually comes when your memory full reached or your listener comes in blocked state. you can check listener state by lsnrctl services command.
this error usually comes when your memory full reached or your listener comes in blocked state. you can check listener state by lsnrctl services command.
TEST CASE 1 with: /3GB /PAE /NoExecute=AlwaysOn switchs in boot.ini
Changes made to Boot.ini
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /noexecute=AlwaysOn /3GB /PAE /fastdetect
MEMORY
Physical RAM = 6GB
OS Paging Area = 2GB
SPFILE.ORA PARAMETERS
SGA_MAX_SIZE=1300M
SGA_TARGET=1300M
PGA_AGGREGATE_TARGET =194M
PROCESSES=500
SESSIONS=555
After Taking connection form sqlpls of HR user
SQL> select * from v$resource_limit where resource_name='processes';
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------------------ ------------------- --------------- ---------- ----------
processes 499 500 500 500
SQL> select * from v$resource_limit where resource_name='sessions';
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------------------ ------------------- --------------- ---------- ----------
sessions 503 504 555 555
SQL> select username , module ,count(username) from v$session group by username, module;
USERNAME MODULE COUNT(USERNAME)
------------------------------ ------------------------------------------------ ---------------
SYSMAN OMS 1
HR SQL*Plus 474
DBSNMP emagent.exe 2
SYSMAN OEM.SystemPool 2
SYS sqlplusw.exe 1
SYSMAN OEM.Loader 1
After Taking 503 session new session blocked by listener because of processes parameter limits reached.
So this is because of switchs I can able to make 500 connections
HR 452 connections is from server side (in that case each sqlplus.exe took 14M aprox from memory which is also addressable) . So I can make more connection from different clients.
HR 22 connections is from client side
TEST CASE 2 with: /NoExecute=OptIn switchs in boot.ini
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /noexecute=OptIn /fastdetect
MEMORY
Same as above
SPFILE.ORA PARAMETERS
Same as above
SQL> select * from v$resource_limit where resource_name='processes';
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------------------ ------------------- --------------- ---------- ----------
processes 266 269 500 500
SQL> select * from v$resource_limit where resource_name='sessions';
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------------------ ------------------- --------------- ---------- ----------
sessions 270 273 555 555
SQL> select username , module ,count(username) from v$session group by username, module;
USERNAME MODULE COUNT(USERNAME)
------------------------------ ------------------------------------------------ ---------------
0
SYSMAN OMS 1
HR SQL*Plus 241
DBSNMP emagent.exe 2
SYS sqlplusw.exe 1
SYSMAN OEM.SystemPool 2
SYSMAN OEM.Loader 1
After Taking total 270 session new session still open and processes parameter limits nopt reached.
HR 241 connections is from server side (in that case each sqlplus.exe took 14M aprox from memory also) . So I can make more connection from different clients.
HR 1 connections is from client side
After this I am facing the below error on connection.
As you can see currently 7.05 GB memory is using less than 900 MB from the 1st case. And we can make HR 271 connections and we make 474 connections of HR in 1st case
TEST CASE 3 with: /NoExecute=OptIn /PAE switchs in boot.ini
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /noexecute=OptIn /PAE /fastdetect
MEMORY
Same as above
SPFILE.ORA PARAMETERS
Same as above
Only 249 session can able to connect to database in which 243 is HR
TEST CASE 4 with: /NoExecute=AlwaysOn /PAE switchs in boot.ini
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Windows Server 2003, Enterprise" /noexecute=AlwaysOn /PAE /fastdetect
MEMORY
Same as above
SPFILE.ORA PARAMETERS
Same as above
Only 255 session can able to connect to database in which 225 is HR
SQL> select * from v$resource_limit where resource_name='sessions';
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------------------ ------------------- --------------- ---------- ----------
sessions 255 257 555 555
SQL> select * from v$resource_limit where resource_name='processes';
RESOURCE_NAME CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------------------ ------------------- --------------- ---------- ----------
processes 251 253 500 500
Hope you all understand the game of this switches.
