Environment 1: 1 Platform: IBMAX360, 4G memory windows2kadvServersp3 + oracle816 exclusive mode 2 memory allocation parameters .. processes ................. 1000 .. shared_pool_size .......... 240000000 .. large_pool_size ........... 614400 .. java_pool_size ............ 32768 .. db_bl
Environment 1: 1 Platform: IBMAX360, 4G memory windows2kadvServersp3 + oracle816 exclusive mode 2 memory allocation parameters .. processes ................ =. 1000 .. shared_pool_size ......... =. 240000000 .. large_pool_size .......... =. 614400 .. java_pool_size ........... =. 32768 .. db_bl
Environment 1:
1 Platform: IBM AX360, 4G memory windows 2 k advServer sp3 + oracle 816 exclusive mode
2. Memory Allocation Parameters
... 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. Maximum number of concurrent users: around 850
Ii. fault description:
When the number of users reaches a certain number (more than 700) Client connection times ora-12560 error, followed by a ora-03114 error, cannot connect. Users connected to the server can access the server normally.
... First, view the warning log file. No obvious error information is displayed.
... View the listner. log File and find the following information (many similar error records are extracted)
.........................
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
.....................
At this time, the user who has already connected to the server can still use normally. Check that there are more than 760 sessions.
... After the oracle service is restarted, new users can be connected. However, when the number of concurrent users reaches more than 750, the same error is reported again.
Cause 3:
... System resource depletion means that the memory allocated to oracle by the system is exhausted. Although we have 4 GB physical memory, normally the system can only allocate 2 GB memory to oracle, which includes all the memory needed by oracle, such as SGA and PGA. In the exclusive mode, each session will allocate about 2 MB of memory separately. In this example, SGA allocates about 600 mb of memory, which is calculated based on the memory allocated by each user. When the number of connections reaches 750, the total allocated memory reaches 2 GB, you cannot add new connections. To solve this problem, you can reduce the SGA size or the memory size allocated for each session without making major adjustments to connect more users.
4. Solution Process:
... Read the oracle documentation, which provides several solutions:
1. Reset the init. ora parameter file and reduce the values of the following four parameters:
... Short_area_size
... Hash_area_size
... Bitmap_merge_area_size
... Create_bitmap_area_seze
... Open_cursone
2. Adjust the SGA size
3. Reduce the number of oracle Job Queues (job_queue_processes) and the number of concurrent queues (parallel_max_servers)
4. Reset and reduce the size of the stack used by the session/thread.
5. Change oracle to mts mode.
6. Change the OS to windows NT Enterprise Edition.
7. Use intel ESMA hardware support, even if large memory is used
.. 1) Use/3G switch on intel System
.. 2) use memory limit 36
... According to the actual situation of the instance, the main goal of the adjustment is to reduce the user's PGA size.
... The main contents of PGA are short_area_size, hash_area_size, open_cursone, And the oracle stack and TNS stack. In this instance, the sorting area is 64 K, and the hash area is 128 K (the value is missing). The number of opened cursors is related to the application and cannot be reduced at will, however, the oracle stack and the TNS Stack are both 1 MB, but there is much room for reduction. Therefore, the goal is to reduce the size of the two stacks.
... Use the orastack command to reduce the size of the two stacks:
D: \ oracle \ ora81 \ bin> oracle oracle.exe 500000
Couldn't open file with CreateFile ()
GetLastError () = 32
... Stop the oracle service and TNS service, and then run the preceding command
D: \ oracle \ ora81 \ bin> oracle 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: \ oracle \ ora81 \ bin> 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
Restart the oracle service and TNS service, open the database, and connect the user to the server. After testing, the database still runs normally when the number of users reaches more than 1350, which solves the existing problems of this instance.
Summary
.... As mentioned in the oracle document, there are many ways to increase the number of user connections. In addition to reducing the user stack, you can also reduce the SGA or change it to the MTS mode, or use a third-party tool to increase the available oracle memory. I have previously summarized how to make oracle in a 32-bit windows operating system to use more than 2 GB memory (http://www.itpub.net/showthread.php...15&pagenumber=1) method, in this security application, we recommend that you use both (reduce user stacks and increase available oracle memory) to improve database performance. However, in this way, it is also impossible to increase the number of user connections without limit. To increase the number of user connections, use MTS.
