Oracle Utilities - Using Hidden
Programs, Import/Export, SQL Loader, oradebug, Dbverify, Tkprof and
More
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 their regular Oracle
monitoring scripts on UNIX databases.
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.
