Home » SQL & PL/SQL » SQL & PL/SQL » connection timeout when calling an oracle plsql function from a java program (oracle 11g)
| connection timeout when calling an oracle plsql function from a java program [message #587790] |
Tue, 18 June 2013 12:09  |
 |
pradeep_kumar
Messages: 4
Registered: June 2013
|
Junior Member |
|
|
Hi,
We have a daily batch job executing a oracle-plsql function. Actually the quartz scheduler invokes a java program which makes a call to the oracle-plsql function. This oracle plsql function deletes data (which is more than 6 months) from 4 tables and then commits the transaction.
This batch job was running successfully in the test environment but started failing when new data was dumped to the tables which happened 2 weeks ago (The code is supposed to go into production this week). Earlier the number of rows in each table was not more than 0.1 million. But now it is 1 million in 3 tables and 2.4 million in the other table.
After running for 3 hours, we are getting a error in java (written in the log file) "...Connection reset; nested exception is java.sql.SQLException: Io exception: Connection reset....". When the row-counts on the tables were checked, it was clear that no record was deleted from any of the tables.
Is it possible in oracle database, for the plsql procedure/function to be automatically terminated/killed when the connection is timed out and the invoking session is no longer active?
Thanks in advance,
Pradeep.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: connection timeout when calling an oracle plsql function from a java program [message #587844 is a reply to message #587836] |
Wed, 19 June 2013 00:12  |
Lalit Kumar B
Messages: 3174
Registered: May 2013
Location: World Wide on the Web
|
Senior Member |
|
|
@Pradeep_kumar,
Few things should be clear to go in correct direction to get the resolution:-
1. Do you mean earlier the batch process took less time, and after addition of new data the process time has increased?
2. Do you find any errors in DB? (like you are logging those errors or not.)
3. Is this code you use in production environment? The exception block seems like an amateur version.
4. In terms of number of rows you cannot measure anything. Talk in terms of size of the tables.
5. What is the rollback segment tablespace allocated? You do a commit after a lot of delete operations I see.
-- While the transactions are in progress, and if the rollback segment is "full". And since there is a transaction that is not committed with data in the rollback segment "blocks" you cannot reuse those blocks. Next oracle tries to extend, but the tablespace is full, hence it fails to extend.
[Updated on: Wed, 19 June 2013 00:16] Report message to a moderator |
|
|
|
| Re: connection timeout when calling an oracle plsql function from a java program [message #587985 is a reply to message #587844] |
Wed, 19 June 2013 12:21 |
|
pradeep_kumar
Messages: 4
Registered: June 2013
|
Junior Member |
|
|
Hi,
Please find my response below:
1. Do you mean earlier the batch process took less time, and after addition of new data the process time has increased?
yes
2. Do you find any errors in DB? (like you are logging those errors or not.)
We are not doing any logging in the PL/SQL procedure and the audit feature on procedures is also not enabled. The only error that is captured is connection timeout error returned to the calling java program.
3. Is this code you use in production environment? The exception block seems like an amateur version.
We wanted to keep the plsql code as simple as possible so that once it goes to maintenance phase, a java developer with basic knowledge of plsql should be able to modify the code. Yes, this code is not yet in production but it will go to production this week-end.
4. In terms of number of rows you cannot measure anything. Talk in terms of size of the tables.
size of each table is approximately 8 MB
5. What is the rollback segment tablespace allocated? You do a commit after a lot of delete operations I see.
-- While the transactions are in progress, and if the rollback segment is "full". And since there is a transaction that is not committed with data in the rollback segment "blocks" you cannot reuse those blocks. Next oracle tries to extend, but the tablespace is full, hence it fails to extend.
It could be the issue. We asked the DBA to check if this is the issue. He hasn't yet responded.
Thank you so much for your help.
I tried to reproduce the error in a similar environment
1) with 100,000+ rows (0.1 million rows in each table)
2) the size of each table is 8 MB.
3) When there were no primary/unique or any other constraints or any indexes on the columns, in all the tables the data older than 6 months got deleted in 9 seconds.
4) When there are primary and foreign keys but no indexes on the columns used in the WHERE clauses, in all the tables the data older than 6 months got deleted in 26.7 minutes.
5) When there are primary and foreign keys and also indexes on the columns used in the WHERE clauses, in all the tables the data older than 6 months got deleted in 26.1 minutes.
|
|
|
|
|
|
|
|
|
|
| Re: connection timeout when calling an oracle plsql function from a java program [message #588028 is a reply to message #587994] |
Thu, 20 June 2013 05:17 |
ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
pradeep_kumar wrote on Wed, 19 June 2013 22:33The intention of Refcursor is if there is an error in executing the stored procedure, it will return ERROR to the java program and the java program will write the error to the log file. We are going to change it as below:
13 WHEN OTHERS THEN
14 v_sql_errm:=SQLERRM;
15 OPEN v_ref_cur FOR SELECT 'FAILURE'||v_sql_errm FROM dual;
16 RETURN v_ref_cur;
17*END f_housekeeping;
Which is an absolutely stupid thing to do. If you just get rid of all the "when others" stuff what will happen is:
- Oracle raises an exception
- The Java program will get that exception, and even when you do nothing else in Java:
- The exception will be printed to STDERR (which should go to the log file anyway)
That will include a stack trace with ALL the relevant information, like in which Java line the error happened, in which PL/SQL Line the error happened, etc...
Which is exactly what should happen. All the "handle exceptions and pass them up the chain to the caller" is ALREADY IMPLEMENTED in the Java / JDBC / PL/SQL Layers. DON'T re-invent the wheel that is already there and working perfectly in a way that makes it absolutely impossible to actually debug errors later on.
|
|
|
|
Goto Forum:
Current Time: Wed May 21 05:09:36 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
