maxmem

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:

$ maxmem

Memory starts at: 141728 ( 229a0)

Memory ends at: 268025856 ( ff9c000)

Memory available: 267884128 ( ff79660)

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:

SQL> connect scott/tiger@ASG920;

Connected.

$ maxmem

Memory starts at: 141728 ( 229a0)

Memory ends at: 267075583 ( feb3fff)

Memory available: 266933855 ( fe9165f)

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.

$ maxmem | awk '$2 ~ /available/ {printf("%s%d\n","# Future Sessions: ",$3/1024/1024)}'

# Future Sessions: 251

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 their regular Oracle monitoring scripts on UNIX databases.