Oracle Exalytics Week : The Summary Advisor

This week we've been taking an in-depth look at Oracle Exalytics In-Memory Machine, the new engineered system from Oracle that combines Sun hardware with customized versions of Oracle Business Intelligence, Oracle Essbase and Oracle TimesTen, all running on 64-bit Oracle Linux. Here's a quick recap of the postings this week, and the ones to come:

So now we know about the role that TimesTen plays in the Exalytics architecture, and that the Oracle BI Server uses cached aggregated data copied into TimesTen to speed up dashboard queries, how does the data get into TimesTen in the first place? This is where the Summary Advisor comes in, a new feature within the Oracle BI Administration tool that only appears once you've enabled Exalytics features on the server.

The Summary Advisor works by taking summary statistics data captured alongside usage tracking data as queries are run, and uses this to estimate the gain that could be had if stored aggregates were used instead. The Summary Advisor uses an iterative algorithm that searches, during each iteration for the best aggregate to recommend for implementation in TimesTen. To do this, it takes the row-count from the proposed aggregated fact table, divides this by the row-count of the existing detail-level fact table, then multiplies the result by the previous query time, giving an estimated query time with the aggregate implemented.

To make these recommendations, usage tracking in 11.1.1.6 has been enhanced to include another table, S_NQ_DB_ACCT that contains details of the physical query associated with the logical one, and a new table called S_NQ_SUMMARY_ADVISOR that's populated, along with the usage tracking tables, once Exalytics is enabled. If you're upgrading from earlier versions of OBIEE and want to use the existing usage tracking data to retrospectively populate these summary statistics, that's possible as well (though it can be fairly time-consuming if you've got lots of data, as the utility has to go and re-generate all the physical SQL statements to complete the required information).

Before you can use the Summary Advisor, though, you need to perform a few configuration steps. Oracle ACS, or a partner such as Rittman Mead, would have installed the Exalytics server on a rack in your datacenter, and performed the initial software install including "wiring" OBIEE and TimesTen together, and enabling the Exadata features. You would then need to create the TimesTen database, and configure the opmn.xml file used by the BI Server so that it's got access to the required TimesTen client files. Then, you'll add references to the TimesTen database into your RPD file, as well as references to the usage tracking and summary statistics tables. Finally, you'll enable usage tracking (the process for which has changed in 11.1.1.6, but more on that at a later date), and you'll be ready to start capturing statistics and using the Summary Advisor.

Once everything is set up, and you've identified some dashboards and analyses that are candidates for performance improvements, the Summary Advisor process works like this:

With the repository open online, select Tools > Utilities > Oracle BI Summary Advisor. The Summary Advisor wizard will then display, and initially ask you to select which business model and mapping layer facts are within scope for the exercise. Start by selecting just a couple of facts, so you can understand the recommendations and assess their impact in isolation.

Then, you define the time window for entries in the summary statistics table to be considered. This can be useful if you've been gathering statistics for a long time, but only want to consider recent performance and query workload.

Next, you select the minimum cumulative time that a query has to have run before it's within scope for the exercise. Again, consider limiting this scope so that only the most expensive queries are considered first, then set the limit lower as space in the TimeTen database allows.

Then, you select the physical database and connection pool setting for the TimesTen database, and indicate how much storage space there is on it. If you've got several Exalytics boxes daisy-chained together via InfiniBand for high availability, you'd add entries for all of them here, and the Summary Advisor would then populate them all in a mirrored fashion, so one is available if the other goes down (also useful for rolling refreshes if you want to keep one TimesTen database available if the other is being refreshed).

The Summary Advisor actually generates a command file, similar to the one generated by the Aggregate Persistence Wizard, at the end of the process, which you then run through the nqcmd.exe utility to pass to the BI Server. Using this page, select the location to save the command file to, which typically would be your workstation where you've also got the Oracle BI Adminstration tool installed.

Next, you get to place a limit on the total time that the Summary Advisor takes coming up with recommendations, and how much extra gain each additional recommendations provides before you stop looking for new ones. The default for the latter is 0%, but you're better off setting it to 10% or so, so that you don't get lots of meaningless aggregates recommended.

At this point, the Summary Advisor is ready to provide you with a list of summary recommendations, which you can then choose to implement or not. It's best (for reasons explained in a moment) to try and implement just a single new aggregate at a time, so uncheck any that aren't needed, then then move onto the final page, where the script is confirmed and the wizard closes.

So once the script has been generated by the Summary Advisor, what's next?

What's next is that you then need to run this script through the nqcmd utility that comes with OBIEE on the server, or the client installation, and which acts as a command-line interface to the BI Server. When you then run this script through nqcmd, passing across the BI Server datasource name, the username and password for the OBIEE administrator, and the name of the script, you should then see the aggregate tables created in the TimesTen database, like this:

whilst when you check back in the Oracle BI Repository, you'll see the aggregate tables mapped into the physical, and business model and mapping layers, as would happen with the Aggregate Persistence Wizard.

One thing you'll need to do if you want to take advantage of the column-based compression in TimesTen, is to make sure you tick the COMPRESS COLUMNS entry in the database features dialog for the TimesTen physical database, as by default it's disabled. Other than that, you should then start to see the BI Server refer to the TimesTen in-memory aggregates when you run suitable queries, giving you physical SQL queries in the nqsquery.log file like this excerpt:


Sending query to database named TT_AGGR_STORE,  connection pool named TT_AGGR_STORE,  logical request hash 9b1a12f5, physical request hash 2f1461f5: [[
select sum(T44087.Billed_Qua0000A5C2) as c1,
     sum(T44087.Revenue0000A5BC) as c2
from 
     ag_2123023723 T44087

So that's the theory, but how well does it work in practice? Check back tomorrow for the final instalment in the Exalytics series.