Month: July 2015

Oracle Critical Patch Update – July 2015

Last week on July 14, Oracle released it’s quarterly Critical Patch Update.  The following Hyperion and BI products are affected by security vulnerabilities:

  • Hyperion Essbase
  • Hyperion Shared Services
  • Hyperion EPMA
  • OBIEE

Hyperion Essbase

There are patches available to fix the vulnerabilities in Essbase for the 11.1.2.2 and 11.1.2.3 versions. The patch for version 11.1.2.3 are the 11.1.2.3.506 Patch Set Update (PSU) for the Essbase Server and Essbase Client (patch numbers 20184072 and 20184082, respectively).  The vulnerability fix for Essbase version 11.1.2.2 is “Upgrade to Hyperion Essbase 11.1.2.3, then apply the patches listed above”.  If you are on an older version of Essbase than 11.1.2.3, it is time to upgrade.

Hyperion Shared Services

The CPU document calls this “Hyperion Common Security,” but they are talking about Shared Services. The vulnerability is listed as affecting versions 11.1.2.4, 11.1.2.3, and 11.1.2.2.  The patch for version 11.1.2.4 is listed as patch number 20876722. This one is interesting because if you search, that patch doesn’t bring anything up on the My Oracle Support site. I also searched for Shared Services patches under 11.1.2.4, but there isn’t even an 11.1.2.4 version available to choose yet.  Edit 7/22/15 – The Shared Services 11.1.2.4.001 patch (number 20876722) came out on 7/21 and is available on My Oracle Support. The 11.1.2.3 patch is the Shared Services 11.1.2.3.700 PSU (number 20675028). That PSU requires patching nearly every EPM product in your environment but gives the ability to run Internet Explorer 11 in its native mode – no more Enterprise Mode needed for 11.1.2.3.  The 11.1.2.2 patch is listed as number 21052487, but this is a bad patch number as well with no results when using their link.

Hyperion Enterprise Performance Management Architect

The EPMA vulnerabilities affect both versions 11.1.2.2 and 11.1.2.3.  In version 11.1.2.3, you have two different options to fix the vulnerabilities: EPMA PSU 11.1.2.3.501 (patch number 19466859) or EPMA PSU 11.1.2.3.700 (patch number 20929659).  If you are on version 11.1.2.2, a patch set update is available on request from Oracle.  I assume that you would need to submit an SR to get a PSU from Oracle Support for 11.1.2.2.

OBIEE

For version 11.1.1.9 of OBIEE, only a patch to Third Party BI software (patch number 21235195) is listed as a critical patch.

The following patches are listed to fix vulnerabilities in version 11.1.1.7:

Essbase fragmentation head-scratcher

With the release of Essbase 11.1.2.3.500, there was an interesting entry in the readme that caused some chatter on Network54.  The readme states:

The INPLACEDATAWRITE setting in essbase.cfg enables or disables in-place data writing.

Prior to this release, each time a data block was updated, it was written to a new disk location. With this release, for Exalytics, Essbase enables in-place data writing.

In-place data writing means that when updates occur, the data block can be written to the same location, as long as the compressed size of the data block fits in its original location on the disk.

In-place data writing can help reduce data fragmentation and lower the need for frequent restructuring of database. It also reduces the need for frequent index updates, resulting in improved performance.

Merely updating a single value of a cell in a single block causes that block to be written to the end of the page file? This made long-time developers scratch their heads because many of us believed that updating values in a block did not cause fragmentation. If submitting data causes fragmentation, imagine how bloated our Planning databases must be. No wonder we hear recommendations to restructure nightly during a planning cycle.

Glenn Schwartzberg said that yes, that’s how he understood it to work.  Edward Roske said there was no way submitting data cause fragmentation.  Cameron Lackpour didn’t believe it either until he did some testing and found that the ESSCMD GETDBSTATS command reported fragmentation after updating a single cell of Sample.Basic. Dan Pressman commented that he had seen the same behavior with compression turned off – BSO writes to the end of the .pag file when blocks are updated.

Test 1:

I tested this out on an 11.1.2.4.000, non-Exalytics sandbox. In Excel, I retrieved a single block of one of our sample cubes (not Sample.Basic, this was a larger demo cube). The page dimensions of my worksheet listed a single level-0 member from each Sparse dimension of the cube. Accounts were on my rows and January was my column dimension (both were level-0 Dense members).

With that sheet, I wrote a macro to do the following:

  • Update each data cell of the grid to a randomly generated number
  • Submit the data for the block back to Essbase
  • Call a MaxL script to stop and restart the Essbase application to flush the cache
  • Loop back to the top, iterating 250 times

While I ran the macro, I (im)patiently watched the database directory to see if the database’s essn.ind or essn.pag files would grow.  As we all know, if there is fragmentation that happens each time the block is submitted back to Essbase, the .pag file should see some growth – especially over 250 iterations. For complete transparency, this database is using the bitmap compression type that is the default for BSO applications.

After 250 iterations of changing the same block and stopping/restarting the application there was no essn.pag file growth. The GETDBSTATS results show the following:

  • Average Clustering Ratio: 0.4431846
  • Average Fragmentation Quotient: 0.8091218

That leads me to believe there is a little bit of fragmentation, so I cleared the cube and loaded in a level-0 extract of the data and ran a calculation.  After that, I ran GETDBSTATS again and found the following:

  • Average Clustering Ratio: 0.5117247
  • Average Fragmentation Quotient: 0.6745301

Test 2:

My next test was to do the same loop except I added in a quick aggregation step after the data was submitted. This time, as the macro ran, I could plainly see the essn.pag file growing every 5-6 loops of the code. The Average Clustering Ratio and Average Fragmentation Quotient also grew as the calculations ran.

What does all of this mean?

Conclusion:

My conclusion is that the documentation on INPLACEDATAWRITE is a little misleading. Essbase without the INPLACEDATAWRITE does NOT write a block to the end of the .pag file if a block is updated. Blocks may be updated all day long by submitting data to them without expanding your .pag files. Fragmentation is caused when a data block is brought into the calculator engine. This is where INPLACEDATAWRITE does its magic on Exalytics machines and allows those blocks to be compressed and returned to their original location in the .pag file, thus preventing fragmentation.

What about the Fragmentation Quotient? Well, in the DBAG, it mentions that the Fragmentation quotient can be high and not indicate a need to defragment since free space in the .pag is created in 8MB chunks. I observed this to be true during Test 2 where excess free space was added to the .pag file and the file would only grow after 5-6 loops of the submit data/calc/stop/start routine. My thought is that an Average Fragmentation Quotient less than 1 (<1%) is essentially no fragmentation at all. After a single calculation script I began to see that Average Fragmentation Quotient above 1 (1.884844). By nature Essbase has some degree of fragmentation all of the time as it’s .pag file is always a little bit bigger than the actual size of the data contained therein. As long as the .pag file isn’t growing and the Average Fragmentation Quotient is less than 1%, I’m counting that as no fragmentation by submitting data to a block.