Home » RDBMS Server » Performance Tuning » Very slow commit (Oracle XE)
Very slow commit [message #404688] Fri, 22 May 2009 16:01 Go to next message
emeded
Messages: 5
Registered: May 2009
Junior Member
Hi,
I am experiencing problems recently with Oracle XE running on server 2003. In my code (java) auto commit is set to false and I do explicit commit in the code. However, in last couple of days, I started noticing big delay between insert and actual insert. What I mean by that is that I can see in my code that insert was successful (no exception), but when I go and look into db (oracle web interface or sqldeveloper), I do not see inserts for next 20-30 min! If I change my code to do auto commit, I can see all (usually 3 inserts to 3 different tables) right away. However, I need to use explicit commit. Same thing is happening (occasionally) when doing plain insert into using sqldeveloper client.
Did anybody ever experienced something like that?

Thanks.

em
Re: Very slow commit [message #404694 is a reply to message #404688] Fri, 22 May 2009 17:47 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You don't see uncommitted rows in other sessions. End of story.

Autocommit is a feature of a particular tool you are using. If it is not available (eg. in a PL/SQL routine) or not switched on then it commits when YOU ask it to. ie. If you leave an uncommitted transaction 2 weeks, you still won't see it in another session.

Ross Leishman
Re: Very slow commit [message #404696 is a reply to message #404694] Fri, 22 May 2009 18:20 Go to previous messageGo to next message
emeded
Messages: 5
Registered: May 2009
Junior Member
Hi,
thanks for your response. That is the problem. My code does explicit commit and after it happens, it takes up to 20 min. to see it in the database.
However, all that was working 3 days ago just fine, and db is running and installed on that machine for over 6 months.
Re: Very slow commit [message #404723 is a reply to message #404696] Sat, 23 May 2009 01:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What you say is not possible from Oracle point of view. It is more likely that you don't commit when you think you do.
Do you check every return code and not ignore error on some of your rdbms calls?

Regards
Michel

[Edit: typo]

[Updated on: Sun, 24 May 2009 23:01]

Report message to a moderator

Re: Very slow commit [message #404831 is a reply to message #404723] Sun, 24 May 2009 22:29 Go to previous messageGo to next message
emeded
Messages: 5
Registered: May 2009
Junior Member
Thanks for your response.
Yes, I am sure I do not swallow any of the exceptions. My explicit commit call is void and does not return any error code, and I know for the fact that couple of days ago, after this line, I would see my record in the database immediately. So, it is nothing in the code that I change that introduced this behavior.
Re: Very slow commit [message #404832 is a reply to message #404688] Sun, 24 May 2009 22:43 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
>So, it is nothing in the code that I change that introduced this behavior.
If your code has not changed & Oracle's code did not change & the OS code did not change, please identify what else did change.

Good luck with your mystery.

If you are so inclined you might post your flawless code for review
Re: Very slow commit [message #404834 is a reply to message #404832] Sun, 24 May 2009 22:52 Go to previous messageGo to next message
emeded
Messages: 5
Registered: May 2009
Junior Member
No need for sarcasm. I never said that nothing changed in OS. Also, I do not know if something could have change in Oracle system. I do not know how some of the caches/memory works in oracle and I do not know if I maybe hit some threshold. That is the reason I posted here.

Anyway, thanks for your input.

Re: Very slow commit [message #404836 is a reply to message #404834] Sun, 24 May 2009 23:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
My explicit commit call is void and does not return any error code,

I didn't talk about just commit error code but ALL Oracle calls.

A commit is just a marker in the redo log, it is immediate. Everything committed is visible for others as soon as commit returns.

Regards
Michel
Re: Very slow commit [message #404989 is a reply to message #404836] Tue, 26 May 2009 00:21 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Quite simply, if Oracle hits a COMMIT statement, then changes will be reflected in the database and available to all sessions IMMEDIATELY.

The only exceptions that come to mind are:
- READ ONLY or SERIALIZABLE transactions in other sessions that are commenced BEFORE the commit will not see the changes.
- A COMMIT will not commit transactions executed under a subordinate AUTONOMOUS TRANSACTION. Similarly, a COMMIT in an AUTONOMOUS TRANSACTION will not commit transactions in a calling procedure.
- A COMMIT within a workspace (using Workspace Manager) will not be seen in other workspaces (I don't really know much about workspaces, so that last statement may not make sense)

If none of these apply to you, you probably have a SQL statement that is raising an exception, which causes control to jump to the end of the PL/SQL block, thereby missing the commit.

Normally this will be visible as an error being raised, except in two cases:

- If you are capturing and suppressing errors (especially with WHEN OTHERS), you might not notice the error.

- If you get a NO_DATA_FOUND exception, then it will cause control to jump to the end of the block, but WILL NOT RAISE THE EXCEPTION back to the calling program (eg. Java) if you executed the procedure using the CALL syntax. This is because CALL is a SQL statement, not a PL/SQL statement, and NO_DATA_FOUND is not considered an error under SQL.

So take a closer look at all your SQLs and see if they could possibly return errors or NO_DATA_FOUND.

Ross Leishman
Re: Very slow commit [message #405151 is a reply to message #404989] Tue, 26 May 2009 10:54 Go to previous messageGo to next message
emeded
Messages: 5
Registered: May 2009
Junior Member
Thanks Ross.
I do call stored procedure through my code, and I think the latest that you point at is what is happening in my case:

Quote:
- If you get a NO_DATA_FOUND exception, then it will cause control to jump to the end of the block, but WILL NOT RAISE THE EXCEPTION back to the calling program (eg. Java) if you executed the procedure using the CALL syntax. This is because CALL is a SQL statement, not a PL/SQL statement, and NO_DATA_FOUND is not considered an error under SQL


I do use CALL syntax to call my stored procedure. How else should I execute the procedure to avoid using CALL?



Here is the procedure I call, and java code that execute it:

   private static void writeRecords(Event[] events) {

        Event currentEvent = null;
        CallableStatement callStartCstmt = null;
        CallableStatement dialogCstmt = null;

        Connection connection = getConnection();
        try {
            callStartCstmt = connection.prepareCall("{call Insert_Call_Start_Event (?, ?, ?, ?)}");   
            dialogCstmt = connection.prepareCall("{call insert_dialog_event (?, ?, ?, ?, ?, ?, ?, ?)}");


            for (int i = 0; i < events.length; i++) {
                currentEvent = events[i];
                int jdbcReturnValue = 1;

                if (events[i] instanceof CallStartEvent) {

                    CallStartEvent callStartEvent = (CallStartEvent) events[i];
                    
                    callStartCstmt.setString(1, callStartEvent.getApplicationSessionId());
                    callStartCstmt.setString(2, callStartEvent.getAni());
                    callStartCstmt.setString(3, callStartEvent.getDnis());
                    callStartCstmt.setString(4, callStartEvent.getBrowserName());

                    jdbcReturnValue = callStartCstmt.executeUpdate();

                } else if (events[i] instanceof DialogEvent) {

                    DialogEvent dialogEvent = (DialogEvent) events[i];

                    dialogCstmt.setString(1, dialogEvent.getApplicationSessionId());
                    dialogCstmt.setTimestamp(2, new java.sql.Timestamp(dialogEvent.getDate().getTime()));
                    dialogCstmt.setString(3, dialogEvent.getContext());
                    dialogCstmt.setString(4, dialogEvent.getResult());
                    dialogCstmt.setInt(5, dialogEvent.getTurns());
                    dialogCstmt.setString(6, dialogEvent.getInputMode());
                    dialogCstmt.setString(7, dialogEvent.getInterpretation());
                    dialogCstmt.setInt(8, dialogEvent.getConfidence());
                    
                    jdbcReturnValue = dialogCstmt.executeUpdate();

                } 

                if (jdbcReturnValue != 1) {
                    logger.info(currentEvent.toString() + " - update returned unexpected value " + jdbcReturnValue);
                }else{
                	logger.info(currentEvent.toString());
                }
            }

            // commit updates for this run of the queue
            connection.commit();
            
        } catch (Exception e) {
            String message = "reporting database exception";
            if (currentEvent != null) {
                message += " while writing event: " + currentEvent.toString();
            }
            logger.warn(message, e);
            
        } finally {
            try {
                callStartCstmt.close();
                dialogCstmt.close();
                connection.close();
                
                logger.info("All Callable Statments and DB Connection closed.");
                
            } catch (SQLException e) {
                logger.error("Error encountered when trying to close resources.", e);
            }
        }

    }



Here is stored procedure:

CREATE OR REPLACE PROCEDURE Insert_Call_Start_Event (conn_id   IN VARCHAR2,
                                                    ani       IN VARCHAR2  DEFAULT NULL,
                                                    dnis      IN VARCHAR2  DEFAULT NULL,
                                                    host      IN VARCHAR2  DEFAULT NULL) AS

BEGIN

	INSERT INTO call (conn_id,
                         ani,
                         dnis,
						 host)
		  VALUES (conn_id,
                         ani,
                         dnis,
                         host);

END;
/

CREATE OR REPLACE PROCEDURE insert_dialog_event (conn_id        IN VARCHAR2,
                                                occurrence_time	IN TIMESTAMP,
                                                context        IN VARCHAR2,
                                                result         IN VARCHAR2,
                                                turns		IN NUMBER,
                                                input_mode     IN VARCHAR2  DEFAULT NULL,
                                                interpretation IN VARCHAR2  DEFAULT NULL,
                                                confidence IN NUMBER  DEFAULT NULL) AS

BEGIN

	INSERT INTO dialog_event (conn_id,
	                   	  event_id,
                          occurrence_time,
                          context,
                          result,
                          turns,
                          input_mode,
                          interpretation,
                          confidence)
		   VALUES (conn_id,
		         		  event_id_sequence.NEXTVAL,
                          occurrence_time,
                          context,
                          result,
                          turns,
                          input_mode,
                          interpretation,
                          confidence);

END;
/


event_id_sequence sequence is only doing increments and it is in the place.
Re: Very slow commit [message #405154 is a reply to message #404688] Tue, 26 May 2009 11:05 Go to previous messageGo to next message
cookiemonster
Messages: 12412
Registered: September 2008
Location: Rainy Manchester
Senior Member
Neither of those procedures can give no_data_found.
Re: Very slow commit [message #405191 is a reply to message #405154] Tue, 26 May 2009 21:44 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I thought the COMMIT was in the PL/SQL, but it is in the Java. So you can ignore most of the advice above.

I don't know enough about JDBC to determine whether there is any possibility of skipping the connection.commit statement, but your design does seem odd.

You are executing the PL/SQL commands inside a loop, but you are checking the result (jdbcReturnValue) outside the loop. Seems like opportunity to miss an error there.

Also, your structure is
if <something>
then <whetever>
else if <something else>
then <whatever else>


If <something> and <something else> are both false, then no PL/SQL will be called. Nor do I see any logging when the PL/SQL is executed. How can you be sure it was even called?

Ross Leishman
Re: Very slow commit [message #405621 is a reply to message #404688] Thu, 28 May 2009 12:43 Go to previous message
coleing
Messages: 213
Registered: February 2008
Senior Member
Just an idea, but if you are disconnecting "mid-session" - ie. without a commit, and its leaving the Oracle session open, then the standard oracle "sweep up old sessions" job could be committing the records when it kills the sessions off?
Previous Topic: Need help on Query Tuning
Next Topic: import sql profile with opt_param hnt
Goto Forum:
  


Current Time: Wed Dec 07 08:50:09 CST 2016

Total time taken to generate the page: 0.09962 seconds