Discoverer Portlet Refresh Tuning and The New cacheThreshold setting

I was called in earlier this week by a client who's using Oracle Portal with Discoverer. They have a number of Portal pages that contain Discoverer Portlets, and they'd been having some problems with the refresh of their portlets.

The way they'd set their Portal page up was that it contained a number of Discoverer Portlets, which were parameterized and tied to a simple parameter drop-down (see this OTN article for more details). When the drop-down value was changed, the value was sent to each of the Discoverer portlets and in theory, each of them would then display a worksheet for this value. In reality, they were having two problems:

1. Firstly, even when the portlets refreshed to their schedule, only certain parameter value selections seemed to be cached, with some parameter values coming back instantly, others caused the Discoverer portlets to show the "Portlet is refreshing" message. Moreover, it seemed pretty random whether the selection of a parameter value would cause a refresh or come back straight away.
2. Secondly, when a portlet needed refreshing, the user had to click on the refresh link repeatedly until the worksheet came back. Ideally, they'd want the page to refresh itself rather than requiring user intervention.

So that I had a sandbox environment to work with, I recreated the portal page and portlets using the Sales History sample schema, and the latest versions of Discoverer and Portal, versions 10.1.2.2 and 10.1.4 respectively. Whenever I come across this sort of situation my initial recommendation is to make sure you've applied the latest patches, we'll see in a moment that this was actually quite good advice.

Anyway, having a play around with the portal page and the portlets demonstrated the behavoir that the customer was experiencing. I could select some parameter values and the worksheet portlets would display instantly, others would require a portlet refresh.

Webcache was turned on and in theory caching Portal pages, so I thought I'd have a delve around in the internal Discoverer tables, in particular the DISCOVERER5.PTM5_CACHE table, to see what was going on.

Taking a look at this table, I noticed that for each portlet, three parameter value selections were stored in table rows. I could see three rows being created and updated for each portlet, with the CCH_URL column containing the value of each worksheet parameter value. What was apparently happening here was that this table contained cached details of each portlet for the last three parameter values selected, meaning that if I went back to portal and selected the parameter values contained in the table rows, the worksheet came back straight away, if it was a value not in this table, a refresh was needed. So how could I get Discoverer to store more than three values in this table?

I then headed over to metalink and did a search on the PTM5_CACHE table name, and came up with Note 432849.1, "Discoverer 10g 10.1.2.2 Patchset Readme Notes" that described a new setting that you could add to the Discoverer configuration file. Called the "cacheThreshold" configuration setting, you could use it, from this patchset release on, to increase the number of worksheet parameter value settings that Discoverer would remember; moreover, setting it to a higher value will mean that, during the worksheet portlet request, Discoverer will run the query for all of the parameter values for that worksheet in the PTM5_CACHE table and store the results in the cache as well. For a worksheet with a small number of parameter values (six in the example I created above) this means that you can potentially pre-seed the cache with all the different parameter values, and have then all remembered so that worksheet portlets come back instantly. Not bad, and exactly what I was looking for - however you need to be aware that if you increase this value too high, your portlet refreshes can take a proportionately higher amount of time to refresh, as the worksheet runs for every parameter value in the PTM5_CACHE table. I decided to go with a value of 15 which wasn't too high and would cover all the parameter values that I had.

To increase the cacheThreshold value, locate the $DISCOVERER_HOME/discoverer/config/configuration.xml file and then find the following line:

<portlet logKey="disco.portlet.fatal"
logLevel="error" pickerCompatibleVersions="10.1.2" maxDataRows="1000">

Edit this line to add the cacheThreshold value:

<portlet logKey="disco.portlet.fatal"
logLevel="error" pickerCompatibleVersions="10.1.2" maxDataRows="1000" cacheThreshold="15">

Restart the Discoverer server and then open the Portal page again, and now you should find that once you select a parameter value and the worksheet is displayed (which may require an initial refresh to get the values), the portlet contents are remembered next time, up to the number of parameter values defined in your cacheThreshold value. Then, when the next scheduled refresh happens, all of the parameter values in PTM5_CACHE will be refreshed (up to, again, your cacheThreshold value) such that results come back straight away.

The other fix we wanted to put in place was to remove the need for users to click on the "refresh portlet" link. To do this, I used a bit of Javascript I found on this OTN Forum thread that looks for any part of the portal page showing the refresh message and triggers a page refresh after a short interval.

<script language="JavaScript" type="text/javascript">
<!--Copyright 2006 jason pschar
function refresh_wait()
{
location.reload(true);
}
if (document.getElementById("refreshPageLink"))

{setTimeout("refresh_wait();",10000);}

//-->

I then created a Dynamic Page portlet and added the code to the HTML section, like this:

Then, I added it as the last portlet to be displayed on the page (placing it in the bottom right-hand corner of the page), in it's own region which I set to not display any region headers, borders and so on. Then, when the page is displayed this new portlet will detect any Discoverer portlet refresh messages, and automatically keep refreshing the page until the message goes away.

Anyway, not a bad new feature, and once you've selected each parameter value and there's an entry for it in the PTM5_CACHE table, the refresh for that value happens automatically. A nice touch is that entries in this table seem to persist across reboots and even different users logging in, as long as you've created your portlets using public connections.

Going back to my patching comment, this shows why making sure you're fully patched up prior to doing some bug-fixing always makes sense, as the cacheThreshold feature only got introduced with 10.1.2.2 and without this, we'd have to settle for a maximum of three worksheet parameter settings being cached. This way, the business can decide how much resources it wants to throw at caching and pre-calculation of portlets based on the required portlet response time, and the issues (chiefly around unpredictability of response time and the need for manual intervention) can be made to go away.