In a previous blog I went through the reasons why setting the cell retrieval threshold was important if you want to maintain a healthy JVM and how to interrogate a heap dump using third party software, if you have not read the post then it is probably worth having a read through it to gain some background information.
In the blog I highlighted that a patch was available for 11.1.2.2 that allowed to set the maximum cell retrieval but unfortunately this had not made it into the on-premise 11.1.2.3 as of the .500 PSU even though it was available in the cloud version, I had this light hearted message for Oracle.
Now I know Oracle would have not taken any notice of it but luckily with the recent planning 11.1.2.3.501 PSU the following defect was addressed.
18663876, 18259065 - When retrieving data through Smart View Ad-Hoc Analysis, any number of rows or columns is retrieved, despite the preferences which were set.
After applying this patch, the below mentioned property need to be set in Administrator -> Application -> Properties screen ERROR_THRESHOLD_NUM_OF_CELLS -> Value for this property will be the maximum number of cells for which the error messages is shown to the user during Ad-hoc Grid operation.
Note 1: This fix applies to Planning data simple forms, SmartView Ad-hoc and Financial Reports against Planning application.
Note 2: Unless overridden, default value of this property -ERROR_THRESHOLD_NUM_OF_CELLS is 250000 cells.
So the functionality has finally made it to 11.1.2.3 and to be sure it was working I had to test it out.
After applying the .501 PSU I created and opened an extremely large form in planning that would pass the default threshold of 250,000 cells.
In the blog I highlighted that a patch was available for 11.1.2.2 that allowed to set the maximum cell retrieval but unfortunately this had not made it into the on-premise 11.1.2.3 as of the .500 PSU even though it was available in the cloud version, I had this light hearted message for Oracle.
Now I know Oracle would have not taken any notice of it but luckily with the recent planning 11.1.2.3.501 PSU the following defect was addressed.
18663876, 18259065 - When retrieving data through Smart View Ad-Hoc Analysis, any number of rows or columns is retrieved, despite the preferences which were set.
After applying this patch, the below mentioned property need to be set in Administrator -> Application -> Properties screen ERROR_THRESHOLD_NUM_OF_CELLS -> Value for this property will be the maximum number of cells for which the error messages is shown to the user during Ad-hoc Grid operation.
Note 1: This fix applies to Planning data simple forms, SmartView Ad-hoc and Financial Reports against Planning application.
Note 2: Unless overridden, default value of this property -ERROR_THRESHOLD_NUM_OF_CELLS is 250000 cells.
So the functionality has finally made it to 11.1.2.3 and to be sure it was working I had to test it out.
After applying the .501 PSU I created and opened an extremely large form in planning that would pass the default threshold of 250,000 cells.
Good news the functionality has been implemented, so how about a large ad-hoc Smart View retrieve through the planning layer.
All good, it is working as expected.
The remaining test it to set the planning property ERROR_THRESHOLD_NUM_OF_CELLS to control the threshold.
It is shame you still have to restart the planning web application server to apply the changes.
Running a large form confirmed that the new threshold of 10,000 cells has been honoured.
The same positive results with a Smart View ad-hoc planning retrieve.
If you are running planning 11.1.2.3 and want to use this functionality then get patching to 501 or newer.
Thanks John, we are very happy you raised this with Oracle and it is in 11.1.2.3. Any rule of thumb sizing guidelines for the threshold level?
ReplyDeleteJoachim
Hi,
ReplyDeleteThanks for this post. I have a question : does this patch resolve the problem of response time in a Smart View Ad-Hoc Analysis ? In fact, with a Smart View ad-hoc Planning retrieve, ad-hoc analysis are very long to execute.
Thanks.
V.