+++

系统环境：

ORACLE V10.2.0.1.0 - Production vsnsta=0

vsnsql=14 vsnxtr=3

Windows NT Version V5.2 Service Pack 2

CPU                 : 16 - type 586, 2 Physical Cores

Process Affinity    : 0x00000000

Memory (Avail/Total): Ph:7678M/8181M, Ph+PgF:9751M/10032M, VA:1938M/2047M

+++

症状：

系统设置processes=350 (sessions=390)，但当会话数到250左右，新连接就会出现错误：TNS-12518: 监听程序无法分发客户机连接(CS客户端程序提示：未连接到oracle)……

相关讨论：

http://www.itpub.net/thread-1286246-1-1.html

http://www.itpub.net/thread-1285545-1-1.html

+++

联系oracle support，提供解决方案如下：

=== ODM Proposed Solution ===

1. Enable 4GB RAM Tuning (4GT)

OR

2. On Windows 2003 we can address more than 4gb of memory, via PAE and USE_INDIRECT_DATA_BUFFERS

OR

3. Decrease the size of the database SGA

OR

4. Reduce oracle per thread/connection stack size from default.

OR

5. Change the connection model from dedicated to shared server.

   === ODM Proposed Solution Justif ===

According to Note 371983.1 solution,

32-bit Windows Server 2003 (Enterprise and Data center editions) include a feature called 4GB RAM Tuning (4GT).

USE_INDIRECT_DATA_BUFFERS controls how the system global area (SGA) uses memory.

(Decrease parameters like shared_pool_size, sort areas parameters, large_pool_size, etc.) giving memory back to the O/S, allowing more connections to be established.

Oracle supplies the ORASTACK utility to allow customers to modify the default stack size of a thread / session when created in the Oracle executable.

Switching to Shared server should allow for more scalability.

+++

最终决定使用4. Reduce oracle per thread/connection stack size from default.

To implement the solution, please execute the following steps:

1. 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)

2. Retest the issue.

3. Migrate the solution as appropriate to other environments.

+++

修改前测试：

昨天修改processes=350,测试连接可以达到349，之前未报错，

怀疑单纯测试占用内存较低，无法重现错误

今天修改processes=450,测试连接

SE_Q PR_Q

356 362

报错：ORA-12518: TNS: 监听程序无法分发客户机连接

修改线程

关闭数据库及服务

修改系统线程

E:oracleproduct10.2.0db_1BIN>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

E:oracleproduct10.2.0db_1BIN>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

测试连接

SQL> select * from

  2  (select count(*) as se_q from v$session) a,

  3  (select count(*) as pr_q from v$process) b;

      SE_Q       PR_Q

---------- ----------

       447        449

可以达到449，再连接则提示：ORA-12516: TNS: 监听程序无法找到匹配协议栈的可用句柄

(当前oracle.exe进程占用内存为1108928K)

增加线程，再测试连接

关闭数据库及服务

修改线程：

E:oracleproduct10.2.0db_1BIN>orastack oracle.exe 2000000

Dump of file oracle.exe

Current Reserved Memory per Thread  = 500000

Current Committed Memory per Thread = 4096

New Reserved Memory per Thread = 2000000

E:oracleproduct10.2.0db_1BIN>orastack tnslsnr.exe 2000000

Dump of file tnslsnr.exe

Current Reserved Memory per Thread  = 500000

Current Committed Memory per Thread = 4096

New Reserved Memory per Thread = 2000000

启动数据库及服务

测试连接

SQL> select * from

  2  (select count(*) as se_q from v$session) a,

  3  (select count(*) as pr_q from v$process) b;

      SE_Q       PR_Q

---------- ----------

       237        239

可以达到239，再连接则提示：ORA-12518: TNS: 监听程序无法分发客户机连接

(当前oracle.exe进程占用内存为916620K)

+++

总结：