1 2 3 Previous Next 64 Replies Latest reply: Jan 28, 2010 6:53 PM by jgarry RSS

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

    dba05
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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 Previous Next