0

The web application allows to request huge amount of information and to cope with the load and timeout I have JMS. Every time data export functionality is called it is routed via JMS and web query is released.

Onward, JMS calls Oracle stored procedure and it takes for while - 5-10 min. to execute it. My initial thought was that the call is asynchronous, because the query is released. However, Weblogic JMS has 15s timeout value for database connection. So, after while it kills the connection because there is no data in the pipe (Oracle stored procedure is busy to pull necessary data).

So far I found the following solutions:

  • Increase timeout. The support at data center were not very happy and pointed out, that there is a problem with app design. The point is, that the query has to be asynchronous on all layers, including JMS->Oracle.

  • Make the stored procedure as a job and close JMS->Oracle connection once the call is initiated. The problem with this approach is that I need to ping Oracle DB constantly in order to find out when the job is completed.

  • The same as second, but to try callback JMS. However, short reading gave my impression, that such solution isn't very popular because it won't be general (hard-coded values, etc).

What would be your suggestions? Thank you in advance.

Improve this question

1 Answer 1

Reset to default
1

If the stored procedure can not be optimized, then I would simply extend the timeout. I think the timeout on JMS can be overridden by some Java annotation, for a particular task. So you do not even have to modify global Weblogic setting.

You course there are ways how to call the procedure anachronistically. Either by using AQ (are you using Advanced queuing and an JMS provider) or by by submitting it as an scheduler job. But there is a risk that you can kill the database if you submit to many jobs to be executed in parallel. Both DBMS_JOB and (newer preferred) DBMS_SCHEDULER_JOB have ways how to restrict the number of concurrent sessions running. So instead of calling the stored procedure directly you will call another wrapper procedure, which will submit a single-shot non-repeating DBMS_SCHEDURER_JOB. Then the scheduler will execute your procedure as a scheduler task. But this solution you have to negotiate with your DBAs. There is a view in Oracle where you can check status

You you were using Oracle Advanced Queuing, you could also submit a job into the Oracle AQ. And then having a single PL/SQL stored procedure (running as "infinite" scheduler job), which will withdraw messages from AQ one by one, and executing the desired stored procedure. When the procedure finishes it can submit it's response into another AQ. And since AQ can act as JMS provider on Weblogic your application will be notified via JMS about procedure finish.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.