One of the most common mistakes I see made in ADF Business Components based applications is a failure to tune the View Objects, and specifically to tune the in Batches of
parameter in the VO tuning section. This setting defaults to 1, which does not always meet the needs of the consuming UI or service interface and should generally be changed. This is a topic that I and others have covered before
Now certainly the batch fetch size is a crucial tuning parameter as it controls how many times the framework has to hit the database to get the rows needed to service a particular function. However, in this posting I wanted to turn my attention to another factor which can also have a significant effect - the iterator RangeSize.
The background to this was some recent detective work on an application where the time taken to display one particular screen was suspiciously long.
The page in question had a tabular display of data, but an inspection of the VO tuning parameters showed that a reasonable Batch size of 51 was being used. What's more the As Needed switch rather than the All at Once option in the VO tuning was being used. So the developer had done totally the right things there.
Running a SQL Trace on the page revealed an interesting thing though. Because the batch size was pretty high we'd expect that the framework would have to only do one or at most two fetches from the cursor to satisfy the needs of that table. However The TKProf output showed that in fact over 150 fetches took place retrieving over 8000 rows!
My thought processes in diagnosing this were to look in the following places:
- Are there alternative VO Instances defined on the AM where the tuning parameters are different (e.g. ALL_ROWS was specified)? We know the base definition is OK but it could be overridden.
- Any programmatic calls to change the Batch Size or fetch node in the VO?
- Any programatic calls to last() on the rowset or iterations through the rowset?
- Check for a RangeSize of -1 on the iterator definition in the pageDef files.
All of these drew a blank. The last one in particular felt like the problem but a search for the value of -1 in the pageDefs of the UI project only turned up legitimate usages of the -1 value.
Hold on I don't Understand This RangeSize?
Maybe I should take a quick step back and explain the iterator RangeSize. So, as we've seen, the tuning options in the view Object will control how often the VO has to go back to the database to get a specific number of rows. The iterator rangeSize is defined in the pageDef file for a particular page, fragment or method activity and it defines how many rows the UI layer should ask the service layer (in this case the VO) for.
Here's a typical definition that you'll see in the pageDef:
You'll see that the rangeSize here is set to 25 which just happens to be the value that is defaulted in when you drag and drop a binding into the page. However, it turns out that 25 is not the default value, something which has a bearing later in this investigation as we'll see.
So in this default case when the iterator is asked for data, it in turn will ask the VO for 25 rows, and if the VO does not already have that many rows in the cache it will have to go back to the database as many times, as determined by the batch-size, as it needs to get enough data.
Back to the Problem page
As it happens the pageDef for the table displaying the problem VO was indeed the defacto default of 25, so, sad to say, it was not the obvious suspect at fault, more investigation was needed.
At this stage the investigation splits into a couple of parallel efforts, manual code inspection, and tracing using the ADF Logging capability
to try and work out what interactions were happening with the problem VO.
Welcome to the Internet, Please Tread with Care
What can we trust? Well in the world of ADF Blogs in the wild there are some great bloggers, but that does not mean that you can just copy without thinking. It turned out that one of the problems with this application was to fall foul of copy-without-thinking syndrome.
The code in question seems innocent enough, it's published out there on the internet as a way of refreshing an iterator:
//Please, Please Don't do this! (My comment)
DCIteratorBinding iterBind= (DCIteratorBinding)dc.get("<your iterator name>");
Two facts to discuss in relation to this code snippet:
- Read the JavaDoc for DCIteratorBinding - the refresh() method is very clearly marked as Internal only, applications should not use. That's what we call "a hint" in the trade, you can choose to ignore it but don't come crying...
- Look at that parameter being passed to the refresh method DCIteratorBinding.RANGESIZE_UNLIMITED - can you guess what that does? Yes it does the same as setting the RangeSize to -1 and will cause the VO to be asked for all of the data for that query. You can see how bad that could be if the VO has the potential to return a lot of rows.
So something to put right already.
But Wait, There's more!
Although the call to refresh was a great catch and the application will be better without it, it turned out not to be the cause - darn.
However, the parellel effort to run some more tracing found the smoking gun. The ADF Log trace showed a double execute on the iterator for the VO in question, or to be more precise, executes on two different iterators bound to the same VO from different regions on the page.
A useful diagnostic here was then to set a breakpoint in the setRangeSize() of oracle.adf.model.binding.DCIteratorBinding. Doing this we could see that the first iterator execution was actually responsible for setting the RangeSize to -1 and the second to the value we where expecting for that table based on the pageDef.
All credit to the development team I was working with here who ferreted out the actual problem, it was finally down to one of omission.
Recall I made the statement earlier about 25 being the defacto default for the RangeSize? Very true, when you create a binding, that's what the IDE puts in for you. But what's the actual default? Well that turns out to be -1. So if you omit the RangeSize from the iterator definition by intent, or mistake, you're going to have a potential side effect you may not have expected! That was exactly the problem in this case - no RangeSize.
Specifically the problem was caused by a method binding in one of the bounded task flows in the page. The pageDef for this method activity included and iterator defined for the problem VO but without a RangeSize defined.
Lessons to Learn
- For each peice of UI that is bound to data understand how the user is going to use that screen - will they view just one record, will they scroll through several or a lot, will they only scroll downwards and process one record at a time and so on. You can't effectivly tune a page until you understand how it will be used.
- Always tune your VO definitions or instances to the requirements of each endpoint. If this means having multiple VO definitions or multiple VO instances with different tuning parameters then do it.
- If you have consciously set your Batch size in the VO tuning parameters to a small number because that's all you see on the page at once then also tune the pageDef iterator RangeSize. Otherwise that defacto 25 RangeSize could generate a bunch of unnecessary calls back to the database. Think about it, if you have a single record form and you've not tuned the VO Batch size and you've not tuned the iterator RangeSize, you could be doing 25 roundtrips to the database just to see one record. Great way to please your DBA!
- Use a RangeSize of -1 with caution. It has its place for iterators that serve short lists used for lookups and menus but anything else is an exception.
- Don't blindly copy everything you see in the blog-o-sphere. If you don't recognize an API call then look it up. If something says it's for internal use only, then guess what, don't use it.
- Never ever, define an iterator in your pageDef without an explicit rangeSize. If you need to see all rows, say so with the -1 value, otherwise use a positive integer.
We all go home older and wiser...