Results 1 to 12 of 12
  1. #1
    Join Date
    Feb 2002
    Posts
    11

    Unanswered: Multithreaded vs Dedicated mode

    Hello,

    We are using Netscape Application Server 4.0 sp6 to connect to Oracle 8.1.7 using ORACLE_OCI. Should Oracle be running in Dedicated or
    Shared (Multithreaded ) mode. Will there be a performance problem
    if I use Dedicated mode. Please let me know. Also how could one
    change from dedicated to multithreaded mode without a reinstall of the database. Any help on this would be really appreciated.

    Thanks,
    Amit

  2. #2
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi,
    You don't have to reinstall.
    I changed from dedicated to shared about 5 months ago and haven't had any problems - there are a few things you should note.
    All users connecting in will use the shared pool for sort operations and the like so you should setup large_pool_size = x as the users will now use this for their sorts etc. and this will prevent ORA-XXX unable to allocate yyy bytes of shared memory.
    Also, what happens now is that you start your listener and then start your server (at this point the server registers with the listener).

    I'll just double check the changes I made in the initsid.ora and tnsnames.ora and post them.
    How many users do you tend to have logged on max?
    Rgs,
    Breen.

  3. #3
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    In the initsid.ora:

    large_pool_size = X

    MTS_DISPATCHERS=
    "(ADDRESS=(PROTOCOL=TCP)(HOST=xx.yy.zzz))(DISPATCH ERS=4)"
    LOCAL_LISTENER= "(ADDRESS = (PROTOCOL = TCP)(HOST =
    xx.yy.zzz)(PORT = 1521))"

    ------------------------
    In the listener.ora:

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xx.yy.zzz)(PORT = 1521))
    )
    )
    )

    ------------------------
    Average user connection will require ~ 160k for transactions.
    Assuming y connections = 160*y K that user connections will need in memory for sorting etc.
    Create a large_pool_size = 160*y K, the user connections can share this memory allocation for their sorts etc.
    Previously it would use the UGA in the shared_pool.
    There is also a large_pool_min_aloc variable which will allocate a minimum of xk for each connection from the large_pool.

    Hope this helps,
    Just stop server,
    Stop, start listener
    Start server
    Bingo I hope
    Breen.

  4. #4
    Join Date
    Feb 2002
    Posts
    11
    Hi Breen,

    Thanks for your reply.Well the application is used by some 10000 users but I think there would be something like 1000 users logged in together.
    What are the configuration parameters we must modify ? Please let us know. Thanks for your help.

    Regards,
    Amit

  5. #5
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi Amit,
    OK - make a backup copy of both your initsid.ora and listener.ora before doing this.

    Make the following changes to your initsid.ora

    large_pool_size = 160M

    MTS_DISPATCHERS= "(ADDRESS=(PROTOCOL=TCP)(HOST=xx.yy.zzz))(DISPATCH ERS=4)"
    LOCAL_LISTENER= "(ADDRESS = (PROTOCOL = TCP)(HOST = xx.yy.zzz)(PORT = 1521))"
    -----------------------------------
    [xx.yy.zzz = your host name or IP Address - I assume with NAS your connections are coming in over TCP/IP and I assume your LISTENER is on the same machine as the server]
    -----------------------------------
    Change your listener.ora to the following:

    LISTENER =
    (DESCRIPTION_LIST =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xx.yy.zzz)(PORT = 1521)))))

    ---------------------------------
    Shutdown the database
    Stop the listener
    Start the listener
    Start the database

    Should be fine - if not.
    Stop database and listener,
    change to your backed up initsid.ora and listener.ora and start up listener and database.

    Let me know how you get on,
    Breen.

  6. #6
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Also, make sure the tnsnames.ora on the NAS servers doesn't have (server=dedicated) - you can put this in the tnsnames of the DBA so they always have a dedicated server for their 'activities'.

    SID =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = xx.yy.zz)(PORT = 1521))
    )
    (CONNECT_DATA = (SERVICE_NAME = sid)
    )
    )

  7. #7
    Join Date
    Feb 2002
    Posts
    11
    Hi again Breen,

    Well I made the changes you suggested and restarted the listener and the database and it seems the database is now running in shared mode.
    Thanks a lot for this. Wanted to clarify a couple of things :

    1. Somebody suggested we should have the following included in the
    listener.ora LISTENER entry you mentioned.
    (connect_data =
    (server = shared)
    )
    Is it ok to have it here or can we do without it?

    2. When I query select server from v$session I see a number of dedicated servers the number of which stays the same all the time.
    Also I do not see any record for SHARED server here.
    If I query v$shared_server, I can see the request and byte statistics.

    3. We are getting several 'Unable to prepare internal statement object' sql exceptions in our log files. Does this have to do with the database
    parameters ?

    4. How do we enable connection pooling on sharedservers ?

    Thanks again for your help !!
    Amit

  8. #8
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi Amit,
    Sorry about the late reply, only back.

    1) I don't think it makes a difference if the (server=shared) entry is made in the listener.ora, it should be shared by default.

    2) If you 'select username,program,server from v$session' you should see dedicated server connections for your background processes ie PMON, SMON etc (this is the way it should be).
    Also, I don't see any 'shared' sever connections - it shows up as 'none'. I haven't worried about this, everything is processing correctly.

    3) Is there an ORA number associated with the 'Unable to prepare internal statement object' - I have never seen this one before.
    Is it consistent and causing problems? What is the application doing at the time it shows up?

    4) The MTS setup is a form of connection pooling - in this context do you mean that NAS will open a set number of connections to the database and use these for any processing, thereby not having to drop and recreate client connections all the time?
    I have found that NAS on our system doesn't work like that - it doesn't appear to reuse connections. I therefore setup a profile with a timeout of 15 minutes and put the NAS user in this profile.

    Let me know how if the problems are continuing.

    Enjoy,
    Breen.

  9. #9
    Join Date
    Feb 2002
    Posts
    11
    Hi Breen,

    Thanks a lot again for your reply. Well the problem we are currently facing is that at peak load hours the Enterprise Java Beans of our
    application fail to respond and this is happenning in the findByPrimaryKey method where there is a lookup query. It seems
    the EJB is not able to open a new connection to the database.

    I believe when the maximum number of connections per dispatcher are reached, new connection requests have to wait until a user disconnects even though some of the connections may be idle.
    Is there a parameter value for the maximum number of connections
    that can be opened in the database simultaneously. How can we change this value upward ?

    Also the 'Unable to prepare internal statement object' is a java.sql.Exception and does not have an ORA number associated.

    I'm sorry for bothering you again and again.

    Regards,
    Amit

  10. #10
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi again Amit,

    In the ininsid.ora make the following entry:

    mts_max_dispatchers=10
    (DEFAULT is 5)

    and change the entry for

    MTS_DISPATCHERS = " .......... (DISPATCHERS=6)"

    Stop and start the database.

    Also if there are idle connections that are not being closed off: create a profile that specifies an idle_time of 15 (minutes) or 10.

    create profile NAME limit idle_time 10;
    alter user USERNAME profile NAME;

    Let me know how it goes.

    Best of luck,
    Breen.

  11. #11
    Join Date
    Feb 2002
    Posts
    11
    Hi Breen,

    Sorry for another question. How can we know if connections are idling or not ?

    Regards,
    Amit

  12. #12
    Join Date
    Mar 2002
    Location
    Ireland
    Posts
    181
    Hi Again Amit,

    Use the combination of STATUS and LAST_CALL_ET in V$SESSION. Check the status for INACTIVE (you may find that they all show up as inactive if they are not processing anything).
    Then check the LAST_CALL_ET as this is the number of seconds since the session was last active.

    Hope it helps,
    Breen.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •