Results 1 to 11 of 11
  1. #1
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467

    Unanswered: Oracle ODBC connections

    Setup: 9i DB on a W2K platform

    When trying to connect to the 9i DB from a remote application via ODBC the developers are experiencing a great slowness. It takes about 5 seconds to connect and then query a table that only contains 7 records. A similar connect & query to a SQL database is almost instantanious and returns 56 records.

    The developers wondered about if they should be connecting to a listener instead. I told them that that is handled behind the scenes when they connect to the DB. (That is correct isn't it).

    They asked about if there are dedicated listeners. I told them yes because that was what I remembered, but then I did a "SHOW PARAMTERS" I did not see a listing for "dedicated listeners". I did see:
    dispatchers -
    (PROTOCOL=TCP) (SERVICE=bci92XDB)
    max_dispatchers - 5
    shared_servers - 1 (Is this part/all of the problem??)
    max_shared_servers - 20
    circuits - 170
    shared_server_sessions - 165
    local_listener - it was null
    remote_listener - it was null

    I am not seeing anything in my alert log.

    Anyone with any experience in this area?

    Anyone have any ideas?
    NOTE: Please disregard the label "Senior Member".

  2. #2
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: Oracle ODBC connections

    Originally posted by Todd Barkus
    Setup: 9i DB on a W2K platform

    When trying to connect to the 9i DB from a remote application via ODBC the developers are experiencing a great slowness. It takes about 5 seconds to connect and then query a table that only contains 7 records. A similar connect & query to a SQL database is almost instantanious and returns 56 records.

    The developers wondered about if they should be connecting to a listener instead. I told them that that is handled behind the scenes when they connect to the DB. (That is correct isn't it).

    They asked about if there are dedicated listeners. I told them yes because that was what I remembered, but then I did a "SHOW PARAMTERS" I did not see a listing for "dedicated listeners". I did see:
    dispatchers -
    (PROTOCOL=TCP) (SERVICE=bci92XDB)
    max_dispatchers - 5
    shared_servers - 1 (Is this part/all of the problem??)
    max_shared_servers - 20
    circuits - 170
    shared_server_sessions - 165
    local_listener - it was null
    remote_listener - it was null

    I am not seeing anything in my alert log.

    Anyone with any experience in this area?

    Anyone have any ideas?
    dedicated listeners you cant display with show parameters.
    BTW your DB is in MTS mode so if you can use dedicated connection then you need specify it in tnsnames.ora with specific port number of dispatcher.
    For display port numbers of dispatchers execute show parameters dispatcher.
    Assume that the first of disachers is listenning on 1522:


    ORG.WORLD =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(Host = 192.168.0.1)(Port = 1522))
    )
    (CONNECT_DATA =(SERVICE_NAME=ORG)(server=DEDICATED))
    )

  3. #3
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482

    Re: Oracle ODBC connections

    Originally posted by Todd Barkus
    Setup: 9i DB on a W2K platform

    When trying to connect to the 9i DB from a remote application via ODBC the developers are experiencing a great slowness. It takes about 5 seconds to connect and then query a table that only contains 7 records. A similar connect & query to a SQL database is almost instantanious and returns 56 records.

    The developers wondered about if they should be connecting to a listener instead. I told them that that is handled behind the scenes when they connect to the DB. (That is correct isn't it).

    They asked about if there are dedicated listeners. I told them yes because that was what I remembered, but then I did a "SHOW PARAMTERS" I did not see a listing for "dedicated listeners". I did see:
    dispatchers -
    (PROTOCOL=TCP) (SERVICE=bci92XDB)
    max_dispatchers - 5
    shared_servers - 1 (Is this part/all of the problem??)
    max_shared_servers - 20
    circuits - 170
    shared_server_sessions - 165
    local_listener - it was null
    remote_listener - it was null

    I am not seeing anything in my alert log.

    Anyone with any experience in this area?

    Anyone have any ideas?
    btw:
    shared_servers - 1 - this value is very small try up to 10
    max_dispatchers - 5 - one dispatcher can handle 1000 connections at once so this value is big. Try change value to 2
    max_shared_servers - increase to 40
    !!and check "processes" - show parameter processes
    This value must be greater than:
    max_dispatchers + max_shared_servers + backgorund processes + some constant

    P.S: how many session do you have in same time or how many is planned? This is important for above setup.

  4. #4
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    processes is 150

    planned connections is < 10

    SQL> show parameters dispatcher

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    dispatchers string (PROTOCOL=TCP) (SERVICE=bci92X
    DB)
    max_dispatchers integer 5
    mts_dispatchers string (PROTOCOL=TCP) (SERVICE=bci92X
    DB)
    mts_max_dispatchers integer 5

    What do you mean by "if I CAN use dedicated dispatchers"? What would keep me from using dedicated?

    Here are some relevant lines from my tnsnames.ora

    BCI92.WME.WMENG.COM =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MPL-BARKUST)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = bci92.bci.com)
    )
    )

    REPOSTRY =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.227)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = repostry.world)
    )
    )

    BCI92 =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MPL-BARKUST)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = bci92.bci.com)
    )
    )
    NOTE: Please disregard the label "Senior Member".

  5. #5
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482
    Originally posted by Todd Barkus
    processes is 150

    planned connections is < 10

    SQL> show parameters dispatcher

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    dispatchers string (PROTOCOL=TCP) (SERVICE=bci92X
    DB)
    max_dispatchers integer 5
    mts_dispatchers string (PROTOCOL=TCP) (SERVICE=bci92X
    DB)
    mts_max_dispatchers integer 5

    What do you mean by "if I CAN use dedicated dispatchers"? What would keep me from using dedicated?

    Here are some relevant lines from my tnsnames.ora

    BCI92.WME.WMENG.COM =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MPL-BARKUST)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = bci92.bci.com)
    )
    )

    REPOSTRY =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 172.16.1.227)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVICE_NAME = repostry.world)
    )
    )

    BCI92 =
    (DESCRIPTION =
    (ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = MPL-BARKUST)(PORT = 1521))
    )
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = bci92.bci.com)
    )
    )
    Ooops sorry for my english... I ment "...so if you would like use dedicated connection..."

  6. #6
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    FYI
    SQL> alter system set max_dispatchers=2 scope=both;
    alter system set max_dispatchers=2 scope=both
    *
    ERROR at line 1:
    ORA-02095: specified initialization parameter cannot be modified

    I get the same for
    alter system set max_shared_servers=40 scope=both;

    I think this is probably because I am running in dedicated mode.

    Here is the strange part (to me) I used the configuration assistant to change it to SHARED it said it did something, but when I tried to set it back to DEDICATED after the test showed the connection took longer the assistant said there was nothing to change. Indeed, when I checked out the parameters they were still set as before.
    NOTE: Please disregard the label "Senior Member".

  7. #7
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    I wonder if I just have a confused system.

    Perhaps the "default data base setup" just isn't sophisticated enough. My tnsnames file shows DEDICATED but there is only one listerener entry in my listener.ora file.

    When I supposedly changed my setting to SHARED the only file that got updated was snmp_ro.ora. Nothing else was touched.

    Any help or suggestions you can give will be appreciated. I have no previous experience with ODBC connections.
    NOTE: Please disregard the label "Senior Member".

  8. #8
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482
    Originally posted by Todd Barkus
    FYI
    SQL> alter system set max_dispatchers=2 scope=both;
    alter system set max_dispatchers=2 scope=both
    *
    ERROR at line 1:
    ORA-02095: specified initialization parameter cannot be modified

    I get the same for
    alter system set max_shared_servers=40 scope=both;

    I think this is probably because I am running in dedicated mode.

    Here is the strange part (to me) I used the configuration assistant to change it to SHARED it said it did something, but when I tried to set it back to DEDICATED after the test showed the connection took longer the assistant said there was nothing to change. Indeed, when I checked out the parameters they were still set as before.
    <snip>
    SQL> alter system set max_dispatchers=2 scope=both;
    alter system set max_dispatchers=2 scope=both
    </snip>
    This parameter is not dynamic so you should to change it in spfile or pfile and restart the instance.

    If you would change from shared server to dedicated just remove the DISPATCHERS (required for shared server) from pfile (spfile) and start the instance.

  9. #9
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    Not sure how familiar you are with listern trace files but mean appears to be servicing the request in 15ms.

    Is that how you read this?

    [15-MAR-2004 12:45:53:334] nscon: doing connect handshake...
    [15-MAR-2004 12:45:53:334] nscon: got NSPTCN packet
    [15-MAR-2004 12:45:53:334] nsevdansw: exit
    [15-MAR-2004 12:45:53:334] nsbeqaddr: connecting...
    [15-MAR-2004 12:45:53:334] sntpcall: Attempting to open pipe \\.\PIPE\ORANTP36C.648
    [15-MAR-2004 12:45:53:350] sntpcall: Successfully established pipe 452 to child
    [15-MAR-2004 12:45:53:350] sntpcall: Attempting to open pipe \\.\PIPE\ORANTP36C.648.w
    [15-MAR-2004 12:45:53:350] sntpcall: Successfully established pipe 460 to child
    [15-MAR-2004 12:45:53:350] nsbeqaddr: doing connect handshake...
    [15-MAR-2004 12:45:53:350] nsbequeath: doing connect handshake...
    [15-MAR-2004 12:45:53:350] sntpwrite: Attempting to write 4 bytes to 452
    [15-MAR-2004 12:45:53:350] sntpwrite: Write returned 4 bytes
    [15-MAR-2004 12:45:53:350] sntpwrite: Attempting to write 62 bytes to 452
    [15-MAR-2004 12:45:53:350] sntpwrite: Write returned 62 bytes
    [15-MAR-2004 12:45:53:350] sntpwrite: Attempting to write 4 bytes to 452
    [15-MAR-2004 12:45:53:350] sntpwrite: Write returned 4 bytes
    [15-MAR-2004 12:45:53:350] sntpread: Attempting to read 4 bytes from 460
    [15-MAR-2004 12:45:53:350] sntpread: Read returned 4 bytes
    [15-MAR-2004 12:45:53:350] sntpread: Attempting to read 53 bytes from 460
    [15-MAR-2004 12:45:53:350] sntpread: Read returned 53 bytes
    [15-MAR-2004 12:45:53:350] nsbequeath: REDIR="(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.1.62)(P ORT=2808))"
    [15-MAR-2004 12:45:53:350] sntpclose: Closing pipe 460
    [15-MAR-2004 12:45:53:350] sntpclose: Closing pipe 452
    [15-MAR-2004 12:45:53:350] nsbeqaddr: handshake is complete; redirecting
    [15-MAR-2004 12:45:53:350] nscon: sending NSPTRD packet
    [15-MAR-2004 12:45:53:350] ntt2err: soc 464 error - operation=5, ntresnt[0]=524, ntresnt[1]=997, ntresnt[2]=0
    [15-MAR-2004 12:45:53:350] nstimarmed: no timer allocated
    [15-MAR-2004 12:45:53:365] ntt2err: soc 464 error - operation=5, ntresnt[0]=530, ntresnt[1]=995, ntresnt[2]=0
    [15-MAR-2004 12:45:53:365] nsclose: closing transport
    [15-MAR-2004 12:45:53:365] nsclose: global context check-out (from slot 6) complete

    If that is the case it looks like the problem is somewhere downstream from the DB. The odd part is that the SQL DB demo responds as fast as this one looks like it should.
    NOTE: Please disregard the label "Senior Member".

  10. #10
    Join Date
    Oct 2003
    Location
    Slovakia
    Posts
    482
    Originally posted by Todd Barkus
    Not sure how familiar you are with listern trace files but mean appears to be servicing the request in 15ms.

    Is that how you read this?

    [15-MAR-2004 12:45:53:334] nscon: doing connect handshake...
    [15-MAR-2004 12:45:53:334] nscon: got NSPTCN packet
    [15-MAR-2004 12:45:53:334] nsevdansw: exit
    [15-MAR-2004 12:45:53:334] nsbeqaddr: connecting...
    [15-MAR-2004 12:45:53:334] sntpcall: Attempting to open pipe \\.\PIPE\ORANTP36C.648
    [15-MAR-2004 12:45:53:350] sntpcall: Successfully established pipe 452 to child
    [15-MAR-2004 12:45:53:350] sntpcall: Attempting to open pipe \\.\PIPE\ORANTP36C.648.w
    [15-MAR-2004 12:45:53:350] sntpcall: Successfully established pipe 460 to child
    [15-MAR-2004 12:45:53:350] nsbeqaddr: doing connect handshake...
    [15-MAR-2004 12:45:53:350] nsbequeath: doing connect handshake...
    [15-MAR-2004 12:45:53:350] sntpwrite: Attempting to write 4 bytes to 452
    [15-MAR-2004 12:45:53:350] sntpwrite: Write returned 4 bytes
    [15-MAR-2004 12:45:53:350] sntpwrite: Attempting to write 62 bytes to 452
    [15-MAR-2004 12:45:53:350] sntpwrite: Write returned 62 bytes
    [15-MAR-2004 12:45:53:350] sntpwrite: Attempting to write 4 bytes to 452
    [15-MAR-2004 12:45:53:350] sntpwrite: Write returned 4 bytes
    [15-MAR-2004 12:45:53:350] sntpread: Attempting to read 4 bytes from 460
    [15-MAR-2004 12:45:53:350] sntpread: Read returned 4 bytes
    [15-MAR-2004 12:45:53:350] sntpread: Attempting to read 53 bytes from 460
    [15-MAR-2004 12:45:53:350] sntpread: Read returned 53 bytes
    [15-MAR-2004 12:45:53:350] nsbequeath: REDIR="(ADDRESS=(PROTOCOL=tcp)(HOST=172.16.1.62)(P ORT=2808))"
    [15-MAR-2004 12:45:53:350] sntpclose: Closing pipe 460
    [15-MAR-2004 12:45:53:350] sntpclose: Closing pipe 452
    [15-MAR-2004 12:45:53:350] nsbeqaddr: handshake is complete; redirecting
    [15-MAR-2004 12:45:53:350] nscon: sending NSPTRD packet
    [15-MAR-2004 12:45:53:350] ntt2err: soc 464 error - operation=5, ntresnt[0]=524, ntresnt[1]=997, ntresnt[2]=0
    [15-MAR-2004 12:45:53:350] nstimarmed: no timer allocated
    [15-MAR-2004 12:45:53:365] ntt2err: soc 464 error - operation=5, ntresnt[0]=530, ntresnt[1]=995, ntresnt[2]=0
    [15-MAR-2004 12:45:53:365] nsclose: closing transport
    [15-MAR-2004 12:45:53:365] nsclose: global context check-out (from slot 6) complete

    If that is the case it looks like the problem is somewhere downstream from the DB. The odd part is that the SQL DB demo responds as fast as this one looks like it should.
    I never seen this error before (i mean 464 error). maybe it's OS related problem.
    Try log TAR in metalink.

  11. #11
    Join Date
    Nov 2003
    Location
    down on the cube farm, left then right then another left
    Posts
    467
    I have used MetaLink so infrequently that I forget about that option. Thanks for the tip.

    The problem appears to be the "object" that the developers were using. They are using a program called iFix which interfaces to programmable logic controllers. Any way, the object works fine with SQL DB but is really a dog with Oracle. The developers did a standard ADO connection and the response time was equal how long it looked like it was taking the server to process the request (ie. 15ms). Looking at the trace files the two approaches create nearly the same output except for the one error you mentioned. That only shows up in the object method. Both return the data in approximately 15ms.

    How is it in Slovakia these days? I assume it starting to look like spring?
    NOTE: Please disregard the label "Senior Member".

Posting Permissions

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