This discussion is archived
1 2 3 5 Previous Next 64 Replies Latest reply: Jan 28, 2010 4:53 PM by jgarry RSS

SQLPLUS connection slow:takes 3-10 seconds to connect to database

dba05 Journeyer
Currently Being Moderated
Hi,

In one of our DBs SQLplus connection time varies a lot.Some times it's fast,takes less than 3 seconds to connect but most of the time it takes a long time to connect.

This is true whether I connect sqlplus "/as sysdba" locally or sqlplus user@dbname.

Database version in 10.2.0.4 and it's running on Solaris 10 Server.

We have other databases running on same server but we didn't find any issues there.

When I did a truss of the sqlplus I found that a jump in time takes place at following line

-----------------------------------
0.8975 ioctl(9, _IOWRN('i', 20, 8), 0xFFFFFFFF7FFF9530) = 0
0.8978 ioctl(9, SIOCGIFFLAGS, 0x1001B7ED0) = 0
0.8980 ioctl(9, SIOCGIFFLAGS, 0x1001B7EF0) = 0
0.8982 ioctl(9, SIOCGIFADDR, 0x1001B7EF0) = 0
0.8985 ioctl(9, SIOCGARP, 0xFFFFFFFF7FFF950C) = 0
0.8989 write(10, "03 w\0\006\0\0\0\0\003 s".., 887) = 887
read(11, 0x1001CF216, 2064) (sleeping...)
4.9681 read(11, "03F4\0\006\0\0\0\0\0\b\0".., 2064) = 1012
4.9699 open("/ap01/apps/oracle/product/10.2.0.4/rdbms/mesg/oraus.msb", O_RDONLY) = 12
4.9703 fcntl(12, F_SETFD, 0x00000001) = 0
4.9705 lseek(12, 0, SEEK_SET) = 0
4.9707 read(12, "1513 "011303\t\t\0\0\0\0".., 256) = 256
4.9710 lseek(12, 512, SEEK_SET) = 512
----------------------------------------------------------------------------------------

I did a relink of oracle home and that didn't help.

If you can give your thoughts on this issue,it will be really helpful.

Thanks...
Sajan
  • 1. Re: SQLPLUS connection slow:takes 3-10 seconds to connect to database
    637286 Explorer
    Currently Being Moderated
    Could you post your listener.ora and tnsnames.ora? Is the behaviour also encountered if you make a connection from a remote server?
    Do you have logon trigger or profile setup in this database instance?

    Edited by: Justiono on Jan 22, 2010 4:19 AM
  • 2. Re: SQLPLUS connection slow:takes 3-10 seconds to connect to database
    ji li Pro
    Currently Being Moderated
    I (we) have experienced similar problems when we did not have our memory configurations set correctly (e.g., swap space, ulimits, etc.). In case of Solaris, you have to also consider your settings for projects to control memory and semaphores.

    How do you have these set?, and also do a ulimit -a and show your settings there.
  • 3. Re: SQLPLUS connection slow:takes 3-10 seconds to connect to database
    dba05 Journeyer
    Currently Being Moderated
    Yes.. We are facing same issue while connecting from remote machines as well.
    We don't have any logon triggers set up.Which profile options you are mentioning?

    Please find listener.ora and tnsnames.ora


    listener
    ---
    LSNR_SOAE1 =
    (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=soae1_svr.domain)(PORT=1535)))
    SID_LIST_LSNR_SOAE1 =
    (SID_LIST =
    (SID_DESC=(ORACLE_HOME=/ap01/apps/oracle/product/10.2.0.4)(SID_NAME=soae1))
    )
    LOG_DIRECTORY_LSNR_SOAE1 = /soae1/apps/oracle/network/log/new
    TRACE_DIRECTORY_LSNR_SOAE1 = /soae1/apps/oracle/network/log/new
    SUBSCRIBE_FOR_NODE_DOWN_EVENT_LSNR_SOAE1 = OFF
    ----
    TNS

    SOAE1 = (DESCRIPTION=
    (ADDRESS=(PROTOCOL=tcp)(HOST=soae1_svr.domain)(PORT=1535))
    (CONNECT_DATA=(SERVICE_NAME=soae1)))
    ------

    ################
    # Filename......: sqlnet.ora
    ################
    NAMES.DIRECTORY_PATH= (LDAP,TNSNAMES)
    ----
  • 4. Re: SQLPLUS connection slow:takes 3-10 seconds to connect to database
    dba05 Journeyer
    Currently Being Moderated
    Hi,

    Thanks for the update...

    Please find the o/p of ulimit -a

    oracle$ ulimit -a
    time(seconds) unlimited
    file(blocks) unlimited
    data(kbytes) unlimited
    stack(kbytes) 8192
    coredump(blocks) 0
    nofiles(descriptors) 256
    vmemory(kbytes) unlimited
    oracle$

    We are monitoring the server using OEM GC and we haven't got alerts about memory usage or swap.

    Also since other databases on same server doesn't have any issue,can we assume that it's not an issue at OS level ? am I correct ?

    Thanks,
    Sajan
  • 5. Re: SQLPLUS connection slow:takes 3-10 seconds to connect to database
    ji li Pro
    Currently Being Moderated
    Okay, that looks good. You're unlimited to use all that is available, but how about your swapspace?
    Can you do a vmstat a few times while you are waiting for yoru sqlplus to start and see if you are getting and read ins or read outs?
  • 6. Re: SQLPLUS connection slow:takes 3-10 seconds to connect to database
    dba05 Journeyer
    Currently Being Moderated
    Hi,

    I will upload the swap details..
    Please note that all are not "unlimited" in ulimit.

    stack(kbytes) 8192
    coredump(blocks) 0
    nofiles(descriptors) 256

    Thanks,
    sajan
  • 7. Re: SQLPLUS connection slow:takes 3-10 seconds to connect to database
    ji li Pro
    Currently Being Moderated
    That's okay, at least the main ones are unlimited.
    You could verify the Oracle installation guide.
    Just as an example, here are the ones we have set to unlimited (of course this is AIX, but same principles).
    $ <rcat1> /home/oracle>ulimit -a
    time(seconds)        unlimited
    file(blocks)         unlimited
    data(kbytes)         unlimited
    stack(kbytes)        4194304
    memory(kbytes)       unlimited
    coredump(blocks)     2097151
    nofiles(descriptors) 2000
    threads(per process) unlimited
    processes(per user)  unlimited
    ---> On second thought, you might bump up the others as well.
    I believe the guide says to set them all at unlimited.

    Edited by: ji li on Jan 21, 2010 4:04 PM
  • 8. Re: SQLPLUS connection slow:takes 3-10 seconds to connect to database
    367117 Journeyer
    Currently Being Moderated
    Sajan.kg wrote:
    Hi,
    This is true whether I connect sqlplus "/as sysdba" locally or sqlplus user@dbname.
    Did you try the following ?

    Get to SQLPLUS prompt and then connect to the database.
    $sqlplus
    
    and then 
    
    connect / as sysdba
    Can you also post the output of
    echo $PATH
  • 9. Re: SQLPLUS connection slow:takes 3-10 seconds to connect to database
    dba05 Journeyer
    Currently Being Moderated
    Please find vmstat output.

    -----------------------------------------------------------------------------
    oracle$ vmstat 1
    kthr memory page disk faults cpu
    r b w swap free re mf pi po fr de sr s1 s2 s3 sd in sy cs us sy id
    0 2 0 9301376 11852288 10304 1506 138764 20 18 0 0 18 -74 18 0 9263 33348 11138 18 9 74
    0 1 0 6699936 9714520 4411 939 92069 12 9 0 0 5 0 4 0 4388 19946 3990 10 6 83
    0 2 0 6745096 9732304 4486 426 100941 0 0 0 0 37 0 37 0 4408 16675 3710 10 5 85
    0 1 0 6472776 9442360 4247 363 90844 0 0 0 0 1 0 1 0 4904 16223 4416 9 6 85
    0 1 0 6470440 9424496 4554 1039 90622 7 7 0 0 0 0 0 0 4534 18027 4145 11 8 81
    0 1 0 6472864 9422200 3548 29 111991 4 4 0 0 2 0 2 0 4391 15079 3471 8 8 84
    0 1 0 6732696 9680136 4917 67 93522 0 0 0 0 2 0 2 0 4256 24562 3556 13 6 81
    0 1 0 6709256 9637472 4962 1860 90700 13 13 0 0 10 0 11 0 4858 48682 4995 23 9 68

    Thanks,
    Sajan
  • 10. Re: SQLPLUS connection slow:takes 3-10 seconds to connect to database
    dba05 Journeyer
    Currently Being Moderated
    Hi,


    I tried this,But it's also slow.
    ------------------------------------------
    Get to SQLPLUS prompt and then connect to the database.

    $sqlplus

    and then

    connect / as sysdba
    -----------------------------------



    oracle$ echo $PATH
    /usr/ccs/bin:/ap01/apps/oracle/product/10.2.0.4/bin:/ap01/apps/oracle/product/10.2.0.4/OPatch:/local/bin:/opt/bin/::/u/dba/oracle/bin:/dba/local/bin:/dba/bin:/local/bin:/bin:/local/sbin:/local/etc:/usr/bin:/usr/ccs/bin:/usr/sbin:/usr/ucb:/local/ddts/bin:/usr/X11/bin/X11:/usr/openwin/bin:/ap01/apps/oracle/product/10.2.0.4/bin:/local/bin
    oracle$

    Thanks,
    Sajan
  • 11. Re: SQLPLUS connection slow:takes 3-10 seconds to connect to database
    711666 Newbie
    Currently Being Moderated
    Hello.
    If im reading this correctly you have some pageouts, this could indicate memory problems.


    Can you please run command: top

    and post the results here

    also sar -d (im using hpux) Report activity for each block device, e.g., disk, i don't know whats the equivalent ommand in solaris ?

    Finally, what else you have running on this system, is it just the slow connection problems ?
  • 12. Re: SQLPLUS connection slow:takes 3-10 seconds to connect to database
    dba05 Journeyer
    Currently Being Moderated
    Hi,

    Other than slow connection problem,database performance is OK.
    As of now there is less load on the database.

    We have total 10 databases running on this machine and no other databases have this issue.

    ------------------------------------------------------
    oracle$ top
    load averages: 5.47, 5.43, 5.48 14:51:26
    659 processes: 650 sleeping, 1 zombie, 1 stopped, 7 on cpu
    CPU states: 82.3% idle, 9.6% user, 8.1% kernel, 0.0% iowait, 0.0% swap
    Memory: 32G real, 10G free, 17G swap in use, 6787M swap free

    PID USERNAME LWP PRI NICE SIZE RES STATE TIME CPU COMMAND
    26517 oracle 1 0 0 643M 613M cpu/8 17:45 2.89% oracle
    26041 oracle 1 0 0 2433M 2416M cpu/1 625:34 2.73% oracle
    2296 oracle 1 60 0 639M 610M sleep 49:47 2.07% oracle
    6769 oracle 1 0 0 2430M 2415M cpu/16 0:50 2.03% oracle
    2255 oracle 1 59 0 639M 589M sleep 164.1H 0.97% oracle
    9390 oracle 12 0 0 3515M 3234M cpu/24 145.6H 0.57% oracle
    26440 oracle 1 59 0 638M 588M sleep 62.4H 0.56% oracle
    4903 oracle 1 59 0 2430M 2419M sleep 0:14 0.22% oracle
    17214 oracle 1 59 0 3505M 3229M sleep 186:44 0.19% oracle
    28713 oracle 11 59 0 848M 688M sleep 2:52 0.17% oracle
    26438 oracle 1 59 0 638M 588M sleep 63.5H 0.11% oracle
    26190 oracle 1 19 0 2435M 2409M cpu/20 294:35 0.11% oracle
    26531 oracle 1 59 0 639M 588M sleep 177.5H 0.10% oracle
    57 root 1 59 0 63M 14M sleep 136.1H 0.07% vxconfigd
    7571 oracle 1 59 0 2432M 2010M sleep 0:12 0.06% oracle
    --------------------------------------------------

    oracle$ sar 5 5

    SunOS thanatos 5.10 Generic_127127-11 sun4v 01/21/2010

    14:55:26 %usr %sys %wio %idle
    14:55:32 13 8 0 79
    14:55:37 10 7 0 84
    14:55:43 12 8 0 80
    14:55:48 13 8 0 79
    14:55:54 12 8 0 80

    Average 12 8 0 81
    oracle$
    ------------------------------------------

    Thanks,
    sajan
  • 13. Re: SQLPLUS connection slow:takes 3-10 seconds to connect to database
    711666 Newbie
    Currently Being Moderated
    Hello again Sajan.


    What's the value of the following parameter in your sqlnet.ora file?
    SQLNET.AUTHENTICATION_SERVICES
  • 14. Re: SQLPLUS connection slow:takes 3-10 seconds to connect to database
    dba05 Journeyer
    Currently Being Moderated
    Hi,

    It's not set in my sqlnet.ora.

    The files has only one line; --->NAMES.DIRECTORY_PATH= (LDAP,TNSNAMES)

    Thanks,
    Sajan
1 2 3 5 Previous Next

Legend

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