 |
|
|
|
Next: ISA 2006 with My sql Connection
|
| Author |
Message |
External
Since: Feb 21, 2005
Posts: 87
|
(Msg. 1) Posted: Wed Feb 13, 2008 3:14 pm
Post subject: Don't do DDL with PreparedStatement with Oracle!
Archived from groups: comp>lang>java>databases (more info?)
|
|
|
Hi all.
I found a customer who wanted to repeat the SQL "TRUNCATE TABLE
MY_TABLE",
so they did:
ps = c.prepareStatement("TRUNCATE TABLE THEIR_TABLE");
This statement *worked fine* the first time they executed it, but
fascinatingly,
every subsequent execution of that statement *silently failed, doing
*nothing*!
The driver got the expected response packet from the DBMS, but Oracle
did
nothing. The customer could even continue to execute this statement,
seemingly
successfully, after they completely dropped the table!
The case was complicated because their code actually created,
executed,
and closed the statement each time, but because the customer was using
a
connection pooling system (like BEA's or Oracle's own JDBC driver),
that will
transparently cache and re-use PreparedStatements (a big performance
feature),
the application would get the same statement under the covers, and
start
getting the odd NO-OP behavior.
Oracle's driver and our pooling are both unable to do anything about
this,
when the SQL is created at runtime by the customer, and this DBMS bug
would force the non-caching of statements for anyone sending DDL, like
that. To reiterate, this is an Oracle DBMS bug. It can be reproduced
with
a simple JDBC client or an OCI+C program, but not SQL-PLUS because
SQL-PLUS never prepares statements....
FYI,
Joe Weinstein at BEA Systems |
|
| Back to top |
|
 | |
