13 Replies Latest reply: Apr 16, 2012 12:47 PM by damorgan RSS

    user connection taking long time

    890566
      Hello All,

      Oracle version : 11.1.0.7
      Platfrom : AIX 6.1 - 2 node RAC

      I have 3 users X,Y,Z with similar privileges but user Z is taking more than 3 minutes for connecting to sqlplus from server , and throwing I/O errorwhen trying from toad or sql developer , where as rest of the users are working fine.

      ORA-03135 When Connecting to the Database [ID 404724.1]

      As per the above metalink I have enabled the events trace level 8 , and didnt get much info.

      Below is the sample TKPROF output for this session.

      Please help me in this regard.

      SQL ID: 8x615vyks733p
      Plan Hash: 1975509590
      select count(*)
      from
      user_objects


      call count cpu elapsed disk query current rows
      ------- ------ -------- ---------- ---------- ---------- ---------- ----------
      Parse 2 0.04 394.82 0 0 0 0
      Execute 2 0.00 0.00 0 0 2 0
      Fetch 4 0.05 48.98 0 1494 0 2
      ------- ------ -------- ---------- ---------- ---------- ---------- ----------
      total 8 0.09 443.80 0 1494 2 2

      Misses in library cache during parse: 1
      Optimizer mode: ALL_ROWS
      Parsing user id: 171

      Rows Row Source Operation
      ------- ---------------------------------------------------
      1 SORT AGGREGATE (cr=747 pr=0 pw=0 time=0 us)
      12 VIEW USER_OBJECTS (cr=747 pr=0 pw=0 time=0 us cost=183 size=0 card=6456)
      12 UNION-ALL (cr=747 pr=0 pw=0 time=0 us)
      12 FILTER (cr=746 pr=0 pw=0 time=0 us)
      12 HASH JOIN (cr=746 pr=0 pw=0 time=0 us cost=183 size=695968 card=6692)
      161 INDEX FULL SCAN I_USER2 (cr=1 pr=0 pw=0 time=0 us cost=1 size=1056 card=48)(object id 47)
      12 TABLE ACCESS FULL OBJ$ (cr=745 pr=0 pw=0 time=0 us cost=182 size=548744 card=6692)
      0 TABLE ACCESS BY INDEX ROWID IND$ (cr=0 pr=0 pw=0 time=0 us cost=2 size=8 card=1)
      0 INDEX UNIQUE SCAN I_IND1 (cr=0 pr=0 pw=0 time=0 us cost=1 size=0 card=1)(object id 41)
      0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us cost=2 size=27 card=1)
      0 INDEX FULL SCAN I_USER2 (cr=0 pr=0 pw=0 time=0 us cost=1 size=19 card=1)(object id 47)
      0 INDEX RANGE SCAN I_OBJ4 (cr=0 pr=0 pw=0 time=0 us cost=1 size=8 card=1)(object id 39)
      0 INDEX RANGE SCAN I_LINK1 (cr=1 pr=0 pw=0 time=0 us cost=0 size=13 card=1)(object id 135)


      Elapsed times include waiting on following events:
      Event waited on Times Max. Wait Total Waited
      ---------------------------------------- Waited ---------- ------------
      SQL*Net message to client 4 0.00 0.00
      row cache lock 162 2.93 443.62
      gc cr block 2-way 8 0.00 0.00
      SQL*Net message from client 3 1912.16 1912.16
      library cache lock 4 0.00 0.00
      library cache pin 4 0.00 0.00
        • 1. Re: user connection taking long time
          890566
          We have also tried bouncing the database , but still it didnt fix the issue.
          Any help in this regard would be appreciated.

          Regards,
          San
          • 2. Re: user connection taking long time
            damorgan
            Assuming your users are on the same subnet I would take a close look at the following:

            1. TNSNAMES.ORA and SQLNET.ORA files on their terminal ... are there name resolution issues?
            2. How long does it take them to ping and TNSPING by comparison?
            3. Are they connecting to the same service?
            4. What happens when the person with the slower connection tries to connect from the desk of someone with a faster connection?
            5. Is there any difference in antivirus software running on the fast and slow desktops ... not just the version but the way it has been configured?
            6. Is this a new behaviour for an existing system or is it behaviour that has always existed?
            7. Can you correlate the beginning of this behaviour with any network-related events?
            • 3. Re: user connection taking long time
              890566
              Thanks for the reply.

              1. I Dont see any issue with the TNS names , because all the other users are able to connect fine.
              2. TNS Ping is coming back quickly.
              3. All of them are connecting through same service and to the same database.
              4. I am connecting directly from server.
              5.
              6. This is the first time I am seeing this , and this user was working fine when I created it and this issue came up all of a sudden
              7. Since I am working on server , I didnt see major issues with network.

              Regards,
              San
              • 4. Re: user connection taking long time
                890566
                Can any one help me with this issue ?

                Thanks and regards,
                San
                • 5. Re: user connection taking long time
                  damorgan
                  You didn't address #5. ;-)

                  Take the user with the slow connection and have them walk over to the desktop of someone with a fast connection.

                  Does "slow" follow them?

                  In other words ... separate the person from the terminal.
                  • 6. Re: user connection taking long time
                    onedbguru
                    typically when this happens it is caused by a mis-configured DNS or hosts file. The wait time is most likely the desktop trying to back-translate the IP address.
                    • 7. Re: user connection taking long time
                      890566
                      I have tried from different desktops but still the issue is the same , Only one user is not able to connect (taking more than 3 minutes when trying directly from host). All the other users with similar privileges are able to connect immediately.

                      When I have traced the events level 8 ,I was seeing waits for row cache locks. I was just wondering if this has got anything to do with the slow connection.

                      Regards,
                      San
                      • 8. Re: user connection taking long time
                        damorgan
                        Please be clearer ... when the slow user walks over to the computer of a user who has a fast response ... do they still get the slow response? YES or NO.

                        If YES then it is the user ... if NO then it is the computer. We need to be clear which it is to proceed.

                        It seems you are saying it is the user but I would like to know that for sure. Thank you.
                        • 9. Re: user connection taking long time
                          onedbguru
                          you said "the other users with similar privileges" privs. So, create a test user with those EXACT privs and see if the problem still exists. Then start removing privs until you find the problem. Check the connect string being used, they may be using jdbc vs "classic" Does the user have a different TNS_ADMIN directory defined?
                          • 10. Re: user connection taking long time
                            890566
                            I have tried from different computers , but the user is still slow. I am pretty sure that it is the user that has the problem.
                            • 11. Re: user connection taking long time
                              890566
                              I have created a different user with exactly same privileges and that user has no issues in loggin in.

                              I have revoked the connect privilege from this user Z.

                              and then tried to drop the user.

                              drop user Z cascade
                              *
                              ERROR at line 1:
                              ORA-01940: cannot drop a user that is currently connected.

                              When I check if there is any existing connection , it says no rows selected.

                              select * from gv$session where USERNAME='Z';

                              no rows selected.

                              I have also bounced the database and tried but still the same problem. Please help me with this.
                              • 12. Re: user connection taking long time
                                890566
                                There is no TNS_ADMIn defined and I am trying all this directly on the host.
                                • 13. Re: user connection taking long time
                                  damorgan
                                  Bounce the database ... drop the user.

                                  If that does not work ... open an SR.

                                  If you can not bounce the database ... open an SR.