Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2002
    Posts
    78

    Unanswered: connection pooling not working

    Hai
    I have enabled connection pooling to my database.Although enabling connection pooling ,still showing status "INACTIVE" and server "NONE".what cause might could be.It should be appreciated ig i get need full solution

    Init.ora file
    =============
    MTS_DISPATCHERS="(PROTOCOL=TCP)(DISPATCHERS=4)(CON NECTIONS=500)(pool=on)(tick=1) (sessions=4000) \ (LISTENER=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(PO RT=1528)(HOST=mohan-2k))))"


    I had quey as connect to system
    ===============================

    select username,server,status from v$session


    USERNAME SERVER STATUS
    ------------------------------ --------- --------
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE

    USERNAME SERVER STATUS
    ------------------------------ --------- --------
    SYSTEM SHARED ACTIVE
    MOHAN NONE INACTIVE


    Dispatchers status
    ============

    LSNRCTL> services mohan
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mohan-2k)(PORT=1528)))
    Services Summary...
    DEMO has 1 service handler(s)
    DEDICATED SERVER established:0 refused:0
    LOCAL SERVER
    demo has 4 service handler(s)
    DISPATCHER established:2 refused:0 current:1 max:4000 state:ready
    D003 <machine: MOHAN-2K, pid: 540>
    (ADDRESS=(PROTOCOL=tcp)(HOST=mohan-2k.nirbeeja.com)(PORT=1594))
    DISPATCHER established:2 refused:0 current:0 max:4000 state:ready
    D002 <machine: MOHAN-2K, pid: 1596>
    (ADDRESS=(PROTOCOL=tcp)(HOST=mohan-2k.nirbeeja.com)(PORT=1593))
    DISPATCHER established:2 refused:0 current:0 max:4000 state:ready
    D001 <machine: MOHAN-2K, pid: 1080>
    (ADDRESS=(PROTOCOL=tcp)(HOST=mohan-2k.nirbeeja.com)(PORT=1592))
    DISPATCHER established:2 refused:0 current:1 max:4000 state:ready
    D000 <machine: MOHAN-2K, pid: 1652>
    (ADDRESS=(PROTOCOL=tcp)(HOST=mohan-2k.nirbeeja.com)(PORT=1591))
    The command completed successfully
    LSNRCTL>



    Thanks in advance

    mohan

  2. #2
    Join Date
    May 2003
    Location
    France
    Posts
    112

    Re: connection pooling not working

    Originally posted by mohan
    Hai
    I have enabled connection pooling to my database.Although enabling connection pooling ,still showing status "INACTIVE" and server "NONE".what cause might could be.It should be appreciated ig i get need full solution

    Init.ora file
    =============
    MTS_DISPATCHERS="(PROTOCOL=TCP)(DISPATCHERS=4)(CON NECTIONS=500)(pool=on)(tick=1) (sessions=4000) \ (LISTENER=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(PO RT=1528)(HOST=mohan-2k))))"


    I had quey as connect to system
    ===============================

    select username,server,status from v$session


    USERNAME SERVER STATUS
    ------------------------------ --------- --------
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE

    USERNAME SERVER STATUS
    ------------------------------ --------- --------
    SYSTEM SHARED ACTIVE
    MOHAN NONE INACTIVE


    Dispatchers status
    ============

    LSNRCTL> services mohan
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mohan-2k)(PORT=1528)))
    Services Summary...
    DEMO has 1 service handler(s)
    DEDICATED SERVER established:0 refused:0
    LOCAL SERVER
    demo has 4 service handler(s)
    DISPATCHER established:2 refused:0 current:1 max:4000 state:ready
    D003 <machine: MOHAN-2K, pid: 540>
    (ADDRESS=(PROTOCOL=tcp)(HOST=mohan-2k.nirbeeja.com)(PORT=1594))
    DISPATCHER established:2 refused:0 current:0 max:4000 state:ready
    D002 <machine: MOHAN-2K, pid: 1596>
    (ADDRESS=(PROTOCOL=tcp)(HOST=mohan-2k.nirbeeja.com)(PORT=1593))
    DISPATCHER established:2 refused:0 current:0 max:4000 state:ready
    D001 <machine: MOHAN-2K, pid: 1080>
    (ADDRESS=(PROTOCOL=tcp)(HOST=mohan-2k.nirbeeja.com)(PORT=1592))
    DISPATCHER established:2 refused:0 current:1 max:4000 state:ready
    D000 <machine: MOHAN-2K, pid: 1652>
    (ADDRESS=(PROTOCOL=tcp)(HOST=mohan-2k.nirbeeja.com)(PORT=1591))
    The command completed successfully
    LSNRCTL>



    Thanks in advance

    mohan
    Maybe, you have to add
    (SERVER=SHARED) in your tnsnames.ora
    and make sure using @ALIAS_SQLNET when connecting if you are on your server

  3. #3
    Join Date
    Oct 2002
    Posts
    78

    Still not working

    Hai
    I am still facing the same problem.It was puzzling to find out where i made mistake .I had set every thing according to oracle doc and showing status "ACTIVE" and server "SHARED" if using "SYSTEM"(user) and "SYS" session,but using any other session except "SYSTEM"and "SYS", status "INACTIVE" server "NONE".It sholud be appereciated if i get needfull solution.I am giving as below how to follwed the steps


    Add parametrs for connection pooling In Init.ora file
    ================================================== ====
    db_name = "demo"
    instance_name = demo
    service_names = demo
    mts_service="demo"

    mts_dispatchers = "(protocol=TCP)(disp=4)(conn=500)(host=mohan-2k)(list=MOHAN)(pool=on)(tick=1) (sessions=4000)"

    MTS_MAX_DISPATCHERS =16
    MTS_SERVERS =2
    MTS_MAX_SERVERS =10

    Tnsnames.ora
    =============
    (The below netservice name used at server where database resides)

    MTS =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.62)(PORT = 1528))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = demo)
    (SRVR = SHARED)
    )
    )





    (The below netservice name used at Client side )

    MM =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mohan-2k)(PORT = 1528))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = demo)
    (SRVR = SHARED)
    )
    )

    Listener.ora
    ============:


    MOHAN =
    (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = mohan-2k)(PORT = 1528))
    )

    SID_LIST_MOHAN =
    (SID_LIST =
    (SID_DESC =
    (GLOBAL_DBNAME = DEMO)
    (ORACLE_HOME = D:\ORACLE\ORA81)
    (SID_NAME = DEMO)
    )
    )

    Status of dispatchers
    =====================:
    LSNRCTL> services mohan
    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mohan-2k)(PORT=1528)))
    Services Summary...
    Service "DEMO" has 2 instances.
    Instance "DEMO"
    Status: READY Total handlers: 1 Relevant handlers: 1
    Class: ORACLE
    DEDICATED established:0 refused:0 current:0 max:0 state:ready
    (ADDRESS=(PROTOCOL=beq)(PROGRAM=oracle)(ENVS=)(ARG V0=oracleDEMO)(ARGS='(
    LOCAL=NO)'))
    Instance "demo"
    Status: READY Total handlers: 4 Relevant handlers: 4
    Class: ORACLE
    D003 established:0 refused:0 current:0 max:4000 state:ready
    (ADDRESS=(PROTOCOL=tcp)(HOST=mohan-2k.nirbeeja.com)(PORT=1272))
    Session: NS
    D002 established:0 refused:0 current:0 max:4000 state:ready
    (ADDRESS=(PROTOCOL=tcp)(HOST=mohan-2k.nirbeeja.com)(PORT=1271))
    Session: NS
    D001 established:0 refused:0 current:0 max:4000 state:ready
    (ADDRESS=(PROTOCOL=tcp)(HOST=mohan-2k.nirbeeja.com)(PORT=1270))
    Session: NS
    D000 established:0 refused:0 current:0 max:4000 state:ready
    (ADDRESS=(PROTOCOL=tcp)(HOST=mohan-2k.nirbeeja.com)(PORT=1269))
    Session: NS
    The command completed successfully
    LSNRCTL>




    SQL> conn mohan/mohan@MM;
    Connected.


    SQL> conn system/manager@mts;


    SQL> select username,server,status from v$session;


    USERNAME SERVER STATUS
    ------------------------------ --------- --------
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE
    DEDICATED ACTIVE

    USERNAME SERVER STATUS
    ------------------------------ --------- --------
    SYSTEM SHARED ACTIVE
    MOHAN NONE INACTIVE



    Thanks in advance
    mohan

  4. #4
    Join Date
    May 2003
    Location
    France
    Posts
    112

    Re: Still not working

    MTS (Multi-threaded server) connections will always appear as NONE
    or SHARED in the SERVER column of the V$SESSION view depending on whether or not a task is currently being serviced by a shared
    server.

    The V$CIRCUIT view gives more detailed information about the usage of
    circuits by each session.

    SQL> select saddr, circuit, dispatcher, server,
    2> substr(queue,1,8) "QUEUE", waiter from v$circuit;

    Hope this will help you

  5. #5
    Join Date
    Oct 2002
    Posts
    78

    Please clarify

    Hai
    Thnaks for your prompt reply

    As for you say i had run the query ,its giving the follwing information .Does this mean the connection pooling is enabled in my database.Please clarify


    sql>select saddr, circuit, dispatcher, server,
    substr(queue,1,8) "QUEUE", waiter from v$circuit;

    SADDR CIRCUIT DISPATCH SERVER QUEUE WAITER
    -------- -------- -------- -------- -------- --------
    23FB1044 24217760 23FA0EE4 23FA0584 SERVER 00
    23FB1898 24217860 23FA1524 00 NONE 00


    Thanks in advance
    mohan

  6. #6
    Join Date
    May 2003
    Location
    France
    Posts
    112

    Re: Please clarify

    Here's the complete definition of v$circuit view; your database seems currently using Dispatchers

    CIRCUIT
    Circuit address

    DISPATCHER
    Current dispatcher process address

    SERVER
    Current server process address

    WAITER
    Address of server process that is waiting for the (currently busy) circuit to become available

    SADDR
    Address of session bound to the circuit

    STATUS
    Status of the circuit:

    BREAK - currently interrupted
    EOF - about to be removed
    OUTBOUND - an outward link to a remote database
    NORMAL - normal circuit into the local database

    QUEUE
    Queue the circuit is currently on:

    COMMON - on the common queue, waiting to be picked up by a server process

    DISPATCHER - waiting for the dispatcher

    SERVER - currently being serviced

    NONE - idle circuit


    MESSAGE0
    Size in bytes of the messages in the first message buffer

    MESSAGE1
    Size in bytes of the messages in the second message buffer

    MESSAGE2
    Size in bytes of the messages in the third message buffer

    MESSAGE3
    Size in bytes of the messages in the fourth message buffer

    MESSAGES
    Total number of messages that have gone through this circuit

    BYTES
    Total number of bytes that have gone through this circuit

    BREAKS
    Total number of breaks (interruptions) for this circuit

    PRESENTATION
    The presentation protocol used by the client and server

  7. #7
    Join Date
    Oct 2002
    Posts
    78

    see this

    Hai
    Thanks for your prompt reply.This below reply got from dbaclick.com forums

    "If you are using the conection pooling for the physical network connections to a multi-threaded server, then the dispatcher's set connections are shared or pooled among multiple client processes".


    According to above reply ,if connection pooling effectively Implemented, pooled amoung multiple client process sessions being shared ,but in my case not like that .Please clarify if connection is enabled at database ,how will come to know whether connection pooling is working or not.I am new to dba responsibility.Please clarify that

    Thanks in advnace
    mohan

  8. #8
    Join Date
    May 2003
    Location
    France
    Posts
    112

    Re: see this

    Originally posted by mohan
    Hai
    Thanks for your prompt reply.This below reply got from dbaclick.com forums

    "If you are using the conection pooling for the physical network connections to a multi-threaded server, then the dispatcher's set connections are shared or pooled among multiple client processes".


    According to above reply ,if connection pooling effectively Implemented, pooled amoung multiple client process sessions being shared ,but in my case not like that .Please clarify if connection is enabled at database ,how will come to know whether connection pooling is working or not.I am new to dba responsibility.Please clarify that

    Thanks in advnace
    mohan
    if connection pooling is effective you can see ora_dxxx_SID process (one per dispatcher), but when dedicated connections are used, this is oracleSID (LOCAL=NO) process.

  9. #9
    Join Date
    Oct 2002
    Posts
    78

    how

    Hai
    As for you say
    if connection pooling is effective you can see ora_dxxx_SID process (one per dispatcher), but when dedicated connections are used, this is oracleSID (LOCAL=NO) process


    Which query can use get those above infornation where connection pooling is effective or not

    Thanks in advance
    mohan

  10. #10
    Join Date
    May 2003
    Location
    France
    Posts
    112

    Re: how

    Originally posted by mohan
    Hai
    As for you say
    if connection pooling is effective you can see ora_dxxx_SID process (one per dispatcher), but when dedicated connections are used, this is oracleSID (LOCAL=NO) process


    Which query can use get those above infornation where connection pooling is effective or not

    Thanks in advance
    mohan

    select server,substr(username,1,15) "ORACLE USERNAME",
    substr(osuser,1,8) "O/S USER", substr (machine,1,7) "MACHINE",
    substr(program,1,35) "PROGRAM"
    from v$session where type='USER';

    These query show you type of connections (SHARED, DEDICATED) used by connections.

    SHARED is ora_dxxx_SID and DEDICATED is oracleSID

  11. #11
    Join Date
    Oct 2002
    Posts
    78

    hai

    Hai
    When execute the below query ,its gicing the below error.Please let me know how to implement connection pooling


    select server,substr(username,1,15) "ORACLE USERNAME",
    substr(osuser,1,8) "O/S USER", substr (machine,1,7) "MACHINE",
    substr(program,1,35) "PROGRAM"
    from v$session where type='USER';

    ERROR at line 4:
    ORA-00911: invalid character

    Thanks in advance
    mohan

  12. #12
    Join Date
    May 2003
    Location
    France
    Posts
    112

    Re: hai

    Originally posted by mohan
    Hai
    When execute the below query ,its gicing the below error.Please let me know how to implement connection pooling


    select server,substr(username,1,15) "ORACLE USERNAME",
    substr(osuser,1,8) "O/S USER", substr (machine,1,7) "MACHINE",
    substr(program,1,35) "PROGRAM"
    from v$session where type='USER';

    ERROR at line 4:
    ORA-00911: invalid character

    Thanks in advance
    mohan
    Sorry, but i copy/paste this query and this is ok on windows interface. but be aware of doinig this in unix environnment

  13. #13
    Join Date
    Oct 2002
    Posts
    78

    hai

    Hai

    Thank you for giving lot of information ,Anyhow the query is working fine

    Thanks
    mohan

Posting Permissions

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