FAQ

[Oracle-l] Wait event SQL*Net break/reset to client caused by duplicate insert?

Peter Hitchman
Apr 8, 2008 at 7:54 pm
Hi,
Oracle 1.1.06 RAC on OEL 4 64 bit.

We are seeing an insert statement reporting "SQL*Net break/reset to client"
as over 1/3 of its time.
On the face of it this event suggests network issues but nothing else backs
this up as the cause.
So I looked at the Java code in question and a trace of one of the sessions.

What is happening is that an attempt is made to insert a row, most of the
time a duplicate error results, the code catches this exception and does an
update.
I was wondering if its the duplicate error and the exception handling which
results in this wait event showing up.

All insights welcome

Regards

Pete
reply

Search Discussions

7 responses

  • Tanel Poder at Apr 9, 2008 at 4:04 pm
    Yes, a SQL*Net break/reset happens when an error/unhandled exception is
    raised during a call (which means that the call executed didn't complete
    normally, thus the call state must be reset).


    The approach of "insert -> if failed then update" is basically what MERGE
    does.


    You need to change the application to use MERGE command. Alternatively you
    could use an "update -> if no-rows-updated then insert" approach, but MERGE
    makes much more sense nowadays.

    --
    Regards,
    Tanel Poder
    http://blog.tanelpoder.com <http://blog.tanelpoder.com/>



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Peter Hitchman
    Sent: Wednesday, April 09, 2008 03:55
    To: oracle-l
    Subject: Wait event SQL*Net break/reset to client caused by duplicate
    insert?

    Hi,
    Oracle 1.1.06 RAC on OEL 4 64 bit.

    We are seeing an insert statement reporting "SQL*Net break/reset to client"
    as over 1/3 of its time.
    On the face of it this event suggests network issues but nothing else backs
    this up as the cause.
    So I looked at the Java code in question and a trace of one of the sessions.

    What is happening is that an attempt is made to insert a row, most of the
    time a duplicate error results, the code catches this exception and does an
    update.
    I was wondering if its the duplicate error and the exception handling which
    results in this wait event showing up.

    All insights welcome

    Regards

    Pete

    --
    http://www.freelists.org/webpage/oracle-l
  • Tanel Poder at Apr 9, 2008 at 4:53 pm
    FYI, I did put together a small test case with further explanation to my
    blog:


    http://blog.tanelpoder.com/2008/04/10/sqlnet-breakreset-to-client/

    --
    Regards,
    Tanel Poder
    http://blog.tanelpoder.com <http://blog.tanelpoder.com/>



    From: Tanel Poder
    Sent: Thursday, April 10, 2008 00:05
    To: 'pjhoraclel_at_gmail.com'; 'oracle-l'
    Subject: RE: Wait event SQL*Net break/reset to client caused by duplicate
    insert?

    Yes, a SQL*Net break/reset happens when an error/unhandled exception is
    raised during a call (which means that the call executed didn't complete
    normally, thus the call state must be reset).


    --
    http://www.freelists.org/webpage/oracle-l
  • Peter Hitchman at Apr 10, 2008 at 9:19 am
    Hi Tanel,
    Thanks for the feedback. Talking to the developer about the code, I
    suggested a change to first update and then insert if no row is updated. I
    also considered using merge, but I thought that it might not be supported by
    the jdbc driver and time pressure means we do not have the time to find out.

    Regards

    Pete
    On Wed, Apr 9, 2008 at 5:53 PM, Tanel Poder wrote:

    FYI, I did put together a small test case with further explanation to my
    blog:

    http://blog.tanelpoder.com/2008/04/10/sqlnet-breakreset-to-client/

    --
    Regards,
    Tanel Poder
    http://blog.tanelpoder.com


    ------------------------------
    *From:* Tanel Poder
    *Sent:* Thursday, April 10, 2008 00:05
    *To:* 'pjhoraclel_at_gmail.com'; 'oracle-l'
    *Subject:* RE: Wait event SQL*Net break/reset to client caused by
    duplicate insert?

    Yes, a SQL*Net break/reset happens when an error/unhandled exception is
    raised during a call (which means that the call executed didn't complete
    normally, thus the call state must be reset).

    --
    Regards

    Pete

    --
    http://www.freelists.org/webpage/oracle-l
  • Peter Hitchman at Apr 10, 2008 at 9:29 am
    Hi,
    Just to follow up, having read your Blog entry ...

    The java code is handling the exception, so is there a difference in the way
    this event gets fired between a java JDBC programme and server side PL/SQL
    code, because of the client/server nature of the java?

    Thanks

    Pete

    On Thu, Apr 10, 2008 at 10:19 AM, Peter Hitchman
    wrote:
    Hi Tanel,
    Thanks for the feedback. Talking to the developer about the code, I
    suggested a change to first update and then insert if no row is updated. I
    also considered using merge, but I thought that it might not be supported by
    the jdbc driver and time pressure means we do not have the time to find out.

    Regards

    Pete


    On Wed, Apr 9, 2008 at 5:53 PM, Tanel Poder
    wrote:
    FYI, I did put together a small test case with further explanation to
    my blog:

    http://blog.tanelpoder.com/2008/04/10/sqlnet-breakreset-to-client/

    --
    Regards,
    Tanel Poder
    http://blog.tanelpoder.com


    ------------------------------
    *From:* Tanel Poder
    *Sent:* Thursday, April 10, 2008 00:05
    *To:* 'pjhoraclel_at_gmail.com'; 'oracle-l'
    *Subject:* RE: Wait event SQL*Net break/reset to client caused by
    duplicate insert?

    Yes, a SQL*Net break/reset happens when an error/unhandled exception is
    raised during a call (which means that the call executed didn't complete
    normally, thus the call state must be reset).


    --
    Regards

    Pete
    --
    Regards

    Pete

    --
    http://www.freelists.org/webpage/oracle-l
  • Amit poddar at Apr 10, 2008 at 11:39 am
    The exception should not be propagated to the client to avoid this
    event. That would mean the exception needs to be handled on the database
    which can only happen in a PLSQL block.

    Handling exception in java means the exception is propagated to the
    client and hence the wait event

    amit

    Peter Hitchman wrote:
    Hi,
    Just to follow up, having read your Blog entry ...

    The java code is handling the exception, so is there a difference in
    the way this event gets fired between a java JDBC programme and server
    side PL/SQL code, because of the client/server nature of the java?
    --
    http://www.freelists.org/webpage/oracle-l
  • Tanel Poder at Apr 10, 2008 at 4:25 pm
    Yep, the reset happens when the exception is propagated back to client - as
    the client is not under Oracle server process'es control and state anymore.

    --
    Regards,
    Tanel Poder
    http://blog.tanelpoder.com <http://blog.tanelpoder.com/>



    From: oracle-l-bounce_at_freelists.org
    On Behalf Of Peter Hitchman
    Sent: Thursday, April 10, 2008 17:29
    To: Tanel Poder
    Cc: oracle-l
    Subject: Re: Wait event SQL*Net break/reset to client caused by duplicate
    insert?

    Hi,
    Just to follow up, having read your Blog entry ...

    The java code is handling the exception, so is there a difference in the way
    this event gets fired between a java JDBC programme and server side PL/SQL
    code, because of the client/server nature of the java?

    Thanks

    Pete

    --
    http://www.freelists.org/webpage/oracle-l
  • Nigel Thomas at Apr 10, 2008 at 11:45 am

    On Thu, 4/10/08, Peter Hitchman wrote:

    From: Peter Hitchman
    I
    suggested a change to first update and then insert if no
    row is updated. I
    also considered using merge, but I thought that it might
    not be supported by
    the jdbc driver and time pressure means we do not have the
    time to find out.
    Peter

    If you don't want to try MERGE, you could always make an anonymous PL/SQL block (which is certainly supported by PL/SQL) and so handle the insert-else-update (or update-else-insert - whichever is the best tradeoff) in one SQL round trip from application server to database; then there will never need to be an exception raised to (and handled by) JDBC.

    Cheers Nigel

Related Discussions

Discussion Navigation
viewthread | post