Why You Should NOT Publish Large Worksheets to Portal

The Discoverer Portlet Provider allows users to publish Discoverer worksheets to Oracle Portal - tables, crosstabs, graphs, and as gauges. One mistake that I have seen users do, though less frequently now than before, is to assume that what does the job as a worksheet in Plus or Viewer will also work just as well when published to Portal. Specifically, I am talking about taking large worksheets and publishing them to Portal. This is bad design and even worse for performance. Let's see with a simple example. Below is a simple crosstab that ships with the Video Stores schema in Discoverer, 'Conditional Formats', from the 'New Features Examples' workbook. I have published this to a Portal page as as worksheet portlet.

This is the OC4J_BI_Forms memory usage before publishing the worksheet as a portlet.

And this is the memory usage after publishing the worksheet to Portal. The memory increase is only marginal.

Now let's look at a large worksheet. This is a worksheet I have created using Discoverer Plus. Yes, it is a deliberately bad design for a worksheet: too many dimensions and hierarchies, no use of page-items, no parameters to reduce the data, and for all practical purposes a very unusable sheet. It returns some 3,300 rows of data: quite useless if you want to do any analysis on the data.

I will now publish this to Portal as a worksheet portlet.

After I have published this to my portal, take a look at the portlet itself. The error message at the bottom of the portlet. No more than a thousand rows of data are cached by Discoverer in the metadata repository, so obviously 3,000 rows of data are not going to get cached. Page items would have been a much better alternative; or parameters.

But more interestingly, look at the memory consumption of the OC4J_BI_Forms component, before and after publishing the portlet. It is obbious that a huge amount of memory has been taken up to publish this single portlet, right? Why?

In the Discoverer Portlet Provider, portlet data is cached in the DISCOVERER5.PTM5_CACHE table in the metadata repository database. The format of this cahce is XML. The BIBeans (table, crosstab, graph, or gauge) object that you see in Portal is created out of this cached data. It requires data to be passed to it as a DOM object.
The retrieved XML is therefore parsed to create the DOM structure. This parsing of data and creation of DOM object is a resource intensive operation. More the data, more costly is the parsing operation. So if the number of rows is huge then the amount of data parsed is a lot and can cause problems.

[Thanks to Gautam for his help on this post]