12 Replies Latest reply: Aug 10, 2011 6:38 AM by 794856 RSS

    oracle-11g connection is very slow

    794856
      Hi Team,

      Installed oracle11g with database yesterday. but the connection to database using tnsnames is very slow even from host server, where as sys / as sysdba is normal in hostserver.

      And checked other databases(10g) connections in the same host server, normal. Here with I spooled alert log file and parameter list. pls do the needful help.


      Aler log file from shut down to startup.

      Sat Aug 06 11:28:54 2011
      Shutting down instance (immediate)
      Stopping background process SMCO
      Shutting down instance: further logons disabled
      Sat Aug 06 11:28:55 2011
      Stopping background process CJQ0
      Stopping background process QMNC
      Stopping background process MMNL
      Stopping background process MMON
      License high water mark = 10
      ALTER DATABASE CLOSE NORMAL
      Sat Aug 06 11:28:58 2011
      SMON: disabling tx recovery
      SMON: disabling cache recovery
      Sat Aug 06 11:28:58 2011
      Shutting down archive processes
      Archiving is disabled
      Sat Aug 06 11:28:58 2011
      ARCH shutting down
      Sat Aug 06 11:28:58 2011
      ARCH shutting down
      Sat Aug 06 11:28:58 2011
      ARCH shutting down
      ARC1: Archival stopped
      ARC0: Archival stopped
      ARC3: Archival stopped
      Sat Aug 06 11:28:58 2011
      ARCH shutting down
      ARC2: Archival stopped
      Thread 1 closed at log sequence 9
      Successful close of redo thread 1
      Completed: ALTER DATABASE CLOSE NORMAL
      ALTER DATABASE DISMOUNT
      Completed: ALTER DATABASE DISMOUNT
      ARCH: Archival disabled due to shutdown: 1089
      Shutting down archive processes
      Archiving is disabled
      Archive process shutdown avoided: 0 active
      ARCH: Archival disabled due to shutdown: 1089
      Shutting down archive processes
      Archiving is disabled
      Archive process shutdown avoided: 0 active
      Sat Aug 06 11:29:01 2011
      Stopping background process VKTM:
      Sat Aug 06 11:29:05 2011
      Instance shutdown complete
      Sat Aug 06 11:32:12 2011
      Starting ORACLE instance (normal)
      LICENSE_MAX_SESSION = 0
      LICENSE_SESSIONS_WARNING = 0
      Picked latch-free SCN scheme 3
      Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
      Autotune of undo retention is turned on.
      IMODE=BR
      ILAT =118
      LICENSE_MAX_USERS = 0
      SYS auditing is disabled
      Starting up:
      Oracle Database 11g Release 11.2.0.1.0 - 64bit Production.
      Using parameter settings in client-side pfile /oracle/ora11g/apps/dbs/initrakshak.ora on machine abml01
      System parameters with non-default values:
      processes                = 700
      sga_max_size             = 30G
      sga_target               = 30G
      control_files            = "/barch10g_db/ora11g/rakshak_control/rkdatabase/control1/rakshak_control01.ctl"
      control_files            = "/barch10g_db/ora11g/rakshak_redo/rkdatabase/control2/rakshak_control02.ctl"
      control_files            = "/barch10g_db/ora11g/rakshak_idx1/rkdatabase/control3/rakshak_control03.ctl"
      db_block_size            = 16384
      compatible               = "11.2.0"
      log_archive_dest         = "/barch10g_db/ora11g/rakshak_archive/rkdatabase/rakshak"
      db_recovery_file_dest    = "/barch10g_db/ora11g/rakshak_idx1/rkdatabase/flash_recovery_area"
      db_recovery_file_dest_size= 2G
      undo_management          = "AUTO"
      undo_tablespace          = "UNDOTBS1"
      sec_case_sensitive_logon = FALSE
      remote_login_passwordfile= "EXCLUSIVE"
      utl_file_dir             = "/barch10g_db/ora11g/ldoutput/"
      plsql_code_type          = "native"
      job_queue_processes      = 100
      cursor_sharing           = "FORCE"
      audit_file_dest          = "/barch10g_db/ora11g/rakshak_idx1/rkdatabase/adump"
      audit_trail              = "DB"
      db_name                  = "rakshak"
      open_cursors             = 700
      diagnostic_dest          = "/barch10g_db/ora11g/rakshak_idx1/rkdatabase/"
      Sat Aug 06 11:32:33 2011
      PMON started with pid=2, OS id=9463
      Sat Aug 06 11:32:34 2011
      VKTM started with pid=3, OS id=9465 at elevated priority
      VKTM running at (10)millisec precision with DBRM quantum (100)ms
      Sat Aug 06 11:32:34 2011
      GEN0 started with pid=4, OS id=9469
      Sat Aug 06 11:32:34 2011
      DIAG started with pid=5, OS id=9471
      Sat Aug 06 11:32:34 2011
      DBRM started with pid=6, OS id=9473
      Sat Aug 06 11:32:34 2011
      PSP0 started with pid=7, OS id=9475
      Sat Aug 06 11:32:34 2011
      DIA0 started with pid=8, OS id=9477
      Sat Aug 06 11:32:34 2011
      MMAN started with pid=9, OS id=9479
      Sat Aug 06 11:32:34 2011
      DBW0 started with pid=10, OS id=9481
      Sat Aug 06 11:32:34 2011
      DBW1 started with pid=11, OS id=9483
      Sat Aug 06 11:32:34 2011
      DBW2 started with pid=12, OS id=9485
      Sat Aug 06 11:32:34 2011
      LGWR started with pid=13, OS id=9487
      Sat Aug 06 11:32:34 2011
      CKPT started with pid=14, OS id=9489
      Sat Aug 06 11:32:34 2011
      SMON started with pid=15, OS id=9491
      Sat Aug 06 11:32:34 2011
      RECO started with pid=16, OS id=9493
      Sat Aug 06 11:32:34 2011
      MMON started with pid=17, OS id=9495
      Sat Aug 06 11:32:34 2011
      MMNL started with pid=18, OS id=9497
      Sat Aug 06 11:32:34 2011
      ORACLE_BASE not set in environment. It is recommended
      that ORACLE_BASE be set in the environment
      Sat Aug 06 11:34:34 2011
      Shutting down instance (immediate)
      Shutting down instance: further logons disabled
      Stopping background process MMNL
      Stopping background process MMON
      License high water mark = 1
      ALTER DATABASE CLOSE NORMAL
      ORA-1507 signalled during: ALTER DATABASE CLOSE NORMAL...
      ARCH: Archival disabled due to shutdown: 1089
      Shutting down archive processes
      Archiving is disabled
      Archive process shutdown avoided: 0 active
      ARCH: Archival disabled due to shutdown: 1089
      Shutting down archive processes
      Archiving is disabled
      Archive process shutdown avoided: 0 active
      Sat Aug 06 11:34:37 2011
      Stopping background process VKTM:
      Sat Aug 06 11:34:40 2011
      Instance shutdown complete
      Sat Aug 06 11:35:55 2011
      Starting ORACLE instance (normal)
      LICENSE_MAX_SESSION = 0
      LICENSE_SESSIONS_WARNING = 0
      Picked latch-free SCN scheme 3
      Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
      Autotune of undo retention is turned on.
      IMODE=BR
      ILAT =118
      LICENSE_MAX_USERS = 0
      SYS auditing is disabled
      Starting up:
      Oracle Database 11g Release 11.2.0.1.0 - 64bit Production.
      Using parameter settings in client-side pfile /oracle/ora11g/apps/dbs/initrakshak.ora on machine abml01
      System parameters with non-default values:
      processes                = 700
      sga_max_size             = 30G
      sga_target               = 30G
      control_files            = "/barch10g_db/ora11g/rakshak_control/rkdatabase/control1/rakshak_control01.ctl"
      control_files            = "/barch10g_db/ora11g/rakshak_redo/rkdatabase/control2/rakshak_control02.ctl"
      control_files            = "/barch10g_db/ora11g/rakshak_idx1/rkdatabase/control3/rakshak_control03.ctl"
      db_block_size            = 16384
      compatible               = "11.2.0"
      log_archive_dest         = "/barch10g_db/ora11g/rakshak_archive/rkdatabase/rakshak"
      db_recovery_file_dest    = "/barch10g_db/ora11g/rakshak_idx1/rkdatabase/flash_recovery_area"
      db_recovery_file_dest_size= 2G
      undo_management          = "AUTO"
      undo_tablespace          = "UNDOTBS1"
      sec_case_sensitive_logon = FALSE
      remote_login_passwordfile= "EXCLUSIVE"
      utl_file_dir             = "/barch10g_db/ora11g/ldoutput/"
      plsql_code_type          = "native"
      job_queue_processes      = 100
      cursor_sharing           = "FORCE"
      audit_file_dest          = "/barch10g_db/ora11g/rakshak_idx1/rkdatabase/adump"
      audit_trail              = "DB"
      db_name                  = "rakshak"
      open_cursors             = 700
      diagnostic_dest          = "/barch10g_db/ora11g/rakshak_idx1/rkdatabase/"
      Sat Aug 06 11:36:16 2011
      PMON started with pid=2, OS id=9648
      Sat Aug 06 11:36:16 2011
      VKTM started with pid=3, OS id=9657 at elevated priority
      VKTM running at (10)millisec precision with DBRM quantum (100)ms
      Sat Aug 06 11:36:16 2011
      GEN0 started with pid=4, OS id=9669
      Sat Aug 06 11:36:16 2011
      DIAG started with pid=5, OS id=9678
      Sat Aug 06 11:36:16 2011
      DBRM started with pid=6, OS id=9686
      Sat Aug 06 11:36:16 2011
      PSP0 started with pid=7, OS id=9697
      Sat Aug 06 11:36:16 2011
      DIA0 started with pid=8, OS id=9704
      Sat Aug 06 11:36:16 2011
      MMAN started with pid=9, OS id=9711
      Sat Aug 06 11:36:16 2011
      DBW0 started with pid=10, OS id=9713
      Sat Aug 06 11:36:16 2011
      DBW1 started with pid=11, OS id=9715
      Sat Aug 06 11:36:16 2011
      DBW2 started with pid=12, OS id=9717
      Sat Aug 06 11:36:16 2011
      LGWR started with pid=13, OS id=9719
      Sat Aug 06 11:36:16 2011
      CKPT started with pid=14, OS id=9721
      Sat Aug 06 11:36:16 2011
      SMON started with pid=15, OS id=9723
      Sat Aug 06 11:36:16 2011
      RECO started with pid=16, OS id=9725
      Sat Aug 06 11:36:16 2011
      MMON started with pid=17, OS id=9727
      Sat Aug 06 11:36:16 2011
      MMNL started with pid=18, OS id=9729
      Sat Aug 06 11:36:16 2011
      ORACLE_BASE from environment = /oracle/ora11g/home
      Sat Aug 06 11:36:40 2011
      alter database mount
      Sat Aug 06 11:36:44 2011
      Successful mount of redo thread 1, with mount id 3292194824
      Database mounted in Exclusive Mode
      Lost write protection disabled
      Completed: alter database mount
      Sat Aug 06 11:36:54 2011
      alter database open
      LGWR: STARTING ARCH PROCESSES
      Sat Aug 06 11:36:54 2011
      ARC0 started with pid=20, OS id=9743
      Sat Aug 06 11:36:55 2011
      ARC0: Archival started
      LGWR: STARTING ARCH PROCESSES COMPLETE
      ARC0: STARTING ARCH PROCESSES
      Sat Aug 06 11:36:55 2011
      ARC1 started with pid=21, OS id=9745
      Sat Aug 06 11:36:55 2011
      ARC2 started with pid=22, OS id=9747
      Sat Aug 06 11:36:55 2011
      ARC3 started with pid=23, OS id=9749
      ARC1: Archival started
      ARC2: Archival started
      ARC2: Becoming the 'no FAL' ARCH
      ARC2: Becoming the 'no SRL' ARCH
      ARC1: Becoming the heartbeat ARCH
      Thread 1 opened at log sequence 9
      Current log# 3 seq# 9 mem# 0: /barch10g_db/ora11g/rakshak_idx1/rkdatabase/redo3/rakshak_redolog3a.log
      Current log# 3 seq# 9 mem# 1: /barch10g_db/ora11g/rakshak_idx1/rkdatabase/redo3/rakshak_redolog3b.log
      Successful open of redo thread 1
      Sat Aug 06 11:36:55 2011
      SMON: enabling cache recovery
      Successfully onlined Undo Tablespace 2.
      Verifying file header compatibility for 11g tablespace encryption..
      Verifying 11g file header compatibility for tablespace encryption completed
      SMON: enabling tx recovery
      Database Characterset is WE8ISO8859P1
      No Resource Manager plan active
      replication_dependency_tracking turned off (no async multimaster replication found)
      Starting background process QMNC
      Sat Aug 06 11:36:55 2011
      QMNC started with pid=25, OS id=9753
      Completed: alter database open
      Sat Aug 06 11:36:56 2011
      db_recovery_file_dest_size of 2048 MB is 0.99% used. This is a
      user-specified limit on the amount of space that will be used by this
      database for recovery-related files, and does not reflect the amount of
      space available in the underlying filesystem or ASM diskgroup.
      ARC3: Archival started
      ARC0: STARTING ARCH PROCESSES COMPLETE
      Sat Aug 06 11:36:58 2011
      Starting background process CJQ0
      Sat Aug 06 11:36:58 2011
      CJQ0 started with pid=24, OS id=9768
      Setting Resource Manager plan SCHEDULER[0x2FF9]:DEFAULT_MAINTENANCE_PLAN via scheduler window
      Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
      Sat Aug 06 11:37:01 2011
      Starting background process VKRM
      Sat Aug 06 11:37:01 2011
      VKRM started with pid=26, OS id=9770
      Sat Aug 06 11:41:55 2011
      Starting background process SMCO
      Sat Aug 06 11:41:55 2011
      SMCO started with pid=29, OS id=9920


      parameter list

      db_name='rakshak'
      +#memory_target=30G+
      processes = 700
      audit_file_dest='/barch10g_db/ora11g/rakshak_idx1/rkdatabase/adump'
      audit_trail ='db'
      db_block_size=16384
      db_recovery_file_dest='/barch10g_db/ora11g/rakshak_idx1/rkdatabase/flash_recovery_area'
      db_recovery_file_dest_size=2G
      diagnostic_dest='/barch10g_db/ora11g/rakshak_idx1/rkdatabase/'
      +#dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'+
      open_cursors=700
      job_queue_processes=100
      remote_login_passwordfile='EXCLUSIVE'
      undo_management='AUTO'
      undo_tablespace='UNDOTBS1'
      +# You may want to ensure that control files are created on separate physical+
      +# devices+
      control_files = '/barch10g_db/ora11g/rakshak_control/rkdatabase/control1/rakshak_control01.ctl','/barch10g_db/ora11g/rakshak_redo/rkdatabase/control2/rakshak_control02.ctl','/barch10g_db/ora11g/rakshak_idx1/rkdatabase/control3/rakshak_control03.ctl'
      compatible ='11.2.0'
      SGA_MAX_SIZE=30G
      SGA_TARGET=30G
      Utl_file_dir='/barch10g_db/ora11g/ldoutput/'
      sec_case_sensitive_logon=FALSE
      plsql_code_type=native
      cursor_sharing='FORCE'
      log_archive_dest='/barch10g_db/ora11g/rakshak_archive/rkdatabase/rakshak'

      If any information is needed, pls let me know.

      thanks in advance


      Regards
      Phani Kumar
        • 1. Re: oracle-11g connection is very slow
          sybrand_b
          I'm not sure whether anyone can 'do the needful', as you didn't provide any 'needful' information, as the issue seems to be your listener.ora and tnsnames.ora

          Apart from that, the configuration of the database seems to indicate almost all resources go to the database, and many parameters have been set to an undesirable value.

          I only mention
          - db_block_size 16k, why?
          - cursor_sharing=force, why?
          - sga_target=30g, how much memory is in that system and why you have set it that high. Aren't you aware just bumping up the SGA doesn't help performance.
          - job_queue_processes=100, why?
          - utl_file_dir has been set, why?



          etc, etc.

          Also
          - is the number of unix semaphore handles sufficient? Should be at least 1450.

          BTW We are not a team, and also we aren't 'friends'

          ----------
          Sybrand Bakker
          Senior Oracle DBA

          Edited by: sybrand_b on 6-aug-2011 9:25
          • 2. Re: oracle-11g connection is very slow
            Billy~Verreynne
            4 digit Oracle version? Operating system version?

            The database has nothing to do with accepting network connections. Thus pasting database logs are useless. The Oracle Listener accepts network connections.

            What is the connection string of the TNS alias used?

            What is the contents of the listener.ora file?

            What is the output of lsnrctl status ?

            If on Windows, post the result of ipconfig. If on Linux, post the results of ifconfig.

            From the sounds of it, the issue sounds like a networking one.
            • 3. Re: oracle-11g connection is very slow
              794856
              Hi Sybrand,

              One database has been running since one and a half year with the said parameters(except 11g parameters) which suggested by vendor. Recently we were asked for a 11g database server installation with same parameters configured(so that db will be upgraded). Request you to give me the optimal values of the mentioned parametes

              As well as when I check the connectivity speed for other databases in the same hosting server using connection string that was normal. Its happening to this database only.

              Pls suggest where could be the problem Because I am new to this site.


              Regards
              Phani Kumar
              • 4. Re: oracle-11g connection is very slow
                794856
                Hi Billy,

                Oracle verion is 11.2.01 and Os version is Linux 5.3

                tns alias used is rakshak11g and rakshak11gsrv

                listner.ora content:

                listener_rakshak11g= (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.27)(PORT = 1522)))
                connect_timeout_listener_rakshak11g = 0
                startup_wait_time_listener_rakshak11g = 0
                sid_list_listener_rakshak11g =
                +(sid_list =+
                +(sid_desc = (GLOBAL_DBNAME = rakshak.abcd.com) (ORACLE_HOME = /oracle/ora11g/apps) (SID_NAME = rakshak))+
                +)+

                tnsnames.ora content:

                rakshak11g =
                +(DESCRIPTION =+
                +(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.27)(PORT = 1522))+
                +(CONNECT_DATA =+
                +(SERVER = DEDICATED)+
                +(SERVICE_NAME = rakshak.abcd.com)+
                +)+
                +)+

                rakshak11gsrv =
                +(DESCRIPTION =+
                +(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.27)(PORT = 1522))+
                +(CONNECT_DATA =+
                +(SERVER = DEDICATED)+
                +(SERVICE_NAME = rakshak.abcd.com)+
                +)+
                +)+

                Pls suggest me for rectifying the problem


                Regards
                Phani Kumar
                • 5. Re: oracle-11g connection is very slow
                  794856
                  and the outputs are:

                  lsnrctls status output:l

                  snrctl status listener_rakshak11g

                  LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 08-AUG-2011 17:12:46

                  Copyright (c) 1991, 2009, Oracle.  All rights reserved.

                  Connecting to (ADDRESS=(PROTOCOL=TCP)(HOST=192.168.247.27)(PORT=1522))
                  STATUS of the LISTENER
                  ------------------------
                  Alias                     listener_rakshak11g
                  Version                   TNSLSNR for Linux: Version 11.2.0.1.0 - Production
                  Start Date                06-AUG-2011 18:27:13
                  Uptime                    1 days 22 hr. 45 min. 32 sec
                  Trace Level               off
                  Security                  ON: Local OS Authentication
                  SNMP                      OFF
                  Listener Parameter File   /oracle/ora11g/apps/network/admin/listener.ora
                  Listener Log File         /oracle/ora11g/apps/log/diag/tnslsnr/abcd01/listener_rakshak11g/alert/log.xml
                  Listening Endpoints Summary...
                  +(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.247.27)(PORT=1522)))+
                  Services Summary...
                  Service "rakshak.abcd.com" has 1 instance(s).
                  Instance "rakshak", status UNKNOWN, has 1 handler(s) for this service...
                  The command completed successfully

                  ifconfig output:

                  ifconfig
                  bond6     Link encap:Ethernet  HWaddr 00:26:55:D3:02:B6
                  inet addr:192.168.247.27  Bcast:192.168.247.255  Mask:255.255.255.0
                  inet6 addr: fe80::226:55ff:fed3:2b6/64 Scope:Link
                  UP BROADCAST RUNNING MASTER MULTICAST  MTU:1500  Metric:1
                  RX packets:1309675596 errors:5 dropped:0 overruns:0 frame:3
                  TX packets:675662383 errors:0 dropped:0 overruns:0 carrier:0
                  collisions:0 txqueuelen:0
                  RX bytes:127166703824 (118.4 GiB)  TX bytes:572189911795 (532.8 GiB)
                  • 6. Re: oracle-11g connection is very slow
                    AronTunzi
                    tnsping '(DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.247.27)(PORT = 1522))(CONNECT_DATA =(SERVER = DEDICATED)(SERVICE_NAME = rakshak.abcd.com)))' 5
                    OK (10 msec)
                    OK (10 msec)
                    OK (0 msec)
                    OK (20 msec)
                    OK (10 msec)


                    Wow, I can ping your DB :-) ... ok not true but pay attention posting IP addresses!


                    You can try to activate listener (and tnsping) tracing while you test this issue.

                    Edited by: Aron Tunzi on Aug 8, 2011 2:20 PM
                    • 7. Re: oracle-11g connection is very slow
                      Billy~Verreynne
                      Aron Tunzi wrote:


                      Wow, I can ping your DB :-) ... ok not true but pay attention posting IP addresses!
                      There is no danger in psoting 192.168 address range addresses. These are private addresses and do not exist on the Internet.

                      See http://en.wikipedia.org/wiki/Private_network for basic details.

                      If you can ping that 192.168 address posted by the OP, it simply means that the IP is in used on your private network - as it would be in use on 1000's of other networks.
                      • 8. Re: oracle-11g connection is very slow
                        Billy~Verreynne
                        Phani  wrote:

                        (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.247.27)(PORT=1522)))
                        Why use port 1522?

                        It is always a good idea to use the standard ports for a network application. There is no logic in obfuscating ports for security purposes. It also makes network management and dealing with quality of service issues for example, much more complex if you do not stick to the registered application ports.

                        Also note that if you provide a dotted IP address, only that address will be used for binding the tcp port as a listening end point. This means no connections will be accepted on localhost and other IP addresses of that server. Make sure that this is what is technically required.
                        ifconfig
                        bond6     Link encap:Ethernet  HWaddr 00:26:55:D3:02:B6
                        Why are you using bonding? How many bonded interfaces are there and now many physical NICs? Bond6 alludes that it is the 7th bonded interface - and at 2 NICs per bonded interface it implies that your server has 14 physical Ethernet interfaces. Which I doubt is true.
                        RX packets:1309675596 errors:5 dropped:0 overruns:0 frame:3
                        Not good to see any errors. What does ethtool stats show? Also check that the physical interfaces are enabled for full duplex. Some Cisco switches do not negotiate it properly and the NIC could be running half duplex.

                        Also - using bonding... does not seem right. The 1st and default bonded interface should be +/dev/bond0+ - and not bond6.

                        Check the server's network configuration (the +ifcfg-*+ files in +/etc/sysconfig/networking-scripts+ directory). Suggest that you get a network engineer (or the like) to assist with reviewing the network setup of that server.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               
                        • 9. Re: oracle-11g connection is very slow
                          794856
                          hi this is the ouput of ethtool stats

                          ethtool stats
                          Settings for stats:
                          Cannot get device settings: No such device
                          Cannot get wake-on-lan settings: No such device
                          Cannot get message level: No such device
                          Cannot get link status: No such device
                          No data available

                          OS admin checked and found that one NIC card is down(redundancy) and got it up by resetting the connections. Eventhough the problem is same.
                          From my part changed the TNSADMIN location to another home where this connectivity is working fine for other databases, recreated the listner service and started. but for this database connectivity is remained slow only. tnsping response is nomal in all cases. but while connecting only I am getting delayed.

                          Regards
                          Phani Kumar

                          Edited by: Phani on Aug 9, 2011 5:38 PM

                          Edited by: Phani on Aug 9, 2011 5:38 PM
                          • 10. Re: oracle-11g connection is very slow
                            AronTunzi
                            Try to check if some strange trigger is enabled.
                            • 11. Re: oracle-11g connection is very slow
                              Billy~Verreynne
                              Phani  wrote:
                              hi this is the ouput of ethtool stats
                              I did not post a actual Linux command, but a function - as in list the stats of the underlying bonded interfaces using the command ethtool. E.g. command:
                              +ethtool --statistics eth0+

                              A total stats view can be obtained using:
                              netstat -s
                              OS admin checked and found that one NIC card is down(redundancy) and got it up by resetting the connections. Eventhough the problem is same.
                              From my part changed the TNSADMIN location to another home where this connectivity is working fine for other databases, recreated the listner service and started. but for this database connectivity is remained slow only. tnsping response is nomal in all cases. but while connecting only I am getting delayed.
                              If the connectivity problem is not due to a network issue, then it could be the time it takes for the listener to create an Oracle dedicated server process to service the client connection. This can be traced via the listener by enabling debug tracing and making a client connection.

                              Another potential problem is something like logon triggers - database code that is executed as part of the logon process and thus seemingly causing a slow logon from a client perspective.
                              • 12. Re: oracle-11g connection is very slow
                                794856
                                Hi Billy,

                                It got resolved now, after removing the nameserver entries from /etc/resolv.conf file, becuase the servers which configured in this file are not existing and not reachable. I read in one thread that oracle 11g will seek for dns names by default. as these servers are not reachable its going delayed.

                                thanks alot for you support. you helped me in trouble shooting the problem in all ways from os, db and network

                                Regards
                                Phani Kumar