External
Since: Feb 21, 2005
Posts: 87
|
(Msg. 2) Posted: Wed Feb 13, 2008 3:43 pm
Post subject: Re: Don't do DDL with PreparedStatement with Oracle! [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
(I don't know why the original was so badly formatted)
Hi all.
I found a customer who wanted to repeat the SQL "TRUNCATE TABLE
MY_TABLE", so they did:
ps = c.prepareStatement("TRUNCATE TABLE THEIR_TABLE");
This statement *worked fine* the first time they executed it, but
fascinatingly, every subsequent execution of that statement
*silently failed, doing *nothing*! The driver got the expected
response packet from the DBMS, but Oracle did nothing. The
customer could even continue to execute this statement, seemingly
successfully, after they completely dropped the table!
The case was complicated because their code actually created,
executed, and closed the statement each time, but because the
customer was using a connection pooling system (like BEA's or
Oracle's own JDBC driver), that will transparently cache and
re-use PreparedStatements (a big performance feature), the
application would get the same statement under the covers, and
start getting the odd NO-OP behavior.
Oracle's driver and our pooling are both unable to do anything
about this, when the SQL is created at runtime by the customer,
and this DBMS bug would force the non-caching of statements for
anyone sending DDL, like that. To reiterate, this is an Oracle
DBMS bug. It can be reproduced with a simple JDBC client or an
OCI+C program, but not SQL-PLUS because SQL-PLUS never prepares
statements....
FYI,
Joe Weinstein at BEA Systems |
|
| Back to top |
|
| |
External
Since: May 18, 2006
Posts: 7
|
(Msg. 3) Posted: Thu Feb 14, 2008 9:02 am
Post subject: Re: Don't do DDL with PreparedStatement with Oracle! [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Feb 13, 3:43 pm, "joeNOS...@BEA.com" <joe.weinst... RemoveThis @gmail.com>
wrote:
> (I don't know why the original was so badly formatted)
> Hi all.
>
> I found a customer who wanted to repeat the SQL "TRUNCATE TABLE
> MY_TABLE", so they did:
>
> ps = c.prepareStatement("TRUNCATE TABLE THEIR_TABLE");
>
> This statement *worked fine* the first time they executed it, but
> fascinatingly, every subsequent execution of that statement
> *silently failed, doing *nothing*! The driver got the expected
> response packet from the DBMS, but Oracle did nothing. The
> customer could even continue to execute this statement, seemingly
> successfully, after they completely dropped the table!
> The case was complicated because their code actually created,
> executed, and closed the statement each time, but because the
> customer was using a connection pooling system (like BEA's or
> Oracle's own JDBC driver), that will transparently cache and
> re-use PreparedStatements (a big performance feature), the
> application would get the same statement under the covers, and
> start getting the odd NO-OP behavior.
> Oracle's driver and our pooling are both unable to do anything
> about this, when the SQL is created at runtime by the customer,
> and this DBMS bug would force the non-caching of statements for
> anyone sending DDL, like that. To reiterate, this is an Oracle
> DBMS bug. It can be reproduced with a simple JDBC client or an
> OCI+C program, but not SQL-PLUS because SQL-PLUS never prepares
> statements....
>
> FYI,
> Joe Weinstein at BEA Systems
It is documented that DDLs should be re-prepared however, I found out
that the OCI driver manages to avoid re-parsing of DDL, throug its
statement cache. The Oracle JDBC team will look into doing the same.
Kuassi
Oracle JDBC product managemenent
http://db360.blogspot.com >> Stay informed about: Don't do DDL with PreparedStatement with Oracle! |
|
| Back to top |
|
| |
External
Since: Feb 21, 2005
Posts: 87
|
(Msg. 4) Posted: Thu Feb 14, 2008 9:27 am
Post subject: Re: Don't do DDL with PreparedStatement with Oracle! [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Feb 14, 9:02 am, "kuassi.men...@gmail.com"
<kuassi.men... DeleteThis @gmail.com> wrote:
> On Feb 13, 3:43 pm, "joeNOS...@BEA.com" <joe.weinst... DeleteThis @gmail.com>
> wrote:
>
>
>
> > (I don't know why the original was so badly formatted)
> > Hi all.
>
> > I found a customer who wanted to repeat the SQL "TRUNCATE TABLE
> > MY_TABLE", so they did:
>
> > ps = c.prepareStatement("TRUNCATE TABLE THEIR_TABLE");
>
> > This statement *worked fine* the first time they executed it, but
> > fascinatingly, every subsequent execution of that statement
> > *silently failed, doing *nothing*! The driver got the expected
> > response packet from the DBMS, but Oracle did nothing. The
> > customer could even continue to execute this statement, seemingly
> > successfully, after they completely dropped the table!
> > The case was complicated because their code actually created,
> > executed, and closed the statement each time, but because the
> > customer was using a connection pooling system (like BEA's or
> > Oracle's own JDBC driver), that will transparently cache and
> > re-use PreparedStatements (a big performance feature), the
> > application would get the same statement under the covers, and
> > start getting the odd NO-OP behavior.
> > Oracle's driver and our pooling are both unable to do anything
> > about this, when the SQL is created at runtime by the customer,
> > and this DBMS bug would force the non-caching of statements for
> > anyone sending DDL, like that. To reiterate, this is an Oracle
> > DBMS bug. It can be reproduced with a simple JDBC client or an
> > OCI+C program, but not SQL-PLUS because SQL-PLUS never prepares
> > statements....
>
> > FYI,
> > Joe Weinstein at BEA Systems
>
> It is documented that DDLs should be re-prepared however, I found out
> that the OCI driver manages to avoid re-parsing of DDL, throug its
> statement cache. The Oracle JDBC team will look into doing the same.
>
> Kuassi
> Oracle JDBC product managemenenthttp://db360.blogspot.com
Hi Kuassi! Thanks. I don't understand though... If I am correct,
the SQL "TRUNCATE TABLE MYTABLE" *needs* to be re-prepared, else
the DBMS will do nothing. Any client-side caching would simply
aggravate the problem, making a user's re-prepare get the same
now-unfunctional statement from the cache.
Joe >> Stay informed about: Don't do DDL with PreparedStatement with Oracle! |
|
| Back to top |
|
| |
External
Since: May 18, 2006
Posts: 7
|
(Msg. 5) Posted: Thu Feb 14, 2008 11:39 am
Post subject: Re: Don't do DDL with PreparedStatement with Oracle! [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Feb 14, 9:27 am, "joeNOS...@BEA.com" <joe.weinst... RemoveThis @gmail.com>
wrote:
> On Feb 14, 9:02 am, "kuassi.men...@gmail.com"
>
>
>
>
>
> <kuassi.men... RemoveThis @gmail.com> wrote:
> > On Feb 13, 3:43 pm, "joeNOS...@BEA.com" <joe.weinst... RemoveThis @gmail.com>
> > wrote:
>
> > > (I don't know why the original was so badly formatted)
> > > Hi all.
>
> > > I found a customer who wanted to repeat the SQL "TRUNCATE TABLE
> > > MY_TABLE", so they did:
>
> > > ps = c.prepareStatement("TRUNCATE TABLE THEIR_TABLE");
>
> > > This statement *worked fine* the first time they executed it, but
> > > fascinatingly, every subsequent execution of that statement
> > > *silently failed, doing *nothing*! The driver got the expected
> > > response packet from the DBMS, but Oracle did nothing. The
> > > customer could even continue to execute this statement, seemingly
> > > successfully, after they completely dropped the table!
> > > The case was complicated because their code actually created,
> > > executed, and closed the statement each time, but because the
> > > customer was using a connection pooling system (like BEA's or
> > > Oracle's own JDBC driver), that will transparently cache and
> > > re-use PreparedStatements (a big performance feature), the
> > > application would get the same statement under the covers, and
> > > start getting the odd NO-OP behavior.
> > > Oracle's driver and our pooling are both unable to do anything
> > > about this, when the SQL is created at runtime by the customer,
> > > and this DBMS bug would force the non-caching of statements for
> > > anyone sending DDL, like that. To reiterate, this is an Oracle
> > > DBMS bug. It can be reproduced with a simple JDBC client or an
> > > OCI+C program, but not SQL-PLUS because SQL-PLUS never prepares
> > > statements....
>
> > > FYI,
> > > Joe Weinstein at BEA Systems
>
> > It is documented that DDLs should be re-prepared however, I found out
> > that the OCI driver manages to avoid re-parsing of DDL, throug its
> > statement cache. The Oracle JDBC team will look into doing the same.
>
> > Kuassi
> > Oracle JDBC product managemenenthttp://db360.blogspot.com
>
> Hi Kuassi! Thanks. I don't understand though... If I am correct,
> the SQL "TRUNCATE TABLE MYTABLE" *needs* to be re-prepared, else
> the DBMS will do nothing. Any client-side caching would simply
> aggravate the problem, making a user's re-prepare get the same
> now-unfunctional statement from the cache.
> Joe- Hide quoted text -
>
> - Show quoted text -
Mea culpa; in fact OCI mark the DDL for re-parsing, which avoids the
no-op on subsequent execution.
Kuassi >> Stay informed about: Don't do DDL with PreparedStatement with Oracle! |
|
| Back to top |
|
| |
External
Since: Feb 21, 2005
Posts: 87
|
(Msg. 6) Posted: Thu Feb 14, 2008 12:01 pm
Post subject: Re: Don't do DDL with PreparedStatement with Oracle! [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
|
|
|
On Feb 14, 11:39 am, "kuassi.men...@gmail.com"
<kuassi.men....DeleteThis@gmail.com> wrote:
> On Feb 14, 9:27 am, "joeNOS...@BEA.com" <joe.weinst....DeleteThis@gmail.com>
> wrote:
>
>
>
> > On Feb 14, 9:02 am, "kuassi.men...@gmail.com"
>
> > <kuassi.men....DeleteThis@gmail.com> wrote:
> > > On Feb 13, 3:43 pm, "joeNOS...@BEA.com" <joe.weinst....DeleteThis@gmail.com>
> > > wrote:
>
> > > > (I don't know why the original was so badly formatted)
> > > > Hi all.
>
> > > > I found a customer who wanted to repeat the SQL "TRUNCATE TABLE
> > > > MY_TABLE", so they did:
>
> > > > ps = c.prepareStatement("TRUNCATE TABLE THEIR_TABLE");
>
> > > > This statement *worked fine* the first time they executed it, but
> > > > fascinatingly, every subsequent execution of that statement
> > > > *silently failed, doing *nothing*! The driver got the expected
> > > > response packet from the DBMS, but Oracle did nothing. The
> > > > customer could even continue to execute this statement, seemingly
> > > > successfully, after they completely dropped the table!
> > > > The case was complicated because their code actually created,
> > > > executed, and closed the statement each time, but because the
> > > > customer was using a connection pooling system (like BEA's or
> > > > Oracle's own JDBC driver), that will transparently cache and
> > > > re-use PreparedStatements (a big performance feature), the
> > > > application would get the same statement under the covers, and
> > > > start getting the odd NO-OP behavior.
> > > > Oracle's driver and our pooling are both unable to do anything
> > > > about this, when the SQL is created at runtime by the customer,
> > > > and this DBMS bug would force the non-caching of statements for
> > > > anyone sending DDL, like that. To reiterate, this is an Oracle
> > > > DBMS bug. It can be reproduced with a simple JDBC client or an
> > > > OCI+C program, but not SQL-PLUS because SQL-PLUS never prepares
> > > > statements....
>
> > > > FYI,
> > > > Joe Weinstein at BEA Systems
>
> > > It is documented that DDLs should be re-prepared however, I found out
> > > that the OCI driver manages to avoid re-parsing of DDL, throug its
> > > statement cache. The Oracle JDBC team will look into doing the same.
>
> > > Kuassi
> > > Oracle JDBC product managemenenthttp://db360.blogspot.com
>
> > Hi Kuassi! Thanks. I don't understand though... If I am correct,
> > the SQL "TRUNCATE TABLE MYTABLE" *needs* to be re-prepared, else
> > the DBMS will do nothing. Any client-side caching would simply
> > aggravate the problem, making a user's re-prepare get the same
> > now-unfunctional statement from the cache.
> > Joe- Hide quoted text -
>
> > - Show quoted text -
>
> Mea culpa; in fact OCI mark the DDL for re-parsing, which avoids the
> no-op on subsequent execution.
>
> Kuassi
Interesting... So how does OCI *know* it's DDL? Or is OCI marking
*every* statement for re-parse? Wouldn't that be a big performance
regression, asking the DBMS to re-parse everything, even the DML
statements that don't need it? >> Stay informed about: Don't do DDL with PreparedStatement with Oracle! |
|
| Back to top |
|
| |
|
You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum
|
|
|
|
|
|
|
FAQ
Profile
Private Messages
Log in
