-
hI, Sam:
Here is the details. Now the processes=100 and sga is about 230MB. We want to test the parameters for oracle. We run a programm and let 500 users to connect to the database at same time. But the 500 users log in by same username/password.
(1). we need to increase the processes to let the users in
(2). we also need to increase the memory
I have no idea about how many memory should be allocated. Are there some rules that I should follow? Or I should check some system tables to define the size for additional users? What is the system tables i should check?
Thanks.
-
O.K, you could do an analysis using the utlbstat and utilestat packages to evaluate the performance. If you set the instance to be of MTS type then, you wouldn't be opening 500 dedicated connections. You could also use the oracle tuning pack to analyze the performance and then tune accordingly. Its is a Windowbased tool that you could use to do your tuning. If you are a unix person, then you could use the
$ORACLE_HOME/rdbms/admin/statspack.doc
utlbstat.sql
utlestat.sql
to view the performance and tune further. Before you use these stats packs, turn on the TIMED_STATS... = true and bounce the instance as
SVRMGRL> start force
Then run the statspack during the peak performance period and then analyze the output and modify the parameters accordingly. On the other hand if you were to use the oracle tuning pack, it would propose changes to the instance, storage setup and etc. You could down load this from the technet site.
Some suggestions are:
keep a good amount of db_buffer_size and an avg. of shared_poo_size. You coud leave the large_pool as it is unless and other wise you are going to use the oracle intermeida or etc. The reason to have a good buffer_size is to reduce the amount of disk reads. Go with your setup initially and then run the statspack and check where to increase what.
First just go with around 230MB and see how the performance is and then you could go ahead and increase. For a 500 process and 2450 concurrent user instance, I only have an SGA of 200MB. So I beleive 230MB should be well enough. Just increase the # of processes and add a good # of RBS. you should be able to cruse.
Tips: set the profile for the user and set the idle time paramter
this would help you to prevent the system running out of
sessions.
Enforce the limit once by,
ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;
set the max number of open cursor limit in your init.ora file.
Sam
-
Thanks very much for the helps. Good luck to everybody.
-
Hi
I think MTS with Connection pooling will be a good choice to try out instead of increasing the processes. Following is an example for the mts_dispatcher setting . MTS_DISPATCHERS = "(PRO=TCP) (CON=10) (DIS=4) (POO=ON) (TIC=4)(SESS=25"
Please comeback if you want more info on setting these parameters.
Santhoshkumar M Nair
Certified Oracle8i Dba
-
For the short term we just increase the processes value. But soon i need to set up the MTS. I am very glad if you can give me some info. on it . I have no idea about configure MTS now.
Thanks very much.
-
Hi
Following is a small note for some info on mts
MTS Configuration
==============
What is MTS?
When we use MTS configuration, multiple users share a specific number of shared processes. As we use a limited number of processes on the server, we can reduce the amount of memory needed and we can support a large number of user populations. Even if we use MTS certain connections may still be dedicated such as connections from internal user, database startup, shutdown etc.
NOTE: When MTS is used session information is placed in the SGA for each user. So a larger SGA is required.
How MTS works
When a database is configured for MTS, oracle will automatically adjusts SGA to handle the MTS processes at startup. In SGA a single request queue and multiple dispatcher response queues will be created as per the parameters. A single request queue handles all incoming requests and the response queues are created for each dispatcher processes and assigned it to the client.
These are steps involved in an MTS connection.
1. Whenever a client makes a request to the listener the listener will redirect the request to a dispatcher and client connects to the dispatcher.
2. The dispatcher places the SQL commands from the client into the request queue. The shared server process monitors the request queue.
3. If a free shared server process is available the request will be handled and the response will be placed in the response queue of the dispatcher.
4. The dispatcher retrieves the response from the queue and returns it to the client.
5. The connection between the client and dispatcher is maintained for the entire session, even if the client is idle.
Initialization parameters used for MTS
LOCAL_LISTENER
This parameter is used to specify the service name of the listener or listeners with which the dispatcher process must register. Make sure that the listener is started before starting the database with MTS. Create an entry in tnsnames.ora and use that name as the value of local_listener. This parameter can also be specified using a LISTENER argument of MTS_DISPATCHERS parameter.
E.g. LOCAL_LISTENER = testmts
-----------------------------------------------------------
MTS_SERVICE
A unique name to identify the MTS Service. Usually the SID
--------------------------------------------------------------
MTS_DISPATCHERS
The number of dispatchers initially started. It can be configured for various protocols. This parameter accepts a number of other sub parameters also. A list of those is given below;
PROTOCOL or PRO or PROT - The protocol used (in NT only TCP/IP can be used)
DISPATCHERS or DISP - The total number of dispatchers initially started.
LISTENER or LIS - name of the local listener (same as local_listener , only one is required).
SESSIONS or SESS - Maximum number of user processes
MULTIPLEX or MULT - Enable connection concentration which is used along with Oracle connection manager. It cannot be used when pool is specified. Parameters are same as POOL. The difference between POOL and MULT are POOL is used at the database level and MULT is used at the MIDDLE TIER. Additional setup with connection manager is required.
POOL or POO - Used for connection pooling (Can be used with Incoming only Outgoing only, BOTH etc.). Using this feature each dispatcher process can be shared by multiple users. If its not used then if all dispatchers are busy then a user should wait till it gets a free dispatcher. If POO is used and all disp. are busy then it waits till a session becomes idle .If it find an idle connection then oracle will disconnect it temporarily and use it for the new user instead of waiting for a free dispatcher.
CONNECTION or CON or CONN - maximum number of network connections that can be established for each dispatcher. This should be less than the SESS parameter. The default is 1024 for Sun Solaris and Windows NT
TICKS or TIC or TICK - this is a 10 second intervel for waiting to establish a connection. The default timeout for a connection when all dispatchers are busy is 10 ticks (100 seconds).
----------------------------------------------------------------------------------------------------
MTS_SERVERS
The total number of shared server process started at the OS level.
-----------------------------------------------------------------------------------------
MTS_MAX_SERVERS
Maximum number of servers that can be started
----------------------------------------------------------------
MTS_MAX_DISPATCHERS
Maximum number of dispatchers that can be started.
--------------------------------------------------------------------
A sample set of entries in initSID.ora is given below
LOCAL_LISTENER = testmts
MTS_SERVICE = testSID
MTS_DISPATCHERS = "(PRO=TCP)(DISP=2)(CON=10)(POO=ON)(TIC=5)(SESS=20)"
MTS_SERVERS = 5
MTS_MAX_SERVERS = 10
MTS_MAX_DISPATCHERS = 15
Check the services command from lsnrctl to verify that MTS is working .Use the following views for additional information
v$DISPATCHER
v$CIRCUIT
v$SHARED_SERVER
v$MTS
v$QUEUE
v$SESSION
Reference: Oracle 8i Network administers guide (Oracle manual). METALINK
HTS
Santosh
