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 Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/102589.gif) |
Michel Cadot
Messages: 68675 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #404834 is a reply to message #404832] |
Sun, 24 May 2009 22:52 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #404989 is a reply to message #404836] |
Tue, 26 May 2009 00:21 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 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 message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #405191 is a reply to message #405154] |
Tue, 26 May 2009 21:44 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
rleishman
Messages: 3728 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 Go to previous message](/forum/theme/orafaq/images/up.png) |
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?
|
|
|
Goto Forum:
Current Time: Thu Aug 22 08:04:41 CDT 2024
|