Shouldn't be, just showing a more efficient way to use prepared statements
as they are meant to be used. You will get faster results and lower CPU
usage on the server.
Jim
"Oliver Zeigermann" <oliver_at_zeigermann.de> wrote in message
news:2jb5o6FvbbviU2_at_uni-berlin.de...
> Hi Jim!
>
> Thanks for the kind reply!
>
> I do not use connection pooling, but work directly on the connection
> class of the JDBC driver shipped with Oracle 10g (same thing with older
> versions, though). The whole thing gets committed later...
>
> The closing of the prepared delete statements could not be part of my
> problem, right?
>
> Cheers,
> Oliver
>
> Jim Kennedy wrote:
>
> > You don't need to close the statement and reissue it. You can just
update
> > the bind variable and execute it again. Much more efficient. Since you
> > can't reproduce this in SQLPlus then the problem is probably in the
Java.
> > Are you using connection pooling from some application server so that
each
> > statement is a different connection? That might give you odd results.
> > Since you didn't commit the delete then another session wouldn't see the
> > rows deleted.
> > Jim
> > "Oliver Zeigermann" <oliver_at_zeigermann.de> wrote in message
> > news:2jb3aiFvrhdmU1_at_uni-berlin.de...
> >
> >>OK, enabled tracing and this is the result:
> >>
> >>
> >>>=====================
> >>>PARSING IN CURSOR #6 len=34 dep=0 uid=65 oct=42 lid=65 tim=105493947922
> >
> > hv=3913151867 ad='67f33cac'
> >
> >>>ALTER SESSION SET SQL_TRACE = TRUE
> >>>END OF STMT
> >>>EXEC #6:c=0,e=519,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=105493947908
> >>>=====================
> >>>PARSING IN CURSOR #4 len=48 dep=0 uid=65 oct=7 lid=65 tim=105493978311
> >
> > hv=3637529011 ad='67da3a90'
> >
> >>>delete from PROPERTIES p where p.VERSION_ID = 28
> >>>END OF STMT
> >>>PARSE
> >
> > #4:c=15625,e=9852,p=0,cr=12,cu=0,mis=1,r=0,dep=0,og=1,tim=105493978298
> >
> >>>EXEC #4:c=0,e=335,p=0,cr=1,cu=1,mis=0,r=1,dep=0,og=1,tim=105493978772
> >>>STAT #4 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE (cr=1 pr=0 pw=0
> >
> > time=163 us)'
> >
> >>>STAT #4 id=2 cnt=1 pid=1 pos=1 obj=51685 op='INDEX RANGE SCAN
> >
> > SYS_C007946 (cr=1 pr=0 pw=0 time=86 us)'
> >
> >>>=====================
> >>>PARSING IN CURSOR #6 len=48 dep=0 uid=65 oct=7 lid=65 tim=105493980587
> >
> > hv=3637529011 ad='67da3a90'
> >
> >>>delete from PROPERTIES p where p.VERSION_ID = 28
> >>>END OF STMT
> >>>PARSE #6:c=0,e=231,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=105493980577
> >>>EXEC #6:c=0,e=259,p=0,cr=1,cu=1,mis=0,r=1,dep=0,og=1,tim=105493980952
> >>>STAT #6 id=1 cnt=0 pid=0 pos=1 obj=0 op='DELETE (cr=1 pr=0 pw=0
> >
> > time=160 us)'
> >
> >>>STAT #6 id=2 cnt=1 pid=1 pos=1 obj=51685 op='INDEX RANGE SCAN
> >
> > SYS_C007946 (cr=1 pr=0 pw=0 time=85 us)'
> >
> >>>=====================
> >>>PARSING IN CURSOR #5 len=94 dep=0 uid=65 oct=3 lid=65 tim=105493993679
> >
> > hv=2310065897 ad='6a3e5ca4'
> >
> >>>select PROPERTY_NAME, VERSION_ID, PROPERTY_NAMESPACE from PROPERTIES p
> >
> > WHERE p.VERSION_ID = 28
> >
> >>>END OF STMT
> >>>PARSE
> >
> > #5:c=15625,e=11137,p=0,cr=10,cu=0,mis=1,r=0,dep=0,og=1,tim=105493993668
> >
> >>>EXEC #5:c=0,e=98,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=105493993898
> >>>FETCH #5:c=0,e=146,p=0,cr=1,cu=0,mis=0,r=1,dep=0,og=1,tim=105493995306
> >>>STAT #5 id=1 cnt=1 pid=0 pos=1 obj=51685 op='INDEX RANGE SCAN
> >
> > SYS_C007946 (cr=1 pr=0 pw=0 time=94 us)'
> >
> >>>=====================
> >>>PARSING IN CURSOR #6 len=35 dep=0 uid=65 oct=42 lid=65 tim=105493997130
> >
> > hv=4067503723 ad='68daac44'
> >
> >>>ALTER SESSION SET SQL_TRACE = FALSE
> >>>END OF STMT
> >>>PARSE #6:c=0,e=155,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=105493997120
> >>>EXEC #6:c=0,e=512,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=105493997740
> >>
> >>I am not really sure what "PARSING IN CURSOR #" means, but it seems the
> >>deletes are in different cursors?! This may be ther problem, right? This
> >>is the Java code I execute (I have explicitely added the paramenters to
> >>the prepared statements to have them in the trace):
> >>
> >>
> >>> statement =
> >>> connection.prepareStatement("ALTER SESSION SET
> >
> > SQL_TRACE = TRUE");
> >
> >>> statement.executeUpdate();
> >>> close(statement);
> >>>
> >>> statement =
> >>> connection.prepareStatement(
> >>> "delete from PROPERTIES p where p.VERSION_ID =
> >
> > "+id);
> >
> >>>// statement.setLong(1, id);
> >>> deleted = statement.executeUpdate();
> >>> System.out.println("Deleted: "+deleted);
> >>> close(statement);
> >>>
> >>> statement =
> >>> connection.prepareStatement(
> >>> "delete from PROPERTIES p where p.VERSION_ID =
> >
> > "+id);
> >
> >>>// statement.setLong(1, id);
> >>> deleted = statement.executeUpdate();
> >>> System.out.println("Deleted: "+deleted);
> >>> close(statement);
> >>>
> >>> statement = connection.prepareStatement("select
> >
> > PROPERTY_NAME, VERSION_ID, PROPERTY_NAMESPACE from PROPERTIES p " +
> >
> >>> "WHERE p.VERSION_ID = "+id);
> >>> rs = statement.executeQuery();
> >>> while (rs.next()) {
> >>> System.out.println("After **** Name
> >
> > "+rs.getString(1));
> >
> >>> System.out.println("After **** Version-Id
> >
> > "+rs.getString(2));
> >
> >>> System.out.println("After **** NS
> >
> > "+rs.getString(3));
> >
> >>> }
> >>> close(statement,rs);
> >>>
> >>> statement =
> >>> connection.prepareStatement("ALTER SESSION SET
> >
> > SQL_TRACE = FALSE");
> >
> >>> statement.executeUpdate();
> >>> close(statement);
> >>
> >>
> >>Both delete statements return the same number (17) and the select
> >>statement still retrieves 17 values.
> >>
> >>How is all this possible?
> >>
> >>Thanks in advance to all the people still interested in helping :)
> >>
> >>Oliver
> >>
> >>Oliver Zeigermann wrote:
> >>
> >>
> >>>I have a very weired delete problem that only occurs when isolation
> >>>level is set to serializable.
> >>>
> >>>
> >>>>delete from PROPERTIES p where p.VERSION_ID = ?
> >>>
> >>>
> >>>deletes 10 rows while when I execute the same request *inside the same
> >>>transaction*, again 10 rows are deleted.
> >>>
> >>>After that
> >>>
> >>>
> >>>>select * from PROPERTIES p where p.VERSION_ID = ?
> >>>
> >>>
> >>>returns 10 rows *inside the same transaction* as well.
> >>>
> >>>Obviously, nothing is removed at all until a invoke commit. Has anyone
> >>>experienced something similar? Or has anyone any idea what might be my
> >>>mistake? As a hint, a final commit does succeed as well...
> >>>
> >>>Cheers and thanks in advance,
> >>>
> >>>Oliver
> >
> >
> >
Received on Wed Jun 16 2004 - 18:25:51 CDT
