Memory Utilities

oracle orastack or maxmem

windows orastack:

Oracle supplies the ORASTACK utility to allow customers to modify the default stack size of a thread / session when created in the Oracle executable. When ORASTACK is run against specific executables it alters the part of the binary header that defines the default stack size used by the create thread API. It is not necessary to change the default number of pages committed by the thread because these will be allocated as required from the stack. By reducing the stack of every session created in the Oracle executable, it is possible to achieve a larger user population. In a system with a 1000 users reducing the stack from 1Mb to 500K would release 500Mb of the address space for other allocations or more users.Customers should thoroughly test their applications against databases that have the stack trimmed to less than 1Mb before using the new stack size in their production systems. If the Oracle stack has been trimmed below the size required by the Oracle server side code a stack overrun will occur and the users session will fail, there will be no trace files or entries in the CORE.LOG file. Oracle Support Services does not recommend that customers trim the stack below 500K, although a number of systems run without error ona 300K stack.

ORASTACK must be run against all processes that can create a thread in theOracle executable, use the following syntax :

orastack executable_name new_stack_size_in_bytes

Below are examples of setting the stack to 500K for the main executables :

orastack oracle.exe 500000
orastack tnslsnr.exe 500000
orastack svrmgrl.exe 500000
orastack sqlplus.exe 500000

In addition, if there are programs on the server machine which connect to the database locally [without SQL*NET], run orastack on those as well.

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:oracle9ibinorastack 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:oracle9ibinorastack 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:oracle9ibinorastack tnslsnr.exe 500000

C:Documents and SettingsAdministrator>orastack

The ORASTACK utility was created to allow users to modify

the amount of stack that's reserved and/or committed by

each thread in the Oracle Server. By running ORASTACK on

certain .exe files, the headers of these executables are patched

to reflect the settings specified. Typically, changing the

commit size of each thread's stack is not necessary, since NT

will commit more stack as needed. However, decreasing each

thread's reserved size may be necessary since this size comes

out of the Oracle Server's 2 gigabyte address space. When

thousands of connections or a large SGA are in use, running

out of address space in the Oracle server process [although

still having available physical memory in the machine] is a

possibility.

Some guidelines for orastack:

- run orastack as: orastack executable_name new_stack_size_in_bytes

For example, 'orastack oracle.exe 500000'

'orastack tnslsnr.exe 500000'

- run orastack on BOTH the sql*net listener executable AND the Oracle RDBMS.

In addition, if there are programs on the server machine which connect to

the database locally [no sql*net], run orastack on those as well.

- lower the stack size in small increments as needed. Anything below 300K

or so is most likely not safe. If needed, begin at 500K as a first pass,

and go lower as needed. Note that lowering the stack too much can cause

stack overruns in the shadow and/or background threads.

一 环境：
1 平台： IBM AX360,4G内存 windows 2k advServer sp3 + oracle 816 独占模式

2 内存分配相关参数
..processes................=.1000
..shared_pool_size.........=.240000000
..large_pool_size..........=.614400
..java_pool_size...........=.32768
..db_block_buffers.........=.90000
..db_block_size............=.4096
..log_buffer...............=.163840
..log_checkpoint_interval..=.10000
..sort_area_size...........=.65536
..sort_area_retained_size..=.65536
..open_cursors.............=.100
..job_queue_processes......=.4
..job_queue_interval.......=.10
..max_dump_file_size.......=.10240

3 最大并发用户数：850个左右

二 故障现象：
....当用户数达到一定的数量时(700多)客户端连接服务器时报ora-12560错误，紧跟着报ora-03114错误，不能连接到服务器。此时已连接到服务器的用户能正常访问数据库。
....首先查看警告日志文件，未见到明显错误信息。
....查看listner.log文件，发现如下信息(很多个类似的错误记录，摘两个上来)
.........................
02-JUL-2003 10:30:09 * (CONNECT_DATA=(SID=ORCL)(CID=(PROGRAM=***.EXE)(HOST=*******)(USER=*****))) * (ADDRESS=(PROTOCOL=tcp)(HOST=*******)(PORT=1136)) * establish * ORCL * 12500
TNS-12500: TNS:listener failed to start a dedicated server process
.TNS-12540: TNS:internal limit restriction exceeded
..TNS-12560: TNSrotocol adapter error
...TNS-00510: Internal limit restriction exceeded
....32-bit Windows Error: 8: Exec format error
02-JUL-2003 10:30:10 * (CONNECT_DATA=(SID=ORCL)(CID=(PROGRAM=***.EXE)(HOST=*****)(USER=***))) * (ADDRESS=(PROTOCOL=tcp)(HOST=******)(PORT=1203)) * establish * ORCL * 12500
TNS-12500: TNS:listener failed to start a dedicated server process
.TNS-12540: TNS:internal limit restriction exceeded
..TNS-12560: TNSrotocol adapter error
...TNS-00510: Internal limit restriction exceeded
....32-bit Windows Error: 8: Exec format error
.....................

