Oracle Utilities - Using Hidden
Programs, Import/Export, SQL Loader, oradebug, Dbverify, Tkprof and
More
Memory Utilities
Utilities exist for both Windows and UNIX systems that help DBAs deal
with memory issues. The orastack utility exists on Windows systems,
while the maxmem utility can be helpful in UNIX. The orastack utility is
only available to Oracle databases on Windows platforms. It is used
primarily to address the ORA-04030 error on Windows servers. The oerr
output for the ORA-04030 error is:
04030, 00000, "out of process memory
when trying to allocate %s bytes (%s,%s)"
// *Cause: Operating system process private memory has been exhausted
// *Action:
This error occurs when Oracle is trying to allocate memory for the
session but none exists. Windows NT has a limitation of 2 GB of RAM
allocated for user processes and a maximum of 2 GB for the system. The
memory counter reaches the maximum addressable memory at 2 GB, and the
ORA-04030 error will occur.
To conserve memory, the amount allocated for each connection process
could be reduced as it is established, using the sort area size
parameter of the instances. The orastack utility can accomplish this.
Since it functions strictly at the operating system level, there is
nothing that can be done inside Oracle to limit the memory obtained upon
a user connection.
The syntax of the command is the orastack keyword followed by the
executable file name:
C:\oracle9i\bin\orastack oracle.exe
Current Reserved Memory per Thread = 1048576
Current Committed Memory Per Thread = 4096
When the command is executed without specifying a new size (as above),
the utility simply displays the memory usage and does not change
anything. The reserved memory is that which is allocated and not backed
up by a data store. The committed memory is that which is allocated and
supported by a data store of some sort (pagefile, physical memory
pages).
Notice the “Reserved Memory per Thread” of 1MB above. Each connection to
the database will instantly grab a megabyte of RAM. The Oracle
executable cannot be active when the command to reduce the size of the
stack is executed. Once the executable is inactive, the orastack utility
can be used to safely reduce the memory acquired on connection.
C:\oracle9i\bin\orastack oracle.exe 500000
After the command is executed, each session that connects to the
database will consume 500K of RAM on connection. 500K should be the
absolute lowest value to set this parameter.
The resetting of this value for oracle.exe applies only to local
(non-SQL*Net) connections. For connections that are initiated from the
listener, the stacks on the tnslsnr.exe executable can be reduced by
running orastack against tnslsnr.exe. This is where most connections to
the database will originate.
C:\oracle9i\bin\orastack tnslsnr.exe 500000
The above is an excerpt from Oracle Utilities - Using
Hidden Programs, Import/Export, SQL Loader, oradebug, Dbverify, Tkprof
and More by Rampant TechPress.
For more details on Oracle
utilities, see the book "Advanced
Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan.
You can buy it direct from the publisher
for 30% off directly from
Rampant TechPress.
