Thursday, August 2, 2012

ADF BC Passivation/Activation and SQL Execution Tuning

Passivation/activation in ADF BC is designed to keep temporary user data across requests, when there are more online users than application pool can handle. If you are new to this, please read from Oracle developer guide - 40 Application State Management. There is added cost to passivation/activation - additional SQL execution. During activation event, its not only temporary data gets initialized - but complete VO SQL gets executed again. This means system could suffer significant performance issues, if there are lots of passivation/activation events happening. In order to avoid frequent passivation/activation, make sure to tune referenced pool size properly for your environment - Stress Testing Oracle ADF BC Applications - Passivation and Activation.

This post demonstrates additional SQL execution during activation event. Download sample application - ActivationSQLApp_v2.zip.

Form is loaded for the first time:


One SQL execution happens as it should:


We are running test with AM pooling disabled, this allows to simulate stress test environment and trigger activation/passivation events:


With AM pool disabled, without closing screen, press Next button in the form - this will navigate to the next record:


SQL will execute one more time:


From the detail log, we can see that SQL execution event is logged as well, when there is activation event:


During normal scenarios, SQL is executed once and data stays in memory. Keep attention, how often passivation/activation happens in your system - this can affect performance significantly.

10 comments:

Anonymous said...

Hi,

Excellent post, as usual. I had a question about shared AM pools. I was testing with 1 AM connection (shared) and saw that the AM instance was "reset" in between different calls (was trying to load the same page with 2 browsers). Also saw the behavior where if there are more than 2-3 web calls, will get a PPR on the page (with AM connection exhausted).
What is the life cycle of Shared AM pool and when does, lets say, 1 instance not enough to be able to be shared among multiple requests.

Andrejus Baranovskis said...

Hi,

I believe this post might help you to get more ideas about Shared AM's and where these kind of AM's is used: http://andrejusb.blogspot.ca/2011/07/adf-code-corner-article-087-how-to.html

Thanks,
Andrejus

Krishna Sumanth said...

Thanks Andrejus,
What's the solution for this problem?

Andrejus Baranovskis said...

Solution is to tune AM properties - referenced pool in particular properly, according to estimated number of concurrent users.

Andrejus

HDS said...

Hi,

Nice post.But I am facing a slightly different issue in production. We have a Jspx page where we have dragged and dropped the VO object(data model) as table. In the view criteria, few fields are marked as selectively required so the user needs to enter a value in those fields to get the data.Executing just the VO query takes forever due to huge amount of data.
THe user normally opens 2-3 tabs for different pages among this one. The user migrated between different tabs for their operations. But we are seeing the VO query being triggered as is once in while even though the user hasn't searched for any data on that page for quiet a bit of time.It is not consistent but it seems that it is been triggered when the session timesout among the muliple tabs.I am not sure what event can cause that to trigger as it is not possible to just execute the VO query through the page due to few fields marked as selectively required.Any help would be of great help as a long running query is crashing the server (stuck thread on weblogic server)

Andrejus Baranovskis said...

Hi,

This is how it works - during AM activation, it will invoke SQL for passivated VO's. You should check when exactly this slow SQL is executed. I would recommend to set row fetch limit for VO instance. I was facing several cases, when WebLogic stuck threads were reported due to unlimited fetch from DB.

Andrejus

HDS said...

Hi. Thank you for the prompt reply. The query does have order by clause which is causing it to run forever.
For now I have programmatically added the order by clause using setOrderByClause in processQuery() method for the Search button. But I would really appreciate to know on the following.

1. Does the order by clause persist whenever the VO object SQL is excuted. For example once the search button is called, setOrderByClause is set, but if I have called vo.executeQuery somewhere else, will the order by clause still persist in that case?

2. When all the AM gets activated?

Thanks again in advance

Anonymous said...

Hi,

Wanted to share the resolution for the issue I posted here start of this year. This was resolved back in Feb.

After further research it was found that the issue is with adding a child VO's (Main/Parent VO and child VO related by a viewlink)attribute in the search pane. When the user clicks on a child VO rows for a particular row in Main Pane/VO and then open a new tab with the page URL on first tab, the VO query attached to main pane executes without the WHERE clause. This is causing stuck thread and filling up the server heap space with rows (ViewRowImpl objects)and potentially crash the server if the data is too huge.
We have selective required search field on this search pane and having corresponding validation in ViewObjectImpl's executeQuery() method to check atleast one of the selectively required fields is populated by user before hitting the search button.
Somehow when the user clicks on child pane row and open another tab(session) with the same URL on first tab,the main VO panes query is skipping
executeQuery() method after calling executeQueryForCollection().
This is causing the main panes VO query to trigger without any search criteria applied to it.
Further, it is seen that this is causing AM instance not to be garbage collected even after the user session timesout and stays in the heap until server is bounced. This is causing memory leaks.

Resolution: We override executeQueryForCollection to add a check to validate the viewcriteria (to check atleast one the field is present to be applied as a view criteria) before calling executeQueryForCollection. If the view criteria is valid call executeQueryForCollection else call setMaxFetchSize(0) before calling executeQueryForCollection to not execute the query. After calling executeQueryForCollection again call setMaxFetchSize(-1).

I am not sure if this is ADF bug when we add a child pane's attribute in search field. we tested it by removing the child VO's attribute in search criteria/pane and confirmed that AM's and corresponding VO objects where cleaning up after session timeout.Also opening a new tab didn't cause the main panes VO object to execute as soon as the page was hit after opening the new tab.

I am not sure if this is combination of activation/passivation happening on VO objects without any attribute marked as key or related to something else. Hope the info helps

Thanks,
-HDS


Andrejus Baranovskis said...

Thanks for update !

Andrejus

Anonymous said...

Hi Andrejus,

Are we aware of any such known issue/memory leaks while using the child View object attribute in parents VO Viewcriteria?

Thanks,
-HDS