此时，原来已连接上服务器的用户还能正常使用。查看此时session达到760多个。
....重启oracle服务后，能连接新的用户数，但当并发用户数达到750个以上时，再次报同样的错误

三 原因分析：
....系统资源耗竭，意味着系统分配给oracle的内存用尽了。虽然我们有4G的物理内存，但正常情况下系统只能给oracle分配2G的内存，这2G的内存中，包括了SGA、PGA等oracle需要使用的全部内存。在独占模式下，每一个session将单独分配2M左右的内存。在本例中，SGA分配了约600M，按每一个用户分配2M内存计算，连接数达到750个时，总分配内存已达到2G，将不能再增加新的连接数。如果要解决这个问题，在不做大的调整的前提下，要么减小SGA大小，要么减小为每一个会话分配的内存大小，以能连接更多的用户。

四 解决过程：
....查阅了oracle文档，文档里提出来了几个解决的办法：
1 重置init.ora参数文件，调小以下四个参数的值：
....short_area_size
....hash_area_size
....bitmap_merge_area_size
....create_bitmap_area_seze
....open_cursone
2 调小SGA的大小
3 减小oracle Job队列数量(job_queue_processes)和并发队列数(parallel_max_servers)
4 重置并减小会话/线程使用的堆栈大小
5 将oracle改为mts模式
6 更换操作系统为windows NT 企业版
7 使用intel的ESMA硬件支持，即使用大内存
..1) 在intel系统上使用 /3G 开关
..2) 使用PSE36内存

....结合本实例的具体情况，决定调整的主要目标为减小用户的PGA大小。
....构成PGA的主要内容有short_area_size, hash_area_size, open_cursone, 以及oracle 堆栈和TNS 堆栈。在本实例中，排序区为64K，hash区为128K(缺少值)，打开的游标数与应用有关，不能随便减小了，然而oracle堆栈和TNS堆栈都是1M，却有较大的减小的余地。因此，调整的目标定为减小这两个堆栈的大小。
....使用orastack 命令来减小这两个堆栈的大小：
D:oracleora81bin>orastack oracle.exe 500000
Couldn't open file with CreateFile()
GetLastError() == 32

....停止oracle服务和TNS服务，再运行以上命令
D:oracleora81bin>orastack oracle.exe 500000

Dump of file oracle.exe

Current Reserved Memory per Thread = 1048576
Current Committed Memory per Thread = 4096

New Reserved Memory per Thread = 500000

D:oracleora81bin>orastack tnslsnr.exe 500000

Dump of file tnslsnr.exe

Current Reserved Memory per Thread = 1048576
Current Committed Memory per Thread = 4096

New Reserved Memory per Thread = 500000

重新启动oracle服务和TNS服务，打开数据库，用户连接到服务器，经测试，用户数到1350以上时数据库仍然运行正常，解决了本实例存在的问题。

五 小结
....事实上，正如oracle文档所指出的那样，要增加用户连接数的途径很多，除了减小用户堆栈之外，还可以减小SGA，或者是更改成MTS方式，或者是使用第三方工具增加oracle可用内存。本人前面小结过如何让oracle在32位的windows操作系统上使用超过2G内存的方法( http://www.itpub.net/showthread.php...15&pagenumber=1 )，在本安全应用中，宜将两者(减小用户堆栈与增加oracle可用内存)结合起来使用，以提高数据库性能。但是，这种方式下，同样不可能无限制地增加用户连接数。要想使用户连接数达到更大，则应使用MTS方式。

unix maxmem:

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:

$ 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%dn","# 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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9599/viewspace-472910/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9599/viewspace-472910/