Discoverer Portlet Refresh Tuning and The New cacheThreshold setting

February 6th, 2008 by

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:

Edit this line to add the cacheThreshold value:

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.

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.

Comments

  1. Steve Fitzgerald Says:

    You make a good point about being on the latest patch level. Discoverer also has some cumulative patches on top of the AS10g 10.1.2.2 patchset that delivers some other portlet fixes.

    Metalink note 237607.1 ALERT: Required and Recommended Patch Levels For All Discoverer Versions

    Resources are also a good point since the Portlet will refresh once for the main portlet and for each unique SSO user, if customizations are allowed. It is good to ensure the session pool is tuned for re-use so dis51ws sessions do not get to high or reach maxproc limit in opmn.xml. Speaking of which, if I remember correctly the latest patches also correct an issue of the dis51ws sessions not dying off correctly on specific OS’s.

    Enjoy reading your technical articles and adventures. You have a nice writing style.

    Thanks for sharing.

  2. Mark Rittman Says:

    Hi Steve,

    Thanks for the comments. I’d agree, adding extra cache items into the PTM5_CACHE table has a big impact on the resources needed to refresh the portlets. We found that, for a typical ten portlet system with around fifty parameter values, we had to bring the refresh frequency of the individual portlets back to once every four hours, with the refreshes being staggered once every ten minutes, otherwise the load on the server became too great. Especially where each portlet refresh takes a minute or so, after a while you find that the last lot of refreshes are still taking place whilst the next lot has already started up, so my advice in the end was to use the feature, but then either refresh infrequently and on a staggered basis, or make sure each portlet parameter only has a small number of values, say at a maximum of ten. That said, we got the entire system running with all portlets returning data immediately, a moderate load on the server at all times and reasonably frequently refreshed data, which wasn’t bad actually and was only really possible through the use of the cacheThreshold parameter.

    Good point about the SSO users and customizations, hadn’t thought of that though. For their system, they used a single public connection for all users and didn’t allow customizations, so this wasn’t an issue, but I’ll bear in it mind if I get to use this feature again in future.

    regards, Mark

  3. Pete Dinin Says:

    Mark –

    The timing of your posting was nearly perfect for us. Just today, we were observing the PTM5_CACHE table trying to figure out why we seemed to be ‘re-caching’ our portlets when we executed with new parameters.

    We are looking to create a portal page with 10 portlets, with 1 parameter that has over 3000 possible values. We wold like the content to be cached (for better user experience), but ourconcern is the scalability of the daily refresh process. Since we have 10 portlets, it would be easy to stagger the refresh times. However, with 3000+ caches for each portlet, how well would the scheduler handle this? We know that we would need to limit the number of discoverer portlet provider sessions so we do not saturate the discoverer server. However, what else should we consider?

Website Design & Build: tymedia.co.uk