Re:RE: Why doesn't Oracle kill dead connections?

From: <dgoulet_at_vicr.com>
Date: Wed, 16 Jan 2002 07:50:06 -0800
Message-ID: <F001.003F1660.20020116073114@fatcity.com>

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">
<!--[if gte mso 9]><xml>
<w:WordDocument>

  <w:Zoom>0</w:Zoom>
  <w:DocumentKind>DocumentEmail</w:DocumentKind>
  <w:EnvelopeVis/>

</w:WordDocument>
</xml><![endif]-->
<style>
<!--

/* Font Definitions */
@font-face

        {font-family:"Comic Sans MS";
        panose-1:3 15 7 2 3 3 2 2 2 4;
        mso-font-charset:0;
        mso-generic-font-family:script;
        mso-font-pitch:variable;
        mso-font-signature:647 0 0 0 159 0;}
@font-face
        {font-family:Garamond;
        panose-1:2 2 4 4 3 3 1 1 8 3;
        mso-font-charset:0;
        mso-generic-font-family:roman;
        mso-font-pitch:variable;
        mso-font-signature:647 0 0 0 159 0;}
@font-face
        {font-family:"Monotype Corsiva";
        panose-1:3 1 1 1 1 2 1 1 1 1;
        mso-font-charset:0;
        mso-generic-font-family:script;
        mso-font-pitch:variable;
        mso-font-signature:647 0 0 0 159 0;}
@font-face
        {font-family:Tahoma;
        panose-1:2 11 6 4 3 5 4 4 2 4;
        mso-font-charset:0;
        mso-generic-font-family:swiss;
        mso-font-pitch:variable;
        mso-font-signature:553679495 -2147483648 8 0 66047 0;}
@font-face
        {font-family:Haettenschweiler;
        panose-1:2 11 7 6 4 9 2 6 2 4;
        mso-font-charset:0;
        mso-generic-font-family:swiss;
        mso-font-pitch:variable;
        mso-font-signature:647 0 0 0 159 0;}

/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal

        {mso-style-parent:"";
        margin:0in;
        margin-bottom:.0001pt;
        mso-pagination:widow-orphan;
        font-size:12.0pt;
        font-family:"Times New Roman";
        mso-fareast-font-family:"Times New Roman";}
a:link, span.MsoHyperlink
        {color:blue;
        text-decoration:underline;
        text-underline:single;}
a:visited, span.MsoHyperlinkFollowed
        {color:purple;
        text-decoration:underline;
        text-underline:single;}
p.MsoAutoSig, li.MsoAutoSig, div.MsoAutoSig
        {margin:0in;
        margin-bottom:.0001pt;
        mso-pagination:widow-orphan;
        font-size:12.0pt;
        font-family:"Times New Roman";
        mso-fareast-font-family:"Times New Roman";}
span.EmailStyle15
        {mso-style-type:personal-reply;
        mso-ansi-font-size:11.0pt;
        mso-ascii-font-family:"Comic Sans MS";
        mso-hansi-font-family:"Comic Sans MS";
        mso-bidi-font-family:Arial;
        color:navy;
        font-weight:normal;
        font-style:normal;}
p.BodyCopy, li.BodyCopy, div.BodyCopy
        {mso-style-name:"Body Copy";
        mso-style-update:auto;
        margin-top:0in;
        margin-right:0in;
        margin-bottom:7.0pt;
        margin-left:0in;
        line-height:14.0pt;
        mso-line-height-rule:exactly;
        mso-pagination:widow-orphan;
        font-size:11.0pt;
        mso-bidi-font-size:10.0pt;
        font-family:Garamond;
        mso-fareast-font-family:"Times New Roman";
        mso-bidi-font-family:"Times New Roman";}
span.EmailStyle18
        {mso-style-type:personal;
        mso-ansi-font-size:10.0pt;
        mso-ascii-font-family:Arial;
        mso-hansi-font-family:Arial;
        mso-bidi-font-family:Arial;
        color:black;}
@page Section1
        {size:8.5in 11.0in;
        margin:1.0in 1.25in 1.0in 1.25in;
        mso-header-margin:.5in;
        mso-footer-margin:.5in;
        mso-paper-source:0;}
div.Section1
        {page:Section1;}

-->
</style>

</head>

<body bgcolor=white lang=EN-US link=blue vlink=purple style='tab-interval:.5in'>

<div class=Section1>

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.<o:p></o:p>

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.<o:p></o:p>

<spa n
class=EmailStyle18><i

style='mso-bidi-font-style:
normal'><font size=2 color="#3366ff" face="Monotype Corsiva"><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>

<![if
!supportEmptyParas]> <![endif]><o:p></o:p>

Stephen P.
Karniotis<o:p></o:p>

Email:         &n bsp;   <a
href="mailto:Stephen.Karniotis_at_Compuware.com">Stephen.Karniotis_at_Compuware.com
</a><o:p></o:p>

Web:         &n bsp;    <a
href="http://www.compuware.com/">www.compuware.com</a><o:p></o:p>

<![if
!supportEmptyParas]> <![endif]><o:p></o:p>

<![if !supportEmptyParas]> <![endif]><o:p></o:p>

-----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?

<![if
!supportEmptyParas]> <![endif]><o:p></o:p>

<span

style='font-size:10.0pt;font-family:Arial;color:black'>Hi</span></font><font
color=black><span
style='color:black;mso-color-alt:windowtext'><o:p></o:p></span></font></p>

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.<o:p></o:p>

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).<o:p></o:p>

Is there any way to force Oracle to kill the session when the user closes the browser?<o:p></o:p>

<span

style='font-size:12.0pt;color:black'>&nbsp;</span></font><font
color=black><span
style='color:black;mso-color-alt:windowtext'><o:p></o:p></span></font></p>

<span

style='font-size:10.0pt;font-family:Arial;color:black'>TIA</span></font><font
color=black><span
style='color:black;mso-color-alt:windowtext'><o:p></o:p></span></font></p>

</div>

</body>

</html>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: dgoulet_at_vicr.com

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 - 09:50:06 CST