Oracle Orastack Utility
Oracle Tips by Burleson Consulting

Using the Orastack Utility

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:

 

 

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:

 

 

When the command is executed without specifying a new size, as shown 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 like pagefile or physical memory pages.

 

Notice the “Reserved Memory per Thread” of 1MB in the previous command.  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. 

 

 

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.

 

 

The orastack utility can be used on any executable that initiates database connections.

Even though orastack is only available for Windows systems, other memory utilities exist on the UNIX platform.  The maxmem utility can be used on UNIX systems to determine when the ORA-04300 error will occur.  Utilizing this utility, the DBA can calculate the number of sessions that can connect to the database before the ORA-04030 error message is encountered.

 

The maxmem utility is a simple program with no command-line options:

 

 

The maxmem utility returns three data items, although only one is really useful to the DBA.  “Memory available” indicates the number of bytes of RAM that are available.  This is critical to know since ORA-04030 errors will occur when this number is less than 1,000,000 (1 MB).

 

If another session connects to the database, the maxmem utility will reflect a reduction in the memory available:

 

 

Based on the delta in the memory available, the memory consumed by this one connection to the database is 950273 bytes, roughly 1 MB.  Subsequent tests indicate that memory allocated for each connection may vary, but it is always close to 1 MB.  Given that a session on this host will grab 1 MB of RAM, awk can be used as part of the maxmem command to indicate the number of sessions it will be able to support.

 

 

This command will display the third field, divided by 1 MB, of any output line that contains “available” in the second field.  This number will represent the number of additional sessions that can be handled by the database, assuming that each will take 1 MB.  Based on the above output, the database can handle approximately 251 database connections before an Oracle memory error occurs.  This number is an approximation based on the earlier benchmark that measured 1 MB for the connection.  The DBA should include this command as part of his regular Oracle monitoring scripts on UNIX databases.

Conclusion

The subjects covered in this chapter were several of the main server-side Oracle utilities, including those used to start and stop the database, detect corruption, manage Oracle files, manage OS memory allocation for database connections, and manage processes.

 

In the next chapter, utilities used with managing SQL will be the topic.

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.