Who likes, when ADF screen hangs on long running SQL query? I guess no one, so today I will describe how to take control on long running SQL queries and bring ADF screen back to life. ADF BC allows to set VO query execution timeout, we can apply this approach to limit SQL query execution time and make it predictable.
We need to simulate long running query, for this purpose I have created PL/SQL function with conditionally infinite loop. Based on function parameter, if parameter value is greater than 0, loop is terminated. This function is simulating both cases - long and fast running queries:
PL/SQL function code, you can find it inside sample application, Model project. In order to run sample application (ADFSQLTimeoutSample.zip) you can compile given PL/SQL function inside HR schema:
We can define new transient attribute on VO level to invoke PL/SQL function (Query Expression) with infinite loop and slow down entire VO query:
VO query statement to be executed:
PL/SQL function accepts input parameter, this parameter is initialized from View Criteria bind variable, but for default VO query execution we need to make it to be required - so SQL query will not fail, when running with empty bind variable:
Because bind variable is marked as Required, it will appear in the UI, so let's set Display Hint = Hide:
We have View Criteria, it will be used to execute search operation and assign bind variable value (Salary):
Thats almost it about VO, AM contains one custom method, this method will be used to set VO query execution timeout limit:
VO query timeout execution limit is set for VO instance, by calling setQueryTimeOut(milliseconds) method:
It is important to catch SQL exception inside executeQueryForCollection method, defined inside VO implementation class. If we don't catch exception inside this method, once timeout will happen - UI will be broken. Exception will be handled by code and reported by JboException that will be rendered in UI:
On controller layer, bounded ADF task flow should invoke default Method Call, it will set query timeout for certain VO instance (as described above):
Sample application is set with maximum VO query execution time to be 6 seconds (6000 milliseconds = 6 seconds * 1000). This means, if query will be running longer than 6 seconds, it will be terminated and control will return back to the user.
Here I'm doing a test now, search is done without specifying value for bind variable (Salary), this means PL/SQL procedure will enter infinite loop. However, because query execution timeout is defined for 6 seconds, it will stop and inform user about terminated long running query:
Provide value for Salary field, we will not enter into infinite loop and results will be returned:
With results rendered, lets try again to execute infinite loop. Again long running query will be terminated nicely:
Let's do a search again, including Salary value - results are returned:
If we would not use query timeout, it would run forever (or at least as long, as it needs to run) and consume all server resources:
We need to simulate long running query, for this purpose I have created PL/SQL function with conditionally infinite loop. Based on function parameter, if parameter value is greater than 0, loop is terminated. This function is simulating both cases - long and fast running queries:
PL/SQL function code, you can find it inside sample application, Model project. In order to run sample application (ADFSQLTimeoutSample.zip) you can compile given PL/SQL function inside HR schema:
We can define new transient attribute on VO level to invoke PL/SQL function (Query Expression) with infinite loop and slow down entire VO query:
VO query statement to be executed:
PL/SQL function accepts input parameter, this parameter is initialized from View Criteria bind variable, but for default VO query execution we need to make it to be required - so SQL query will not fail, when running with empty bind variable:
Because bind variable is marked as Required, it will appear in the UI, so let's set Display Hint = Hide:
We have View Criteria, it will be used to execute search operation and assign bind variable value (Salary):
Thats almost it about VO, AM contains one custom method, this method will be used to set VO query execution timeout limit:
VO query timeout execution limit is set for VO instance, by calling setQueryTimeOut(milliseconds) method:
It is important to catch SQL exception inside executeQueryForCollection method, defined inside VO implementation class. If we don't catch exception inside this method, once timeout will happen - UI will be broken. Exception will be handled by code and reported by JboException that will be rendered in UI:
On controller layer, bounded ADF task flow should invoke default Method Call, it will set query timeout for certain VO instance (as described above):
Sample application is set with maximum VO query execution time to be 6 seconds (6000 milliseconds = 6 seconds * 1000). This means, if query will be running longer than 6 seconds, it will be terminated and control will return back to the user.
Here I'm doing a test now, search is done without specifying value for bind variable (Salary), this means PL/SQL procedure will enter infinite loop. However, because query execution timeout is defined for 6 seconds, it will stop and inform user about terminated long running query:
Provide value for Salary field, we will not enter into infinite loop and results will be returned:
With results rendered, lets try again to execute infinite loop. Again long running query will be terminated nicely:
Let's do a search again, including Salary value - results are returned:
If we would not use query timeout, it would run forever (or at least as long, as it needs to run) and consume all server resources:
Good post, but I'd rather set a resource quota in the database itself - this way no matter where the SQL statement is being issued from it will be controlled at a single point.
ReplyDeletehttp://download.oracle.com/docs/cd/B19306_01/network.102/b14266/authoriz.htm#i1009538
[]s
Maiko
Hi Maiko,
ReplyDeleteVery true. However in most of the cases DBA's are from different team, and they are not exactly happy to adjust DB settings based on development request. Also it might be there are different systems using same DB, and it may require different SQL execution time. Of course, both options are good, but I believe its quite powerful to control SQL execution from ADF app itself as well.
Andrejus
Great post but i want to know
ReplyDeletehow to get the SQL Error code in order to override it.
how you got 27126 error code.
What is 27126 error code?
ReplyDeleteAndrejus
you wrote in this screenshot
ReplyDeletehttp://3.bp.blogspot.com/-fO-90hiIJP4/TkLL8vBCrNI/AAAAAAAAFTs/WnKlcuFN-8o/s1600/10.png
in executeForQueryCollection method
if sqlse.getErrorCode().equals("27126")
This is the sql Error Code I Mean
I want to know how I catch the sql Error returned
You can retrieve SQL exception text in the same method, and re-throw new JboException or update existing one.
ReplyDeleteAndrejus
Good post. I was searching for a way to prevent the UI from getting broken after a timeout and this did the trick. What I don't understand is why throwing a JBOException instead of allowing the SQLStmtException to propagate fixed the problem because SQLStmtException is a JBOException. Ahh the mysteries of ADF...
ReplyDeleteWe have the statement-timeout set to 5 minutes in the jdbc-connection-pool-params settings which sets the timeout for every query but the UI was broken after we displayed the timeout message and clicking anywhere on the page would cause a fatal exception. Re-throwing a different JboException fixed this.
ReplyDeleteIf i remember correctly, SqlStmtException was not propagated to UI and application was in broken stage. Thats the reason of using JboException.
ReplyDeleteI did some experimenting on how to force a query to be canceled from the UI and wrote about it here.
ReplyDeletehttp://dkleppinger.blogspot.com/2012/01/how-to-cancel-long-running-query-from.html
It will not work to process multitasking request, means you can press Cancel button - but it will be processed only after query request will be finished. I was testing this, it doesn't work in ADF even with pure JavaScript implementation.
ReplyDeleteVO instance should be accessed through AM module, otherwise there might be problems on stress test environment, when AM pooling will be activated.
Andrejus
Since PS2 ADF will process requests on the same Session concurrently if they are not on the same page. ie. In a different browser window or browser tab or running within a Frame. That is what I experimented with and it does work. I was able to immediately cancel a long running query running in a different window. The Other window would return immediately with the "User canceled query" message.
ReplyDeleteYes, but this is different use case. Who would like to press Cancel button in different tab :)
ReplyDeleteAndrejus
I posted a sample application that implements a Cancel button that cancels the query from the UI. It can be downloaded here
ReplyDeletehttp://dkleppinger.blogspot.com/2012/11/how-to-cancel-long-running-query-from.html
Thanks for update :) I like your new sample solution much more comparing to the previous one.
ReplyDeleteAndrejus
Hi Andrejus,
ReplyDeleteI have an issue here.
1. I executed a query and got results back, results rendered as table.
2. I executed a long running query now. The query times out and the message in the catch block is shown. (Which looks good from the UI). But when I checked the logs I see an SQLSyntaxErrorException, and the printed query has 2 where clause. Something like WHERE ORDER_NUMBER = :fbkKy__0 is appened and the value for fbkKy__0 seems to be the primary key value for the first row of the results shown in step 1. Any idea how I can get rid of this error?