This discussion is archived
12 Replies Latest reply: Aug 10, 2011 4:38 AM by 794856 RSS

oracle-11g connection is very slow

794856 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Explorer
    Currently Being Moderated
    Try to check if some strange trigger is enabled.
  • 11. Re: oracle-11g connection is very slow
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points