This is a multi-part message in MIME format.
------_=_NextPart_001_01C8EB2F.0B26D8C8
Content-Type: text/plain;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable
Hi Everyone,
=20
I have a jdbc application which maintanes a connection to a database.
=20
Every morning, the application fires up and runs a set of queries using
this long standing connection.
=20
To generate a set of re****ts the application runs the same sets of
queries, using a prepare statement, open cursor for statement, fetch
data and close cursor. From one day to another, the code used should be
exactly the same.
=20
I have tracked this using querytext auditing....
=20
Why is it that even though the code says 'prepare this statement', some
queries do not seem to have a prepared statement executed more than
once? They use statements of the same name prepared several days earlier
by prior executions of the application.
=20
For example:
This was prepared and used on 18/07/2008 03:04:11.
SELECT G.aid rowid, V.field_id field_id, V.value value, COUNT(*) n
FROM REP_GOBZ G, INT_VALUE V WHERE 1=3D1 AND G.status =3D 1000 AND =
G.pid
>=3D 100000000 AND G.pid <=3D 899999999 AND V.field_id =3D ? AND =
G.rep_id =3D
V.rep_id GROUP BY 1,2,3
=20
The same section of code ran the next day, but the querytext auditing
shows no sign of the prepare statement, only the cursor open for
readonly.
=20
Trouble was, in between the executions the tables were dropped and
recreated, and the second (and subsequent) executions generated some
weird messages in the errlog, which is how I found it.
=20
Martin Bowes
------_=_NextPart_001_01C8EB2F.0B26D8C8
Content-Type: text/html;
charset="US-ASCII"
Content-Transfer-Encoding: quoted-printable
<html xmlns:v=3D"urn:schemas-microsoft-com:vml" =
xmlns:o=3D"urn:schemas-microsoft-com:office:office" =
xmlns:w=3D"urn:schemas-microsoft-com:office:word" =
xmlns:m=3D"http://schemas.microsoft.com/office/2004/12/omml"
=
xmlns=3D"http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=3DContent-Type content=3D"text/html; =
charset=3Dus-ascii">
<meta name=3DGenerator content=3D"Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
@[EMAIL PROTECTED]
15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
..MsoChpDefault
{mso-style-type:ex****t-only;}
@[EMAIL PROTECTED]
Section1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext=3D"edit" spidmax=3D"1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext=3D"edit">
<o:idmap v:ext=3D"edit" data=3D"1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=3DEN-GB link=3Dblue vlink=3Dpurple>
<div class=3DSection1>
<p class=3DMsoNormal>Hi Everyone,<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>I have a jdbc application which maintanes a =
connection to a
database.<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>Every morning, the application fires up and runs a =
set of
queries using this long standing connection.<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>To generate a set of re****ts the application =
runs the
same sets of queries, using a prepare statement, open cursor for =
statement,
fetch data and close cursor. From one day to another, the code used =
should be
exactly the same.<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>I have tracked this using querytext =
auditing….<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>Why is it that even though the code says 'prepare =
this
statement', some queries do not seem to have a prepared statement =
executed more
than once? They use statements of the same name prepared several days =
earlier
by prior executions of the application.<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>For example:<o:p></o:p></p>
<p class=3DMsoNormal>This was prepared and used on 18/07/2008 =
03:04:11.<o:p></o:p></p>
<p class=3DMsoNormal>SELECT G.aid rowid, V.field_id =
field_id, V.value
value, COUNT(*) n FROM REP_GOBZ G, INT_VALUE V WHERE
1=3D1 AND G.status =3D 1000 AND G.pid >=3D =
100000000 AND G.pid
<=3D 899999999 AND <b>V.field_id =3D ?</b> AND G.rep_id =
=3D
V.rep_id GROUP BY 1,2,3<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>The same section of code ran the next day, but the =
querytext
auditing shows no sign of the prepare statement, only the cursor open =
for
readonly.<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>Trouble was, in between the executions the tables =
were
dropped and recreated, and the second (and subsequent) executions =
generated
some weird messages in the errlog, which is how I found =
it.<o:p></o:p></p>
<p class=3DMsoNormal><o:p> </o:p></p>
<p class=3DMsoNormal>Martin Bowes<o:p></o:p></p>
</div>
</body>
</html>
------_=_NextPart_001_01C8EB2F.0B26D8C8--
|
