An environment:
1 Platform: HP, 2G memory Windows 2K AdvServer SP3 Oracle 817 exclusive mode
2 memory allocation related parameters
..processes ............... =. 350
..shared_pool_size ......... =. 314572800
..large_pool_size ........ =. 614400
..java_pool_size ......... =. 0
..db_block_buffers ......... =. 67372
..db_block_size ........... =. 16384
..log_buffer ............... =. 163840
..log_checkpoint_interval .. =. 10000
..sort_area_size ......... =. 52428800
..sort_area_retained_size .. =. 52428800
..open_cursors ........... =. 300
..job_queue_processes ... =. 4
..job_queue_interval ....... =. 10
..max_dump_file_size ....... =. 10240
3 Maximum number of concurrent users: 100
Two faults:
.... When the number of users reaches a certain number (more) Client connection server Times TNS-12500 error, the user who is connected to the server can access the database.
.... First check the warning log file and not see the obvious error message.
.... View the listner.log file, find the following information (many similar error records, pick two up)
.......................
02-JUL-2003 10:30:09 * (connection_data = (SID = ORCL) (CID = (Program = *******) (user = *****) ))) * (Pecol = 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 = ***. EXE) (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 point, users who have been connected to the server can also be used normally. See if the session reaches more than 50.
.... After restarting the Oracle service, you can connect the number of new users, but when the number of concurrent users reaches more than 15, the same error is reported again.
Three causes analysis:
.... System resource is exhausted, meaning that the system is allocated to Oracle's memory. Although we have 2G physical memory, the system can only assign 1.3G memory to Oracle under normal circumstances. In this 1.3G memory, including SGA, PGA and other Oracle needs to use all memory. In exclusive mode, each user will separately allocate 50M (sort_area_size) memory. In this example, the UGA assigns about 2G, assigns 50M memory computing according to each user, and the total distribution memory has reached 2G, and the number of new connections will not be added. If you want to solve this problem, if you don't make a big adjustment, you either decrease the SGA size, or reduce the memory size assigned to each session to connect to more users.
Four resolution process:
.... Review the Oracle documentation, a few solutions have been raised in the document:
1 Reset the init.ora parameter file, adjust the value of the following four parameters:
.... sort_area_size
.... hash_area_size
.... bitmap_merge_area_size
.... CREATE_BITMAP_AREA_SEZE
.... Open_CURSOONE
2 Reduce the number of Oracle Jobs (JOB_QUE_PROCESSES) and concurrent queues (Parallel_max_servers)
3 Reset and reduce the stack size used by session / threads
4 change Oracle to MTS mode
.... Combined with the specific situation of the present example, the main target of the adjustment is determined to reduce the user's PGA size.
.... The main content of the PGA has sort_area_size, hash_area_size, open_cursor, and Oracle stack and TNS stack. In this example, the sort zone is 64K, the HASH region is 128K (missing value), the number of open cursors is related to the application, but the Oracle stack and the TNS stack are 1m, but there is a big reduction Open. Therefore, the adjustment target is determined to reduce the size of these two stacks.
.... Use an oraStack command to reduce the size of these two stacks:
D: / oracle / ora81 / bin> Orstack Oracle.exe 500000
COULDN '' 'file with createfile ()
GetLastError () == 32
.... Stop Oracle Services and TNS services, run the above command
D: / oracle / ora81 / bin> Orstack 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> Orstack TNSLSnr.exe 500000
Dump of file tnslsnr.exe
Current reserved memory per thread = 1048576current committed memory per thread = 4096, @ COMMITTED MEMORY PER THREAD
New reserved memory per thread = 500000
Restart the Oracle service and TNS service, open the database, the user is connected to the server, and the database is still running normally when the number of users to 50 or more, and the problem exists in this instance.
Five-small knot
.... in fact, as indicated by Oracle documents, there are many ways to increase the number of users, in addition to reducing the user stack, can also reduce SGA, or change into MTS mode, or use third parties Tools increase Oracle available memory. In this secure application, it is appropriate to combine both (reducing user stacks with increasing Oracle) to improve database performance. However, in this way, it is also impossible to increase the number of user connections without limitation. To make the user connection to a larger, you should use the MTS mode.
