Re: Why doesn't Oracle kill dead connections?

From: Mogens Nørgaard <mln_at_miracleas.dk>
Date: Wed, 16 Jan 2002 15:38:57 -0800
Message-ID: <F001.003F220D.20020116152037@fatcity.com>

DCD was invented and implemented for 7.1 of Oracle because of the strange and wonderful handling of timeout stuff in Unix. Typically, it can take between 8 and 80 hours for Unix to discover that a session should be killed since it no longer has a connection to the Oracle database. The problem is this:

Sometimes it works. Sometimes it doesn't.

There can be many reasons for a packet not to return.

Back to Life: But DCD works by sending this special, empty packet, every n minutes. If the packet comes back with an error attached, the connection will be terminated. If it doesn't it's assumed that the connection is alive and kicking.

Mogens

dgoulet_at_vicr.com wrote:

>Question 1 is how long does the report take to run? If you have DCD set up with
>a time-out of 1 second your network is going to take a heck of a hit just from
>probe packets. The most common setting I've run into is 15 to 30 minutes so
>that does not help. Setting up a profile for the Internet user that allocates a
>max cpu can likewise be nasty in that the time allocation may not be large
>enough for the report to run. This can get especially frustrating as the amount
>of data that the report must process grows. The last point is that I do not
>believe DCD works with sessions that are ACTIVE since the client side (yes the
>OAS server is a client) is waiting for the database and not responding to
>anything else. I believe it only works with INACTIVE sessions which is not your
>problem. The other point is that the WEB is a stateless world where even the
>web server may not be cognizant of the client closing his/her browser. Do you
>have the same problem if the client hits the STOP button?
>
>Dick Goulet
>
>____________________Reply Separator____________________
>Author: "Karniotis; Stephen" <Stephen_Karniotis_at_compuware.com>
>Date: 1/16/2002 6:15 AM
>
>Much of this is controlled through Oracle's SQL*Net product. It needs to be
>configured. It also depends on what version of the database you are using.
>Oracle8.0/8i and beyond have mechanisms called profiles (oracle7 had them as
>well but didn't work well) that can assist in dead connection detection.
>
>The problem with Web-based dead connection is that if the application server
>retains the connection, the stuff will work. However, if the application
>server has started the SQL/PL/SQL process and then goes off to do other
>work, and return back, the dead connection stuff may disconnect you by
>accident.
>
>Guess it is trial and error.
>
>Thank You
>
>Stephen P. Karniotis
>Technical Alliance Manager
>Compuware Corporation
>Direct: (248) 865-4350
>Mobile: (248) 408-2918
>Email: <mailto:Stephen.Karniotis_at_Compuware.com>
>Stephen.Karniotis_at_Compuware.com
>Web: <http://www.compuware.com/> www.compuware.com
>
>
>-----Original Message-----
>Sent: Tuesday, January 15, 2002 7:20 PM
>To: Multiple recipients of list ORACLE-L
>
>Hi
>I have a Reporting appliaction. The reports are run from the browser . The
>reports are basically Pl/SQL packages .the request is made from the browser
>and then communicated to the database thru OAS .(oracle application server)
>which acts as web server.
>The problem is when a user executes a report (i.e. hits the RUN button) and
>then closes the broser before the execution of the report is
>completed.Oracle still contimues running the query in the database. The
>session still remains ACTIVE in the database even though the client has
>closed the connection (i.e. the user has closed the browser).
>Is there any way to force Oracle to kill the session when the user closes
>the browser?
>
>TIA
>
><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
><html xmlns:v="urn:schemas-microsoft-com:vml"
>xmlns:o="urn:schemas-microsoft-com:office:office"
>xmlns:w="urn:schemas-microsoft-com:office:word"
>xmlns="http://www.w3.org/TR/REC-html40">
>
><head>
><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=iso-8859-1">
>
>
><meta name=ProgId content=Word.Document>
><meta name=Generator content="Microsoft Word 9">
><meta name=Originator content="Microsoft Word 9">
><link rel=File-List href="cid:filelist.xml_at_01C19E6E.8C318010">
>
><style>
>
></style>
>
></head>
>
><body bgcolor=white lang=EN-US link=blue vlink=purple style='tab-interval:.5in'>
>
><div class=Section1>
>
>face="Comic Sans MS">font-family:"Comic Sans MS"'>Much of this is controlled through Oracle’s
>SQL*Net product.  It needs to be
>configured.  It also depends on
>what version of the database you are using.yes">  Oracle8.0/8i and beyond have mechanisms called profiles
>(oracle7 had them as well but didn’t work well) that can assist in dead
>connection
>detection.<o:p></o:p>
>
>face="Comic Sans MS">font-family:"Comic Sans MS"'><![if
>!supportEmptyParas]> <![endif]><o:p></o:p>
>
>face="Comic Sans MS">font-family:"Comic Sans MS"'>The problem with Web-based dead connection is that
>if the application server retains the connection, the stuff will work.<span
>style="mso-spacerun: yes">  However, if the application server has
>started the SQL/PL/SQL process and then goes off to do other work, and return
>back, the dead connection stuff may disconnect you by
>accident.<o:p></o:p>
>
>face="Comic Sans MS">font-family:"Comic Sans MS"'><![if
>!supportEmptyParas]> <![endif]><o:p></o:p>
>
>face="Comic Sans MS">font-family:"Comic Sans MS"'>Guess it is trial and
>error.<o:p></o:p>
>
>face="Comic Sans MS">font-family:"Comic Sans MS"'><![if
>!supportEmptyParas]> <![endif]><o:p></o:p>
>
><spa
>n
>class=EmailStyle18><i
>style='mso-bidi-font-style:
>normal'><span
>style='font-size:10.0pt;mso-bidi-font-size:12.0pt;font-family:"Monotype
>Corsiva";
>color:#3366FF;font-weight:bold;font-style:italic'>Thank
>You<o:p></o:p>
>
>face=Arial>Arial'><![if
>!supportEmptyParas]> <![endif]><o:p></o:p>
>
>normal'>mso-bidi-font-size:12.0pt;font-family:Tahoma;mso-bidi-font-family:Arial;
>color:blue;font-weight:bold'>Stephen P.
>Karniotis<o:p></o:p>
>
>face=Haettenschweiler>font-family:Haettenschweiler;color:red;mso-bidi-font-weight:bold'>Technical
>Alliance Manager<o:p></o:p>
>
>face=Haettenschweiler>font-family:Haettenschweiler;color:red;mso-bidi-font-weight:bold'>Compuware
>Corporation<o:p></o:p>
>
>face=Haettenschweiler>font-family:Haettenschweiler;color:red;mso-bidi-font-weight:bold'>Direct:<span
>style='mso-tab-count:1'>         
>(248)
>865-4350<o:p></o:p>
>
>face=Haettenschweiler>font-family:Haettenschweiler;color:red;mso-bidi-font-weight:bold'>Mobile:<span
>style='mso-tab-count:1'>         
>(248)
>408-2918<o:p></o:p>
>
>face=Haettenschweiler>font-family:Haettenschweiler;color:red;mso-bidi-font-weight:bold'>Email:<span
>style='mso-tab-count:1'>         &n
>bsp;   <a
>href="mailto:Stephen.Karniotis_at_Compuware.com"><span
>style='font-size:12.0pt;color:red'>Stephen.Karniotis_at_Compuware.com
></a><o:p></o:p>
>
>face=Haettenschweiler>font-family:Haettenschweiler;color:red;mso-bidi-font-weight:bold'>Web:<span
>style='mso-tab-count:1'>         &n
>bsp;    <a
>href="http://www.compuware.com/">12.0pt;color:red'>www.compuware.com</a><o:p></o:p></
>span>
>
>face=Arial>Arial;mso-bidi-font-weight:bold'><![if
>!supportEmptyParas]> <![endif]><o:p></o:p>
>
><span
>class=EmailStyle15><font
>size=2 color=navy face="Comic Sans MS">mso-bidi-font-size:12.0pt;font-family:"Comic Sans MS"'><![if
>!supportEmptyParas]> <![endif]><o:p></o:p>
>
>face=Tahoma><span
>style='font-size:10.0pt;font-family:Tahoma;color:black'>-----Original
>Message----- 
>From: Sona
>[mailto:sona_us_at_hotmail.com] 
>Sent: Tuesday, January 15, 2002
>7:20 PM 
>To: Multiple recipients of list
>ORACLE-L 
>Subject: Why doesn't Oracle kill
>dead connections?
>
>Roman"><span
>style='font-size:12.0pt'><![if
>!supportEmptyParas]> <![endif]><o:p></o:p>
>
>face=Arial><span
>style='font-size:10.0pt;font-family:Arial;color:black'>Hi<font
>color=black><span
>style='color:black;mso-color-alt:windowtext'><o:p></o:p>
>
>face=Arial>I have
>a Reporting appliaction. The reports are run from the browser . The reports are
>basically Pl/SQL packages .the request is made from the browser and then
>communicated to the database thru  OAS .(oracle application server) which
>acts as web server.mso-color-alt:windowtext'><o:p></o:p>
>
>face=Arial>The
>problem is when a user executes a report (i.e. hits the RUN button) and then
>closes the broser before the execution of the report is completed.Oracle still
>contimues running the query in the database. The session still remains ACTIVE
>in the database even though the client has closed the connection (i.e. the user
>has closed the browser).black;mso-color-alt:windowtext'><o:p></o:p>
>
>face=Arial>Is
>there any way to force Oracle to kill the session when the user closes the
>browser?windowtext'><o:p></o:p>
>
>face="Times New Roman"><span
>style='font-size:12.0pt;color:black'> <font
>color=black><span
>style='color:black;mso-color-alt:windowtext'><o:p></o:p>
>
>face=Arial><span
>style='font-size:10.0pt;font-family:Arial;color:black'>TIA<font
>color=black><span
>style='color:black;mso-color-alt:windowtext'><o:p></o:p>
>
></div>
>
></body>
>
></html>
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Mogens =?ISO-8859-1?Q?N=F8rgaard?=
  INET: mln_at_miracleas.dk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Wed Jan 16 2002 - 17:38:57 CST