Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1

    Unanswered: Dropped Sessions?

    We have a procedure that ran overnight for several hours before the SQL session it was running in was dropped when cmctl was stopped & restarted (we stop & restart cmctl every morning when we bounce our database).

    We've gone back to the developers to ask them why the job is taking so long; their reply is:

    "In our experience it could be caused by the sql session being disconnected although still showing as though it is connected to the client machine. The disconnection is not caused by a "kill session" operation but by dropped network packets."

    i.e. what they're saying is that the session is dropped long before 6am, because of dropped packets, but still shows as being connected when cmctl stops at 6am. (BTW, the logs show that that the procedure stopped at 6am, as a result of losing its connection to Oracle.)

    I have never heard of this one before! Is it for real, or are they grasping at straws?
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Number 1 ... "Bouncing the database every morning ???"
    Number 2 ... Trace the procedure to see what it's doing ...Find the worst SQL and go from there ...
    Number 3 ... You can set the user that runs the overnight process, up in
    a profile that a low idle time setting ... Oracle will automatically get rid
    of the session once that time has passed.
    Number 4 ... Network issues may be involved, but that means other problems as well ...
    Number 5: Shoot the developers !!!! .... Could also potentially be Number 1


    HTH
    Gregg
    Last edited by gbrabham; 09-23-04 at 12:06.

  3. #3
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Gregg,

    With regard to Numbers 1 & 5, my hands are tied somewhat because the software is provided by a 3rd party company, & the developers are partly working for that company as well, so I have orders to follow!

    What I was really after knowing is if anyone has ever heard of sessions being dropped by a network but still showing in Oracle as being connected?

    (BTW - the procedure was re-written & now takes only 15 minutes, so it's pretty obvious where the problem was, but the management still want to know if what the developers said could actually happen)
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  4. #4
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    Yes it can happen... If the network connection drops, you will still have a session in Oracle ... You can handle getting rid of those sessions in a couple of ways... I personally prefer the profile method where I can specific the "idle" time for groups of uses ...

    Gregg

  5. #5
    Join Date
    Jun 2004
    Posts
    796
    Provided Answers: 1
    Gregg, thanks for your answer. I'm actually implementing a user profile in the next week or so that, among other things, will be setting an idle time to disconnect - maybe then things will be a little clearer when trying to work out what happened when.

    I assume, then, that dropped session on the network side but still showing as connected on the Oracle side is a 'feature' of Oracle!
    90% of users' problems can be resolved by punching them - the other 10% by switching off their PCs.

  6. #6
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    It all depends on how the connection was terminated. If the network connection is broken and the timeout is unlimited, oracle will wait for input from that user. Personally, I have always implemented a timeout value for inactivity.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •